March 28, 2013 at 12:04 pm
How to improve performance with the LIKE clause. I have the following view definition
SELECT Distinct
c.EMP_ID as ID,
e.name as Display
FROM Case1 c, Employer e
where c.EMP_ID = e.EMP_ID
and c.Segment = e.Segment
and e.name is not null
and (
(c.dischargedt is not null and c.discharge_entered_dt>=convert(varchar(10),getdate()-14,101))
)
Doing a wildcard search on Display column using LIKE %xyz%
The performance is too slow because of the join in the view. If i go search directly against the Employer table with the LIKE clause it is faster.
Both tables (employer and case1)have the necessary indexes.
Any suggestions?
March 28, 2013 at 12:23 pm
First, try changing the JOIN to a more common version of the JOIN, which may (or may not) help.
SELECT Distinct
c.EMP_ID as ID,
e.name as Display
FROM Case1 c
INNER JOIN Employer e
ON c.EMP_ID = e.EMP_ID
and c.Segment = e.Segment
and e.name is not null
WHERE c.dischargedt is not null
and c.discharge_entered_dt>=convert(varchar(10),getdate()-14,101)
Then run your performance checks again because I'm pretty sure it's not the JOIN that's your problem. I believe it's in the WHERE clause where you're not using a SARGable expression to find your date.
A thought would be to further change the code to be:
DECLARE @TodayDate CHAR(10) = convert(varchar(10),getdate()-14,101)
SELECT Distinct
c.EMP_ID as ID,
e.name as Display
FROM Case1 c
INNER JOIN Employer e
ON c.EMP_ID = e.EMP_ID
and c.Segment = e.Segment
and e.name is not null
WHERE c.dischargedt is not null and c.discharge_entered_dt>=@TodayDate
Don't use VARCHAR for the conversion. The date is always going to be 10 characters, so make it a CHAR so you don't get the extra overhead charged for a variable that isn't really variable. Also, your WHERE clause is potentially processing that CONVERT on a row-by-row basis (ouch!) to compare it to the column. Since that part of the query will never change, just stick it in a variable and compare it directly to make sure that you don't run into the reconversion time after time.
March 28, 2013 at 12:28 pm
kk1173 (3/28/2013)
Both tables (employer and case1)have the necessary indexes.
Something else to consider.
Are you absolutely 100% positive that the indexes are 1) being used by the query engine and 2) properly updated with statistics and 3) unfragmented and 4) indeed the indexes that this query needs to use?
Indexing is an art, not a science. And I've seen many a person think that they have the proper indexes but don't because they don't fully grasp how the engine interacts with the indexes. Even I have, at times, coded the "necessary indexes" only to find out later on that the engine doesn't like those indexes at all.
March 28, 2013 at 12:31 pm
Why are you using the following code?
c.discharge_entered_dt>=convert(varchar(10),getdate()-14,101)
What is the datatype of discharge_entered? You do know that character evaluations will not produce the same thing as datetime evaluations. If your datatype is datetime you not only have introduced a bug you have also slowed down your process.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2013 at 12:34 pm
discharged_entered is a date field.
March 28, 2013 at 12:36 pm
...
WHERE c.dischargedt is not null and c.discharge_entered_dt >= dateadd(dd, -14, getdate())
March 28, 2013 at 12:37 pm
Would help to see the DDL for the table(s) including indexes. Also, if you are using SQL Server 2008, a proper filtered index may be of benefit here.
March 28, 2013 at 12:37 pm
kk1173 (3/28/2013)
discharged_entered is a date field.
Then forget all the convert nonsense. Just compare it to a datetime.
c.discharge_entered_dt >= DATEADD(DAY, -14, getdate())
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2013 at 12:38 pm
Brandie Tarvin (3/28/2013)
Also, your WHERE clause is potentially processing that CONVERT on a row-by-row basis (ouch!) to compare it to the column.
Actually this won't run it for every row. The optimizer is smart to realize that the value will be constant so it only determines that value once.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2013 at 12:39 pm
Sean Lange (3/28/2013)
kk1173 (3/28/2013)
discharged_entered is a date field.Then forget all the convert nonsense. Just compare it to a datetime.
c.discharge_entered_dt >= DATEADD(DAY, -14, getdate())
Sean, This won't necessarily work for him. If that field has actual times in it, and he needs to ignore those times, then your suggestion will drop records he needs to keep.
March 28, 2013 at 12:46 pm
Brandie Tarvin (3/28/2013)
Sean Lange (3/28/2013)
kk1173 (3/28/2013)
discharged_entered is a date field.Then forget all the convert nonsense. Just compare it to a datetime.
c.discharge_entered_dt >= DATEADD(DAY, -14, getdate())
Sean, This won't necessarily work for him. If that field has actual times in it, and he needs to ignore those times, then your suggestion will drop records he needs to keep.
If discharged_entered is declared as a date datatype, then this:
c.discharge_entered_dt >= cast(DATEADD(DAY, -14, getdate()) as date)
March 28, 2013 at 12:46 pm
Brandie Tarvin (3/28/2013)
Sean Lange (3/28/2013)
kk1173 (3/28/2013)
discharged_entered is a date field.Then forget all the convert nonsense. Just compare it to a datetime.
c.discharge_entered_dt >= DATEADD(DAY, -14, getdate())
Sean, This won't necessarily work for him. If that field has actual times in it, and he needs to ignore those times, then your suggestion will drop records he needs to keep.
Ah yes...those pesky times. Good catch. Still don't need to resort to converting to a char.
c.discharge_entered_dt >= DATEADD(DAY, -14, dateadd(dd, datediff(dd, 0, getdate()), 0))
--edit--
Or the one Lynn posted. Not sure which would end up performing better but I suspect it would be pretty close.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2013 at 1:23 pm
Going back to the original point...I don't think optimizing the view is going to help a whole lot here.
The OP is querying this view using a like statement that forces a table scan. By doing this:
SELECT ... FROM MyView WHERE MyColumn LIKE '%value%';
This is always going to produce a scan - no matter how optimized the view is.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 28, 2013 at 1:37 pm
If he has an index on that column and he can drop the first % he can get a seek instead of a scan.
ie..
SELECT ... FROM MyView WHERE MyColumn LIKE 'value%';
March 28, 2013 at 1:39 pm
Wildcard search needs to be done. If I do a value% it will do BeginWith.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply