140 likes | 258 Views
High Performance Functions. SQLBits VI. Going backwards is faster than going forwards. Simon Sabin. Independent SQL Server Consultant and Trainer Database design and development, Business Intelligence, Performance tuning and troubleshooting SQL Server since 6.5
E N D
High Performance Functions SQLBits VI
Simon Sabin • Independent SQL Server Consultant and Trainer • Database design and development, Business Intelligence, Performance tuning and troubleshooting • SQL Server since 6.5 • Email: Simon@SqlKnowHow.com • Blog: http://Sqlblogcasts.com/blogs/simons • Twitter: simon_sabin
Overview • TSQL Functions what are they • Why they are evil • Solving by going backwards • Considerations for data access • Is CLR an option
TSQL Functions • Logical solution to ensure code reuse • Introduced in SQL 2000 • Three different flavours • Scalar Function • Inline Table Valued Function • Multi Statement Table Value function
Scalar Functions • Not visible in execution plans • All too visible in profiler • Issues with Estimates and Statistics • Results in nested loop joins • Performance • The code is interpreted each call • Parallelism not possible
Solutions • Don’t use them in the first place • If you have to then • Profiler • Filter by object type 18004 - UDF • Bad estimates • Use query hints HASH / MERGE
Inline Table Valued Functions • What are they? • A function that returns a query • A bit like a parameterised view • Query is consumed into the main query • Resolved down to the base functions
How to? • Write a function that returns a TABLE • Use whatever parameters you want • Specify you query as the return statement • Using the parameters as required • In your calling query use a subquery or • CROSS APPLY/OUTER APPLY
Whats great • IO included in SET STATISTICS IO • Additional predicates handled • Performance near to system functions • No noise in Profiler • Parallelism capable • Can return multiple values
What about CLR • Compiled code • Can be very simple • Even simple solutions are quick • Can be complex • Complex solutions are generally quicker • Don’t be afraid
Summary • Don’t use scalar functions • Convert to inline table valued functions • Consider using CLR
Q&A • Now • Just ask • Later • @Simon_Sabin • Simon@SQLKnowHow.com