Ref
Select Statements
Avoid Select *
SELECT fields instead of using SELECT *
Avoid Select DISTINCT – select more fields for uniqueness.
Avoid using SELECT DISTINCT, select more fields to create unique results.
DISTINCT needs more process internally
Create joins with INNER JOIN (not WHERE)
SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID
-Suggested
SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers
INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID
Use WHERE instead of HAVING to define filters
Using Having
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.CustomerID, Customers.Name
HAVING Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
Using Where
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
Try to Use wildcards at the end of a phrase only
SELECT City FROM Customers
WHERE City LIKE ‘%Char%’
-- instead of that use the below
SELECT City FROM Customers
WHERE City LIKE ‘Char%’
Use TOP to sample query results
SELECT TOP 10 [CityID]
,[CityName]
,[ValidFrom]
,[ValidTo]
FROM [WideWorldImporters].[Application].[Cities]
Use CTE in complex places ???
Run time consuming queries during off-peak hours
Selecting from large tables (>1,000,000 records)
Cartesian Joins or CROSS JOINs
Looping statements
SELECT DISTINCT statements
Nested subqueries
Wildcard searches in long text or memo fields
Multiple schema queries
Columns in Index and Where condition
All columns involved in indexes should appear on WHERE and JOIN clauses on the same sequence they appear on index
Avoid VIEWs.
Due to performance degradation - Avoid VIEWs. Use them only when there are benefits of doing so. Do not abuse them.
Avoid cursors at all costs!
Instead of cursors, use table variable or Temporary table.
Pull the date from source table to temptable
Get the ROWCOUNT() - use while loop to scan one by one until maximum number.
Critical query to Stored procedure
Verify if a critical query gains performance by turning it in a stored procedure.
User the proper index
If a query is slow and your index is not being used by it (remember to check your execution plan), you can force it using WITH(INDEX=index_name), right after the table declaration on the FROM clause.
Avoid IN operators
Use EXISTS or NOT EXISTS or BETWEEN instead of IN or NOT IN or COUNT(*). IN operators creates a overload on database
Other Tips
Avoid too much JOINs on your query: use only what is necessary!
Always restrict the number of rows and columns of your result.
Verify if your server isn’t suffering from not-enough-disk-space illness.
The decreasing performance order of operators is: = (faster)>, >=, <, <=, LIKE, <> (slower)
Always avoid to use functions on your queries. SUBSTRING is your enemy. Try to use LIKE instead
Sometimes is better to make various queries with UNION ALL than a unique query with too much OR operations on WHERE clause.
OR condition we can replace with Union of two queires will reduce the operatior consumption
When there is a HAVING clause, it is better to filter most results on the WHERE clause and use HAVING only for what it is necessary
If there is a need of returning some data fast, even if it is not the whole result, use the FAST option.
SQL Server provides a query hint OPTION(FAST 'N') for retrieving the N number of rows quickly while continuing query execution. This Fast query hint tells (forces) SQL Queries optimizer to give a specific number of rows (represented by 'N') quickly without waiting for the complete data set to appear.
Use, if possible, UNION ALL instead of UNION. The second eliminates all redundant rows and requires more server’s resources.
Use less subqueries. If you must use it, try to nest all of them on a unique block.
Avoid to do much operations on your WHERE clause. If you are searching for a + 2 > 7, use a > 5 instead.
Use more variable tables and less temporary tables.
To delete all rows on a table, use TRUNCATE TABLE statement instead of DELETE.
If you have a IDENTITY primary key and do dozens of simultaneous insertions on in, make it a non-clusterized primary key index to avoid bottlenecks.
A
How to find Slow Running Queries
Database Engine Tuning Advisor
DMVs
Find Slow Queries With SQL DMVs
https://stackify.com/performance-tuning-in-sql-server-find-slow-queries/
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
Others
No comments:
Post a Comment