Today I demonstrate a more efficient method to retrieve data using a stored procedure, while providing different sort options.
If you show the query plan from yesterday you will see that it calculates the sort for all options, and then uses the one specified by the user.
This query differs in that it creates a separate sub plan for each sort option. In the old days this would defeat the purpose of using a stored procedure, because it could not cache any execution plan. SQL Server now caches sub plans, and utilizes them based on our switching option. So, the one procedure handles the logic, and the SQL Server query engine handles the plan creation, execution, and caching.
Another implementation is to allow the user to specify the order by string as a comma separated list. In the procedure you append the order by clause to an existing text, and execute the new query. The problem with this model is that you gobble up your dynamic query cache with each unique sort option. However, this is not that different from the method demonstrated today. In each case, a new query plan is created for each unique sort order specification.
The stored procedure is self explanatory. I won’t be reviewing anything regarding the syntax, because it is basic SQL.
Cheers,
Ben
IF OBJECT_ID(‘dbo.prsDemoProedureWithSeparateQuery’) IS NOT NULL
BEGIN
DROP PROCEDURE prsDemoProedureWithSeparateQuery
END
GO
CREATE PROCEDURE dbo.prsDemoProedureWithSeparateQuery
(
@SortColumn INT = 1
)
AS
— Enums
DECLARE @ID INT = 1
DECLARE @PetType INT = 2
DECLARE @SurName INT = 3
DECLARE @City INT = 4
DECLARE @Country INT = 5
DECLARE @Composite INT = 6
IF @SortColumn = @ID
SELECT *
FROM SortDemo
ORDER BY ID
IF @SortColumn = @PetType
SELECT *
FROM SortDemo
ORDER BY PetType
IF @SortColumn = @SurName
SELECT *
FROM SortDemo
ORDER BY SurName
IF @SortColumn = @City
SELECT *
FROM SortDemo
ORDER BY City
IF @SortColumn = @Country
SELECT *
FROM SortDemo
ORDER BY Country
IF @SortColumn = @Composite
SELECT *
FROM SortDemo
ORDER BY Country, City, Surname
GO
EXEC dbo.prsDemoProedureWithSeparateQuery 1
EXEC dbo.prsDemoProedureWithSeparateQuery 2
EXEC dbo.prsDemoProedureWithSeparateQuery 3
EXEC dbo.prsDemoProedureWithSeparateQuery 4
EXEC dbo.prsDemoProedureWithSeparateQuery 5
EXEC dbo.prsDemoProedureWithSeparateQuery 6