Speeding up this function??

Question

Speeding up this function??

f.keler 18216

Valued Member

Points: 61
More actions
April 6, 2017 at 6:30 am

#104480

This function gets related streetname(s) and housenumber(s) and returns this as a string. I'm wondering if this can be rewritten more efficient.
Thanx,
Ferdy
-----------
ALTER FUNCTION [dbo].[CreateTrajectCSV](
@Id AS INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @lst VARCHAR(max)
DECLARE @aanvraagType NVARCHAR(80)
DECLARE @aanvraagId INT
SET @aanvraagType = (SELECT tblWebAanvraagType.AanvraagType FROM tblWebAanvraagType WHERE tblWebAanvraagType.AanvraagTypeID =
(SELECT tblWebAanvraag.AanvraagType FROM tblWebAanvraag WHERE tblWebAanvraag.AanvraagID = @Id) )
-- Bij Graafbon-aanvraag het traject ophalen via de bijbehorende vergunning-aanvraag
IF @aanvraagType = 'Graafbon'
SET @aanvraagId = (SELECT tblVergunningGegevens.AanvraagID FROM tblVergunningGegevens WHERE tblVergunningGegevens.VergunningID =
(SELECT tblWebAanvraag.VolgensVergunning FROM tblWebAanvraag WHERE tblWebAanvraag.AanvraagID = @Id) )
ELSE
SET @aanvraagId = @Id
SELECT @lst = ISNULL(@lst+' | ','') + htblStraten.Straatnaam +
IIF(ISNULL(tblWebAanvraagStraat.Huisnr,'')='','',' ' + tblWebAanvraagStraat.Huisnr) +
IIF(ISNULL(tblWebAanvraagStraat.NadereOmschrijving,'')='','',' (' + tblWebAanvraagStraat.NadereOmschrijving + ')') +
', ' + htblStraten.Plaatsnaam
FROM tblWebAanvraag INNER JOIN
tblWebAanvraagStraat ON tblWebAanvraag.AanvraagID = tblWebAanvraagStraat.AanvraagID INNER JOIN
htblStraten ON tblWebAanvraagStraat.StraatID = htblStraten.StraatID
WHERE tblWebAanvraag.AanvraagID = @aanvraagId
RETURN @lst
END

Viewing 10 posts - 1 through 9 (of 9 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Please will you also show us how you're using the function? You might get a good performance improvement by rewriting it as an inline table valued function.

John

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

f.keler 18216 - Thursday, April 6, 2017 6:30 AM
This function gets related streetname(s) and housenumber(s) and returns this as a string. I'm wondering if this can be rewritten more efficient.
Thanx,
Ferdy
-----------
ALTER FUNCTION [dbo].[CreateTrajectCSV](
@Id AS INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @lst VARCHAR(max)
DECLARE @aanvraagType NVARCHAR(80)
DECLARE @aanvraagId INT
SET @aanvraagType = (SELECT tblWebAanvraagType.AanvraagType FROM tblWebAanvraagType WHERE tblWebAanvraagType.AanvraagTypeID =
(SELECT tblWebAanvraag.AanvraagType FROM tblWebAanvraag WHERE tblWebAanvraag.AanvraagID = @Id) )
-- Bij Graafbon-aanvraag het traject ophalen via de bijbehorende vergunning-aanvraag
IF @aanvraagType = 'Graafbon'
SET @aanvraagId = (SELECT tblVergunningGegevens.AanvraagID FROM tblVergunningGegevens WHERE tblVergunningGegevens.VergunningID =
(SELECT tblWebAanvraag.VolgensVergunning FROM tblWebAanvraag WHERE tblWebAanvraag.AanvraagID = @Id) )
ELSE
SET @aanvraagId = @Id
SELECT @lst = ISNULL(@lst+' | ','') + htblStraten.Straatnaam +
IIF(ISNULL(tblWebAanvraagStraat.Huisnr,'')='','',' ' + tblWebAanvraagStraat.Huisnr) +
IIF(ISNULL(tblWebAanvraagStraat.NadereOmschrijving,'')='','',' (' + tblWebAanvraagStraat.NadereOmschrijving + ')') +
', ' + htblStraten.Plaatsnaam
FROM tblWebAanvraag INNER JOIN
tblWebAanvraagStraat ON tblWebAanvraag.AanvraagID = tblWebAanvraagStraat.AanvraagID INNER JOIN
htblStraten ON tblWebAanvraagStraat.StraatID = htblStraten.StraatID
WHERE tblWebAanvraag.AanvraagID = @aanvraagId
RETURN @lst
END

This could be rewritten as an inline table valued function. It might speed things up.
However, I can't test and the code I'm about to post might not be the most efficient or exact, but I tried to keep the logic.
ALTER FUNCTION [dbo].[CreateTrajectCSV]( @Id AS INT ) RETURNS TABLE AS RETURN SELECT STUFF(( SELECT ' | ' + s.Straatnaam + ISNULL( ' ' + was.Huisnr,'') + ISNULL(' (' + was.NadereOmschrijving + ')','') + ', ' + s.Plaatsnaam FROM tblWebAanvraag wa JOIN tblWebAanvraagStraat was ON wa.AanvraagID = was.AanvraagID JOIN htblStraten s ON was.StraatID = s.StraatID WHERE wa.AanvraagID = ( SELECT TOP 1 AanvraagID FROM ( SELECT 1 ordr, vg.AanvraagID FROM tblWebAanvraag wa JOIN tblWebAanvraagType wat ON wa.AanvraagType = wat.AanvraagType JOIN tblVergunningGegevens vg ON wa.VolgensVergunning = vg.VergunningID WHERE wa.AanvraagID = @Id AND wa.AanvraagType = 'Graafbon' UNION ALL SELECT 2, @Id) x ORDER BY ordr) FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '') AS lst;

To know more about what I did here, check the following articles:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
http://www.sqlservercentral.com/articles/T-SQL/91724/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

f.keler 18216 Valued Member Points: 61 More actions · Answer 3

John Mitchell-245523 - Thursday, April 6, 2017 6:53 AM
Please will you also show us how you're using the function? You might get a good performance improvement by rewriting it as an inline table valued function.
John

It gets called like this.

SELECT tblWebAanvraag.AanvraagID,

dbo.CreateTrajectCSV(tblWebAanvraag.AanvraagID) AS Traject

FROM tblWebAanvraag

WHERE tblWebAanvraag.Verwijderd = 0

f.keler 18216 Valued Member Points: 61 More actions · Answer 4

Luis Cazares - Thursday, April 6, 2017 6:58 AM
f.keler 18216 - Thursday, April 6, 2017 6:30 AM
This function gets related streetname(s) and housenumber(s) and returns this as a string. I'm wondering if this can be rewritten more efficient.
Thanx,
Ferdy
-----------
ALTER FUNCTION [dbo].[CreateTrajectCSV](
@Id AS INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @lst VARCHAR(max)
DECLARE @aanvraagType NVARCHAR(80)
DECLARE @aanvraagId INT
SET @aanvraagType = (SELECT tblWebAanvraagType.AanvraagType FROM tblWebAanvraagType WHERE tblWebAanvraagType.AanvraagTypeID =
(SELECT tblWebAanvraag.AanvraagType FROM tblWebAanvraag WHERE tblWebAanvraag.AanvraagID = @Id) )
-- Bij Graafbon-aanvraag het traject ophalen via de bijbehorende vergunning-aanvraag
IF @aanvraagType = 'Graafbon'
SET @aanvraagId = (SELECT tblVergunningGegevens.AanvraagID FROM tblVergunningGegevens WHERE tblVergunningGegevens.VergunningID =
(SELECT tblWebAanvraag.VolgensVergunning FROM tblWebAanvraag WHERE tblWebAanvraag.AanvraagID = @Id) )
ELSE
SET @aanvraagId = @Id
SELECT @lst = ISNULL(@lst+' | ','') + htblStraten.Straatnaam +
IIF(ISNULL(tblWebAanvraagStraat.Huisnr,'')='','',' ' + tblWebAanvraagStraat.Huisnr) +
IIF(ISNULL(tblWebAanvraagStraat.NadereOmschrijving,'')='','',' (' + tblWebAanvraagStraat.NadereOmschrijving + ')') +
', ' + htblStraten.Plaatsnaam
FROM tblWebAanvraag INNER JOIN
tblWebAanvraagStraat ON tblWebAanvraag.AanvraagID = tblWebAanvraagStraat.AanvraagID INNER JOIN
htblStraten ON tblWebAanvraagStraat.StraatID = htblStraten.StraatID
WHERE tblWebAanvraag.AanvraagID = @aanvraagId
RETURN @lst
END
This could be rewritten as an inline table valued function. It might speed things up.
However, I can't test and the code I'm about to post might not be the most efficient or exact, but I tried to keep the logic.
ALTER FUNCTION [dbo].[CreateTrajectCSV]( @Id AS INT ) RETURNS TABLE AS RETURN SELECT STUFF(( SELECT ' | ' + s.Straatnaam + ISNULL( ' ' + was.Huisnr,'') + ISNULL(' (' + was.NadereOmschrijving + ')','') + ', ' + s.Plaatsnaam FROM tblWebAanvraag wa JOIN tblWebAanvraagStraat was ON wa.AanvraagID = was.AanvraagID JOIN htblStraten s ON was.StraatID = s.StraatID WHERE wa.AanvraagID = ( SELECT TOP 1 AanvraagID FROM ( SELECT 1 ordr, vg.AanvraagID FROM tblWebAanvraag wa JOIN tblWebAanvraagType wat ON wa.AanvraagType = wat.AanvraagType JOIN tblVergunningGegevens vg ON wa.VolgensVergunning = vg.VergunningID WHERE wa.AanvraagID = @Id AND wa.AanvraagType = 'Graafbon' UNION ALL SELECT 2, @Id) x ORDER BY ordr) FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '') AS lst;
To know more about what I did here, check the following articles:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
http://www.sqlservercentral.com/articles/T-SQL/91724/

Cool thanks! Don't understand the logic behind it but I'll give it a try 😉

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

f.keler 18216 - Thursday, April 6, 2017 7:20 AM
Cool thanks! Don't understand the logic behind it but I'll give it a try 😉

Be sure to understand the logic. You'll have to support this solution. Ask any questions that you might have.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

f.keler 18216 - Thursday, April 6, 2017 7:17 AM
John Mitchell-245523 - Thursday, April 6, 2017 6:53 AM
Please will you also show us how you're using the function? You might get a good performance improvement by rewriting it as an inline table valued function.
John
It gets called like this.
SELECT tblWebAanvraag.AanvraagID,
dbo.CreateTrajectCSV(tblWebAanvraag.AanvraagID) AS Traject
FROM tblWebAanvraag
WHERE tblWebAanvraag.Verwijderd = 0

OK, if that query returns more than one row, then going to an ITVF (as Luis has already shown you) is likely to help.

John

f.keler 18216 Valued Member Points: 61 More actions · Answer 7

Ok I redefined the function as described above, but how do I use it correctly?
Previously I could use this to get the returned string and show it as 'Traject'. But now I have to get it out of the table that gets returned.

SELECT tblWebAanvraag.AanvraagID, dbo.CreateTrajectCSV(tblWebAanvraag.AanvraagID) AS Traject FROM tblWebAanvraag

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

Luis Cazares - Thursday, April 6, 2017 6:58 AM
...
To know more about what I did here, check the following articles:
...
http://www.sqlservercentral.com/articles/T-SQL/91724/

I'll just repeat it. That article talks about how to use the function.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Something like this:

SELECT c.Traject FROM tblWebAanvraag w CROSS APPLY dbo.CreateTrajectCSV(w.AanvraagID) c WHERE w.Verwijderd = 0

John