Microsoft SQL Server

SQL Query Optimization at the Stored Procedure Level

You can realize some big performance gains optimizing your SQL queries at the stored procedure level. Proper handling of local variables and floating point numbers as well as query and database settings all present opportunities for query optimization.

Here are some examples illustrating common SQL query optimization opportunities.

Local Variables at Stored Procedure Level

Using local variable at the stored procedure level we are able to enable or disable parameter sniffing as needed. SQL Server’s execution environment sniffs at the parameter values in an SQL statement call that triggered a compilation or recompilation and passes them to the query optimizer to check for opportunities to speed up the query execution plans.

If we see a bell curve distribution in the number of rows retrieved for the filtered column, we can use local variables to disable parameter sniffing.

Here is a stored procedure with parameter sniffing enabled:

@questionid as int
as
set nocount on
begin
select * from Answers where Question_Id=@questionid
end

The same stored procedure can be written this way to avoid parameter sniffing:

ALTER procedure [dbo].[GetAnswers_local]
@questionid as int
as
begin
declare @questionid_local as int
set @questionid_local =@questionid
select * from Answers where Question_Id=@questionid_local
end

For more information on parameter sniffing, check out this post.

Using Table Variables

Using table variable we can store intermediate result sets and perform various SQL operations like various types of joins on these table variables at the stored procedure level.

Floating Point at Application Level

If we are seeing performance issues, we can move floating point operations out of the stored procedure and handle them at the application level .

SELECT a.shampoobrands_id as SHAMPOOBRANDID,

SUM(Value* p.Weight) as ProportionAware,
                SUM(SQUARE(Value)*(p.Weight)) as    CorrectedSumOfSquares
FROM Answers(nolock) a, ShampooBrands(nolock) b, Panelists(nolock) p
                WHERE a.ShampooBrands_Id = b.Id and a.Panelist_Id=p.Id
                AND b.ShampooSegmentSet_Id IN (SELECT id FROM ShampooSegmentSets(nolock) where ShampooSegment_Id = 1)
                AND Question_Id = 2
                AND Panelist_Id IN
                (
                SELECT id FROM Panelists
                )
                group by a.ShampooBrands_Id

This sample query, which has been sanitized, came from an actual client project.

At the time of the import into a SQL table it was multiplied by 10^9 and converted into a numeric value to avoid floating point handling.

After operating at the stored procedure level, numeric data was sent to the application and then at application level the result was again divided by 10^9 to restore its floating points. If Weight is taken as a floating point number, SUM as ProportionAware Multiplication of Value and Weight will be an expensive operation since this particular operation takes place across all the rows in the result set.

Nolock

We can also use NOLOCK with table names to ignore lock on table by any transaction.

SELECT ShampooBrands.Id,ShampooBrands.Name FROM ShampooBrands

Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement.

However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk.

Nocount

Finally, we can SET NOCOUNT ON at the stored procedure level. This stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

The post SQL Query Optimization at the Stored Procedure Level appeared first on OptimusBI.