Ok we have a simple udf that takes a XML integer list and returns a table:
CREATE FUNCTION [dbo].[udfParseXmlListOfInt]
(
@ItemListXml XML (dbo.xsdListOfInteger)
)
RETURNS TABLE
AS
RETURN
( --- parses the XML and returns it as an int table ---
SELECT ListItems.ID.value('.','INT') AS KeyValue
FROM @ItemListXml.nodes('//list/item') AS ListItems(ID)
)
In a stored procedure we create a temp table using this UDF
INSERT INTO @JobTable
(JobNumber, JobSchedID, JobBatID, StoreID, CustID, CustDivID, BatchStartDate, BatchEndDate, UnavailableFrom)
SELECT JOB.JobNumber,
JOB.JobSchedID,
ISNULL(JOB.JobBatID,0),
STO.StoreID,
STO.CustID,
ISNULL(STO.CustDivID,0),
AVL.StartDate,
AVL.EndDate,
ISNULL(AVL.StartDate, DATEADD(day, -8, GETDATE()))
FROM dbo.udfParseXmlListOfInt(@JobNumberList) TMP
INNER JOIN dbo.JobSchedule JOB ON (JOB.JobNumber = TMP.KeyValue)
INNER JOIN dbo.Store STO ON (STO.StoreID = JOB.StoreID)
INNER JOIN dbo.JobSchedEvent EVT ON (EVT.JobSchedID = JOB.JobSchedID AND EVT.IsPrimary = 1)
LEFT OUTER JOIN dbo.Availability AVL ON (AVL.AvailTypID = 5 AND AVL.RowID = JOB.JobBatID)
ORDER BY JOB.JobSchedID;
For a simple list of 10 JobNumbers in SQL2005 this returns in less than 1 second, in 2008 this run against the exact same data returns in 7 min. This is on a much faster machine with more memory. Any ideas?
