How to use a UDF that contains a loop in where clause

Transact-SQL
1

I have a UDF that looks through a trail of categories and parent categories in order to return a value belonging to the top category record.
And I have a query that needs to select products belonging to categories depending on which value is returned from the above UDF for the given product.
Referencing the UDF in the where clause of my select statement makes execution time WAY too long...
How can I speed up execution time?

my UDF looks like this:
CREATE FUNCTION GetTopCategoryForGoogle
(
@productID int
)
RETURNS int
AS
BEGIN
declare @catid int, @sendToGoogle int, @parentcatid int, @amazonItemType nvarchar(100)
select top 1 @catid = catid from catproduct where productid = @productID
select @parentcatid = parentcatid, @sendToGoogle = sendToGoogle from category where catid = @catid
WHILE @sendToGoogle = 1 and @parentcatid > 0
BEGIN
set @catid = @parentcatid
select @parentcatid = parentcatid, @sendToGoogle = sendToGoogle from category where catid = @catid
END

return @sendToGoogle
END
GO

and my select statement looks like this:
with a as (select id,title,productlink,barcode,image,price,dbo.GetTopCategoryForGoogle(id) as catSendToGoogle from qryproducts where numinstock > 0 and sendProdToGoogle = 1) select * from a where catSendToGoogle = 1

Thanks in advance.

2

this looks like a performance nightmare. Can you provide DDL and sample data with expected output and we can write something a lot more performant that using that function

1 Like