Stored Procedure tuning...

Folks,

I have a tricky situation where in I need to fetch all the value from a table if the input criteria is of a particular value. For e.g. If the customer is a admin he has to see all the customers. The tricky part here is
a) I want the code to be reusable meaning I dont want to write the same query in a IF block
b) The SP should be sans dynamic queries
c) optimum performance.

If you see the query below - Few tables are repeated in the JOIN conditions. I dont have any other option other than to do this.

I need few other tips to improve the performance of the below SP.

Scenario - If the customercode is of value 'TBC' I dont have to filter the apDetails table with the customercode else I need to filter it

alter procedure test (@customerCode varchar(3))
as

SELECT DISTINCT it.ItemValue FROM attribute a
JOIN product p ON p.productId=a.productId AND a.attributeTypeId=7
AND p.Status = 'A'
AND LTRIM(RTRIM(a.[Value])) = LTRIM(RTRIM('Sri'))
JOIN productType pt ON pt.productTypeId = p.productTypeId AND ManualAPU= 0
JOIN PRODUCTCONFIGURATIONusage pcu ON p.productId=pcu.productId
JOIN configurationItemUsage c ON pcu.configId=c.configId
JOIN custall ca ON ca.configId=c.configId
JOIN configurationItemUsage ci ON ci.configId=pcu.configId
JOIN item it ON it.ItemId=ci.ItemId
AND it.ItemTypeId = 1
AND it.ItemValue IN (SELECT ModelNumber FROM apdetails where customerCode = @CustomerCode)
JOIN configurationItemUsage ciu ON ciu.configId=pcu.configId
JOIN item it1 ON it1.ItemId=ciu.ItemId AND it1.ItemTypeId=2
AND EXISTS (SELECT 1 FROM apdetails where customerCode = @CustomerCode AND ModelSeries = it1.ItemValue)

I can make the query to work to cater my need by incl. an IF clause... But I dont want to do in this way. Can we add a case statement in the filter criteria??

If @customerCode = 'TBC'

SELECT DISTINCT it.ItemValue FROM attribute a
JOIN product p ON p.productId=a.productId AND a.attributeTypeId=7
AND p.Status = 'A'
AND LTRIM(RTRIM(a.[Value])) = LTRIM(RTRIM('Sri'))
JOIN productType pt ON pt.productTypeId = p.productTypeId AND ManualAPU= 0
JOIN PRODUCTCONFIGURATIONusage pcu ON p.productId=pcu.productId
JOIN configurationItemUsage c ON pcu.configId=c.configId
JOIN custall ca ON ca.configId=c.configId
JOIN configurationItemUsage ci ON ci.configId=pcu.configId
JOIN item it ON it.ItemId=ci.ItemId
AND it.ItemTypeId = 1
AND it.ItemValue IN (SELECT ModelNumber FROM apdetails )
JOIN configurationItemUsage ciu ON ciu.configId=pcu.configId
JOIN item it1 ON it1.ItemId=ciu.ItemId AND it1.ItemTypeId=2
AND EXISTS (SELECT 1 FROM apdetails where ModelSeries = it1.ItemValue)

ELSE

SELECT DISTINCT it.ItemValue FROM attribute a
JOIN product p ON p.productId=a.productId AND a.attributeTypeId=7
AND p.Status = 'A'
AND LTRIM(RTRIM(a.[Value])) = LTRIM(RTRIM('Sri'))
JOIN productType pt ON pt.productTypeId = p.productTypeId AND ManualAPU= 0
JOIN PRODUCTCONFIGURATIONusage pcu ON p.productId=pcu.productId
JOIN configurationItemUsage c ON pcu.configId=c.configId
JOIN custall ca ON ca.configId=c.configId
JOIN configurationItemUsage ci ON ci.configId=pcu.configId
JOIN item it ON it.ItemId=ci.ItemId
AND it.ItemTypeId = 1
AND it.ItemValue IN (SELECT ModelNumber FROM apdetails where customerCode = @CustomerCode)
JOIN configurationItemUsage ciu ON ciu.configId=pcu.configId
JOIN item it1 ON it1.ItemId=ciu.ItemId AND it1.ItemTypeId=2
AND EXISTS (SELECT 1 FROM apdetails where customerCode = @CustomerCode AND ModelSeries = it1.ItemValue)

Please help me out. The reason why I dont want to do like this is there are 5 such scenario's. The SP size is big now and this will make it even more huge...

Regards
Sri

The only thing you need to change, as far as I can see, is this:

AND (it.ItemValue IN (SELECT ModelNumber FROM apdetails where customerCode = @CustomerCode))

which you can expand to this:

AND ((@CustomerCode = 'tbc') OR (it.ItemValue IN (SELECT ModelNumber FROM apdetails where customerCode = @CustomerCode)))

or even this:

AND ((@CustomerCode = 'tbc') OR EXISTS (SELECT * FROM apdetails where customerCode = @CustomerCode AND ModelNumber = it.ItemValue))

This way it can all be done in a single query.

Thanks a ton Adrian for your prompt response... Again I have a question here. The scenario here is If the customercode is TBC I dont need to filter it based on the customerCode but I need to check if the ItemValue is present in apdetails table...

I tried it with a sample query

create procedure test(@customerCode varchar(3))
as
Select * from item Where itemTypeId = 1 AND
ItemValue IN (((Select ModelNumber from apdetails) AND (@customerCode = 'TBC'))
OR (Select ModelNumber from apdetails where customerCode = @customerCOde))

but got an error (below)

Msg 156, Level 15, State 1, Procedure test, Line 4
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Procedure test, Line 5
Incorrect syntax near ')'.

How to go about??

Thanks
Sri

Try the following...

create procedure test(@customerCode varchar(3))
as
Select * from item i
join apdetails a on i.ItemValue = a.ModelNumber
where itemTypeId = 1 AND
customerCode = @customerCOde
AND (@customerCode = 'TBC')


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

What about ...

AND ((@CustomerCode = 'tbc') OR EXISTS (SELECT * FROM apdetails WHERE customerCode = @CustomerCode AND ModelNumber = it.ItemValue))
AND (it.ItemValue IN (SELECT ModelNumber FROM apdetails))

Thanks a lot Adrian and Mohammad... Very much appreciated for your timely help.
I need a small suggestion for the query performance. I was suggested not to use IN statement. If I avoild IN I end up in a circular reference viz ModeluNumber = it.ItemValue...

Which one is better? Or is there anyother way to implement this?

Regards
Sro

If you have performance problems, then you might start to look at IN clauses. This type, with a subquery, should be fine - provided you have indexes, PKs or UNIQUE constraints covering ItemValue and ModelNumber.

The subquery I printed in bold is equivalent to an INNER JOIN between those tables (provided you also have DISTINCT or GROUP BY). SQL Server would probably use the exact same execution plan for both versions.

Cool Beans!!! Thanks for your help Adrian