July 21, 2017 at 3:49 am
Hi all
I've got a query that runs (outside a stored procedure) in around 8 seconds to return 45609 records.
I've converted it to a stored procedure to match everything else we're doing (it's load a local data warehouse from some of the worst 3rd-party tables you've ever seen, but the INSERT will be added later).
Converting the query to a procedure seems to kill the performance. It went from 8 seconds as a standard query to upwards for 20 minutes (when I decided I'd had enough waiting and stopped it!).
There are no parameters (so no parameter sniffing).
Apart from one being a query and one being a stored procedure, the two sets of code are absolutely identical.
Both query are using a linked server as the original tables are on server A and the code is on server B.
I've checked the Activity Monitor on server A while the stored procedure is running and I can see a lot of ASYNC_NETWORK_IO waits (which don't happen with the original query).
Anyone any ideas off the top of their heads?
July 21, 2017 at 3:53 am
Can you post both execution plans please? Actual plans, not estimated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2017 at 4:04 am
Hi Gail
Here is the execution plan for the query.
I'll post the stored procedure one when it eventually finishes.
I can let you have the estimated one for the stored procedure it it'll be any help (while I'm waiting for the procedure to finish, could be a while)?
::edit:: The stored procedure is still running after 26 minutes and I'm not hopeful of it finishing any time soon.
July 21, 2017 at 4:18 am
Can you also post the actual query, it is truncated in the execution plan.
😎
July 21, 2017 at 4:23 am
Hi Eirikur
Here's the query (it's a basic SELECT for now):-SELECT
[ECAttendanceLocalID] = ra_m.AccountNumber
,PatientLocalID = hr_mrn.PrefixMedicalRecordNumber
,DiagnosisSeqID = COALESCE(dp.SortOrder,dp2.SortOrder)
,ProblemInstanceID = COALESCE(dp.ProblemInstanceID,dp2.[ProblemID])
,SnomedDesc = COALESCE(mpp_m.Name,mpp_m2.Name)
,UkProblemCondition = COALESCE(mpp_u.UkProblemCondition,mpp_u2.UkProblemCondition)
,UkProblemSubAnalysis = MAX(COALESCE(mpp_u.UkProblemSubAnalysis,mpp_u2.UkProblemSubAnalysis))
,UkProblemArea = MAX(COALESCE(mpp_u.UkProblemArea,mpp_u2.UkProblemArea))
,UkProblemSide = MAX(COALESCE(mpp_u.UkProblemSide,mpp_u2.UkProblemSide))
,DiagnosisComments = CASE
WHEN RIGHT(RTRIM(TextLines.Comments),2) = ' |' THEN LEFT(TextLines.Comments,LEN(TextLines.Comments) - 2)
ELSE TextLines.Comments
END
,RowUpdateDateTime = PL.LatestRowUpdateDateTime
FROM
[pl_MT_EC_Diagnosis] PL
INNER JOIN [MEDITECHDR01-M1].livefocdb_daily.dbo.RegAcct_Main ra_m
ON PL.SourceID = ra_m.SourceID
AND PL.VisitID = ra_m.VisitID
INNER JOIN (SELECT
d.SourceID
,d.EmrDocDataID
,d.VisitID
,d.Status
,d.Document_EmrDocID
FROM
[MEDITECHDR01-M1].livefocdb_daily.dbo.EmrDocData_Main d
INNER JOIN (SELECT
SourceID
,VisitID
,MAX(EmrDocDataID) AS MaxDoc
FROM
[MEDITECHDR01-M1].livefocdb_daily.dbo.EmrDocData_Main
WHERE
Status = 'Signed'
AND Document_EmrDocID = 'ED.GPLET'
GROUP BY
SourceID
,VisitID) latestdocument
ON d.SourceID = latestdocument.SourceID
AND d.EmrDocDataID = latestdocument.MaxDoc) docs
ON ra_m.SourceID = docs.SourceID
AND ra_m.VisitID = docs.VisitID
LEFT JOIN [MEDITECHDR01-M1].livefocdb_daily.dbo.EmrDocData_ProblemInstance dp
ON docs.SourceID = dp.SourceID
AND docs.EmrDocDataID = dp.EmrDocDataID
LEFT JOIN [MEDITECHDR01-M1].[livefocdb_daily].[dbo].[EdmAcct_Problems] dp2
ON docs.SourceID = dp2.SourceID
AND docs.VisitID = dp2.VisitID
LEFT JOIN [MEDITECHDR01-M1].[livefocdb_daily].[dbo].[MisPatProblem_UkAccidentEmergCodes] mpp_u
ON dp.[ProblemInstanceID] = mpp_u.MisPatProblemID
AND dp.SourceID = mpp_u.SourceID
LEFT JOIN [MEDITECHDR01-M1].[livefocdb_daily].[dbo].[MisPatProblem_UkAccidentEmergCodes] mpp_u2
ON dp2.ProblemID = mpp_u2.MisPatProblemID
AND dp2.SourceID = mpp_u2.SourceID
LEFT JOIN [MEDITECHDR01-M1].livefocdb_daily.dbo.[MisPatProblem_Main] mpp_m
ON dp.[ProblemInstanceID] = mpp_m.MisPatProblemID
AND dp.SourceID = mpp_m.SourceID
LEFT JOIN [MEDITECHDR01-M1].livefocdb_daily.dbo.[MisPatProblem_Main] mpp_m2
ON dp2.ProblemID = mpp_m2.MisPatProblemID
AND dp2.SourceID = mpp_m2.SourceID
LEFT JOIN [MEDITECHDR01-M1].livefocdb_daily.dbo.HimRec_MedicalRecordNumbers hr_mrn
ON ra_m.SourceID = hr_mrn.SourceID
AND ra_m.PatientID = hr_mrn.PatientID
AND hr_mrn.MrnPrefixID = 'RU'
LEFT OUTER JOIN (SELECT
mult1.[SourceID]
,mult1.[PatientID]
,mult1.[ProblemID]
,REPLACE(REPLACE(REPLACE(REPLACE((SELECT
TextLine + ' | '
FROM
[MEDITECHDR01-M1].livefocdb_daily.dbo.EmrPat_Problems_ProblemComment mult2
WHERE
mult2.SourceID = mult1.SourceID
AND mult2.PatientID = mult1.PatientID
AND mult2.ProblemID = mult1.ProblemID
ORDER BY
mult2.PatientID
,mult2.ProblemID
FOR XML PATH (''))
,'
',''),'&','&'),CHAR(13),''),CHAR(10),'') AS Comments
FROM
[MEDITECHDR01-M1].livefocdb_daily.dbo.EmrPat_Problems_ProblemComment mult1
GROUP BY
mult1.[SourceID]
,mult1.[PatientID]
,mult1.[ProblemID]) TextLines
ON TextLines.[SourceID] = COALESCE(dp.[SourceID],dp2.[SourceID])
AND TextLines.[PatientID] = ra_m.[PatientID]
AND TextLines.ProblemID = COALESCE(dp.ProblemInstanceID,dp2.[ProblemID])
WHERE
-- docs.Document_EmrDocID = 'ED.GPLET'
-- AND docs.Status = 'Signed'
-- AND CONVERT(DATE,ra_m.ArrivalDateTime) >= '20161102' --> @LastUpdateDateTime
COALESCE(mpp_u.UkProblemCondition,mpp_u2.UkProblemCondition) IS NOT NULL
GROUP BY
ra_m.AccountNumber
,hr_mrn.PrefixMedicalRecordNumber
,COALESCE(dp.SortOrder,dp2.SortOrder)
,COALESCE(dp.ProblemInstanceID,dp2.[ProblemID])
,COALESCE(mpp_m.Name,mpp_m2.Name)
,COALESCE(mpp_u.UkProblemCondition,mpp_u2.UkProblemCondition)
,CASE
WHEN RIGHT(RTRIM(TextLines.Comments),2) = ' |' THEN LEFT(TextLines.Comments,LEN(TextLines.Comments) - 2)
ELSE TextLines.Comments
END
,PL.LatestRowUpdateDateTime
The process list table at the start of the FROM clause is a synonym which points to the correct table on the linked server.
July 21, 2017 at 4:42 am
Something to try...
Linked servers have estimation problems if the linked server isn't using a sysadmin account (which it shouldn't be)
Try inserting the data you need from the linked servers into temp tables (NOT table variables), then use the temp tables in the query. Might help.
Alternately, if there are lots of tables coming from a single linked server, consider using OPENQUERY to pass an entire subset of the query to the remote server for execution.
Also, this prevents any index usage
COALESCE(mpp_u.UkProblemCondition,mpp_u2.UkProblemCondition) IS NOT NULL
Consider something like
mpp_u.UkProblemCondition,mpp_u2 Is NOT NULL OR mpp_u2.UkProblemCondition IS NOT NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2017 at 4:59 am
Hi Gail
The linked servers are using an account with SysAdmin privileges. It's a SQL-only login.
I'll alter the WHERE clause with your suggestion.
Unfortunately, the SP is still running (nearly 2 hours).
Thanks
::edit::
On a side-note, I've found the culprit!!!
I commented out this section (in a copy of the SP):- --LEFT OUTER JOIN (SELECT
-- mult1.[SourceID]
-- ,mult1.[PatientID]
-- ,mult1.[ProblemID]
-- ,REPLACE(REPLACE(REPLACE(REPLACE((SELECT
-- TextLine + ' | '
-- FROM
-- [MEDITECHDR01-M1].livefocdb_daily.dbo.EmrPat_Problems_ProblemComment mult2
-- WHERE
-- mult2.SourceID = mult1.SourceID
-- AND mult2.PatientID = mult1.PatientID
-- AND mult2.ProblemID = mult1.ProblemID
-- ORDER BY
-- mult2.PatientID
-- ,mult2.ProblemID
-- FOR XML PATH (''))
-- ,'
',''),'&','&'),CHAR(13),''),CHAR(10),'') AS Comments
-- FROM
-- [MEDITECHDR01-M1].livefocdb_daily.dbo.EmrPat_Problems_ProblemComment mult1
-- GROUP BY
-- mult1.[SourceID]
-- ,mult1.[PatientID]
-- ,mult1.[ProblemID]) TextLines
-- ON TextLines.[SourceID] = COALESCE(dp.[SourceID],dp2.[SourceID])
-- AND TextLines.[PatientID] = ra_m.[PatientID]
-- AND TextLines.ProblemID = COALESCE(dp.ProblemInstanceID,dp2.[ProblemID])
to remove the comments text and it returned the data in 10 seconds (with the same number of records).
So, just need to figure out why that particular section is causing all the issues......
July 21, 2017 at 6:52 am
Gail has pretty much given you the answer to why that section is slow.
The coalesce on the join condition means indexes can't be used. The real problem is all those cross-server calls in the left join subquery. The problem is that the processing is happening on the calling server, so it essentially has to pull back the data across the network for each table and then do the work. If you change that section to leverage OPENQUERY to query the remote server instead (all the tables in the one OPENQUERY), the processing will happen on the other side and only the data you need will go over the network.
I had the same type of problem and changing the cross-server call to leverage OPENQUERY made a huge different in the speed of the query.
I'm sure someone will correct me if the details aren't quite right.
July 21, 2017 at 10:54 am
Do you have the sp's plan yet?
😎
July 21, 2017 at 11:54 am
richardmgreen1 - Friday, July 21, 2017 4:59 AMHi GailThe linked servers are using an account with SysAdmin privileges. It's a SQL-only login.
I'll alter the WHERE clause with your suggestion.Unfortunately, the SP is still running (nearly 2 hours).
Thanks
::edit::
On a side-note, I've found the culprit!!!
I commented out this section (in a copy of the SP):---LEFT OUTER JOIN (SELECT
-- mult1.[SourceID]
-- ,mult1.[PatientID]
-- ,mult1.[ProblemID]
-- ,REPLACE(REPLACE(REPLACE(REPLACE((SELECT
-- TextLine + ' | '
-- FROM
-- [MEDITECHDR01-M1].livefocdb_daily.dbo.EmrPat_Problems_ProblemComment mult2
-- WHERE
-- mult2.SourceID = mult1.SourceID
-- AND mult2.PatientID = mult1.PatientID
-- AND mult2.ProblemID = mult1.ProblemID
-- ORDER BY
-- mult2.PatientID
-- ,mult2.ProblemID
-- FOR XML PATH (''))
-- ,' ',''),'&','&'),CHAR(13),''),CHAR(10),'') AS Comments
-- FROM
-- [MEDITECHDR01-M1].livefocdb_daily.dbo.EmrPat_Problems_ProblemComment mult1
-- GROUP BY
-- mult1.[SourceID]
-- ,mult1.[PatientID]
-- ,mult1.[ProblemID]) TextLines
-- ON TextLines.[SourceID] = COALESCE(dp.[SourceID],dp2.[SourceID])
-- AND TextLines.[PatientID] = ra_m.[PatientID]
-- AND TextLines.ProblemID = COALESCE(dp.ProblemInstanceID,dp2.[ProblemID])to remove the comments text and it returned the data in 10 seconds (with the same number of records).
So, just need to figure out why that particular section is causing all the issues......
1) You may not have to find out why this is causing an issue. Put the results of the query without this in it into a temp table and then join this mess to that. Oh, and when you do that, create a field in the temp table that IS the output of the COALESCE of the two fields involved and then your join is straight up. THAT should definitely fix your issues right up.
2) I believe others are incorrect in stating that "indexes cannot be used" because of the COALESCE(..) JOIN clause. The correct statement is that indexes cannot be SEEKed should it actually be more efficient to do so. They can be SCANned, which can still be significantly more efficient than a table scan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 21, 2017 at 11:57 am
Are both the ad-hoc query and the stored procedure being called in the same way, or was the stored proc automated? Something to check is that the sessions running these have the same connection properties, such as ARITHABORT which Microsoft says should always be set to ON, as it is in SSMS, even though for SQL Agent and other connections the default is OFF:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql
You can configure an instance to default all connections to ARITHABORT ON using the server properties, connections page, arithmetic abort setting:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-properties-connections-page
July 24, 2017 at 3:40 am
Hi all
I've solved the issue and the entire SP now runs in 14 seconds (and that includes an insert into our working table).
All I've done is to move the entire list of comments into a local temp table (on the receiving server) and then get the latest version from there.
Our current issue is that these comments are stored in documents and you can multiple documents that have been started but not finished.
If anyone knows of a better way of getting the latest document from a list (they are sequentially numbered), I'm all ears (or eyes in this case).
Regards
Richard
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply