Server is slwo in Production Server

  • vijaymadh

    Grasshopper

    Points: 18

    Hi,

    in my SP where caluse is written like below. This one is getting extreemly slow(1 min) in Production.

    Can anyone help me the reason for this. how I ca fix it so that it is fast forever?

    --------------

    Where

    AND ( @fABCField IS NULL

    OR @fABCField = ''

    OR a.fABCField LIKE '%' + @fABCField

    + '%'

    )

    AND ( @fStatus IS NULL

    OR @fStatus <= 0

    OR a.fStatus = @fStatus

    )

    AND ( @fABCStatus IS NULL

    OR @fABCStatus <= 0

    OR a.fFinalABCStatus = @fABCStatus

    )

    AND ( @fCountryID IS NULL

    OR @fCountryID <= 0

    OR b.fCountryID = @fCountryID

    )

    AND ( @fCompanyID IS NULL

    OR @fCompanyID <= 0

    OR b.fCompanyID = @fCompanyID

    )

    AND ( @fDepartmentID IS NULL

    OR @fDepartmentID <= 0

    OR b.fDepartmentID = @fDepartmentID

    )

    AND ( @fTelePhoneNo IS NULL

    OR @fTelePhoneNo = ''

    OR h.fTelephone LIKE @fTelePhoneNo + '%'

    )

    AND ( @ABCID IS NULL

    OR @ABCID <= 0

    OR a.fABCId = @ABCID

    )

    AND ( @fABCDept IS NULL

    OR @fABCDept <= 0

    OR c.fDepartmentId = @fABCDept

    )

    AND ( @fBusinessName IS NULL

    OR @fBusinessName = ''

    OR h.fCompanyName LIKE '%' + @fBusinessName

    + '%'

    )

    AND ( @fDeptStatus IS NULL

    OR @fDeptStatus <= -3

    OR d.fStatus = @fDeptStatus

    )

    AND ( @fABCType IS NULL

    OR @fABCType <= 0

    OR k.fABClicationType = @fABCType

    )

    AND ( @enteredByUserId IS NULL

    OR @enteredByUserId <= 0

    OR a.fCreatedID = @enteredByUserId

    )

    AND ( @fAgentNo IS NULL

    OR @fAgentNo = ''

    OR m.fAgentNo LIKE @fAgentNo + '%'

    )

    AND ( ( @deptStartDate IS NULL

    OR @deptStartDate = ''

    )

    AND ( @deptEndDate IS NULL

    OR @deptEndDate = ''

    )

    OR ( ( @deptStartDate IS NOT NULL

    AND @deptStartDate != ''

    AND ( @deptEndDate IS NULL

    OR @deptEndDate = ''

    )

    )

    AND ( CONVERT(DATETIME, d.fCreated) >= CONVERT(DATETIME, @deptStartDate) )

    )

    OR ( ( @deptEndDate IS NOT NULL

    AND @deptEndDate != ''

    AND ( @deptStartDate IS NULL

    OR @deptStartDate = ''

    )

    )

    AND ( CONVERT(DATETIME, d.fCreated) <= CONVERT(DATETIME, @deptEndDate) )

    )

    OR ( ( @deptStartDate IS NOT NULL

    AND @deptStartDate != ''

    AND @deptEndDate IS NOT NULL

    AND @deptEndDate != ''

    )

    AND CONVERT(DATETIME, d.fCreated) BETWEEN CONVERT(DATETIME, @deptStartDate)

    AND

    CONVERT(DATETIME, @deptEndDate)

    )

    )

    AND ( ( @startDate IS NULL

    OR @startDate = ''

    )

    AND ( @endDate IS NULL

    OR @endDate = ''

    )

    OR ( ( @startDate IS NOT NULL

    AND @startDate != ''

    AND ( @endDate IS NULL

    OR @endDate = ''

    )

    )

    AND ( CONVERT(DATETIME, a.fCreated) >= CONVERT(DATETIME, @startDate) )

    )

    OR ( ( @endDate IS NOT NULL

    AND @endDate != ''

    AND ( @startDate IS NULL

    OR @startDate = ''

    )

    )

    AND ( CONVERT(DATETIME, a.fCreated) <= CONVERT(DATETIME, @endDate) )

    )

    OR ( ( @startDate IS NOT NULL

    AND @startDate != ''

    AND @endDate IS NOT NULL

    AND @endDate != ''

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    So you post only a part of the query, no details about the server itself and what is running on that server, no table definitions, no information on indexes, no query plans and you expect us to fix your query forever?

    Let me see, where did I put my magic wand?

    ps: sorry for the irony

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • vijaymadh

    Grasshopper

    Points: 18

    Because that is not required, I know that the problem is in where statemenet, we are using too many optional parameters and they are causing problem. We have fixed it by recompiling last time.

    However I do not want it to happen again and again so looking for some optimal and permanenet solution.

    Kindly suggest if you think something can help. this is not the problem of indexing I think.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    vijaymadh (2/9/2012)


    Because that is not required, I know that the problem is in where statemenet, we are using too many optional parameters and they are causing problem. We have fixed it by recompiling last time.

    However I do not want it to happen again and again so looking for some optimal and permanenet solution.

    Kindly suggest if you think something can help. this is not the problem of indexing I think.

    Honestly, for such a horrible WHERE clause, 1 minute is pretty decent.

    I guess, because we don't really know the volume of data involved.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • vijaymadh

    Grasshopper

    Points: 18

    ๐Ÿ™‚ this is a dynamic query. Data is not big returning 10-20 rows and total data in tables should not be more than 1000 rows,

    As I mentioned this is nothing to do with data/idex etc. It has something to do with query plan generation and usage.

    Which I am not properly aware and want to understand how this might hamper the performance.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    I don't have a quick answer for you - query compiling is not really my strong suit - but I can recommend you this books you can download for free at this site:

    Inside the SQL Server Query Optimizer

    [/url]

    SQL Server Execution Plans

    [/url]

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • tfifield

    SSCrazy Eights

    Points: 9655

    Vijay,

    For this type of complicated WHERE clause containing many OR type conditions, I've found I can usually get better performance by doing several queries with a UNION. If the conditions of each query are mutually exclusive then you could use UNION ALL, which gives better performance than UNION because it avoids the costly sort operation to weed out duplicates.

    It also makes for easier reading.

    Todd Fifield

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Koen Verbeeck (2/9/2012)


    Let me see, where did I put my magic wand?

    Actually, in this specific case....

    Magic wand for this query: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden

    SSC Guru

    Points: 997128

    Even after passing Gail's "Magic Wand" over this (and you really should read her article if you haven't already), LIKE filters that begin with a wild-card will always be tough on performance because they really don't get along well with indexes. Code like the following snip from the original post is what I'm talking about...

    OR a.fABCField LIKE '%' + @fABCField

    + '%'

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42493

    Jeff, I understand what you said. But many time business requirements drive the code (front end / back end).

    If the requirement is to search any / all customers who have โ€˜JMโ€™ in his name I donโ€™t think we have any other options except like operator and full text indexing. If you have something for it in your toolkit, I am very much interested to know it.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply