| Author |
 Topic  |
|
|
sanghavi17389
Starting Member
India
26 Posts |
 Posted - 05/08/2012 : 02:14:35
|
SELECT drm.requestid, DRM.RunningBoard,DRM.DutyBoard,DRM.DriverId,DRM.PublicServiceCode,DRM.ServiceCode,DRM.DepotCode,DRM.Direction,DRM.EOJMethod,DRM.JourneyNo,CONVERT(datetime, MW.MessageTimeStamp,2) as MessageTimeStamp FROM FactETMPositionRequests DRM JOIN ETMMessage EM ON EM.ETMMessageID= DRM.ETMMessageID JOIN MessageWrapper MW ON EM.WrapperID= MW.ID WHERE ((case when MW.MessageTimeStamp like '%00/00/00%' or MW.MessageTimeStamp like '03/%' or MW.MessageTimeStamp like '08/%' then '01/01/9999' else CONVERT(datetime, MW.MessageTimeStamp,2) end)) between convert(date, '06/05/2012 12:13:59',103) and convert (date,'08/05/2012 00:00:00',103)
-My this query take much more time.
-I want to optimize this query.
All suggestions are welcomed...
harshal sanghavi |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 05/08/2012 : 02:25:28
|
what is the datatype for MessageTimeStamp ?
KH
Time is always against us
|
 |
|
|
sanghavi17389
Starting Member
India
26 Posts |
Posted - 05/08/2012 : 02:33:11
|
it is varcharchar(20).
I am getting data for eg. 12/05/25 that's y I have used CONVERT(datetime, MW.MessageTimeStamp,2).
Moreover in my this messagetimestamp field sometimes invalid data like '00/00/00' or '03/01/01' or '08/00/00' are dumped.
So I have to ignore such data.
harshal sanghavi |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/08/2012 : 04:29:44
|
In that case, your query cannot be optimized because it cannot take advantage of any index.
However... If you can add a calculcated/computed column, like this
ALTER TABLE dbo.Table1 ADD theDate AS (CASE WHEN ISDATE(Col1) = 1 THEN CONVERT(DATE, Col1, 103) ELSE NULL END)
Now you can index the column and the query will be faster.
N 56°04'39.26"
E 12°55'05.63" |
|
|
| |
Topic |
|
|
|
Optimize select query.
