April 19, 2012 at 12:01 am
cAN SOMEONE help me optimize this query - I am really struggling. I have attached explain plan and query. If there is anything else needed please let me know as I really need help
thanks
April 19, 2012 at 4:11 am
Your statistics look to be off. You've got implicit data conversions on all your predicates, possibly causing all the scans, and it looks like you may have a multi-statement table valued function at work in there.
That's the low hanging fruit. I'd address all that first.
"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
April 19, 2012 at 4:40 am
thanks Grant - whats the alternative - particularly to the multi table value function?
I kinda though that my stats were off because of the estimate rows and actual rows for the index scans. The implicit conversions seemed to be a problem there because when I changed the query for one predicate from
where <varchar column> = 34
to
where <varchar column> = '34'
the actual and estimated rows where the same (i.e it treated 34 like a varchar instead of a number)
I was hoping that some indexes might be the answer as my ability to change the query in the code is limited due to having limited development support
Any more advice mate?
April 20, 2012 at 3:58 am
Not knowing the internals of the multi-statement UDF, it's hard to know what to do about it, but those things are notorious performance pigs if you're dealing with more than a few rows, or, as in this case, you're dealing with more than a few rows and you're treating them like a real table, running JOIN & WHERE clauses on them. This is because the multi-statement UDF doesn't have statistics. In general, in a situation like this, pull the code out of the UDF & make it a derived table for joining against will generally work better (not always). Second choice, load that data into a temp table & then JOIN against that. At least then you have statistics.
Use the right data types. As you see, using varchar for varchar works better.
No other suggestions at the moment because I haven't dug past the basics on the query.
"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
May 2, 2012 at 6:43 am
OP can you please post the related index definition too?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 2, 2012 at 6:53 am
1 .statistics out of date for every table.
2. non-sarge-able column use which causing the index scan here.
3. function use also affecting here.
4. check if you have properly used the indexes or not (in case of "order by" too )
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply