August 3, 2010 at 6:53 pm
I have a performance issue with a stored procedure that contains correlated subqueries that get data in XML format and put that data into a standard Query. Here is an excerpt from the stored procedure:
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,
DSUSER.BDP_WPS_Approvals(WPS.lID) As APPROVALS,
DSUSER.BDP_WPS_Monitors(WPS.lID) 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_
WHERE WPS.lWorkPackageID= @lWorkPackageID
The two functions BDP_WPS_Approvals and BDP_WPS_Monitors are simular and return XML data (only real difference is the linking table used). Here is one of the two functions:
CREATE FUNCTION [DSUSER].[BDP_WPS_Approvals] (@lWPSID AS INT)
RETURNS varchar(8000)
AS
BEGIN
return (
SELECT
a.lUserID ID,
a.lApprovalState ApprovalState,
a.dtDate ApprovedWhen,
a.sReason ApprovedWhy
FROM tblApproval a
JOIN tblApproval_BDP_WPS wps on a.lID = wps.lApprovalID
WHERE lItemID = @lWPSID
FOR XML PATH('Approvals'), ELEMENTS('User'))
END
This function is the source of the performance issue, once commented out the query is instant, otherwise it takes 15 to 25 seconds. Changing the Database structure is not an option. SQL Server 2005 is the minimum supported version that this query must support. Thanks for any insights and approaches to the problem.
August 3, 2010 at 8:36 pm
Matthew,
Realize that a function like this is going to be called for every record in the result set. It would probably be better to add a column to the output of this query so that you can then join against it in the from clause. This may require your grouping by that column.
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 4, 2010 at 4:21 am
Also you can pre-create a #temp table with approval as XML colum for each item id and you can join in your main query later.
Somthing like this..
Select
lItemID,
[ApprovalXML] = (
SELECT
a.lUserID ID,
a.lApprovalState ApprovalState,
a.dtDate ApprovedWhen,
a.sReason ApprovedWhy
FROM
tblApproval a
JOIN tblApproval_BDP_WPS wps on
a.lID = wps.lApprovalID
WHERE
lItemID = A.lItemID
FOR XML PATH('Approvals'), ELEMENTS('User')
)
Into
#TempApproval
From
(
SELECT
Distinct
lItemID
FROM
tblApproval a
JOIN tblApproval_BDP_WPS wps on
a.lID = wps.lApprovalID
) A
August 4, 2010 at 6:46 am
WayneS - Thank you for the information, it makes a lot of sense to post the scripts to allow people to help me with this issue and I will in the future.
Fredy James - I tried using your implementation however there seems to be a syntax error, I fixed the ones I knew how to but when I run the query it returns:
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'ELEMENTS'.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'A'.
Is this because it thinks that multiple records will be returned in the subquery?
Here is my modifed SQL:
Select
lItemID,
[ApprovalXML] = (
SELECT
a.lUserID ID,
a.lApprovalState ApprovalState,
a.dtDate ApprovedWhen,
a.sReason ApprovedWhy
FROM
dsuser.tblApproval a
JOIN dsuser.tblApproval_BDP_WPS wps on
a.lID = wps.lApprovalID
WHERE
lItemID = A.lID
FOR XML PATH('Approvals'), ELEMENTS('User')
)
Into
#TempApproval
From
(
SELECT
Distinct
lItemID
FROM
dsuser.tblApproval a
JOIN dsuser.tblApproval_BDP_WPS wps on
a.lID = wps.lApprovalID
) A
Table Create SQL:
/****** Object: Table [tblApproval] Script Date: 08/04/2010 08:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tblApproval](
[lID] [int] IDENTITY(1,1) NOT NULL,
[lUserID] [int] NOT NULL,
[dtDate] [datetime] NULL,
[sReason] [varchar](4096) NULL,
[lApprovalState] [int] NULL,
CONSTRAINT [PK_tblApproval] PRIMARY KEY CLUSTERED
(
[lID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [tblApproval_BDP_WPS] Script Date: 08/04/2010 08:12:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [tblApproval_BDP_WPS](
[lApprovalID] [int] NOT NULL,
[lItemID] [int] NOT NULL,
CONSTRAINT [PK_tblApproval_BDP_WPS] PRIMARY KEY CLUSTERED
(
[lApprovalID] ASC,
[lItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Test Data:
SET IDENTITY_INSERT tblApproval ON
GO
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1345, 4, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1346, 6, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1347, 5, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1348, 2, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1349, 1, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1350, 3, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1351, 4, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1352, 6, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1353, 5, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1354, 2, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1355, 1, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1356, 3, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1357, 4, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1358, 6, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1359, 5, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1360, 2, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1361, 1, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1362, 3, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1363, 4, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1364, 6, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1365, 5, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1366, 2, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1367, 1, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1368, 3, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1369, 4, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1370, 6, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1371, 5, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1372, 2, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1373, 1, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1374, 3, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1375, 4, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1376, 6, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1377, 5, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1378, 2, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1379, 1, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1380, 3, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1381, 4, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1382, 6, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1383, 5, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1384, 2, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1385, 1, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1386, 3, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1387, 4, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1388, 6, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1389, 5, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1390, 2, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1391, 1, 'Jan 1 1900 12:00AM', '', 0)
INSERT INTO tblApproval(lid, lUserId, dtDate, sReason, lApprovalState) VALUES(1392, 3, 'Jan 1 1900 12:00AM', '', 0)
GO
SET IDENTITY_INSERT tblApproval OFF
GO
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1345, 7)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1346, 7)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1347, 7)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1348, 7)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1349, 7)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1350, 7)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1351, 8)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1352, 8)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1353, 8)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1354, 8)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1355, 8)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1356, 8)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1357, 9)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1358, 9)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1359, 9)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1360, 9)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1361, 9)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1362, 9)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1363, 10)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1364, 10)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1365, 10)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1366, 10)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1367, 10)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1368, 10)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1369, 11)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1370, 11)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1371, 11)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1372, 11)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1373, 11)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1374, 11)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1375, 12)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1376, 12)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1377, 12)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1378, 12)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1379, 12)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1380, 12)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1381, 13)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1382, 13)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1383, 13)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1384, 13)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1385, 13)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1386, 13)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1387, 14)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1388, 14)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1389, 14)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1390, 14)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1391, 14)
INSERT INTO tblApproval_BDP_WPS(lApprovalID, lItemID) VALUES(1392, 14)
GO
August 4, 2010 at 6:48 am
Changing the function into an inline-table valued function would also help, I guess...
CREATE FUNCTION [DSUSER].[BDP_WPS_Approvals] (@lWPSID AS INT)
RETURNS TABLE
AS
RETURN
(SELECT
(SELECT
a.lUserID ID,
a.lApprovalState ApprovalState,
a.dtDate ApprovedWhen,
a.sReason ApprovedWhy
FROM tblApproval a
JOIN tblApproval_BDP_WPS wps ON a.lID = wps.lApprovalID
WHERE lItemID = @lWPSID
FOR XML PATH('Approvals'), ELEMENTS
) itvf_APPROVALS
)
So your query would look like
SELECT
...
itvf_APPROVALS As APPROVALS,
DSUSER.BDP_WPS_Monitors(WPS.lID) 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 dbo.[BDP_WPS_Approvals] (WPS.lID)
WHERE WPS.lWorkPackageID= @lWorkPackageID
August 4, 2010 at 6:49 am
The test data I posted was just for the table involved with the subqueries, If I can get something that will allow me to join the data from these two tables (IE it is the lID value from the tblApproval_BDP_WPS table and the XML as specified in the function) that would be optimal.
August 4, 2010 at 8:03 am
LutzM, I implemented your suggestion, however running the query the old way and the your way resulted in the same duration (12 seconds with my test case).
it seems that there may be no way around doing a subquery without a major change to the database structure (which I do not have autherization to do)
August 4, 2010 at 8:33 am
One slight improvement on Lutz's function:
CREATE FUNCTION [DSUSER].[BDP_WPS_Approvals] (@lWPSID AS INT)
RETURNS TABLE
WITH SCHEMABINDING
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 4, 2010 at 10:24 am
Matthew VanDerlofske-459596 (8/4/2010)
LutzM, I implemented your suggestion, however running the query the old way and the your way resulted in the same duration (12 seconds with my test case).it seems that there may be no way around doing a subquery without a major change to the database structure (which I do not have autherization to do)
Please post both queries together with the actual execution plan. Maybe we can find the reason (and a solution)...
@Wayne: You're certainly right! I tend to forget to add the schemabinding... :blush:
August 4, 2010 at 4:35 pm
I updated my two table fuctions to include the "WITH SCHEMABINDING".
Here are the two functions:
ALTER FUNCTION [DSUSER].[BDP_PTS_App_Mon_XML] (@lWPSID AS INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(SELECT
(SELECT
a.lUserID ID,
a.lApprovalState ApprovalState,
a.dtDate ApprovedWhen,
a.sReason ApprovedWhy
FROM dsuser.tblApproval a
JOIN dsuser.tblApproval_BDP_PTS pts ON a.lID = pts.lApprovalID
WHERE lItemID = @lWPSID
FOR XML PATH('User'), root('Approvals')
) approvals,
(SELECT
a.lUserID ID,
a.lAlertMode AlertMode
FROM dsuser.tblMonitor a
JOIN dsuser.tblMonitor_BDP_PTS pts ON a.lID = pts.lMonitorID
WHERE lItemID = @lWPSID
FOR XML PATH('User'), root('Monitors')
) monitors
)
ALTER FUNCTION [DSUSER].[BDP_WPS_App_Mon_XML] (@lWPSID AS INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(SELECT
(SELECT
a.lUserID ID,
a.lApprovalState ApprovalState,
a.dtDate ApprovedWhen,
a.sReason ApprovedWhy
FROM dsuser.tblApproval a
JOIN dsuser.tblApproval_BDP_WPS wps ON a.lID = wps.lApprovalID
WHERE lItemID = @lWPSID
FOR XML PATH('User'), root('Approvals')
) approvals,
(SELECT
a.lUserID ID,
a.lAlertMode AlertMode
FROM dsuser.tblMonitor a
JOIN dsuser.tblMonitor_BDP_WPS wps ON a.lID = wps.lMonitorID
WHERE lItemID = @lWPSID
FOR XML PATH('User'), root('Monitors')
) monitors
)
and here is the Stored Procedure (full)
ALTER PROCEDURE [DSUSER].[procDSGetWorkPackageSlots2]
@lWorkPackageID int, /*required*/
@bIncludeGhosts smallint = 0
AS
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
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 DSUSER.BDP_WPS_App_Mon_XML (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
FROM tblBDP_PACKAGETYPESLOT PTS
CROSS APPLY DSUSER.BDP_PTS_App_Mon_XML (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
August 4, 2010 at 5:45 pm
Hmm.... it seems like the execution plan got lost somewhere in between (2 bytes doesnt sound right...).
Anyway. Step 1 would be to replace UNION with UNION ALL to eliminate a sort distinct operation. But I think it's possible to get rid of the UNION operation in the first place. In order to test the idea I'm having I'd like to have some ready to use table def and sample dato together with the expected result.
Why do you need to have two separate columns for the xml structure of approvals and monitors? Would it be possible to have those two combined in one xml structure?
August 5, 2010 at 5:25 am
I am not sure what happed with the Execution Plan but I will attach it again.
I had never known the difference between an Union and a Union All, thanks for the information.
The two columns are needed for the XML because that is what the code is expecting, it will take several months of dev,testing, alpha, beta, then release to make a code change to recieve just one column with both datum within and we need a fix within the month.
I will get you table defs and sample data asap.
August 5, 2010 at 7:22 am
Attached is everything needed to run the query.
Sample execute:
exec dbo.procDSGetWorkPackageSlots 16927, -1
August 5, 2010 at 8:03 am
Matthew, have tried using the #temp table option. Just came from work.. couldn't participate before.. here is the fixed query. you may have to create the same for monitor as well:
SELECT
BW.lItemID,
[ApprovalXML] = (
SELECT
a.lUserID ID,
a.lApprovalState ApprovalState,
a.dtDate ApprovedWhen,
a.sReason ApprovedWhy
FROM
tblApproval a
JOIN tblApproval_BDP_WPS wps ON
a.lID = wps.lApprovalID
WHERE
wps.lItemID = BW.lItemID
FOR XML PATH('Approvals'), ELEMENTS --('User')
)
INTO
#TempApproval
From
(
SELECT
DISTINCT
wps.lItemID
FROM
tblApproval a
JOIN tblApproval_BDP_WPS wps ON
a.lID = wps.lApprovalID
) BW
August 5, 2010 at 9:10 am
Matthew, do you have an example that will actually trigger both parts of the UNION statement? With the data you provided I would suggest to delete the secon UNION part since it isn't used ... 😉
Also, can you add non-clustered indexes to tblMonitor_BDP_WPS, tblMonitor_BDP_PTS , tblApproval_BDP_WPS and tblApproval_BDP_PTS based on lItemID? It seems like most of the execution time is caused by the table scans of those rather huge tables (more than 2mill rows for tblApproval_BDP_WPS).
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply