Skip to content

Instantly share code, notes, and snippets.

@cincauhangus

cincauhangus/Example - CTE

Last active December 28, 2015 02:09
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cincauhangus/7425797 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cincauhangus/7425797 to your computer and use it in GitHub Desktop.
Download ZIP
SQL Performance Tuning
Raw
Example - CTE
USE [AdventureWorks2012]
GO
--#1
--Return the LoginID and Hire Date column from the HumanResource.Employee table
--For Employees who are Sales People
--HINT: Use BusinessEntityID to join to the Sales.SalesPerson table
--only include Sales people who actually sold something in the last year
--Sort the result by Hire Date
--what type of join did you use?
--how many rows were returned?
SELECT E.LoginID, E.HireDate
FROM HumanResources.Employee E
JOIN Sales.SalesPerson S
ON E.BusinessEntityID = S.BusinessEntityID
WHERE S.SalesLastYear > 0
ORDER BY E.HireDate
--#2
--Return the Product Name and Product Comment from the Production.Product table
--For ALL Products (whether they have comments or not)
--HINT: Use ProductID to join to the Production.ProductReview table
--Sort the result to show those products that do have comments first
--what type of join did you use?
--how many rows were returned?
SELECT p.Name, pr.Comments
FROM Production.Product p
LEFT JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID
ORDER BY pr.Comments DESC
--#3
--Return the TerritoryID and TerritoryName from the Sales.SalesTerritory table
--Alias the Name AS Territory, also return the SalesYTD from the second table
--Return info for ALL Territories whether they have been assigned to a salesperson or not
--HINT: Use TerritoryID to join to the Sales.SalesPerson table
--what type of join did you use?
--how many rows were returned?
SELECT st.TerritoryID, st.Name AS Territory, FORMAT(SUM(sp.SalesYTD), 'c') AS SalesYTD
FROM Sales.SalesTerritory st
RIGHT JOIN Sales.SalesPerson sp
ON st.TerritoryID = sp.TerritoryID
GROUP BY st.TerritoryID, st.Name
Raw
Example - Files, FileGroups, Partitions
USE master
GO
CREATE DATABASE [FGTest]
GO
ALTER DATABASE FGTEST ADD FILEGROUP FG2
ALTER DATABASE FGTEST ADD FILEGROUP FG3
ALTER DATABASE FGTEST ADD FILEGROUP FG4
ALTER DATABASE FGTEST ADD FILEGROUP FG5
GO
ALTER DATABASE FGTEST
ADD FILE (
NAME = FG2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg2.ndf'
)
TO FILEGROUP FG2
ALTER DATABASE FGTEST
ADD FILE (
NAME = FG3,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg3.ndf'
)
TO FILEGROUP FG3
ALTER DATABASE FGTEST
ADD FILE (
NAME = FG4,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg4.ndf'
)
TO FILEGROUP FG4
ALTER DATABASE FGTEST
ADD FILE (
NAME = FG5,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg5.ndf'
)
TO FILEGROUP FG5
---------------------------------------------
use FGTest
go
create partition function customer_partfunc(int)
as range right
for values (25000,50000,75000)
go
create partition scheme customer_partscheme
as partition customer_partfunc
to (fg2, fg3, fg4, fg5)
create table customersPartitioned (
FirstName nvarchar(40),
LastName nvarchar(40),
CustomerNumber int)
on customer_partscheme (CustomerNumber)
select FirstName from customersPartitioned
where CustomerNumber < 50002
Raw
Example - Queries
USE [AdventureWorks2012]
GO
--#1
--Return the LoginID and Hire Date column from the HumanResource.Employee table
--For Employees who are Sales People
--HINT: Use BusinessEntityID to join to the Sales.SalesPerson table
--only include Sales people who actually sold something in the last year
--Sort the result by Hire Date
--what type of join did you use?
--how many rows were returned?
SELECT E.LoginID, E.HireDate
FROM HumanResources.Employee E
JOIN Sales.SalesPerson S
ON E.BusinessEntityID = S.BusinessEntityID
WHERE S.SalesLastYear > 0
ORDER BY E.HireDate
--#2
--Return the Product Name and Product Comment from the Production.Product table
--For ALL Products (whether they have comments or not)
--HINT: Use ProductID to join to the Production.ProductReview table
--Sort the result to show those products that do have comments first
--what type of join did you use?
--how many rows were returned?
SELECT p.Name, pr.Comments
FROM Production.Product p
LEFT JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID
ORDER BY pr.Comments DESC
--#3
--Return the TerritoryID and TerritoryName from the Sales.SalesTerritory table
--Alias the Name AS Territory, also return the SalesYTD from the second table
--Return info for ALL Territories whether they have been assigned to a salesperson or not
--HINT: Use TerritoryID to join to the Sales.SalesPerson table
--what type of join did you use?
--how many rows were returned?
SELECT st.TerritoryID, st.Name AS Territory, FORMAT(SUM(sp.SalesYTD), 'c') AS SalesYTD
FROM Sales.SalesTerritory st
RIGHT JOIN Sales.SalesPerson sp
ON st.TerritoryID = sp.TerritoryID
GROUP BY st.TerritoryID, st.Name
SELECT E.LoginID, E.HireDate
FROM HumanResources.Employee E
WHERE E.HireDate > '1/1/2005'
ORDER BY E.HireDate
SELECT st.TerritoryID, st.Name AS Territory, FORMAT(SUM(sp.SalesYTD), 'c') AS SalesYTD
FROM Sales.SalesTerritory st
RIGHT JOIN Sales.SalesPerson sp
ON st.TerritoryID = sp.TerritoryID
GROUP BY st.TerritoryID, st.Name
SELECT p.FirstName, p.LastName, c.AccountNumber, s.SubTotal
FROM sales.Customer c
JOIN person.Person p
ON c.PersonID = p.BusinessEntityID
JOIN sales.SalesOrderHeader s
ON c.CustomerID = s.CustomerID
WHERE s.SubTotal > 50000
ORDER BY s.SubTotal
Raw
Example - Table Valued Functions
USE master
GO
CREATE DATABASE [FGTest]
GO
ALTER DATABASE FGTEST ADD FILEGROUP FG2
ALTER DATABASE FGTEST ADD FILEGROUP FG3
ALTER DATABASE FGTEST ADD FILEGROUP FG4
ALTER DATABASE FGTEST ADD FILEGROUP FG5
GO
ALTER DATABASE FGTEST
ADD FILE (
NAME = FG2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg2.ndf'
)
TO FILEGROUP FG2
ALTER DATABASE FGTEST
ADD FILE (
NAME = FG3,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg3.ndf'
)
TO FILEGROUP FG3
ALTER DATABASE FGTEST
ADD FILE (
NAME = FG4,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg4.ndf'
)
TO FILEGROUP FG4
ALTER DATABASE FGTEST
ADD FILE (
NAME = FG5,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg5.ndf'
)
TO FILEGROUP FG5
---------------------------------------------
use FGTest
go
create partition function customer_partfunc(int)
as range right
for values (25000,50000,75000)
go
create partition scheme customer_partscheme
as partition customer_partfunc
to (fg2, fg3, fg4, fg5)
create table customersPartitioned (
FirstName nvarchar(40),
LastName nvarchar(40),
CustomerNumber int)
on customer_partscheme (CustomerNumber)
select FirstName from customersPartitioned
where CustomerNumber < 50002
Raw
Notes - Join Info
JOIN types (INNER, OUTER, FULL JOIN)
Nested Loops (INNER JOIN)
1. small -> big tables - pk/fk should be indexed
Merge (OUTER JOIN)
2. big -> big tables - pk/fk should be indexed on both tables
Hash
3. any joins, any size table - with no meaningful index
Raw
Notes - Temporary Objects
Temporary Objects
1) Do not use #temp tables in NEW stored procedures
2) Change cursors or #temp tables in OLD stored procedures that perform poorly to new type of temp objects
3) Use CTEs when multiple levels of aggregations, i.e. perform and aggregate (SUM, MIN...), JOIN and perform another aggregate
4) Use TVF when (input) parameterized lists (table-type actions) - use CROSS APPLY or OUTER APPLY to JOIN
5) Use TVP as lightweight storage < 1000 rows
@cincauhangus
Copy link
Author

cincauhangus commented Nov 13, 2013

http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment