Optimizing query using the DateTime and like operator

Question

Optimizing query using the DateTime and like operator

Tushar Chheda

Mr or Mrs. 500

Points: 585
More actions
October 25, 2006 at 11:03 pm

#113985

Hi All,
I am currently facing a tough performance issue when I have a datetime parameter in the where clause of a query.
So when I search or mine data with the like clause for date time based search the query takes very long time to complete.
I have about 1 million records to search based on datetime parameter. and it take more that 20 mins to complete a single query given below
" select FirstName, LastName, Country, EmailAdd, PhoneNo from Clients where DateRegistered like 'Sep 12 2004%' "
Can any body help me in tunning this simple SQL statment. The execution plan shows IndexScan. I have a clustered index on the ClientID for this table and there a non-clustered Index on the DateRegistered field with a fill factor of 90. To also mention that the table has 32 columns.
Kindly help in resolving this performance issue.
Thanks,
Tushar

Viewing 5 posts - 1 through 4 (of 4 total)

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

ijaz SSCommitted Points: 1912 More actions · Answer 1

Hi,

I can't understand why you are using the like to compare the date field. You should used the equal operater then the performance will significally improve.

Other thing is that if you remove the Index on the column then you will also see a difference.

if you want the records of the same date, then you should used the between operator,

'20061029 00:00' and '20061029 23:59:59.999'. and hopefully this will sortout your problem.

" select FirstName, LastName, Country, EmailAdd, PhoneNo from Clients where DateRegistered between '20061029 00:00' and '20061029 23:59:59.999'

Hope this would slove your problem

cheers

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Oh, no, no, no.... don't use BETWEEN... it's a form of "Death by SQL". First, 23:59:59.999 will be rounded up to the next day and you will get all of the next day entries that have a time of 00:00:00.000. You should always use something like the following...

WHERE somedatecolumn >= somestartdate
  AND somedatecolumn <  someenddate+1

Also notice that because there is no calculation done on "somedatecolumn", an index will be used if one is available.

--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".

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

Bill Bertovich SSC Rookie Points: 28 More actions · Answer 3

Tushar,

I don't belive the BETWEEN operation is an issue any longer. It was a carryover optimizer snafu from the Sybase days that found its way into earlier SQL Server versions.

Append ' 23:59:59.997' to the "date only" representation to get the last instant of a given day.

23:59:59.998 and 23:59:59.999 always round up to midnight of the next day. Microsoft has some bogus answer having to do with ODBC compatibility or something like that. I've used this technique processing hundreds of millions of rows without consequence. For example, the following will give you all rows occuring today:

declare @BeginDate datetime

declare @EndDate datetime

set @BeginDate = convert( varchar(6), getdate(),112) -- midnight today

set @EndDate = convert(varchar(19), @BeginDate, 112) + ' 23:59:59.997'

-- The following commands should parse to the equivilent compiled code:

select ... from ... where mydatecolumn >= BeginDate and mydatecolumn < @EndDate

select ... from ... where mydatecolumn between BeginDate and @EndDate

Hope this helps.

Bill

Bill Bertovich SSC Rookie Points: 28 More actions · Answer 4

oops, this:

set @EndDate = convert(varchar(19), @BeginDate, 112) + ' 23:59:59.997'

should be this:

set @EndDate = convert(varchar(6), @BeginDate, 112) + ' 23:59:59.997'