Using SELECT with a variable in column region of a query.

Question

Using SELECT with a variable in column region of a query.

yahrii

Grasshopper

Points: 10
More actions
March 16, 2009 at 10:43 am

#135656

Hey all
I have a storedprocedure in which I call a number of scalar-valued functions:
SELECT
DokumentID AS DokumentID
, dbo.fncSearch_GetDokumentNoteByTypeString(D.DokumentID_PK, ',', 10) AS OfficialNote
, dbo.fncSearch_GetDokumentReferenceByTypeString(D.DokumentID_PK, ',', 10) AS OfficialReference
...
5 other functions are called and they are all pretty similar.
The functions return an nvarchar of concatenated rows, example:
DECLARE @X NVARCHAR(MAX)
SELECT @X = COALESCE(@X+',','')+ISNULL(DN.Text,'')
FROM dbo.tblDokumentNote DN WITH (NOLOCK)
WHERE DN.DokumentID_FK = @DokumentID AND DN.NoteTypeID_FK = @NoteTypeID ORDER BY DN.NoteID_PK
RETURN (@X)
This works, but unfortunately the many calls to the functions means very bad performance, so I'm looking to put the functionality into the original query instead, hoping performance will improve. The question is whether it is possible to take the SELECT from the function and insert it instead of the function call:
DECLARE @X NVARCHAR(MAX)
SELECT
DokumentID AS DokumentID
, (SELECT @X = COALESCE(@X+',','')+ISNULL(DN.Tekst,'')
FROM dbo.tblDokumentNote DN WITH (NOLOCK)
WHERE DN.DokumentID_FK = @DokumentID AND DN.NoteTypeID_FK = @NoteTypeID ORDER BY DN.NoteID_PK) AS OfficialNote
...
Putting a SELECT here is normally not a problem, but with the variable @X it is. Using the variable in this way is not possible. Anyone have any ideas?
Thanks in advance...

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

A couple of suggestions:

(1) To improve performance, rewrite your scalar functions as inline table valued functions. They run MUCH faster in queries that produce large result sets.

(2) Substitute your concatenation scheme with code based on the following example, which will solve your problem with @X.

Please let me know if you have any questions, or if this is helpful to you. Good hunting.

Bob

;with someTable (foo) as-- just using this cte as an example table

(select 'A' union all

select 'B' union all

select 'C' union all

select 'D'

)

-- the STUFF is used to get rid of the first comma, which will be at position 1 within the string

select stuff((

SELECT ',' + foo

FROM someTable

ORDER BY foo

FOR XML PATH('')

),1,1,'') as [Concatenated Foo]

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

yahrii Grasshopper Points: 10 More actions · Answer 2

Thanks Bob!

I've implemented it for one of the functions and the performance really improves. On top of that I also learned a little about APPLY and other useful stuff 😀

Greatly appreciated, thanks.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Great 🙂

I only learned about the speed of ITVFs in the last month or so, from someone else in here. The performance gains are enough to make me slowly start converting scalar functions for future use as ITVFs. As time allows, we may even go back and change queries in legacy code to CROSS APPLY the new functions.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Great 😀

I only learned about the speed of ITVFs in the last couple of months, from someone else in here. The performance gains are enough to justify the effort of converting the scalar functions.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills