May 8, 2012 at 12:13 am
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...
May 8, 2012 at 12:38 am
We need the DDL (CREATE TABLE statement) for the tables involved in the query, including any indexes. Also the actual execution plan, saved and uploaded as a .sqlplan file.
Just looking at the query itself, after reformatting it to make it more readable, there doesn't appear to be much that can be done to improve the query withot the information requested.
May 8, 2012 at 2:31 am
Lynn Pettis (5/8/2012)
We need the DDL (CREATE TABLE statement) for the tables involved in the query, including any indexes. Also the actual execution plan, saved and uploaded as a .sqlplan file.Just looking at the query itself, after reformatting it to make it more readable, there doesn't appear to be much that can be done to improve the query withot the information requested.
+1
May 8, 2012 at 3:05 am
Try this
1. Create a temp table contains required column in FactETMPositionRequests and ETMMessage Table. Filter should be
MW.MessageTimeStamp between convert(date, '06/05/2012 12:13:59',103) and convert (date,'08/05/2012 00:00:00',103) and
Month(MW.MessageTimeStamp) not in (0,3,8)
2. Join the temp table with MessageWrapper ON EM.WrapperID= MW.ID
3. Apply no clustered index on ID fro the temp table.
May 8, 2012 at 4:16 am
Case statements in the WHERE claue are going to lead to some less than satisfying execution plans. I'd suggest breaking down into different queries rather than do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2012 at 6:52 am
with the DDL, we could see if the column MessageTimeStamp was datetime or not...i suspect it's a varchar because of the code converting it to datetime.
you could add persisted calculated columns which pull out the parts you are testing, and put indexes on them.
WHERE (( CASE
WHEN MW.MessageTimeStamp LIKE '%00/00/00%'
OR MW.MessageTimeStamp LIKE '03/%'
OR MW.MessageTimeStamp LIKE '08/%'
you could pull out the TIME portion and the MONTH as calculated columns to help, i think.
then you can test If calcMONTH IN(3,8) or calcTIME = = convert(time,'00:00:00.000')
Lowell
May 8, 2012 at 7:06 am
i would wager that this is faster
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
CONVERT(datetime, MW.MessageTimeStamp,2)
between convert(date, '06/05/2012 12:13:59',103) and convert (date,'08/05/2012 00:00:00',103)
and MW.MessageTimeStamp not like '03/%'
and MW.MessageTimeStamp not like '08/%'
and MW.MessageTimeStamp not like '00/%'
but i can't test it without DDL
also - why store dates in a varchar column? if it's a date then store it in a date/datetime column and get rid of those nasty CONVERT uses
MVDBA
May 9, 2012 at 4:54 am
No. In this query, it will give me error. because there while doing join, it will consider all data including invalid data. And conversion of such data into date will give error.
That's y I have used 'case'.
May 9, 2012 at 7:07 am
hmsanghavi (5/9/2012)
No. In this query, it will give me error. because there while doing join, it will consider all data including invalid data. And conversion of such data into date will give error.That's y I have used 'case'.
i disagree
your date range is looking for dates between 06/05/2012 and 08/05/2015
any instance of mw.messagetimestamp that starts 03/% or 08/% is converted to 01/01/9999 which is outside your filter range and therefore not included in your results
it would ]most likely therfore be more effficient to re-write this as
where mw.messagetimestamp NOT LIKE '03/%'
and mw.messagetimestamp NOT LIKE '08/%'
MVDBA
May 9, 2012 at 7:12 am
Still waiting for you to post the DDL (CREATE TABLE statement) for the table(s) involved in your query along with the indexes defined on the tables. Could also use sample data (not production data, and not a lot) along with the expected results based on the sample data.
May 10, 2012 at 1:10 am
Hi Mike,
I had already tried as per you said but an error was occured that time.
Error was about date conversion because some data are not well formated in messagetimestamp field.
Simply I put where condition and in that i checked not valid formated date using like but it was giving date conversion error.
That's y later on I had changed my query.
In that I use case.
Hope you can understand what I am going to explain.
May 10, 2012 at 2:00 am
so isn't your real problem getting all of the dateimes into a valid format and changing that column from varchar to datetime.
that would allow you to write decent SQL and the query optimiser stands a chance at giving you a decent plan
then you can start using the datepart function
i kinda feel that it's pointless trying top optimise code that lies of a fundametally flawed foundation
MVDBA
May 10, 2012 at 2:04 am
hmsanghavi (5/10/2012)
Hi Mike,I had already tried as per you said but an error was occured that time.
Error was about date conversion because some data are not well formated in messagetimestamp field.
Simply I put where condition and in that i checked not valid formated date using like but it was giving date conversion error.
That's y later on I had changed my query.
In that I use case.
Hope you can understand what I am going to explain.
hold on - somethings not right about that reply
how can
where mw.messagetimestamp NOT LIKE '03/%'
and mw.messagetimestamp NOT LIKE '08/%'
give you a datetime conversion error??
the datetime conversion error is somewhere else in your query
MVDBA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply