What is Sql performance tuning? Sql performance tuning is a recurring and difficult operation that initially might seem to have very little to no benefit. But if you look as on stored procedure tat only save 1 second multiplied over hundreds of executions 1 second could count together to save other processes minuets down the line.
To start there are 4 major considerations that makes the biggest impact:
1. Number of subsequent selects 2. Table size 3. Joins 4. Aggregations
Number of subsequent selects: One of the first things everyone should consider when creating a script is to reduce the number of select run against a table. It might sound as a trivial statement, but it might surprise any one on how many times a table is accessed for one line over a large script. To avoid this from happening move all variable declarations to one area and set multiple variables with one select.
``` DECLARE @Name VARCHAR(300) DECLARE @DaysToManufacture INT DECLARE @ProductSubcategoryID INT DECLARE @ProductModelID INT
SELECT @Name = Name ,@DaysToManufacture = DaysToManufacture ,@ProductSubcategoryID = ProductSubcategoryID ,@ProductModelID = ProductModelID
FROM [AdventureWorks2019].[Production].[Product] WHERE ProductID = 779 ```
Table size
Large tables is a vague term as what dictates whether a table gest classified as a large can depend on the table structure more than the row count as a table with multiple triggers, foreigner key references and indexes can cause a table with millions of rows to be quicker. But to mitigate table size from impacting the script is to reduce the subset of data to work with by making use of better defined where clause, adding an limit clause to the select and to if the sub set will need to be accessed more than once consider temporarily storing the data in a variable table or temp table that can be dropped when it is no longer needed.
``` DECLARE @ProductRange TABLE ( Name VARCHAR(300) ,DaysToManufacture INT ,ProductSubcategoryID INT ,ProductModelID INT ) INSERT INTO @ProductRange ( Name ,DaysToManufacture ,ProductSubcategoryID ,ProductModelID ) SELECT Name ,DaysToManufacture ,ProductSubcategoryID ,ProductModelID ,SellStartDate FROM [AdventureWorks2019].[Production].[Product]
SELECT * FROM @ProductRange ```
Joins
Joining tables should be limited to only the necessary tables as the cost on the criterial use between the table. This means that all the rows in the smaller table are evaluated for matching rows. So, to avoid this cost it is worth adding a sub select with the necessary aggregations to prefilter the set to be joined to only the set that will pass the main select aggregations.
``` SELECT ProductCategory.Name AS [Category] ,Product.DaysToManufacture ,Product.ProductModelID ,Product.SellStartDate FROM ( SELECT Name ,DaysToManufacture ,ProductSubcategoryID ,ProductModelID ,SellStartDate FROM [AdventureWorks2019].[Production].[Product] AS Product WHERE SellStartDate BETWEEN '2008-01-01' AND '2008-05-30' ) Product INNER JOIN [AdventureWorks2019].[Production].[ProductCategory] AS ProductCategory ON ProductCategory.ProductCategoryID = Product.ProductSubcategoryID WHERE ProductCategory.[ModifiedDate] BETWEEN '2008-01-01' AND '2008-05-30' ```
Aggregations
The were clause is not only to filter the select to the subset you want to see, it has the biggest impact on the execution time as it is the first thing that is executed on the table when looking at the Query execution Plan. So, it is worth the time to define all the relevant clauses as it affects the aggregate functions and the involved time to calculate them.
``` SELECT TOP (1000) [ProductID] ,[Name] ,[ProductNumber] ,[MakeFlag] ,[FinishedGoodsFlag] ,[Color] ,[SafetyStockLevel] ,[ReorderPoint] ,[StandardCost] ,[ListPrice] ,[Size] ,[SizeUnitMeasureCode] ,[WeightUnitMeasureCode] ,[Weight] ,[DaysToManufacture] ,[ProductLine] ,[Class] ,[Style] ,[ProductSubcategoryID] ,[ProductModelID] ,[SellStartDate] ,[SellEndDate] ,[DiscontinuedDate] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2019].[Production].[Product] WHERE [ModifiedDate] BETWEEN '2008-01-01' AND '2008-05-30' ```
These are a couple of steps with a large impact on the execution time. Sql performance tuning is a rabid hole digging for millisecond that will drive you insane some days and very happy on others. Happy digging.
