November 16, 2011 at 2:08 am
Hello friends,
I am in the middle of tuning a query and need your advice on this.
The first look at execution plan suggests me that the compute scalar operator gets a lot of rows as the input and thus this leads to a lot of computation on the fly. The query takes 60-90 seconds and I believe this has some scope for tuning.
Please review my execution plan and suggest if you can notice few areas where improvements can be made.
Thanks
Chandan
November 16, 2011 at 2:56 am
The filter predicate
trans.Company_Link =
CASE
WHEN @Company_Link <> 0
THEN
@Company_Link
ELSE
trans.Company_Link
END
looks like a great candidate for dynamic sql.
See here for more details: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Moreover, you have lots of LEFT JOINs, that don't help overall performance.
Hope this helps
Gianluca
-- Gianluca Sartori
November 16, 2011 at 3:10 am
Gianluca Sartori (11/16/2011)
The filter predicate
trans.Company_Link =
CASE
WHEN @Company_Link <> 0
THEN
@Company_Link
ELSE
trans.Company_Link
END
looks like a great candidate for dynamic sql.
See here for more details: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Moreover, you have lots of LEFT JOINs, that don't help overall performance.
Hope this helps
Gianluca
Thanks for your reply.
Can you please suggest in more detail about what you mentioned about the filter predicate
November 16, 2011 at 5:17 am
chandan_jha18 (11/16/2011)
Thanks for your reply.Can you please suggest in more detail about what you mentioned about the filter predicate
Actually, the link that was provided is a really good place to start: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/. Gail's blog post explains why this type of pattern can cause performance issues, and provides examples of some ways to resolve it. I definitely would second the recommendation to read that first.
November 16, 2011 at 11:26 am
I agree with you. I will discuss this with my developer after going through Gails' blog link.
But as a DBA, what would someone spot in the execution plan apart from index scans that can make him\her worry?
Regards
Chandan
November 16, 2011 at 11:32 am
Either go fully dynamic as is suggested in Gail's article or divide and conquer.
Apply the base filters with max effect first.
Save that as temp table, then finish the joins.
The compute scalar are huge because you have a ton of cases, isnull and the like. Nothing you can do about that unless you don't really need them.
I can't tune this at distance. No magik option here, no easy button.
I'd need access to the DB.
November 16, 2011 at 11:40 am
yeah i'd consider either dynamic sql as suggested, or breaking this up into 4 procedures.
a master proc that ends up returning one of 4 proc results based on the parameters;
my pseudocode below, insteead of being SELECt...WHERE would be proc calls instead.
IF @DateOption = 'P' --THEN
BEGIN
IF @Company_Link <> 0 --THEN
BEGIN
SELECT [the fullquerybody] WHERE trans.PostedTransactionDate BETWEEN @StartDate AND @EndDate
AND ISNULL(veh.No_Charge, 0) = 0
AND ISNULL(prod.No_Charge, 0) = 0
AND trans.Company_Link = @Company_Link
END
ELSE
BEGIN
SELECT [the fullquerybody] WHERE trans.PostedTransactionDate BETWEEN @StartDate AND @EndDate
AND ISNULL(veh.No_Charge, 0) = 0
AND ISNULL(prod.No_Charge, 0) = 0
END
END
ELSE
BEGIN
BEGIN
IF @Company_Link <> 0 --THEN
BEGIN
SELECT [the fullquerybody] WHERE trans.TransactionDate BETWEEN @StartDate AND @EndDate
AND ISNULL(veh.No_Charge, 0) = 0
AND ISNULL(prod.No_Charge, 0) = 0
AND trans.Company_Link = @Company_Link
END
ELSE
BEGIN
SELECT [the fullquerybody] WHERE trans.TransactionDate BETWEEN @StartDate AND @EndDate
AND ISNULL(veh.No_Charge, 0) = 0
AND ISNULL(prod.No_Charge, 0) = 0
END
END
END
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply