Optimizing the performance of T-SQL script in SQL Server

A first solution is to use JOIN in the query directly, rewriting as follows

 SELECT PC.LastName + '' + PC.FirstName [Customer Name]



      



 , SC.CustomerType







 FROM Sales.Customer SC







 LEFT OUTER JOIN YES Sales.Individual







 ON = SC.CustomerID SI.CustomerID







 LEFT OUTER JOIN Person.Contact PC







 ON = SI.ContactID PC.ContactID

In this second version I simply used the tables in the function GetName putting them in the FROM clause. I also replaced the function call GetName on the list of columns in the SELECT statement by directly concatenating the two columns to the Contact table.

Here's what the profiler shows executing this query

As you can see the face of numerous calls from the previous version of the query, the new version is a single call which of course means a big saving in terms of performance.

Now let's see what happens by converting the original scalar function GetName in a function that returns a table instead (inline table). First, create the function and denominiamola GetNameTable

 CREATE FUNCTION GetNameTable (@ CustomerID int)







 RETURNS TABLE







 AS

 





 (RETURN



  



 SELECT LastName + ',' + FirstName [Customer Name]



  



 FROM Sales.Customer SC



  



 LEFT OUTER JOIN YES Sales.Individual



  



 ON = SC.CustomerID SI.CustomerID



  



 LEFT OUTER JOIN Person.Contact PC



  



 ON = SI.ContactID PC.ContactID

 

  



 WHERE CustomerID = @ SC.CustomerID



 



 )

As you can see the query that extracts the data is equal to that of the scalar function GetName, the only difference is that the function returns a table GetNameTable instead of a varchar value. To use this new feature he uses is necessary to use the CROSS APPLY operator as follows

 SELECT I. [Customer Name]



      



 , SC.CustomerType







 FROM Sales.Customer SC







 CROSS APPLY GetNameTable (SC.CustomerID) The

In this case the result of the following will Profiler

Let's take a final example of how to write the original query more efficiently. This time we will create and use the following view

 CREATE VIEW View_GetName







 AS



  



 SELECT LastName + ',' + FirstName [Customer Name]



        



 , SC.CustomerID

 

  



 FROM Sales.Customer SC



  



 JOIN THE Sales.Individual



  



 ON = SC.CustomerID SI.CustomerID



  



 JOIN Person.Contact PC



  



 ON = SI.ContactID PC.ContactID







 GO

Based on this view we can write our query as follows

 V. SELECT [Customer Name]

 

      



 , CustomerType







 FROM Sales.Customer SC







 LEFT OUTER JOIN View_GetName V







 ON SC.CustomerID CustomerID = R.

In this case the result of Profiler is equal to that of the previous two examples. These three examples are equivalent although presenting small differences in performance. The most efficient approach is the CROSS JOIN resulting in a slightly lower CPU usage (you can see from the data of the Profiler).

These examples are intended to highlight that the use of scalar functions in the list of columns in a SELECT statement or in a WHERE clause is an inefficient practice. The negative effects of this practice is directly proportional to the amount of data extracted from the queries that are used. When used in this way, the scalar functions behave like a cursor that is called repeatedly, and then weigh down the development of our instructions. If, therefore, used in some of your queries considered scalar functions to rewrite the same in one of the suggested alternative.

Those seen in this article are just some of the possible measures to improve the performance of our T-SQL queries and other useful devices will be discussed in future articles.

• <<
• 1
• 2
• 3
• 4
• >>