OK, I have been working on this view for a while. The view pulls all of the correct information, but it is painfully slow (48 seconds to build a view just under 100k rows). I know at least part of the problem is that the underlying table structure was not well thought out ahead of time and unfortunately we cannot change it... Here is the full query
The parts that I am particularly interested in are the two areas where I have to make subquery calls over and over to separate tables. For instance
Uses the same code twice, once as the main subquery and then again as a nested subquery, and I have to do this like 16 times. I have tried this with a scalar valued function and it slowed it down massively. I have considered breaking this down into multiple steps, creating a few views and then creating an aggregate view, but I haven't been able to see where that would cut any time. Looking at the Execution Plan, there are no big time draws, but there are a bunch at about 3% on tblSpecimenPQ195. That table has a clustered index on ProcessQuestionID and SpecimenID. Any thoughts on what I might try to speed this up a bit?
Thanks,
wb
Code:
SELECT PrimaryTbl.SpecimenID, COALESCE (SecondaryTbl.PptID, PrimaryTbl.PptID) AS pptid,
CASE
WHEN LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 1) = '0' THEN substring((COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid)), 2, 2)
ELSE LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 3)
END AS CollectionSiteID,
s.Abbreviation AS CollectionSiteName,
CASE
WHEN PrimaryTbl.parentspecimenid = '' THEN 'Parent'
ELSE 'Child'
END AS parentorchild,
PrimaryTbl.SpecimenSourceID,
(SELECT ValueName
FROM dbo.tblMetaElementValues
WHERE (ElementID = 8) AND (Active = 1) AND (Value = PrimaryTbl.SpecimenSourceID)) AS SpecimenSourceName,
PrimaryTbl.SpecimenStoredID,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_18
WHERE (ElementID = 9) AND (Active = 1) AND (Value = PrimaryTbl.SpecimenStoredID)) AS SpecimenStoredName,
PrimaryTbl.Quantity,
CASE
WHEN PrimaryTbl.UnitID = 0 THEN 'unknown'
WHEN PrimaryTbl.UnitID = 1 THEN 'µl'
WHEN PrimaryTbl.UnitId = 2 THEN 'ml'
END AS Unit,
PrimaryTbl.CollectionDate,
PrimaryTbl.Comments,
PrimaryTbl.ParentSpecimenID,
CASE
WHEN PrimaryTbl.PptVisitType = 0 THEN SecondaryTbl.PptVisitType
ELSE PrimaryTbl.PptVisitType
END AS PptVisitType,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_17
WHERE (ElementID = 82) AND (Active = 1) AND
(Value = CASE
WHEN PrimaryTbl.PptVisitType = 0 THEN SecondaryTbl.PptVisitType
ELSE PrimaryTbl.PptVisitType
END)) AS PptVisitTypeName,
PrimaryTbl.PptVisitDate,
PrimaryTbl.CurrentQuantity,
PrimaryTbl.CurrentQuantityUnitID,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_16
WHERE (ElementID = 47) AND (Active = 1) AND (Value = PrimaryTbl.CurrentQuantityUnitID)) AS CurrentQuantityUnitName,
PrimaryTbl.Concentration,
PrimaryTbl.ConcentrationUnitID,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_15
WHERE (ElementID = 42) AND (Active = 1) AND (Value = PrimaryTbl.ConcentrationUnitID)) AS ConcentrationUnitName,
PrimaryTbl.NumOfThawed,
L.SiteID AS StorageLabID,
L.LabNameShort AS StorageLabName,
PrimaryTbl.RefrigeratorID,
PrimaryTbl.BoxID,
PrimaryTbl.Row,
PrimaryTbl.Col,
--if specimenstoredid in 2,35,9, then fasting needs to be inherited from the parent
Case
WHEN PrimaryTbl.specimenstoredid in (2,9,35) THEN SecondaryTbl.Fasting
ELSE PrimaryTbl.Fasting
END AS Fasting,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_14
WHERE (ElementID = 434) AND (Active = 1) AND (Value = Case
WHEN PrimaryTbl.specimenstoredid in (2,9,35) THEN SecondaryTbl.Fasting
ELSE PrimaryTbl.Fasting
END)) AS FastingName,
PrimaryTbl.Status,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_13
WHERE (ElementID = 29) AND (Active = 1) AND (Value = PrimaryTbl.Status)) AS StatusName,
PrimaryTbl.ReasonOfChange,
PrimaryTbl.ReasonOfDefective,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_12
WHERE (ElementID = 61) AND (Active = 1) AND (Value = PrimaryTbl.ReasonOfDefective)) AS ReasonOfDefectiveName,
PrimaryTbl.OtherReasonOfDefective,
PrimaryTbl.TubeSize,
PrimaryTbl.MainSelected,
(SELECT Answer
FROM dbo.tblSpecimenPQ195
WHERE (ProcessQuestionID = 422) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimensFrozenAtMinus70OrColder,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_11
WHERE (ElementID = 422) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_26
WHERE (ProcessQuestionID = 422) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimensFrozenAtMinus70OrColderName,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_25
WHERE (ProcessQuestionID = 431) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS PQComments,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_24
WHERE (ProcessQuestionID = 435) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimenPlacedImmedOnIceOrAt4Celsius,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_10
WHERE (ElementID = 435) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_23
WHERE (ProcessQuestionID = 435) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimenPlacedImmedOnIceOrAt4CelsiusName,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_22
WHERE (ProcessQuestionID = 438) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS FreezerNumberSpecimenStoredAt,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_21
WHERE (ProcessQuestionID = 439) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimenHemolyedLipemicIcteric,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_9
WHERE (ElementID = 439) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_20
WHERE (ProcessQuestionID = 439) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimenHemolyedLipemicIctericName,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_19
WHERE (ProcessQuestionID = 442) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS TimeFromCollectionToFreezingHours,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_18
WHERE (ProcessQuestionID = 443) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS EquipmentFailureDeviationFromProtocol,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_8
WHERE (ElementID = 443) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_17
WHERE (ProcessQuestionID = 443) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS EquipmentFailureDeviationFromProtocolName,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_16
WHERE (ProcessQuestionID = 450) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS VolumeRawUrineProcessed,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_15
WHERE (ProcessQuestionID = 451) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimenInverted,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_7
WHERE (ElementID = 451) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_14
WHERE (ProcessQuestionID = 451) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimenInvertedName,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_13
WHERE (ProcessQuestionID = 454) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimenProcessedWithin,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_6
WHERE (ElementID = 454) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_12
WHERE (ProcessQuestionID = 454) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimenProcessedWithinName,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_11
WHERE (ProcessQuestionID = 455) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS TissuePosition,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_5
WHERE (ElementID = 455) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_10
WHERE (ProcessQuestionID = 455) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS TissuePositionName,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_9
WHERE (ProcessQuestionID = 456) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS StorageTemperature,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_4
WHERE (ElementID = 456) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_8
WHERE (ProcessQuestionID = 456) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS StorageTemperatureName,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_7
WHERE (ProcessQuestionID = 457) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS ProcessingMethod,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_3
WHERE (ElementID = 457) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_6
WHERE (ProcessQuestionID = 457) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS ProcessingMethodName,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_5
WHERE (ProcessQuestionID = 458) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS PathologyDeptBlockNumber,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_4
WHERE (ProcessQuestionID = 459) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS PrimaryTissueType,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_2
WHERE (ElementID = 459) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_3
WHERE (ProcessQuestionID = 459) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS PrimaryTissueTypeName,
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_2
WHERE (ProcessQuestionID = 460) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimenProcessedProtocol,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_1
WHERE (ElementID = 460) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_1
WHERE (ProcessQuestionID = 460) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimenProcessedProtocolName
FROM dbo.tblSpecimenBank195 AS PrimaryTbl
LEFT OUTER JOIN dbo.tblSpecimenBank195 AS SecondaryTbl ON PrimaryTbl.ParentSpecimenID = SecondaryTbl.SpecimenID
INNER JOIN NewCompass.dbo.tblSite AS s ON s.SiteID =
(CASE
WHEN LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 1)
= '0' THEN substring((COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid)), 2, 2)
ELSE LEFT(COALESCE (SecondaryTbl.pptid, PrimaryTbl.pptid), 3)
END) LEFT JOIN tblLabs L on PrimaryTbl.LabID=L.LabID
Code:
(SELECT Answer
FROM dbo.tblSpecimenPQ195
WHERE (ProcessQuestionID = 422) AND (SpecimenID = PrimaryTbl.SpecimenID)) AS SpecimensFrozenAtMinus70OrColder,
(SELECT ValueName
FROM dbo.tblMetaElementValues AS tblMetaElementValues_11
WHERE (ElementID = 422) AND (Value =
(SELECT Answer
FROM dbo.tblSpecimenPQ195 AS tblSpecimenPQ195_26
WHERE (ProcessQuestionID = 422) AND (SpecimenID = PrimaryTbl.SpecimenID)))) AS SpecimensFrozenAtMinus70OrColderName,Thanks,
wb
