Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Does the SQL server optimiser stink?

Author  Topic 

Teroman
Posting Yak Master

115 Posts
Posted - 2002-03-18 : 13:29:41
Well, maybe the titles a bit strong, but ive been writing some pretty complex queries recently, and have found that some of the plans it choses really are poor.

I type my queries in a pretty logical way usually, join tables in a good order, try to keep rowcount down as much as possible.

I've found that if something i think should be fast takes a long time i just copy and paste the query, and type "option (force order)" after one of them to compare plans. Loads of times my way is so much faster it beggars belief.

The one that inspired me to post this was a query that was taking about 1 to 1.5 mins. Query plan showed its getting 1.3 million rows out of a table, and then going join crazy. Simply forcing it to do things my way takes the query to about a second, and only gets an few dozen rows out of the table in question. Madness!

I know optimising (automatically) is hard, and we have this command to point SQL server in the right direction, but if people dont know about it (I didnt for years) then optimising is a nightmare.

This has kind of got me thinking, are there any other commands people use to bend SQL server to their will? Index hints i know about, but are there any more esoteric ones out there. And what are the dangers, I know that if the ratios of data (better word?) change dramatically things could seize up.

Thanks for listening, went on a bit of a rant, but this has been really winding me up.

col

PS, just got the official timings

SQL Server version : 105638 ms
Colins version : 396 ms

slight difference ;)

izaltsman
A custom title

1139 Posts
Posted - 2002-03-18 : 13:56:01
Are the statistics on your tables up to date?
Other than keeping the stats up to date, there isn't much you can do. In addition to FORCE ORDER and INDEX hints, I occasionally use OPTION LOOP JOIN to keep SQL Server from performing merge joins in random places.


Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2002-03-18 : 15:07:01
quote:
I know optimising (automatically) is hard, and we have this command to point SQL server in the right direction, but if people dont know about it (I didnt for years) then optimising is a nightmare.


I agree with you on that part, but...my inner curmudgeon likes to think that newbies (not saying you are!) should endure a little bit of hardship, even at the risk of making them think SQL Server "stinks". If everything is really easy, we'll simply end up with more people with half-assed knowledge who believe they're god, when they're not (again, you definitely are not one of these people!) That's another reason why I like to recommend The Guru's Guide to Transact-SQL; it's the best reference I know to show how LITTLE you know about SQL Server (plus it's got a great section on query optimization!)

Once they've knocked the software around a little, then they can start working on hand-tuning queries. It gives people something to aspire to. IMHO it's better to approach the software as having certain flaws or quirks that you can work around (which it certainly does), instead of assuming it's perfect and letting IT make all the decisions for you. I have to admit that if I had to spend any time optimizing all my queries when I first started, I would've given up on SQL Server and stayed with Access.

To be fair to Microsoft, when you think about all of the possible queries that can be written, regardless of table structures, indexes or lack thereof, I think it's pretty amazing that it can OPTIMIZE any of them. Don't forget it has to be as generic as possible; it's a little harsh to think that it can get EVERY query right. And if it nudges you towards doing things like consistently updating statistics and other good database practices, then that's a good thing.

I've had a number of Oracle queries that performed really bad, and all that was needed was to re-write the WHERE clause to put certain sections first! While the change made a huge improvement, THAT IS POOR OPTIMIZATION! It just happens to be a quirk of Oracle, yet you will never get an Oracle junkie to admit it is a flaw.

My 2¢, FWIW

BTW, when you ran your hand-crafted query, did you flush the data cache (DBCC DROPCLEANBUFFERS) beforehand? I wouldn't be surprised that the 396 ms was because the data was in cache already. Try clearing the data cache and see if it comes back as quickly. If it still does, then you seem to have found a query that completely stumped the optimizer. They're out there!

Hey, can you post the query by any chance?

Edited by - robvolk on 03/18/2002 15:10:37
Go to Top of Page

Teroman
Posting Yak Master

115 Posts
Posted - 2002-03-19 : 04:44:23
Thanks all,

did the buffer clearing, for some reason this morning the times are about 67 seconds and 3 seconds for each

Have taken several deep breaths since yesturday, all toys returned to pram ;)

the query follows, hope you can follow it

Its aim is to find the largest date in the derrived table fitting the having clause, but use a default date (the isnull in the top select) if there are now rows. Some of the where clause is duplicated in the derrived table to try to keep the joins to the large table down to a minimum.

And the problem is in the derrived table.
The large table is TankGaugeReading, indexed on Site, Tank, Day(in that order).
Left to its own devices SQL server joins everything except the RuleRange table in, then does the date join to restrict the rows. So it uses the right index, just not enough columns in it. The date really does make quite a bit of difference to the number of rows returned.

I'm no guru yet, but I've been doing this for a few years, and recently I seem to have found the optimiser doing a lot of "interesting" things. Again I agree that writing an optimiser is something I cant even consider doing, suppose I should just be happy that MS trust me enough to let me hint at will.

ok, here it is

select SM.nSiteID, SM.nSiteGroupID, T.nTankGroupID, isnull(dateadd(dd, -1, max(D.dtDay)) , RR.dtRunFrom)
from NBM_C..SiteMaster SM
join #RuleRange RR
on SM.nSiteGroupID = RR.nSiteGroupID
join NBM_C..TankGroup T
on SM.nSiteID = T.nSiteID
and T.dtEnd is NULL

left outer join (

select TGR.nSiteID,
TGR.nTankGroupID,
TGR.dtDay
from NBM_C..SiteMaster SM
join #RuleRange RR
on SM.nSiteGroupID = RR.nSiteGroupID

join NBM_C..TankGroup TG
on TG.nSiteID = SM.nSiteID
and TG.dtEnd is NULL

join NBM_W..TankGaugeReading TGR
on TG.nSiteID = TGR.nSiteID
and TG.nTankID = TGR.nTankID
and TGR.dtDay between RR.dtRunFrom and RR.dtRunTo

join NBM_A..EventTolerance ET
on ET.nSiteGroupID = SM.nSiteGroupID
and ET.nEventTypeID = @nLowStockEvent

where SM.nIsCurrent = 1
and SM.nSiteGroupID = isnull(@nSiteGroupID, SM.nSiteGroupID)
and SM.nSiteID = isnull(@nSiteID, SM.nSiteID)
and SM.nSiteSysVer is not NULL

group by TGR.nSiteID,
TGR.nGradeID,
TGR.nTankGroupID,
TGR.dtDay,
ET.nTolerance,
ET.nTolerance2

having sum(TGR.nMeasuredVolume) < (ET.nTolerance * count(*))
and sum(TGR.nMeasuredVolume) < (sum(TG.nCapacity) * ET.nTolerance2 / 100)

) D

on SM.nSiteID = D.nSiteID
and T.nTankGroupID = D.nTankGroupID

where SM.nIsCurrent = 1
and SM.nSiteGroupID = isnull(@nSiteGroupID, SM.nSiteGroupID)
and SM.nSiteID = isnull(@nSiteID, SM.nSiteID)
and SM.nSiteSysVer is not NULL

group by SM.nSiteID, SM.nSiteGroupID, T.nTankGroupID, RR.dtRunFrom


here are the I/O stats if you're interested

SQL server:

Table '#RuleRange'. Scan count 1328936, logical reads 2740981, physical reads 0, read-ahead reads 0.
Table 'TankGaugeReading'. Scan count 683, logical reads 24856, physical reads 93, read-ahead reads 20269.
Table 'TankGroup'. Scan count 169, logical reads 2120, physical reads 0, read-ahead reads 0.
Table 'SiteMaster'. Scan count 775, logical reads 1631, physical reads 0, read-ahead reads 0.
Table 'EventTolerance'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.

Mine:

Table 'EventTolerance'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
Table 'TankGaugeReading'. Scan count 718, logical reads 3559, physical reads 96, read-ahead reads 0.
Table 'TankGroup'. Scan count 396, logical reads 4561, physical reads 2, read-ahead reads 0.
Table '#RuleRange'. Scan count 406, logical reads 886, physical reads 1, read-ahead reads 0.
Table 'SiteMaster'. Scan count 2, logical reads 20, physical reads 1, read-ahead reads 9.

Long post, sorry!

col
Go to Top of Page

Teroman
Posting Yak Master

115 Posts
Posted - 2002-03-19 : 07:59:41
I suppose this is the PS

Fristly, yes, my stats are up to date

Also a query about derrived tables

I though that these were done first, then the outer queries were joined in, but this seems not to be the case.

In other places ive sped up a query dramatically by using a temp table to do the derrived bit, and a seperate query to do the rest.

It seems the optimiser trys to take it all on at once, obviously this is going to make life difficult for it.

Certainly I dont expect it to get everything perfect, and in most situations it does a very good job. Its just when you structure your query on purpose and it goes off on its own little ride that my hair starts to come away from my scalp.

I guess I should be glad manual intervention is needed, provides me with something to do, but on the other hand there are a lot of things I could usefully do rather than optimise things if it was already done.

So I both like it, and I dont, excellent!

Oh well, at least there is beer to look forward to at the end of the day.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2002-03-20 : 13:59:39
quote:
I've had a number of Oracle queries that performed really bad, and all that was needed was to re-write the WHERE clause to put certain sections first! While the change made a huge improvement, THAT IS POOR OPTIMIZATION! It just happens to be a quirk of Oracle, yet you will never get an Oracle junkie to admit it is a flaw.


FYI/FWIW it seems that I'm not alone in this opinion:

http://www.dbmsmag.com/9607d06.html
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site