February 11, 2010 at 8:50 am
I've got a stored procedure that assembles a list of unique patients and then outer joins this list with a view I constructed that retrieves the most recent date a certain action was taken on the patient's account. It's a left join because not all patients will necessarily have had this particular action in their history. The view I constructed also contains an outer join out of necessity. I'm dealing with extraordinarily small result sets ( < 2000 patients), yet the left outer join is taking almost 3 seconds to process. The view itself runs sub 100ms if I do a simple select * without a joining table. Changing the outer join to an inner join in the main SQL clause gets performance back to the 100ms range. I've attached the .sqlplan files for both the inner join and outer join variations. It appears that the outer join is causing sql server to evaluate 450,000 distinct records in the execution of the view. All tables are indexed on any columns that are referenced in a join or where clauses. Any help would be appreciated.
e**Main SQL clause containing an outer join**
SELECT p.*, pe.* from dbo.[Patient]
LEFT OUTER JOIN [dbo].[viMostRecentPatientEvent] pe
ON p.PatientId = ON pe.PatientId
**View containing an outer join**
CREATE VIEW [dbo].[viMostRecentPatientEvent]
AS
SELECT topPatientEvent.PatientID,
dde.DocDataID,
dde.DocDataEventID,
dde.OrgEventCounter as EventCounter,
coreField.EventType,
dueDateField.DueDate,
statusField.Status,
abbreviation,
dde.DocumentTypeOrgID
FROM dbo.DocDataEvent AS dde
INNER JOIN
DocumentTypeOrg as docType on docType.DocumentTypeOrgID = dde.DocumentTypeOrgID
INNER JOIN
(SELECT p.PatientID, MAX(dde.DocDataEventID) AS DocDataEventID
FROM dbo.DocDataEvent AS dde
INNER JOIN dbo.DocData AS dd ON dde.DocDataID = dd.DocDataID
INNER JOIN S7Qtfi.dbo.PM_PatientAccount AS p ON dd.PatientAccountID = p.PatientAccountID
GROUP BY p.PatientID) AS topPatientEvent ON topPatientEvent.DocDataEventID = dde.DocDataEventID
INNER JOIN
(SELECT ddf.FieldValue AS EventType, ddf.DocDataEventID
FROM dbo.DocDataField AS ddf
INNER JOIN dbo.DocumentFieldOrg AS dfo ON ddf.DocumentFieldOrgID = dfo.DocumentFieldOrgID
INNER JOIN dbo.DocumentField AS df ON dfo.DocumentFieldID = df.DocumentFieldID
WHERE (df.DocumentCoreField = 1)) AS coreField ON coreField.DocDataEventID = dde.DocDataEventID
INNER JOIN
(SELECT ddf.FieldValue AS Status, ddf.DocDataID
FROMdbo.DocDataField AS ddf
INNER JOIN dbo.DocumentFieldOrg AS dfo ON ddf.DocumentFieldOrgID = dfo.DocumentFieldOrgID
INNER JOIN dbo.DocumentField AS df ON dfo.DocumentFieldID = df.DocumentFieldID
WHERE (df.Code = 'status')) AS statusField ON statusField.DocDataID = dde.DocDataID
LEFT OUTER JOIN
(SELECT ddf.FieldValueDateTime AS DueDate, ddf.DocDataID
FROM dbo.DocDataField AS ddf
INNER JOIN dbo.DocumentFieldOrg AS dfo ON ddf.DocumentFieldOrgID = dfo.DocumentFieldOrgID
INNER JOIN dbo.DocumentField AS df ON dfo.DocumentFieldID = df.DocumentFieldID
WHERE (df.Code = 'datedue')) AS dueDateField ON dueDateField.DocDataID = dde.DocDataID
February 11, 2010 at 10:19 am
Most of the query cost seems to be in the Key Lookup for DocDataField.FieldValueDateTime in the last outer join in the view.
Try adding a covering index to avoid the lookup.
I also noticed sometimes that, in this kind of scenario, the query can benefit from some speed improvement when using the CROSS APPLY syntax.
SELECT p.*,
pe.*
from dbo.[Patient]
CROSS APPLY (
SELECT * FROM [dbo].[viMostRecentPatientEvent]
WHERE PatientId = p.PatientId
) AS pe
Without table definition scripts and sample data it's just a shot in the dark, but it could work.
I suggest that you look into the index first of all.
Regards
Gianluca
-- Gianluca Sartori
February 15, 2010 at 11:55 am
Hi Gianluca,
Thanks for taking the time to post. When first troubleshooting this, I started looking at the need for a covering index to avoid the Key Lookup. However, when I looked a little further up the execution plan, I noticed that the real driver for the slow perf was the Nested Loop right before the Key Lookup. The Actual Number of Rows for that Nested Loop is listed as 454,116. I don't have a single table in my database that even comes close to having that many rows. For this particular query, there should be at most a couple of thousand rows to process. This fact is borne out by the Inner Join version of the query, which only iterates over 1300~ records.
Is there any way to figure out how/why SQL is iterating over such an unecessarily large resultset in the Outer Join scenario?
Gianluca Sartori (2/11/2010)
Most of the query cost seems to be in the Key Lookup for DocDataField.FieldValueDateTime in the last outer join in the view.Try adding a covering index to avoid the lookup.
I also noticed sometimes that, in this kind of scenario, the query can benefit from some speed improvement when using the CROSS APPLY syntax.
SELECT p.*,
pe.*
from dbo.[Patient]
CROSS APPLY (
SELECT * FROM [dbo].[viMostRecentPatientEvent]
WHERE PatientId = p.PatientId
) AS pe
Without table definition scripts and sample data it's just a shot in the dark, but it could work.
I suggest that you look into the index first of all.
Regards
Gianluca
February 16, 2010 at 2:26 am
It's hard to tune a query without table definition scripts and indexes.
That said, you could try to get rid of one of the outer joins in your view:
CREATE VIEW [dbo].[viMostRecentPatientEvent]
AS
SELECT topPatientEvent.PatientID,
dde.DocDataID,
dde.DocDataEventID,
dde.OrgEventCounter AS EventCounter,
coreField.EventType,
statusField.DueDate,
statusField.Status,
abbreviation,
dde.DocumentTypeOrgID
FROM dbo.DocDataEvent AS dde
INNER JOIN DocumentTypeOrg AS docType
ON docType.DocumentTypeOrgID = dde.DocumentTypeOrgID
INNER JOIN (
SELECT p.PatientID, MAX(dde.DocDataEventID) AS DocDataEventID
FROM dbo.DocDataEvent AS dde
INNER JOIN dbo.DocData AS dd
ON dde.DocDataID = dd.DocDataID
INNER JOIN S7Qtfi.dbo.PM_PatientAccount AS p
ON dd.PatientAccountID = p.PatientAccountID
GROUP BY p.PatientID
) AS topPatientEvent
ON topPatientEvent.DocDataEventID = dde.DocDataEventID
INNER JOIN (
SELECT ddf.FieldValue AS EventType, ddf.DocDataEventID
FROM dbo.DocDataField AS ddf
INNER JOIN dbo.DocumentFieldOrg AS dfo
ON ddf.DocumentFieldOrgID = dfo.DocumentFieldOrgID
INNER JOIN dbo.DocumentField AS df
ON dfo.DocumentFieldID = df.DocumentFieldID
WHERE (df.DocumentCoreField = 1)
) AS coreField
ON coreField.DocDataEventID = dde.DocDataEventID
INNER JOIN (
SELECT ddf.DocDataID,
MAX(ddf.FieldValue) AS Status,
MAX(ddf.FieldValueDateTime) AS DueDate
FROM dbo.DocDataField AS ddf
INNER JOIN dbo.DocumentFieldOrg AS dfo
ON ddf.DocumentFieldOrgID = dfo.DocumentFieldOrgID
INNER JOIN dbo.DocumentField AS df
ON dfo.DocumentFieldID = df.DocumentFieldID
WHERE df.Code IN ('status', 'datedue')
GROUP BY ddf.DocDataID
) AS statusField
ON statusField.DocDataID = dde.DocDataID
Once again, it's a shot in the dark. Can you provide table scripts with indexes and some sample data?
-- Gianluca Sartori
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply