T-SQL User-Defined Functions, or: How to kill performance in one, easy step
This page contains the description for my conference session “T-SQL User-Defined Functions, or: How to kill performance in one, easy step”, and links to the slide deck and demo code used in this presentation.
|Target audience||Database developers and DBAs with enough experience to write and understand basic T-SQL queries.|
|Short description||An overview of the various types of user-defined functions available in SQL Server and their performance impact.|
|Duration||The ideal length for this session is 60 – 75 minutes.|
|Full abstract||User-defined functions in SQL Server are very much like custom methods and properties in .Net languages. At first sight, they seem to be the perfect tool to introduce code encapsulation and reuse in T-SQL. So why is this feature mostly avoided by all T-SQL gurus?
The reason is performance. In this session, you will learn how user-defined functions feed the optimizer with misleading and insufficient information, how the optimizer fails to use even what little information it has, and how this can lead to shocking query performance.
However, you will also see that there is a way to avoid the problems. With just a little extra effort, you can reap the benefits of code encapsulation and reuse, and still get good performance.
Click here to download a ZIP file containing the slide deck and demo code used in this presentation.
(Note: my presentations tend to evolve over time, so the version you find here may not be an exact match for the version you witnessed)