optimising makes it 10 times worse?

Question

Post reply

optimising makes it 10 times worse?

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

Gianluca, looks like you have it covered. You may need to experiment to get the best fill factor for your busy tables.

Ninja, perhaps I should have come up with a better title for my topic 🙂

---------------------------------------------------------------------

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Thanks George.

I think I'm on the right track, but sometimes, since I set up this plan, the optimizer decides to pick a very odd plan.

Odd plan + 75 mln rows = pain.

I'll experiment with fillfactors. Thanks for the hint!

-- Gianluca Sartori

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

amazingly good maintenance stuff here: ola.hallengren.com. You can have it update statistics on indexes that are not defragged at all or are only reorg'd.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

TheSQLGuru (10/15/2010)
amazingly good maintenance stuff here: ola.hallengren.com. You can have it update statistics on indexes that are not defragged at all or are only reorg'd.

Thanks for the tip, Kevin, this is exactly what I'm using and I find it very useful.

-- Gianluca Sartori

David.Poole SSC Guru Points: 76034 More actions · Answer 5

Have you considered writing a view and exposing that to Business Objects instead of letting it use the base tables?

How many tables are used in the joins for the query?

LinkedIn Profile

george sibbald SSC Guru Points: 104210 More actions · Answer 6

David, thanks but don't go there. the part of the query that performs badly only accesses one table but it is unioned to another query which accesses 19 tables. (then there is another union)

How would the view help performance unless it was indexed? It still has to access the same base tables.

---------------------------------------------------------------------

David.Poole SSC Guru Points: 76034 More actions · Answer 7

If you are getting locking/blocking activity on a table and are not bothered by dirty reads then using the READUNCOMMITTED hint in the join may alleviate this.

If you are doing a union are either sides of the union naturally distinct? If so think of UNION ALL instead.

19 joins and multiple unions probably causes the query engine to give up optimising the query.

Can any of the data be pre-calculated?

LinkedIn Profile

george sibbald SSC Guru Points: 104210 More actions · Answer 8

David.Poole (10/18/2010)
If you are getting locking/blocking activity on a table and are not bothered by dirty reads then using the READUNCOMMITTED hint in the join may alleviate this.
If you are doing a union are either sides of the union naturally distinct? If so think of UNION ALL instead.

there was no blocking going on. I tried union all and the query returned exactly the same results so yes naturally distinct, only knocked two seconds off the run time though. 🙁

19 joins and multiple unions probably causes the query engine to give up optimising the query.
Can any of the data be pre-calculated?

If the optimiser didn't give up it certainly may as well have done. good point.

Pre-calcualtion don't know. I was wondering if they could use divide and conquer with the query, store partial results in a temp table and simplify the joins? How much influence can you have over a query produced by business objects?

I'll pass these ideas on to the devs david.

---------------------------------------------------------------------