Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Performance - Using Inline/Table Functions Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 5:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:23 AM
Points: 116, Visits: 189
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?
Post #1545347
Posted Wednesday, February 26, 2014 6:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:23 AM
Points: 116, Visits: 189
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
Post #1545363
Posted Wednesday, February 26, 2014 9:22 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 4,465, Visits: 6,393
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 at GMail
Post #1545460
Posted Wednesday, February 26, 2014 11:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:23 AM
Points: 116, Visits: 189
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.
Post #1545526
Posted Wednesday, February 26, 2014 9:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 35,769, Visits: 32,433
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545690
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse