May 31, 2016 at 1:17 am
Hello Experts,
I am working on following query optimization as it is taking 22 hrs to execute at client side.
Any one please help me to share thoughts to optimize this query
Thanks in advance
SELECT
10
,43
,655
,MedRecReviewID
,NULL
,NULL
,0
FROM
(SELECT DISTINCT bo.MedRecReviewID
FROM [CPROD1].dbo.TABLE_A bo WITH (NOLOCK)
INNER JOIN [CPROD1].[dbo].[TABLE_B] mrr WITH (NOLOCK) ON mrr.MedRecReviewID = bo.MedRecReviewID
INNER JOIN [PM_PROD_Stage].[dbo].[TABLE_C] vh WITH (NOLOCK) ON vh.GUID = bo.VisitGUID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_D] V WITH (NOLOCK) ON V.ClientVisitGUID = vh.GUID
LEFT OUTER JOIN [CPROD1].[dbo].[TABLE_E] pres WITH (NOLOCK) ON pres.PrescriptionID = mrr.PrescriptionID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_F] pre ON pre.PrescriptionID = pres.PrescriptionID
WHERE mrr.MedRecReviewDetailID IN
(SELECT bo.MedRecReviewDetailID FROM [CPROD1].dbo.TABLE_B bo WITH (NOLOCK)
INNER JOIN [CPROD1].[dbo].[TABLE_A] mrr WITH (NOLOCK) ON mrr.MedRecReviewID = bo.MedRecReviewID
INNER JOIN [PM_PROD_Stage].[dbo].[TABLE_C] vh WITH (NOLOCK) ON vh.GUID = mrr.VisitGUID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_D] V WITH (NOLOCK) ON V.ClientVisitGUID = vh.GUID
LEFT OUTER JOIN [CPROD1].[dbo].[TABLE_E] pres WITH (NOLOCK) ON pres.PrescriptionID =bo.PrescriptionID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_F] pre WITH (NOLOCK) ON pre.PrescriptionID = pres.PrescriptionID
EXCEPT
(SELECT MedRecReviewDetailID FROM [PM_PROD_AcuteCare].[dbo].[TABLE_G] v WITH (NOLOCK)
UNION
SELECT MedRecReviewDetailID FROM [PM_PROD_StageCDC].dbo.[TABLE_H] WITH (NOLOCK) )
)
AND dbo.MedRecReviewID NOT IN
(SELECT GUID FROM [dbo].[TABLE_I] WITH (NOLOCK) WHERE AuditTableID=43 AND AuditRunId = 10 )
)x
May 31, 2016 at 1:26 am
I have added attachment as this is not in sql intended format
May 31, 2016 at 2:58 am
Can you confirm that these two queries are logically the same apart from the DISTINCT?
SELECT DISTINCT bo.MedRecReviewID
FROM [CPROD1].dbo.TABLE_A bo WITH (NOLOCK)
INNER JOIN [CPROD1].[dbo].[TABLE_B] mrr WITH (NOLOCK) ON mrr.MedRecReviewID = bo.MedRecReviewID
INNER JOIN [PM_PROD_Stage].[dbo].[TABLE_C] vh WITH (NOLOCK) ON vh.GUID = bo.VisitGUID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_D] V WITH (NOLOCK) ON V.ClientVisitGUID = vh.GUID
LEFT OUTER JOIN [CPROD1].[dbo].[TABLE_E] pres WITH (NOLOCK) ON pres.PrescriptionID = mrr.PrescriptionID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_F] pre ON pre.PrescriptionID = pres.PrescriptionID
SELECT bo.MedRecReviewDetailID
FROM [CPROD1].dbo.TABLE_B bo WITH (NOLOCK)
INNER JOIN [CPROD1].[dbo].[TABLE_A] mrr WITH (NOLOCK) ON mrr.MedRecReviewID = bo.MedRecReviewID
INNER JOIN [PM_PROD_Stage].[dbo].[TABLE_C] vh WITH (NOLOCK) ON vh.GUID = mrr.VisitGUID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_D] V WITH (NOLOCK) ON V.ClientVisitGUID = vh.GUID
LEFT OUTER JOIN [CPROD1].[dbo].[TABLE_E] pres WITH (NOLOCK) ON pres.PrescriptionID =bo.PrescriptionID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_F] pre WITH (NOLOCK) ON pre.PrescriptionID = pres.PrescriptionID
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
May 31, 2016 at 3:08 am
It's a bit difficult without a testcase (tablescript, indexscript, data).
Points of interest:
The use of distinct bo.MedRecReviewID : why distinct
The inner joins in the upper select: can be replaced with where exists as you only need the MedRecReviewID
UNION instead of UNION ALL in de exceptclause
Those are all tables?
May 31, 2016 at 3:40 am
Focus your attention first on constructing something which can be used as a reference query – 24 hours is too long to wait for two queries to run to see if they generate the same results and you’d run the risk of data changes affecting the original query but not the replacement. You can probably do that by breaking the query up into separate parts like this:
IF OBJECT_ID('tempdb..#MedRecReviewID') IS NOT NULL DROP TABLE #MedRecReviewID;
SELECT bo.MedRecReviewID, mrr.MedRecReviewDetailID
INTO #MedRecReviewID
FROM [CPROD1].dbo.TABLE_A bo WITH (NOLOCK)
INNER JOIN [CPROD1].[dbo].[TABLE_B] mrr WITH (NOLOCK) ON mrr.MedRecReviewID = bo.MedRecReviewID
INNER JOIN [PM_PROD_Stage].[dbo].[TABLE_C] vh WITH (NOLOCK) ON vh.GUID = bo.VisitGUID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_D] V WITH (NOLOCK) ON V.ClientVisitGUID = vh.GUID
LEFT OUTER JOIN [CPROD1].[dbo].[TABLE_E] pres WITH (NOLOCK) ON pres.PrescriptionID = mrr.PrescriptionID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_F] pre ON pre.PrescriptionID = pres.PrescriptionID
IF OBJECT_ID('tempdb..#MedRecReviewDetailID') IS NOT NULL DROP TABLE #MedRecReviewDetailID;
SELECT bo.MedRecReviewDetailID
INTO #MedRecReviewDetailID
FROM [CPROD1].dbo.TABLE_B bo WITH (NOLOCK)
INNER JOIN [CPROD1].[dbo].[TABLE_A] mrr WITH (NOLOCK) ON mrr.MedRecReviewID = bo.MedRecReviewID
INNER JOIN [PM_PROD_Stage].[dbo].[TABLE_C] vh WITH (NOLOCK) ON vh.GUID = mrr.VisitGUID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_D] V WITH (NOLOCK) ON V.ClientVisitGUID = vh.GUID
LEFT OUTER JOIN [CPROD1].[dbo].[TABLE_E] pres WITH (NOLOCK) ON pres.PrescriptionID =bo.PrescriptionID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_F] pre WITH (NOLOCK) ON pre.PrescriptionID = pres.PrescriptionID
SELECT
10
,43
,655
,MedRecReviewID
,NULL
,NULL
,0
FROM (
SELECT DISTINCT bo.MedRecReviewID
FROM #MedRecReviewID bo
WHERE bo.MedRecReviewDetailID IN (
SELECT MedRecReviewDetailID
FROM #MedRecReviewDetailID
EXCEPT
(
SELECT MedRecReviewDetailID FROM [PM_PROD_AcuteCare].[dbo].[TABLE_G] v WITH (NOLOCK)
UNION all -- UNION
SELECT MedRecReviewDetailID FROM [PM_PROD_StageCDC].dbo.[TABLE_H] WITH (NOLOCK)
)
)
AND bo.MedRecReviewID NOT IN (SELECT GUID FROM [dbo].[TABLE_I] WITH (NOLOCK) WHERE AuditTableID=43 AND AuditRunId = 10 )
) x
That’s why I asked if those two code blocks were essentially the same query – because if they are, then you can substitute both of them with a single #temp table containing MedRecReviewID and MedRecReviewDetailID, like this:
SELECT DISTINCT bo.MedRecReviewID, mrr.MedRecReviewDetailID
INTO #MedRecReviewID
FROM [CPROD1].dbo.TABLE_A bo WITH (NOLOCK)
INNER JOIN [CPROD1].[dbo].[TABLE_B] mrr WITH (NOLOCK) ON mrr.MedRecReviewID = bo.MedRecReviewID
INNER JOIN [PM_PROD_Stage].[dbo].[TABLE_C] vh WITH (NOLOCK) ON vh.GUID = bo.VisitGUID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_D] V WITH (NOLOCK) ON V.ClientVisitGUID = vh.GUID
LEFT OUTER JOIN [CPROD1].[dbo].[TABLE_E] pres WITH (NOLOCK) ON pres.PrescriptionID = mrr.PrescriptionID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_F] pre ON pre.PrescriptionID = pres.PrescriptionID
SELECT
10
,43
,655
,MedRecReviewID
,NULL
,NULL
,0
FROM (
SELECT DISTINCT bo.MedRecReviewID
FROM #MedRecReviewID bo
WHERE NOT EXISTS (SELECT 1 FROM [PM_PROD_AcuteCare].[dbo].[TABLE_G] v WHERE v.MedRecReviewDetailID = bo.MedRecReviewDetailID)
AND NOT EXISTS (SELECT 1 FROM [PM_PROD_StageCDC].dbo.[TABLE_H] h WHERE h.MedRecReviewDetailID = bo.MedRecReviewDetailID)
AND NOT EXISTS (SELECT 1 FROM [dbo].[TABLE_I] WITH (NOLOCK) WHERE AuditTableID = 43 AND AuditRunId = 10 AND [GUID] = bo.MedRecReviewID)
) x
Since you know that the #temp table contains the MedRecReviewDetailID's associated with MedRecReviewID, you don't have to check for them 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
May 31, 2016 at 3:59 am
Thanks for your reply.
There will be one bo.MedRecReviewID and it can be associated with many bo.MedRecReviewDetailID.
The query is returning 4 rows
1043655187379NULLNULL0
1043655193478NULLNULL0
1043655204245NULLNULL0
1043655213895NULLNULL0
I changed the query by following way but it is returning only 1 record. It took 18 hrs to complete.
1043655211449NULLNULL0
IF OBJECT_ID('tempdb..##temp_MedRecReviewDetailID_11446166') IS NOT NULL
BEGIN
DROP TABLE ##temp_MedRecReviewDetailID_11446166
END
SELECT bo.MedRecReviewDetailID
INTO ##temp_MedRecReviewDetailID_11446166
FROM [CPROD1].[dbo].[TABLE_B] bo WITH (NOLOCK)
INNER JOIN [CPROD1].[dbo].[TABLE_A] mrr WITH (NOLOCK) ON mrr.MedRecReviewID = bo.MedRecReviewID
INNER JOIN [PM_PROD_Stage].[dbo].[TABLE_C] vh WITH (NOLOCK) ON vh.GUID = mrr.VisitGUID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_D] V WITH (NOLOCK) ON V.ClientVisitGUID = vh.GUID
LEFT OUTER JOIN [CPROD1].[dbo].[TABLE_E] pres WITH (NOLOCK) ON pres.PrescriptionID =bo.PrescriptionID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_F] pre WITH (NOLOCK) ON pre.PrescriptionID = pres.PrescriptionID
EXCEPT
(SELECT MedRecReviewDetailID FROM [PM_PROD_AcuteCare].[dbo].[TABLE_G] v WITH (NOLOCK)
UNION
SELECT MedRecReviewDetailID FROM [PM_PROD_StageCDC].[dbo].[TABLE_H] WITH (NOLOCK) )
SELECT
10
,43
,655
,MedRecReviewID
,NULL
,NULL
,0
FROM (SELECT DISTINCT bo.MedRecReviewID
FROM [CPROD1].[dbo].[TABLE_B] bo WITH (NOLOCK)
INNER JOIN ##temp_MedRecReviewDetailID_11446166 tmpM ON tmpM.MedRecReviewDetailID = bo.MedRecReviewDetailID
WHERE NOT EXISTS
(SELECT GUID FROM [dbo].[TABLE_I] WITH (NOLOCK) WHERE GUID = bo.MedRecReviewID AND AuditTableID=43 AND AuditRunId = 10 )
)x
-- Attached require_opimize_the_sql_query_1.txt which is in SQL intended format
May 31, 2016 at 4:02 am
Yes, all those are tables
We are fetching data from different databases and all DBs are on the same server
May 31, 2016 at 4:14 am
Can you post an estimated execution plan for this query please, as a .sqlplan file attachment:
SELECT bo.MedRecReviewID, mrr.MedRecReviewDetailID
INTO #MedRecReviewID
FROM [CPROD1].dbo.TABLE_A bo WITH (NOLOCK)
INNER JOIN [CPROD1].[dbo].[TABLE_B] mrr WITH (NOLOCK) ON mrr.MedRecReviewID = bo.MedRecReviewID
INNER JOIN [PM_PROD_Stage].[dbo].[TABLE_C] vh WITH (NOLOCK) ON vh.GUID = bo.VisitGUID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_D] V WITH (NOLOCK) ON V.ClientVisitGUID = vh.GUID
inner JOIN [CPROD1].[dbo].[TABLE_E] pres WITH (NOLOCK) ON pres.PrescriptionID = mrr.PrescriptionID
INNER JOIN [PM_PROD_AcuteCare].[dbo].[TABLE_F] pre ON pre.PrescriptionID = pres.PrescriptionID
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
May 31, 2016 at 4:17 am
sure, I will check with support team and let you know.
I don't have direct access to the client server.
May 31, 2016 at 8:01 am
1) GUIDS
2) probably unnecessary SORT/DISTINCT for the UNION
3) Most importantly what is the hardware configuration?
4) Have you done file IO stall and wait stats analysis during these hours-long runs?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 31, 2016 at 8:26 am
Thanks Kevin for reply
3) Most importantly what is the hardware configuration?
-- Which are the particular hardware configuration parameters you require for further suggestion
4) Have you done file IO stall and wait stats analysis during these hours-long runs?
-- No, I have not done
May 31, 2016 at 8:28 am
Thanks for reply
3) Most importantly what is the hardware configuration?
-- Which are the particular hardware configuration parameters you require to further suggestions.
4) Have you done file IO stall and wait stats analysis during these hours-long runs?
-- NO, I have not done
May 31, 2016 at 9:14 am
vijay.vesanekar (5/31/2016)
Thanks Kevin for reply3) Most importantly what is the hardware configuration?
-- Which are the particular hardware configuration parameters you require for further suggestion
4) Have you done file IO stall and wait stats analysis during these hours-long runs?
-- No, I have not done
RAM, CPU, and IO config at a minimum. Also note if it is virtualized or not. Also, what is the size of the database tables you are hitting?
You MUST do differential file IO stall and differential wait stats analysis here. You need to see what is happening to the box while the query is running. Take a snapshot of the DMVs for those two things, wait for 3-5 minutes, take another snapshot then diff the two sets and divide by time.
You should also use sp_whoisactive to determine what the query is doing and waiting on while it is running. It has many parameters - learn to use them (Adam Machanic has a 30-day blog post series on it). What may help you here is get_additional_info = 2 and setting a delay interval of maybe 60-120 seconds.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 31, 2016 at 10:18 am
Maybe a bit off topic but nobody else mentioned all those NOLOCK hints. That hint may make your queries a little bit faster but they do come with some very serious ramifications. Might be worth reading up about that hint before you litter your entire database with it. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/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/
May 31, 2016 at 10:21 am
Sean Lange (5/31/2016)
Maybe a bit off topic but nobody else mentioned all those NOLOCK hints. That hint may make your queries a little bit faster but they do come with some very serious ramifications. Might be worth reading up about that hint before you litter your entire database with it. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
Kinda silly to point those out for a query that runs for a day, right? What system out there can accept zero DML for that long? :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply