Please enable JavaScript.
Coggle requires JavaScript to display documents.
SQL - Coggle Diagram
SQL
Optimize Factors
-
-
-
-
look for the actual execution plan in SSMS, you get a suggestion about an index
- NOT Functions Wrapped Around
Joins and WHERE Clauses
-
-
Example: WHERE LEFT(Person.LastName, 3) = 'For';
-> optimize:
WHERE Person.LastName LIKE 'For%';
-
free to put complex string/date/numeric logic onto scalar variables or parameters, but not on columns
- NOT Implicit Conversions - Chuyển đổi ngầm định
comparing two data types that are different enough from each other that they cannot be automatically converted.
-> Instead, SQL Server converts every single value in the table prior to applying the filter.
-
-
- SELECT fields, rather than using SELECT *
- Avoid DISTINCT in SELECT query
can use GROUP BY, CTE or a subquery
4,1 To check the existence of records,
use EXISTS() rather than COUNT()
EXISTS() method is more effective as it exits processing as soon as it finds the first entry of the record
-
E.g
EXISTS (SELECT Id, Name FROM Business WHERE Name like ‘ABC%’)
- Limit your working data set size
- Use WHERE instead of HAVING if can
-
7, Ignore linked/corelated subqueries
-> use JOIN
-
-
E.g:
SELECT b.Name, b.Phone, b.Address, b.Zip, (SELECT CompanyName FROM Company WHERE ID = b.CompanyID) AS CompanyName FROM Business b
- Use of temp table instead of sub queries
-
9, Don’t run queries in a loop
-
4.2, Usind EXISTS() instead of IN
E.g:
where productid IN
(select productid from [AdventureWorks2019].[Production].[TransactionHistory]);
->
where EXISTS
(select productid from [AdventureWorks2019].[Production].[TransactionHistory])
7, use wildcards (e.g %xx%)
-
-