1 / 14

High Performance Functions

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

nyssa-tillman
Download Presentation

High Performance Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. High Performance Functions SQLBits VI

  2. Going backwards is faster than going forwards

  3. 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

  4. Overview • TSQL Functions what are they • Why they are evil • Solving by going backwards • Considerations for data access • Is CLR an option

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. Summary • Don’t use scalar functions • Convert to inline table valued functions • Consider using CLR

  13. Then you’ll be happy

  14. Q&A • Now • Just ask • Later • @Simon_Sabin • Simon@SQLKnowHow.com

More Related