August 5, 2010 at 9:40 am
Yeah sorry the sample data did not include any slots that would be picked up the the union, To get a data set that would get data from that second query simply delete 1 or more entries from the tblBDP_WorkPackageSlot table.
As for the index, I can create one but I am confused on why it is needed. There already is a clustered index with both the lApprovalId (or lMonitorId in the case of monitors) and lItemId. Why do we another index for lItemId, is it not covered under the first index?
August 5, 2010 at 10:27 am
Matthew VanDerlofske-459596 (8/5/2010)
Yeah sorry the sample data did not include any slots that would be picked up the the union, To get a data set that would get data from that second query simply delete 1 or more entries from the tblBDP_WorkPackageSlot table.As for the index, I can create one but I am confused on why it is needed. There already is a clustered index with both the lApprovalId (or lMonitorId in the case of monitors) and lItemId. Why do we another index for lItemId, is it not covered under the first index?
The "problem" with the existing index is: it will not help limiting the data specified in the WHERE condition in your functions.
FROM dbo.tblApproval a
JOIN dbo.tblApproval_BDP_PTS pts ON a.lID = pts.lApprovalID
WHERE lItemID = @lWPSID
Therefore you get a clustered index scan (which is equal to a table scan) for those table followed by an index spool (which is basically the same as creating an index to support the WHERE condition). The current index will not help here since an index is read from left right and lItemID is not the leftmost column in the index.
August 5, 2010 at 11:24 am
Continuing my prev. post: You have an index lApprovalId and lItemId. Using the phone book example your index is sorted by last name (=lApprovalId) and first name (=lItemId).
But your WHERE condition will filter the data by first name (=lItemId). You're trying to find all people with the first name 'Joe'. Therefore, you have to read each and every name in the whole book. If you would create another index for your phone book (ordered by first name, last name) you could find all Joe's instantly.
August 5, 2010 at 11:34 am
Thank you for the explaination, that change has really made the difference, the query now runs on average less then 1 second which is well within acceptable limits.
Thank you for all your help.
August 5, 2010 at 12:04 pm
For the love of the game, I tried your sp without using the functions to see how fast it can run. With test dat you provided, the result was before 00:00:00.325 and after 00:00:00.125.
If you have time, let me know how the sp perform with real data without using the functions.
Cheers
ALTER PROCEDURE [procDSGetWorkPackageSlots]
@lWorkPackageID int, /*required*/
@bIncludeGhosts smallint = 0
AS
--Prepare Approval XML for all the possible values of tblApproval_BDP_WPS.lItemID
SELECT
BW.lItemID,
[ApprovalsXML] = (
SELECT
a.lUserID ID,
a.lApprovalState ApprovalState,
a.dtDate ApprovedWhen,
a.sReason ApprovedWhy
FROM
dbo.tblApproval a
JOIN dbo.tblApproval_BDP_WPS wps ON
a.lID = wps.lApprovalID
WHERE
wps.lItemID = BW.lItemID
FOR XML PATH('User'), root('Approvals')
),
[MonitorsXML] = (
SELECT
a.lUserID ID,
a.lAlertMode AlertMode
FROM
dbo.tblMonitor a
JOIN dbo.tblMonitor_BDP_WPS wps ON
a.lID = wps.lMonitorID
WHERE
wps.lItemID = BW.lItemID
FOR XML PATH('User'), root('Monitors')
)
INTO
#tmpBDP_WPS_App_Mon_XML
From
(
SELECT
DISTINCT
wps.lItemID
FROM
tblApproval a
JOIN tblApproval_BDP_WPS wps ON
a.lID = wps.lApprovalID
) BW
--Prepare Monitor XML for all possible PTS item values
SELECT
BP.lItemID,
[ApprovalsXML] = (
SELECT
a.lUserID ID,
a.lApprovalState ApprovalState,
a.dtDate ApprovedWhen,
a.sReason ApprovedWhy
FROM
dbo.tblApproval a
JOIN dbo.tblApproval_BDP_PTS pts ON
a.lID = pts.lApprovalID
WHERE
pts.lItemID = BP.lItemID
FOR XML PATH('User'), root('Approvals')
),
[MonitorsXML] = (
SELECT
a.lUserID ID,
a.lAlertMode AlertMode
FROM
dbo.tblMonitor a
JOIN dbo.tblMonitor_BDP_PTS pts ON
a.lID = pts.lMonitorID
WHERE
pts.lItemID = BP.lItemID
FOR XML PATH('User'), root('Monitors')
)
INTO
#tmpBDP_PTS_App_Mon_XML
From
(
SELECT
DISTINCT
pts.lItemID
FROM
tblApproval a
JOIN tblApproval_BDP_PTS pts ON
a.lID = pts.lApprovalID
) BP
--------------------------------------------------------------------------
--Main query
SELECT WPS.lID, WPS.lWORKPACKAGEID,
WPS.lPACKAGETYPESLOTID,
WPS.dtDUEDATE,
WPS.lSEQUENCENUMBER,
WPS.lDOCID, WPS.lSTATUS, WPS.dtNEXTALERT,
WPS.bISMANDATORY, WPS.lAPPROVALSREQUIRED,
CASE
WHEN WPS.lDOCID > 0 THEN IsNull(D.STITLE,'<purged>')
ELSE ''
END AS sDOCTITLE,
IsNull(PTS.SNAME,'<OTHER>') AS sNAME,
D.bDELETED_,
D.lCLASS$ AS DOCCLASSID,
D.lPAGES AS DOCNUMPAGES,
D.nTYPE_ AS DOCTYPE,
D.lDOCID_ AS SOURCEDOCID,
-- approvals AS APPROVALS,
-- monitors AS MONITORS
WPS_XML.ApprovalsXML AS APPROVALS,
WPS_XML.MonitorsXML AS MONITORS
FROM tblBDP_WORKPACKAGESLOT WPS
LEFT JOIN tblBDP_PACKAGETYPESLOT PTS ON WPS.lPACKAGETYPESLOTID = PTS.lID
LEFT JOIN tblDOCUMENT D ON WPS.lDOCID = D.lDOCID_
--CROSS APPLY BDP_WPS_App_Mon_XML (WPS.lID)
LEFT JOIN #tmpBDP_WPS_App_Mon_XML WPS_XML ON
WPS_XML.lItemID = WPS.lID
WHERE WPS.lWorkPackageID= @lWorkPackageID
UNION
SELECT -1 AS lID, -1 AS lWORKPACKAGEID,
lID AS lPACKAGETYPESLOTID,
NULL AS dtDUEDATE,
lDEFAULTSEQUENCENUMBER AS lSEQUENCENUMBER,
NULL AS lDOCID,0 AS lSTATUS,NULL AS dtNEXTALERT,
bISMANDATORY, lAPPROVALSREQUIRED,
NULL AS sDOCTITLE,
sNAME,
NULL AS bDELETED_,NULL AS DOCCLASSID,NULL AS DOCNUMPAGES,NULL AS DOCTYPE,NULL AS SOURCEDOCID,
-- approvals AS APPROVALS,
-- monitors AS MONITORS
PTS_XML.ApprovalsXML AS APPROVALS,
PTS_XML.MonitorsXML AS MONITORS
FROM
tblBDP_PACKAGETYPESLOT PTS
--CROSS APPLY BDP_PTS_App_Mon_XML (lID)
LEFT JOIN #tmpBDP_PTS_App_Mon_XML PTS_XML ON
PTS_XML.lItemID = PTS.lID
WHERE lPACKAGETYPEID=(SELECT lPACKAGETYPEID FROM tblBDP_WORKPACKAGE WHERE lID= @lWorkPackageID)
AND PTS.lID NOT IN (SELECT lPACKAGETYPESLOTID FROM tblBDP_WORKPACKAGESLOT WHERE lWORKPACKAGEID= @lWorkPackageID)
AND @bIncludeGhosts <> 0
ORDER BY lWORKPACKAGEID DESC,lSEQUENCENUMBER
go
August 5, 2010 at 12:15 pm
Matthew VanDerlofske-459596 (8/5/2010)
Thank you for the explaination, that change has really made the difference, the query now runs on average less then 1 second which is well within acceptable limits.Thank you for all your help.
Glad I could help 😀
Btw: did you change UNION to UNION ALL as well? Because the execution plan shows a distinct sort as the last operation, as expected.
What you also might want to try: change your UNION ALL query to get all data except the XML column and wrap into a CTE (common table expression or subquery, in this case). Perform the CROSS APPLY operation on the result set by merging your two functions into one function (SELECT statements separated by a CASE statement). I'm not sure if it will perform better but I would give it a try...
August 5, 2010 at 1:55 pm
LutzM: I did change to a union all, I will give your additional suggestion a try later, for now our customer is very happy.
Fredy James: I tried your query against our real database (several million rows) and the duration was 00:01:25 (h,m,s). I have attached the execution plan just for the hell of it.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply