June 8, 2009 at 4:55 am
One of my developers has written a table-valued function that:
1. Creates a temporary table from a SELECT based on parameters passed to the function
2. Joins the temporary table to a CTE to produce the output table
The function runs very slowly (75 secs to produce 15,000 rows). However part 1 run on its own runs in less than a second. If I write the rows produced in part 1 to a database table, and do the join as per part 2, then that also runs in less than a second.
The execution plan indicates that it is part 1 that is causing the problem, but the plan itself looks reasonable (uses the indexes I would expect) and looks the same as if I run part 1 on its own. Running part 1 to produce the database table took less than a second.
I can upload the code if required, though it's a little messy.
Has anyone come across this behaviour before and is there a workaround?
Thanks
June 8, 2009 at 5:04 am
Hi Derek,
Could you post the code with some sample table and table/index definitions
And the code that runs you TVF as well please 🙂
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 8, 2009 at 5:21 am
Here is the code, extracted from the function for testing purposes but it displays the same behaviour:
DECLARE@RESULT
TABLE(TMacIDINT, IOID INT, DateAndTime DateTime, Data REAL, TagVARCHAR(20),
IOName VARCHAR(100), TMacName VARCHAR(100),UOMNameVARCHAR(50),
IsDigital INT, ErrorDetected INT, LowSetpoint REAL,HighsetPoint REAL)
DECLARE @IOIDList TABLE (IOID INT PRIMARY KEY)
INSERT INTO @IOIDList SELECT [Value] FROM FN_ParseDelimitedStrings('298518',',')
--SELECT 1
--DECLARE @IOData TABLE(IOID INT, DateAndTime DATETIME, Data INT, IsDigital INT, RowIndex INT)
--INSERT INTO @IODATA
--SELECT iod.IOID, s.DateAndTime, Data = CASE WHEN ad.SnapshotID IS NULL THEN dd.data ELSE ad.Data END,
-- IsDigital = CASE WHEN ad.SnapshotID IS NULL THEN 1 ELSE 0 END,
-- ROW_NUMBER () OVER (ORDER BY iod.IOID, s.DateAndTime)
--FROM IODefinitions AS iod
-- INNER JOIN @IOIDList iolist ON iolist.IOID = iod.IOID
-- INNER JOIN Snapshots AS s ON s.TMacID = iod.TMacID AND DateAndTime BETWEEN '2009-05-01' AND '2009-06-01'
-- LEFT OUTER JOIN AnalogData ad ON ad.Snapshotid = s.snapshotid AND ad.IOID = iod.IOID
-- LEFT OUTER JOIN DigitalData dd ON dd.Snapshotid = s.snapshotid AND dd.IOID = iod.IOID
--WHERE
-- NOT (ad.Data IS NULL AND dd.Data IS NULL)
-- AND (iod.TMacSideLiveLogFlag = 0) --
--SELECT 2
;
WITH Details_CTE
As
(SELECT iod.IOID, tm.TmacID, iod.Tag, iod.IOName, tm.TMacName, UOMHeader.UOMName, iod.LowSetpoint, iod.HighsetPoint
FROM IODefinitions AS iod
INNER JOIN @IOIDList iolist ON iolist.IOID = iod.IOID
INNER JOIN TMacUnits AS tm ON tm.TMacID = iod.TMacID
LEFT OUTER JOIN UOMHeader ON iod.UOMID = UOMHeader.UOMID
WHERE (iod.TMacSideLiveLogFlag = 0) )
INSERT INTO @RESULT
SELECTdetails.TmacID, details.IOID, D1.DateAndTime, d2.Data - d1.Data AS Data, details.Tag,
details.IOName,details.TMacName, details.UOMName, D1.IsDigital, NULL,
details.LowSetpoint, details.HighSetpoint
FROMTemp.IOData AS D1 INNER JOIN Temp.IOData AS D2 ON (D1.rowindex = D2.rowindex -1) AND (D1.IOId=D2.IOID)
--FROM@IOData AS D1 INNER JOIN Temp.IOData AS D2 ON (D1.rowindex = D2.rowindex -1) AND (D1.IOId=D2.IOID)
INNER JOIN Details_CTE AS details ON D1.IOID = details.IOID
ORDER BY details.IOID, D1.DateAndTime ASC
SELECT * FROM @Result
The table Temp.IOData contains exactly the same rows as @IOData if you uncomment out the lines from 'SELECT 1' and 'SELECT 2'. If you run just the code from 'SELECT 1' to 'SELECT 2' it runs in less than a second. If you run the select that includes the CTE but joining to Temp.IOData it runs in less than a second. If you change that select so that it joins the CTE to @IOData then it takes 70 seconds, and the execution plan (and the fact that the output from SELECT 2 takes 70 seconds to appear) indicates that it is the creation of @IOData that takes the time.
Some of the data is a bit sensitive, so may take some time to appear (after I've scrambled the sensitive columns).
Cheers
Derek
June 8, 2009 at 5:37 am
Sorry I'm a bit confused.
If all the code that you posted is uncommented is all of that code inside your function?
The second thing is you should be aware that having a TVF that is multiline will very often create a slow performing query and the chances are that it will have to run for every row that the function joins to.
Would it not be possible to simply have one select statement that could return your results as a non-multiline function?
for example instead of inserting into the temp table simply construct a subquery?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 8, 2009 at 8:49 am
Yes, the code that was commented out acually runs inside the function. It was commented out so I could test against a database table.
I have taken your advice and collapsed it all into a single line function using sub-queries, and it is now giving sub-second responses.
Thank you very much for your help.
Derek
June 8, 2009 at 9:08 am
happy to help 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 8, 2009 at 11:00 am
This also may have been parameter sniffing. I believe that inline-TVF's are treated as table expressions (like Views and CTEs) and thus would not be susceptible to parameter-sniffing.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2009 at 2:13 am
Thanks Barry, I wasn't aware of that 🙂
Learn't something new for the day .
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 9, 2009 at 4:07 am
After reading some informaion on parameter sniffing I've taken the original query that was causing the problem and found that by using OPTION (RECOMPILE) on one of the SELECTs the response times go back down to sub-second.
Thank you everyone for your help
Derek.
June 9, 2009 at 10:13 am
Glad we could help!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply