SQL Server Performance

Stored Procedure tuning...

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Sri316, May 23, 2007.

  1. Sri316 New Member

    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
  2. Adriaan New Member

    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.
  3. Sri316 New Member

    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
  4. MohammedU New Member

    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.
  5. Adriaan New Member

    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))
  6. Sri316 New Member

    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
  7. Adriaan New Member

    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.
  8. Sri316 New Member

    Cool Beans!!! Thanks for your help Adrian

Share This Page