January 5, 2011 at 11:34 am
Hi All,
I would like to get some help regarding optimizing the below query. Is there anything very obvious in the below query which can be improved? i would not be able to post DDL & DML;
Any help would be greatly appreciated.
SELECT w1.ASSIGNMENT_ID, w1.WORKSHEET_ID, w1.EFFECTIVE_DATE, w1.APPROVED_BY, w3.CREATED_BY
FROM WORKSHEET_PAYROLL_VW w1
INNER JOIN WORKSHEET w3 ON w3.WORKSHEET_ID = w1.WORKSHEET_ID
WHERE w1.EFFECTIVE_DATE
BETWEEN
CASE WHEN @p_MOST_RECENT_ONLY = 'Y' THEN
(SELECT MAX(w2.EFFECTIVE_DATE)
FROM WORKSHEET_PAYROLL_VW w2
WHERE w1.ASSIGNMENT_ID = w2.ASSIGNMENT_ID AND
(ISNULL(@p_WKS_EFFECTIVE_DATE,w2.EFFECTIVE_DATE<=w2.EFFECTIVE_DATE))
ELSE ISNULL(@p_WKS_EFFECTIVE_DATE,w1.EFFECTIVE_DATE)
END
AND
CASE WHEN @p_MOST_RECENT_ONLY = 'Y' THEN
(SELECT MAX(w2.EFFECTIVE_DATE)
FROM WORKSHEET_PAYROLL_VW w2
WHERE w1.ASSIGNMENT_ID = w2.ASSIGNMENT_ID AND
(ISNULL(@p_WKS_TO_EFFECTIVE_DATE,w2.EFFECTIVE_DATE)>=w2.EFFECTIVE_DATE))
ELSE ISNULL(@p_WKS_TO_EFFECTIVE_DATE,w1.EFFECTIVE_DATE)
END
January 5, 2011 at 11:40 am
Well, the highly conditional Where clause will probably cause an overly broad execution plan to be built.
Can you separate each of those CASE clauses into separate procs and have the parent proc pick which one to call based on the parameter value? That often helps in those cases.
Based on the names, I'm assuming some of the objects in the From clause are views intead of tables. That often causes query slowdown, because views are usually overbuilt for their use. Would be worth checking into. Do they include tables/columns that aren't needed for this query? Do they nest to other views?
Beyond that, without DDL or at least an execution plan, no, it just looks like a query. Not having those things makes tuning into guesswork.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2011 at 11:53 am
The view has many columns which are not used in the query. I tried creating index on the view but received a message saying:
Cannot create index because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.
January 5, 2011 at 12:39 pm
Views are tricky to index. Lots of rules on that.
The better bet is to write out the query you really need in this, instead of using views.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2011 at 3:49 am
consider this,
Original query
update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid=@WMPID
update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid in
(select workmgmtplanid from workmgmtlinkedplans with(nolock) where linkedworkmgmtplanid=@WMPID)
update workmgmtplan with(rowlock) set IsWebPlannerTracked=1 where planid in
(select linkedworkmgmtplanid from workmgmtlinkedplans with(nolock) where workmgmtplanid=@WMPID)
Modified query
update workmgmtplan set IsWebPlannerTracked=1 where planid in
(
Select @WMPID
Union
select workmgmtplanid from workmgmtlinkedplans with(nolock) where linkedworkmgmtplanid= @WMPID
Union
select linkedworkmgmtplanid from workmgmtlinkedplans with(nolock) where workmgmtplanid= @WMPID
)
Is it possible to replace UNION with CASE expression to obtain the same result?
January 6, 2011 at 7:46 am
Replace "Union" with "Union All", and you should have a decent query there. Not sure what you mean about replacing Union with Case statements.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2011 at 6:01 pm
Thank You all for your opinion and insight. It turned out that there was a function call in where clause which was slowing down the query. I removed the function call and added a pre-query to fix this issue.
January 7, 2011 at 7:32 am
Can you say which function call it was in the where clause and how you fixed it? Not sure I see it in your OP statement - or was it in the view(s)?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 7, 2011 at 7:45 am
Yes it was in the View.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply