April 28, 2009 at 9:08 am
I have been tasked with addressing a performance problem. At the heart lies a nasty block of Cursor code that basically appends the results of multiple calls to a table-valued function. One of the tvf input parameters is from an earlier SELECT (hence the cursor).
I can't change any of the underlying calls/signatures (as to not affect the calling UI code).
I have attempted to recreate a simplified dummy version of my attempted solution. It seems like I should be able to use some kind of incrementing counter but it's eluding me.
Anybody have any non cursor/loop suggestions here?
/*
First, create the table-valued function....
select * from dbo.tvfGetNumbers (7)
*/
CREATE FUNCTION tvfGetNumbers
(
@startNum tinyint
)
RETURNS
@tbl TABLE
(number tinyint)
AS
BEGIN
INSERT INTO @tbl SELECT @startNum
INSERT INTO @tbl SELECT @startNum + 1
INSERT INTO @tbl SELECT @startNum + 2
RETURN
END
GO
/*
Now call the tvf and append results without a loop/cursor
*/
DECLARE @tblDriver table (RecNo int IDENTITY PRIMARY KEY, StartNum int)
DECLARE @tblResults table (Number int)
DECLARE @ctr int
SET @ctr = 1
INSERT INTO @tblDriver SELECT 1
INSERT INTO @tblDriver SELECT 10
INSERT INTO @tblDriver SELECT 100
--SELECT * FROM @tblDriver
--Note: Next statement doesn't work, but I'd like it to 🙂
INSERT INTO @tblResults
select * from dbo.tvfGetNumbers (SELECT StartNum FROM @tblDriver WHERE @ctr = RecNo = @ctr + 1)
select * from @tblResults
April 28, 2009 at 9:13 am
I'm slightly confused, what are you trying to accomplish with this code?
April 28, 2009 at 9:16 am
Append the results of multiple calls to a table-valued function. The tvf must be called once for each row in @tblDriver with the value of StartNum.
April 28, 2009 at 9:17 am
Would this work?
It requires a tally table but does not require a function
SELECT
RecNo,
StartNum + N -1
FROM @tblDriver, Tally
WHERE N < 4
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 9:21 am
P.S if you wanted to use the function method then you would need something like a cross apply.
select *
from @tblDriver
CROSS APPLY dbo.tvfGetNumbers (StartNum)
However this would not be great code as the function is multiline and would probably not perform well as your data load increased...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 9:30 am
The code does work!
INSERT INTO @tblResults
select tvf.number
from @tblDriver
CROSS APPLY dbo.tvfGetNumbers (StartNum) tvf
Do you think it will be a better performer than the existing cursor code? (which essentially walks RBR-style thru @tblDriver (which has many more than 3 rows in reality) calling the ugly real version of tvfGetNumbers)
Unfortunately I can't get rid of the function.
April 28, 2009 at 9:36 am
use the tally solution
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 9:39 am
But I thought you said I had to get rid of the tvf to use the TALLY solution? I can't get rid of the tvf. Did you mean use the CROSS APPLY?
April 28, 2009 at 9:41 am
sorry miss understanding.
When you say you can't get ride of the tvf! why is that?
I'm saying don't change or delete or alter the tvf. just leave it in the DB if you have to.
but why do you HAVE TO have it in your query?
I'm not 100% sure without testing it but my guess is that your tvf being multi-line is going to be RBAR anyway
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 9:52 am
You can drop your current function dbo.tvfGetNumbers and replace it with this:
create function dbo.tvfGetNumbers
(
@startnum tinyint
)
RETURNS TABLE
AS
return
(select @startnum as number union
select @startnum + 1 union
select @startnum + 2)
Then the following will work:
DECLARE @tblDriver table (RecNo int IDENTITY PRIMARY KEY, StartNum int)
DECLARE @tblResults table (Number int)
INSERT INTO @tblDriver SELECT 1
INSERT INTO @tblDriver SELECT 10
INSERT INTO @tblDriver SELECT 100
INSERT INTO @tblResults
select number from @tblDriver cross apply dbo.tvfGetNumbers(StartNum);
select * from @tblResults
I am still concerned as I am still confused as to what you are trying to accomplish here.
April 28, 2009 at 9:54 am
Christopher, the tvf is actually doing a lot of complicated logic and returning 8 fields as opposed to the simplifed dummy version I created. I need the functionality that the tvf provides, I was just trying to eliminate that complexity from my posting. Sorry for the confusion :crazy:
April 28, 2009 at 9:56 am
ok I see...
Would it be possible to see the function?
That way we can help you optimise the query.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 9:59 am
Trust me, you do NOT want to see the tvf! It has been marked as 'technical debt' and is slated for re-engineering both from the db and ui ends. But until we have a dedicated block of time for that, we thought we could at least get rid of the CURSOR code that calls it.
April 28, 2009 at 10:03 am
Then I would says that what Lynn and I have posted with regards to the cross apply is what you looking for.
HOWEVER. because you going to be using multiline functions you will still have RBAR and without test there is no promise that the function will run faster than the cursor!!!
you gonna have to test it and hope for the best 🙂
P.S I'm sure there are loads of very very clever people here that could help you with that function, and prob faster than you expect
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 10:18 am
Thanks for the help!
The tvf has about 10 nullable parameters, and returns 45 fields many of which are NULL based on which parameters you send in. There is one INSERT INTO statement that is 5 pages long (not kidding) with lots of hardcoding and other really sweet stuff.
When we get to the point of refactoring that, I am sure I'll be the one stuck with it so I'll probably show back up then, with tears in my eyes.....
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply