August 16, 2012 at 5:38 am
Hi,
The query I am working on runs very slow due to a piece of code -
CREATE_DT > = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1
I need to fetch data from the midnight of previous day and for that I have used the above code in the following query:
------------------------------
SELECT
MB.BOOKING_ID, BOOKING_NUM, MB.SC_NUM AS [SC NUM], POL_LOCATION_CD,
FIRST_POD_LOCATION_CD, PDL_LOCATION_CD, SERVICE_CD,
TRADE_CD, MB.CREATE_DT, [SHIPPER NAME], [BOOKING PARTY], [FORWARDER NAME],
USER_LAST_NAME,DG_APPROVAL_INDICATOR,
c.REEFER_FLG,
a.[BOOKING_EQUIPMENT],
b.GEN_COMMOD_CD
FROM MG_BOOKING MB
INNER JOIN MG_ISEC_APPLICATION_USERS MIAU
ON MB.CREATE_USER_ID = MIAU.[USER_ID]
INNER JOIN MG_OFFICE MO
ON MB.BOOKING_OFFICE_CD = MO.OFFICE_CD
AND MO.ORGANIZATION_COMPANY_CD = 'KFR'
WHERE CREATE_DT > = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1
--------------------------------
Could someone please suggest an alternate code which can replace the above one and runs more faster ?
Thanks,
Paul
August 16, 2012 at 6:18 am
What indexes are on the tables?
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
August 16, 2012 at 6:20 am
pwalter83 (8/16/2012)
Hi,The query I am working on runs very slow due to a piece of code -
CREATE_DT > = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1
Could someone please suggest an alternate code which can replace the above one and runs more faster ?
Thanks,
Paul
Hi Paul
The expression DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1 will only run once in your query and is very quick - it's not the issue here. If you've identified it as the issue by running the query with and without this predicate, then it's likely that there's no suitable index on CREATE_DT. Can you post the actual plan please?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2012 at 7:20 am
ChrisM@Work (8/16/2012)
pwalter83 (8/16/2012)
Hi,The query I am working on runs very slow due to a piece of code -
CREATE_DT > = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1
Could someone please suggest an alternate code which can replace the above one and runs more faster ?
Thanks,
Paul
Hi Paul
The expression DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1 will only run once in your query and is very quick - it's not the issue here. If you've identified it as the issue by running the query with and without this predicate, then it's likely that there's no suitable index on CREATE_DT. Can you post the actual plan please?
Thanks Chris,
The query excutes and fetches data almost instantly if I use CREATE_DT > = getdate() - 1 instead of the above.
By creating an index on CREATE_DT, the query has become faster but still takes a minute and a half to run. I still think its the expression thats causing it to run slowly.
Please find the actual execution plan attached.
Thanks,
Paul
August 16, 2012 at 7:37 am
pwalter83 (8/16/2012)
ChrisM@Work (8/16/2012)
pwalter83 (8/16/2012)
Hi,The query I am working on runs very slow due to a piece of code -
CREATE_DT > = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1
Could someone please suggest an alternate code which can replace the above one and runs more faster ?
Thanks,
Paul
Hi Paul
The expression DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1 will only run once in your query and is very quick - it's not the issue here. If you've identified it as the issue by running the query with and without this predicate, then it's likely that there's no suitable index on CREATE_DT. Can you post the actual plan please?
Thanks Chris,
The query excutes and fetches data almost instantly if I use CREATE_DT > = getdate() - 1 instead of the above.
By creating an index on CREATE_DT, the query has become faster but still takes a minute and a half to run. I still think its the expression thats causing it to run slowly.
Please find the actual execution plan attached.
Thanks,
Paul
The two expressions return different results:
SELECT getdate() - 1 -- 2012-08-15 14:27:48.220
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) - 1 -- 2012-08-15 00:00:00.000
using getdate() - 1 eliminates 14 hour's worth of data from MG_BOOKING.
To see if this is the case, try these two alternatives:
WHERE MB.CREATE_DT > = DATEADD(HOUR,-14,GETDATE()-1) -- similar to DATEADD(D, 0, DATEDIFF(D, 1, GETDATE()))
WHERE MB.CREATE_DT > = DATEADD(HOUR,0,GETDATE()-1) -- similar to getdate() - 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2012 at 7:50 am
ChrisM@Work (8/16/2012)
pwalter83 (8/16/2012)
Hi,The query I am working on runs very slow due to a piece of code -
CREATE_DT > = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1
Could someone please suggest an alternate code which can replace the above one and runs more faster ?
Thanks,
Paul
Hi Paul
The expression DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1 will only run once in your query and is very quick - it's not the issue here. If you've identified it as the issue by running the query with and without this predicate, then it's likely that there's no suitable index on CREATE_DT. Can you post the actual plan please?
If you had posted the entire query originally we would have had a better shot at helping. Your date logic is not at all the blame for the slow running here. It is your three correlated subqueries generating delimited lists that is causing the slowdown.
_______________________________________________________________
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/
August 16, 2012 at 8:06 am
Sean Lange (8/16/2012)
ChrisM@Work (8/16/2012)
pwalter83 (8/16/2012)
Hi,The query I am working on runs very slow due to a piece of code -
CREATE_DT > = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1
Could someone please suggest an alternate code which can replace the above one and runs more faster ?
Thanks,
Paul
Hi Paul
The expression DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) -1 will only run once in your query and is very quick - it's not the issue here. If you've identified it as the issue by running the query with and without this predicate, then it's likely that there's no suitable index on CREATE_DT. Can you post the actual plan please?
If you had posted the entire query originally we would have had a better shot at helping. Your date logic is not at all the blame for the slow running here. It is your three correlated subqueries generating delimited lists that is causing the slowdown.
Thanks Sean,
I have run the full query below with and without the date logic and its generating instant results without the date logic. The 3 subqueries have nothing to do with the slow running. The query was running fast until I changed the date logic.
---------------------------------
SELECT
MB.BOOKING_ID, BOOKING_NUM, MB.SC_NUM AS [SC NUM], POL_LOCATION_CD,
FIRST_POD_LOCATION_CD, PDL_LOCATION_CD, SERVICE_CD,
TRADE_CD, MB.CREATE_DT, [SHIPPER NAME], [BOOKING PARTY], [FORWARDER NAME],
USER_LAST_NAME,DG_APPROVAL_INDICATOR,
c.REEFER_FLG,
a.[BOOKING_EQUIPMENT],
b.GEN_COMMOD_CD
FROM MG_BOOKING MB
INNER JOIN MG_ISEC_APPLICATION_USERS MIAU
ON MB.CREATE_USER_ID = MIAU.[USER_ID]
INNER JOIN MG_OFFICE MO
ON MB.BOOKING_OFFICE_CD = MO.OFFICE_CD
AND MO.ORGANIZATION_COMPANY_CD = 'KFR'
LEFT JOIN
(
SELECT
BOOKING_ID,
MAX(CASE WHEN COMPANY_ROLE_CD = 'SH' THEN PARTY_NAME ELSE NULL END) AS [SHIPPER NAME],
MAX(CASE WHEN COMPANY_ROLE_CD = 'BK' THEN PARTY_NAME ELSE NULL END) AS [BOOKING PARTY],
MAX(CASE WHEN COMPANY_ROLE_CD = 'FW' THEN PARTY_NAME ELSE NULL END) AS [FORWARDER NAME]
FROM MG_BOOKING_PARTY
WHERE COMPANY_ROLE_CD IN ('SH','BK','FW')
GROUP BY BOOKING_ID
)MGP
ON MB.BOOKING_ID = MGP.BOOKING_ID
CROSS APPLY (
SELECT
=
STUFF(
(SELECT ', ' + mbc.GENERAL_COMMODITY_CD
FROM MG_BOOKING_COMMODITY mbc
WHERE mbc.BOOKING_ID = mb.BOOKING_ID
--AND mber.BOOKING_NUM = mb.BOOKING_NUM AND mber.TRADE_CD = mb.TRADE_CD
--ORDER BY GENERAL_COMMODITY_CD
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'')
) b (GEN_COMMOD_CD)
CROSS APPLY (
SELECT
=
STUFF(
(SELECT '' + mbc.REEFER_FLG
FROM MG_BOOKING_COMMODITY mbc
WHERE mbc.BOOKING_ID = mb.BOOKING_ID
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,0,'')
) c (REEFER_FLG)
-- 18/06/12 - req to add MG_BOOKING_EQUIPMENT_REQ table
CROSS APPLY (
SELECT
=
STUFF(
(SELECT ', ' + CAST(mber.EQ_CNT AS VARCHAR) + ' ' + mber.KL_EQUIPMENT_TYPE_CD
FROM MG_BOOKING_EQUIPMENT_REQ mber
WHERE mber.BOOKING_ID = mb.BOOKING_ID
--AND mber.BOOKING_NUM = mb.BOOKING_NUM AND mber.TRADE_CD = mb.TRADE_CD
ORDER BY KL_EQUIPMENT_TYPE_CD
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'')
) a (BOOKING_EQUIPMENT)
WHERE MB.CREATE_DT > = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) - 1
AND MB.BOOKING_TYPE_CD = 'FCL'
AND MB.BOOKING_STATUS_CD NOT IN ('C','D')
---------------------------------
Thanks,
Paul
August 16, 2012 at 8:19 am
So if you totally remove the date check then it runs fast again? That sounds like indexing to me.
_______________________________________________________________
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/
August 16, 2012 at 8:24 am
Paul, have you tried these two yet?
WHERE MB.CREATE_DT > = DATEADD(HOUR,-14,GETDATE()-1) -- similar to DATEADD(D, 0, DATEDIFF(D, 1, GETDATE()))
WHERE MB.CREATE_DT > = DATEADD(HOUR,0,GETDATE()-1) -- similar to getdate() - 1
It's kinda fundamental that you do.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2012 at 9:20 am
ChrisM@Work (8/16/2012)
Paul, have you tried these two yet?
WHERE MB.CREATE_DT > = DATEADD(HOUR,-14,GETDATE()-1) -- similar to DATEADD(D, 0, DATEDIFF(D, 1, GETDATE()))WHERE MB.CREATE_DT > = DATEADD(HOUR,0,GETDATE()-1) -- similar to getdate() - 1
It's kinda fundamental that you do.
Thanks Chris,
DATEADD(HOUR,-14,GETDATE()-1) only goes back 14 hours from getdate(). The date logic I need to apply should go back to midnight of previous day like if you execute DATEADD(D, 0, DATEDIFF(D, 1, GETDATE())) the result would be Aug 15 2012 00:00:00...
August 16, 2012 at 9:23 am
pwalter83 (8/16/2012)
ChrisM@Work (8/16/2012)
Paul, have you tried these two yet?
WHERE MB.CREATE_DT > = DATEADD(HOUR,-14,GETDATE()-1) -- similar to DATEADD(D, 0, DATEDIFF(D, 1, GETDATE()))WHERE MB.CREATE_DT > = DATEADD(HOUR,0,GETDATE()-1) -- similar to getdate() - 1
It's kinda fundamental that you do.
Thanks Chris,
DATEADD(HOUR,-14,GETDATE()-1) only goes back 14 hours from getdate(). The date logic I need to apply should go back to midnight of previous day like if you execute DATEADD(D, 0, DATEDIFF(D, 1, GETDATE())) the result would be Aug 15 2012 00:00:00...
That's the point, Paul. You need to know if missing out 14 hour's worth of data makes the query run much faster than including it. Check the expression again.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply