Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sanghavi17389
Starting Member
26 Posts |
 Posted - 2012-05-08 : 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)
17689 Posts |
Posted - 2012-05-08 : 02:25:28
|
what is the datatype for MessageTimeStamp ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
sanghavi17389
Starting Member
26 Posts |
Posted - 2012-05-08 : 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
30421 Posts |
Posted - 2012-05-08 : 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 thisALTER 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" |
|
|
|
|
|
|
|
|
Optimize select query.