Query Performance - Using Inline/Table Functions

Question

Post reply

Query Performance - Using Inline/Table Functions

Jako de Wet

Hall of Fame

Points: 3123
More actions
February 26, 2014 at 5:49 am

#304782

Hi everyone, I have query that doesn't even register a time when running it. But when I add the lines that are commented out in the code below, it takes between 20 and 30 seconds! When I run the code for functions directly, it also runs without registering a time. I know when I include it like this, it loses the Indexing capabilities?
SELECT
.
.
.
----, ISNULL(CAST(NULLIF(dbo.ufnGetRetail(I.ISBN13),0.00) AS VARCHAR(20)), 'N/A') RetailPrice
----, ISNULL(CAST(NULLIF(SP.LocalPrice,0.00) AS VARCHAR(20)),'on request') LocalPrice
.
.
.
FROM
TABLE1 T1
INNER JOIN dbo.TABLE2 T2 ON T1 = T2
---OUTER APPLY dbo.ufnGetLocal(T1.ISBN13) SP
FOR XML AUTO, ROOT ('BS')
Any idea how to have the functions included but have the query response time come down?

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

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

Jako de Wet Hall of Fame Points: 3123 More actions · Answer 1

I went through the functions and realized they are not needed as advanced as they are in this query.

"Fixed" it by using this:

SELECT

.

, ISNULL(CAST(NULLIF(PR.RetailPrice,0.00) AS VARCHAR(20)), 'N/A') RetailPrice

,

(

SELECT TOP 1

(LocalPrice * 1.08) LocalPrice

FROM LocalPriceList

WHERE RecordReference = T1.Key

ORDER BY LocalPriceList ASC

) LocalPrice

.

FROM

TABLE1 T1

INNER JOIN dbo.TABLE2 T2 ON T1.Key = T2.Key

LEFT JOIN RetailPriceList PR ON PR.Key = T1.Key

FOR XML AUTO, ROOT ('BS')

Guess its probably not the best, but performance wise its WAY better

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

UDFs can be one of the worst things you can do in SQL Server. Read my Death by UDF chapter in the SQL Server MVP Deep Dives 2 book and/or download my SQL Saturday presentation by the same name here: http://www.sqlsaturday.com/viewsession.aspx?sat=122&sessionid=10200

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jako de Wet Hall of Fame Points: 3123 More actions · Answer 3

Thanks Kevin

Will read through it tomorrow.

Interested to read how to get info back in a query that requires more than a plain join.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Jako de Wet (2/26/2014)
Thanks Kevin
Will read through it tomorrow.
Interested to read how to get info back in a query that requires more than a plain join.

Do the functions have the word "BEGIN" in them? If they do and they're in the FROM clause, then they are mTVFs (Multi-Statement Table Valued Functions) and not iTVFs (Inline Table Valued Functions). mTVFs are usually worse than even scalar functions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)