September 12, 2012 at 7:55 am
Hi folks,
I have an issue that's been driving me crazy for the better part of a day and a half now.
I was asked to take a look at a Query written by some developers that didn't perform as well as they'd hoped. I'm not very experienced at analyzing SQL Execution Plans but I do know enough to muddle through. After running some basics stats using "SET STATISTICS TIME ON" & "SET STATISTICS IO ON" I noticed some high logical reads on one particular table. So then I decided to build a simple index to help this query out especially since it was going to be used heavily in production in the near future. Then that's when the real fustration hit if I forced the table to use my index the query ran significantly faster than before. Reads on the one large table went down from over 150,000 to 66. However I did not want to start adding code with dependancies on Indexes but I coudn't figure why on earth it wanted to use a least efficient index.
Eventually I tracked it down to a a join statement on a small table. If I changed it to a LEFT join than the right index was used and the query ran fast and everybody was happy. Now I'm at the point where I don't mind telling the developers to change the JOIN especially since the joining columns don't have any NULL values in the database. In fact they don't even accept NULL values so I know I'm ok there. The question I'm left though really is why?
I added some quasi code. Essentially the second INNER join on table C is what is the difference in this whole mess. Table C is a very small table (4 columns and 30 some odd rows). I've read somewhere that sometimes inner joins to small tables can add complexity. However I could not find any good information beyond that. If anybody can shed a little bit of light on this I would appreciate it. If only for my sanity's sake... 😉
SELECT
.....
.....
FROM
TableA a
INNER JOIN TableB b ON b.ID = a.ID
INNER JOIN TableC c ON c.somecolumn = a.somecolumn AND c.anothercolumn = a.anothercolumn
LEFT JOIN TableD d ON d.anotherID = a.anotherID
LEFT JOIN TableE e ON e.yetanotherID = d.yetanotherID
WHERE
....
September 12, 2012 at 8:09 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2012 at 8:42 am
Attached requested files...
September 12, 2012 at 10:25 am
yb751 (9/12/2012)
Attached requested files...
First of all you only posted 1 table (Content). What about ContentVersion, ContentFormat, License, Website?
Your where clause is absolutely insane. Holy Cast insanity batman!!!!
(N'STRY' IS NULL) OR (( CAST(LEN(N'STRY') AS int)) = 0)
Seriously? Unless this is generated as dynamic sql this is pointless. You are checking conditions on hardcoded values.
CAST( GetUtcDate() AS datetime))
GetUTCDate() returns a datetime. There is no reason to cast it as a datetime.
(convert(datetime, '2012-09-10 00:00:00.000', 121) IS NULL)
Another check for a hardcoded value not being null.
CAST( [Extent2].[VersionGmt] AS datetime) >= convert(datetime, '2012-09-10 00:00:00.000', 121)
No need to use convert on the right side here assuming VersionGmt is a datetime. Given the name I assume this must be the timestamp in GMT. So here again you are casting a datetime to a datetime.
This has the look of a catch all type of query. Take a look at Gail's article (http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]). It demonstrates an excellent approach to this type of query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2012 at 12:15 pm
I have to control over that query...I'm not any happier about it either.
...and yes it is dynamically created.
September 12, 2012 at 12:23 pm
First of all you only posted 1 table (Content). What about ContentVersion, ContentFormat, License, Website?
Because that is the only table that was causing performance issues when I broke down the SQL Query on table at a time. Also if you read my original post you will notice I'm not looking at optimizing the query itself which I have no control over. Even a theoritical response would suffice to my question. I just can't uderstand why a JOIN to such a small table would make so much of a difference.
Stats using old index /w INNER JOIN:
Table 'Content'. Scan count 1, logical reads 30649
SQL Server Execution Times: CPU time = 1328 ms, elapsed time = 1405 ms.
Stats using new index /w LEFT JOIN:
Table 'Content'. Scan count 1, logical reads 59
SQL Server Execution Times: CPU time = 16 ms, elapsed time = 2 ms.
September 14, 2012 at 9:00 am
I can't see the attachments. Based on what's described, I have a guess. That sounds like a messy complex query. I'll bet, if you look at the Reason for Early Termination, it's a timeout. If so, the reason you're seeing a poor choice from the optimizer is because it hasn't finished it's work. It's just presenting you with the best plan it can at the moment.
Regardless of if the code is generated or not, I'd go and point out all the issues to the development team in question or things are likely to go downhill as more and more queries of this type are running on your system. They'll be doing nothing but scanning all the tables for every access and blaming SQL Server for not working right. I've been there WAY too often. Point out to them the issues now. They may not fix them, but later, your behind is covered.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply