Speeding up this function??
-
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
-
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
- f.keler 18216 - Thursday, April 6, 2017 6:30 AM
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/ - John Mitchell-245523 - Thursday, April 6, 2017 6:53 AM
It gets called like this.
SELECT tblWebAanvraag.AanvraagID,dbo.CreateTrajectCSV(tblWebAanvraag.AanvraagID) AS TrajectFROM tblWebAanvraagWHERE tblWebAanvraag.Verwijderd = 0 - Luis Cazares - Thursday, April 6, 2017 6:58 AM
Cool thanks! Don't understand the logic behind it but I'll give it a try
- f.keler 18216 - Thursday, April 6, 2017 7:20 AMCool 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.
- f.keler 18216 - Thursday, April 6, 2017 7:17 AM
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
-
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 - Thursday, April 6, 2017 6:58 AM
I'll just repeat it. That article talks about how to use the function.
-
Something like this:
SELECT c.Traject
FROM tblWebAanvraag w
CROSS APPLY dbo.CreateTrajectCSV(w.AanvraagID) c
WHERE w.Verwijderd = 0John
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply