Subquery within Case statement
-
hi everyone, im busy optimizing a stored proc that is the longest running and does the most I/O in from our application. Is there a better way to write this particular case statement?
Selectmc.MovementIdentifierContext,
mc.MovementIdentifier,
mc.MovementIdentifierSequence,
mc.Data,
mc.[XML],
case
when mr.RelationshipIdentifier IN (
select CardIdentifier
from "CardCollection" cc
join "EntityRelationship" er on cc.CardIdentifier = er.RelationshipIdentifier
where er.Relationship = 'Card'
and er.EntityIdentifier IN ( Select er2.EntityIdentifier
from EntityRelationship er2
where er2.Relationship = 'Affiliate'
and er2.RelationshipIdentifier = @ReferrerIdentifier ) )
then 'false' else 'true'
end as AccessDenied
from
"MovementCollection" mc
join "MovementRelationship" mr on mc.MovementIdentifier = mr.MovementIdentifier
-
The logic seems a little more complicated than it has to be. I think you can just use LEFT joins in place of your subqueries and simply test values in the CASE statement. To be certain, would you mind posting up some sample schema and data for one false case and one true case? That way you will be certain of getting a tested solution.
Thanks,
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
anyone? 🙁
-
Is there a better way to write this particular case statement?
Maybe, perhaps as Bob has suggested using "outer join" and possibly "group by" instead of the sub-query, but you'll need to post table definitions, some sample data with expected results.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi,
Try this,
creat the function like this
CREATE FUNCTION TR_FA (@mr_RelationshipIdentifier varchar(10),@ReferrerIdentifier varchar(10))
RETURNS varchar(10)
begin
if @mr_RelationshipIdentifier not in (
select cc.CardIdentifier
from CardCollection cc,
EntityRelationship er,
EntityRelationship er2
where cc.CardIdentifier = er.RelationshipIdentifier
and er.Relationship = 'Card'
and er.EntityIdentifier = er2.EntityIdentifier
and er2.Relationship = 'Affiliate'
and er2.RelationshipIdentifier = @ReferrerIdentifier)
begin
return 'FALES'
end
else 'TRUE'
end
and call this function in the select statement
mc.Data,
mc.[XML],
select * from DB..TR_FA (mr.RelationshipIdentifier,@ReferrerIdentifier )
from
"MovementCollection" mc
ARUN SAS
-
Perhaps you can try this one?
WITH AccessDenied( CardIdentifier ) AS
(
SELECT DISTINCT CardIdentifier
FROM
CardCollection AS cc
INNER JOIN
EntityRelationship AS er ON
cc.CardIdentifier = er.RelationshipIdentifier
INNER JOIN
EntityRelationship AS er2 ON
er2.Relationship = 'Affiliate'
AND er2.RelationshipIdentifier = @ReferrerIdentifier
AND er2.EntityIdentifier = er.EntityIdentifier
WHERE er.Relationship = 'Card'
)
SELECT
mc.MovementIdentifierContext
, mc.MovementIdentifier
, mc.MovementIdentifierSequence
, mc.Data
, mc.[XML]
, CASE WHEN ad.CardIdentifier IS NULL THEN 'true' ELSE 'false' END AS AccessDenied
FROM
MovementCollection mc
INNER JOIN
MovementRelationship mr ON
mc.MovementIdentifier = mr.MovementIdentifier
LEFT OUTER JOIN
AccessDenied AS ad ON
ad.CardIdentifier = RelationshipIdentifier
-
The fundamental problem with the original query is the JOIN in the subquery. (These always seem to end up as nested loops which is rarely a good idea.)
You should avoid joins in subqueries by either using outer joins or using nested EXISTS or IN subqueries. Here are two more examples:
SELECT
mc.MovementIdentifierContext,
mc.MovementIdentifier,
mc.MovementIdentifierSequence,
mc.Data,
mc.[XML],
CASE
WHEN EXISTS
(
SELECT *
FROM MovementRelationship mr
WHERE mr.MovementIdentifier = mc.MovementIdentifier
AND EXISTS
(
SELECT *
FROM CardCollection cc
WHERE mr.RelationshipIdentifier = cc.CardIdentifier
AND EXISTS
(
SELECT *
FROM EntityRelationship er
WHERE er.RelationshipIdentifier = cc.CardIdentifier
AND er.Relationship = 'Card'
AND EXISTS
(
SELECT *
FROM EntityRelationship er2
WHERE er2.EntityIdentifier = er.EntityIdentifier
AND er2.Relationship = 'Affiliate'
AND er2.RelationshipIdentifier = @ReferrerIdentifier
)
)
)
)
THEN 'false'
ELSE 'true'
END AS AccessDenied
FROM
MovementCollection mc
SELECT
mc.MovementIdentifierContext,
mc.MovementIdentifier,
mc.MovementIdentifierSequence,
mc.Data,
mc.[XML],
CASE
WHEN mr.MovementIdentifier IS NULL
THEN 'true'
ELSE 'false'
END AS AccessDenied
FROM MovementRelationship mr
JOIN CardCollection cc
ON mr.RelationshipIdentifier = cc.CardIdentifier
JOIN EntityRelationship er
ON er.RelationshipIdentifier = cc.CardIdentifier
AND er.Relationship = 'Card'
JOIN EntityRelationship er2
ON er2.EntityIdentifier = er.EntityIdentifier
AND er2.Relationship = 'Affiliate'
AND er2.RelationshipIdentifier = @ReferrerIdentifier
RIGHT JOIN MovementCollection mc
ON mc.MovementIdentifier = mr.MovementIdentifier
-
firstly, thanks for all the replies...
ive tried various options, but so far the use of a CTE gives me the best result
though i am still busy testing
Are there any disadvantages to using a CTE in my stored proc??
WITH AccessDenied( CardIdentifier ) AS(
SELECT DISTINCT
CardIdentifier
FROM
CardCollection AS cc
INNER JOIN EntityRelationship AS er ON cc.CardIdentifier = er.RelationshipIdentifier
INNER JOIN EntityRelationship AS er2 ON er2.Relationship = 'Affiliate'
AND
er2.RelationshipIdentifier = @ReferrerIdentifier
AND
er2.EntityIdentifier = er.EntityIdentifier
WHERE
er.Relationship = 'Card'
)
SELECT
mc.MovementIdentifierContext,
mc.MovementIdentifier,
mc.MovementIdentifierSequence,
mc.Data,
mc.[XML],
CASE WHEN ad.CardIdentifier IS NULL THEN 'true' ELSE 'false' END
FROM
MovementCollection mc
INNER JOIN MovementRelationship mr ON mc.MovementIdentifier = mr.MovementIdentifier
LEFT OUTER JOIN AccessDenied AS ad ON ad.CardIdentifier = RelationshipIdentifier
-
I dont't know the datamodel, but perhaps you can remove the DISTINCT in the CTE for further optimization?
-
anyone?
I was waiting for you to post up the samples I requested, partner. 😉
By failing to do that, you have people guessing at solutions... which may be wasting both their time and yours. Please take time to read the article here[/url] before submitting your next question. If you follow that format, you will get much quicker replys, the solutions are more likely to be right the first time, and you will make friends among the volunteers who are helping you out. Thanks. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
sorry bout that, will follow posting hints better next time
-
No problem. Did any of the answers posted work for you?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
all of it worked with some minor tweaking but the CTE worked the best, its weird though because i was testing the use of a table variable but wasnt seeing that much of a difference compared to a CTE.
Got the proc down from 41 sec (yes i know its ridiculous) down to 31ms....
:w00t:
-
What's ridiculous about cutting 25% of the work out? If you can do that with everything that runs on your SQL server, you are doing well.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
The way I read it: Execution time is 31ms instead of 41000ms. That's a big difference! 😀
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply