February 8, 2012 at 11:53 pm
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 != ''
February 9, 2012 at 12:06 am
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
February 9, 2012 at 12:14 am
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.
February 9, 2012 at 12:16 am
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
February 9, 2012 at 12:21 am
🙂 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.
February 9, 2012 at 12:28 am
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]
[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 10, 2012 at 11:44 am
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
February 10, 2012 at 12:50 pm
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
February 11, 2012 at 4:20 am
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
Change is inevitable... Change for the better is not.
February 11, 2012 at 4:44 am
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply