July 3, 2012 at 9:47 am
Hi,
I have created a query which is very slow to process due to FOR XML PATH usage. Could someone please tell how it can be made to run fast:
------------------------------------------------------------------
SELECT MB.BOOKING_ID, BOOKING_NUM, MB.SC_NUM AS [INVALID 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], c.REEFER_FLG,USER_LAST_NAME,DG_APPROVAL_REQUIRED_FLG,
a.[BOOKING_EQUIPMENT], b.GEN_COMMOD_CD
FROM MG_BOOKING MB
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)
INNER JOIN MG_ISEC_APPLICATION_USERS MIAU
ON MB.CREATE_USER_ID = MIAU.[USER_ID]
-- 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)
INNER JOIN MG_OFFICE MO
ON MB.BOOKING_OFFICE_CD = MO.OFFICE_CD
AND MO.ORGANIZATION_COMPANY_CD = 'KFR'
WHERE MB.CREATE_DT > = GETDATE() - 7
AND MB.BOOKING_TYPE_CD = 'FCL'
AND MB.BOOKING_STATUS_CD = 'F'
AND (MB.SC_NUM NOT IN (SELECT SC_NUM from MiniFCA..MF_SC_NUMBER)
OR MB.SC_NUM IS NULL)
ORDER BY MB.BOOKING_ID
------------------------------------------------------------------
Thanks,
Paul
July 3, 2012 at 10:00 am
Start with this from Gail. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
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/
July 4, 2012 at 2:16 am
Sean Lange (7/3/2012)
Start with this from Gail. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
I think the reason for the slow performance could be due to these lines of codes. I have also attached the Execution plan.
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)
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)
July 4, 2012 at 8:58 am
pwalter83 (7/4/2012)
Sean Lange (7/3/2012)
Start with this from Gail. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]I think the reason for the slow performance could be due to these lines of codes. I have also attached the Execution plan.
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)
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)
Does anyone have any idea about it ? even a slight hint would help.
Thanks,
Paul
July 5, 2012 at 2:49 am
July 5, 2012 at 4:57 am
Hi Paul
Here's what I suggest you do.
First, comment out the three CROSS APPLY blocks and the references to their results in the output of the query, like so:
SELECT
MB.BOOKING_ID, BOOKING_NUM, MB.SC_NUM AS [INVALID 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_REQUIRED_FLG--,
--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 > = GETDATE() - 7
AND MB.BOOKING_TYPE_CD = 'FCL'
AND MB.BOOKING_STATUS_CD = 'F'
AND (MB.SC_NUM NOT IN (SELECT SC_NUM from MiniFCA..MF_SC_NUMBER)
OR MB.SC_NUM IS NULL)
ORDER BY MB.BOOKING_ID
--SET ANSI_WARNINGS ON
Run the query with and without the code blocks and compare the time.
Second, with the three code blocks active, get the actual plan (not the estimated plan) and post it here.
The estimated plan indicates that almost the entire cost of running the query is clustered index scans of the three main tables and suggests an index which may help - the actual plan may well be different.
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
July 5, 2012 at 5:38 am
ChrisM@Work (7/5/2012)
Hi PaulHere's what I suggest you do.
First, comment out the three CROSS APPLY blocks and the references to their results in the output of the query, like so:
SELECTMB.BOOKING_ID, BOOKING_NUM, MB.SC_NUM AS [INVALID 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_REQUIRED_FLG--,
--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 > = GETDATE() - 7
AND MB.BOOKING_TYPE_CD = 'FCL'
AND MB.BOOKING_STATUS_CD = 'F'
AND (MB.SC_NUM NOT IN (SELECT SC_NUM from MiniFCA..MF_SC_NUMBER)
OR MB.SC_NUM IS NULL)
ORDER BY MB.BOOKING_ID
--SET ANSI_WARNINGS ON
Run the query with and without the code blocks and compare the time.
Second, with the three code blocks active, get the actual plan (not the estimated plan) and post it here.
The estimated plan indicates that almost the entire cost of running the query is clustered index scans of the three main tables and suggests an index which may help - the actual plan may well be different.
Thanks for your suggestion.
I compared the 2 queries (with and without the cross apply) and found that it took 3:57 to run the query with cross apply and 1:27 without it and it yielded 21 rows.
Please also find the Actual Exec plan attached.
Thanks,
Paul
July 5, 2012 at 6:24 am
You need a new index on MG_BOOKING_COMMODITY, on BOOKING_ID and include GENERAL_COMMODITY_CD & REEFER_FLG. This should improve performance by about 65%.
MG_BOOKING would benefit from an index on at least some of the columns in your WHERE clause, it's difficult to tell which ones without knowledge of the distribution of the column values. How many rows in the table?
How many rows are returned by the query if you comment out some of the filters like so:
WHERE 1 = 1
--AND MB.CREATE_DT > = GETDATE() - 7
AND MB.BOOKING_TYPE_CD = 'FCL'
AND MB.BOOKING_STATUS_CD = 'F'
--AND (MB.SC_NUM NOT IN (SELECT SC_NUM from MiniFCA..MF_SC_NUMBER)
--OR MB.SC_NUM IS NULL)
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
July 5, 2012 at 7:05 am
pwalter83 (7/5/2012)
Sean Lange (7/3/2012)
Start with this from Gail. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Hi Sean,
Would you be able to help me now ?
Thanks,
Paul
Sorry yesterday was a holiday here in the US. It looks like Chris has made much the same suggestions I would. Let us know what happens and we will see where to go next.
_______________________________________________________________
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/
July 5, 2012 at 7:13 am
Sean Lange (7/5/2012)
pwalter83 (7/5/2012)
Sean Lange (7/3/2012)
Start with this from Gail. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Hi Sean,
Would you be able to help me now ?
Thanks,
Paul
Sorry yesterday was a holiday here in the US. It looks like Chris has made much the same suggestions I would. Let us know what happens and we will see where to go next.
Sean - thanks for the second opinion, hope your ID celebration was up to par 🙂
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
July 5, 2012 at 7:21 am
pwalter83 (7/5/2012)
ChrisM@Work (7/5/2012)
Hi PaulHere's what I suggest you do.
First, comment out the three CROSS APPLY blocks and the references to their results in the output of the query, like so:
SELECTMB.BOOKING_ID, BOOKING_NUM, MB.SC_NUM AS [INVALID 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_REQUIRED_FLG--,
--c.REEFER_FLG,
--a.[BOOKING_EQUIPMENT],
--b.GEN_COMMOD_CD
FROM MG_BOOKING MB
That worked wonders !!! Thanks a ton for your help. It takes around 20 sec now to bring back the data.
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 > = GETDATE() - 7
AND MB.BOOKING_TYPE_CD = 'FCL'
AND MB.BOOKING_STATUS_CD = 'F'
AND (MB.SC_NUM NOT IN (SELECT SC_NUM from MiniFCA..MF_SC_NUMBER)
OR MB.SC_NUM IS NULL)
ORDER BY MB.BOOKING_ID
--SET ANSI_WARNINGS ON
Run the query with and without the code blocks and compare the time.
Second, with the three code blocks active, get the actual plan (not the estimated plan) and post it here.
The estimated plan indicates that almost the entire cost of running the query is clustered index scans of the three main tables and suggests an index which may help - the actual plan may well be different.
Thanks for your suggestion.
I compared the 2 queries (with and without the cross apply) and found that it took 3:57 to run the query with cross apply and 1:27 without it and it yielded 21 rows.
Please also find the Actual Exec plan attached.
Thanks,
Paul
July 5, 2012 at 7:53 am
Sean Lange (7/5/2012)
pwalter83 (7/5/2012)
Sean Lange (7/3/2012)
Start with this from Gail. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Hi Sean,
Would you be able to help me now ?
Thanks,
Paul
Sorry yesterday was a holiday here in the US. It looks like Chris has made much the same suggestions I would. Let us know what happens and we will see where to go next.
thats no problem, Sean, Happy Independence Day !!!
July 5, 2012 at 7:56 am
hope your ID celebration was up to par 🙂
If by that you mean the Sporting KC win last night then I would say my celebration was up to par. 😉
_______________________________________________________________
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/
July 5, 2012 at 1:50 pm
Hi,
I don't know but "AND (MB.SC_NUM NOT IN (SELECT SC_NUM from MiniFCA..MF_SC_NUMBER)
OR MB.SC_NUM IS NULL)
" is not something like a Catch All Query and it produces a table scan?
July 5, 2012 at 2:05 pm
You migth need a index on the field BOOKING_ID of the table MG_BOOKING_COMMODITY
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply