September 13, 2016 at 8:59 am
We have one query which used by users to pull the report everyday in the morning. Which, i generally run through SQL and it takes only 1 to 2 minutes but it takes forever for users to pull the report from application.
Statistics are updated and indexes seems looks good too. Is there anything else i can do run the query faster?
September 13, 2016 at 9:23 am
EasyBoy (9/13/2016)
We have one query which used by users to pull the report everyday in the morning. Which, i generally run through SQL and it takes only 1 to 2 minutes but it takes forever for users to pull the report from application.Statistics are updated and indexes seems looks good too. Is there anything else i can do run the query faster?
This is probably because the users are getting a much poorer execution plan when accessing the data via the application.
Can you capture the actual execution plan for when you run the query via SQL and over the application and post them here?
-- Itzik Ben-Gan 2001
September 13, 2016 at 9:35 am
I am extremely sorry about the SQL plan. By mistake, I have attached other execution plan. Please find corrected sql plan.
September 13, 2016 at 12:01 pm
That's only one query plan. You'd need to post the plan from the application and the plan from the code that you're running both in order to see if there are differences and what differences there are.
A couple of questions for things you might investigate. First, just verifying, both these are against the exact same database on the exact same server, right? Assuming that's correct, you need to get the execution plan from the app and check the ANSI settings. Assuming everything is going to the same database and server, the differences have to be in the settings. I'm saying that because you have a hard coded query, no parameters and no local variables. Is that also an accurate representation of the real situation? Assuming so, we can eliminate parameter or variable sniffing as being the issue.
However, we really need that second execution plan.
Just looking at this plan, the filtering from 3.5 million rows to only return 25 (or in the example given, zero), is pretty insane. The estimated and actuals are fairly in line most of the way through the plan, so it's unlikely to be out of date or missing statistics. The issues are going to be in the query itself. This is guaranteed to get you scans:
DM.destinationname LIKE '%GETIT1.02'
You need to eliminate any LIKE statements with leading wild cards. It will prevent good index filtering. If you have to, create a calculated column or a second storage column to find the rows that match that value. All the <> filters are also likely to cause you overhead. Better declarative AND filters, so instead of <> 9, use >0 and <8 or >9 and <max value (assuming there is such a thing). Otherwise, all those <> are really causing headaches for the optimizer. This is also going to kill performance and index use:
DM.destinationname LIKE ( '%' + R.[version] )
The NOLOCK hints and the HASH JOIN hints are unlikely to be doing you any favors. The hash match is 76% of the estimated cost. I'm really unclear why you're getting a SEEK for 3.5 million rows on a table that has 3.5 million rows. It should just scan. I suspect it's related to the query hints.
There are a ton of tuning opportunities here.
"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
September 13, 2016 at 12:13 pm
Just another quick note to make sure we don't spend a lot of time barking up the wrong tree: Unless I've gone completely crazy (always a possibility), the query for which you uploaded an execution plan is a rather different query from the one you posted in the text file.
Cheers!
September 13, 2016 at 8:13 pm
Jacob Wilkins (9/13/2016)
Just another quick note to make sure we don't spend a lot of time barking up the wrong tree: Unless I've gone completely crazy (always a possibility), the query for which you uploaded an execution plan is a rather different query from the one you posted in the text file.Cheers!
Didn't even see that. Nice catch.
"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
September 14, 2016 at 2:36 am
in WHERE clause change all checks like this:
[worksheetname] IS NOT NULL
AND Datalength([worksheetname]) > 0
to this:
[worksheetname]>''
_____________
Code for TallyGenerator
September 14, 2016 at 2:43 am
You have 3 OR's in line with all the AND's.
Looks like the logic of the WHERE clause is broken.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply