Geeks With Blogs
My Place For SQL Lets Talk SQL

It was packed room of some 40  Odd people in Noida and from developers to  DBAs anb my 8 Hour long Seminar/Training/ Presentation for them.

I have used lots of examples in that seminar worth mentioning are the last 13 examples but you can just try reading from here and hopefuly after page reads you will be on track

WARNING First few pages are un formatted and might confuse you but carry on soon you will see a nice road ahead.Read SARGS,OPTIMIZER, and INDEXs for refernce here

Lets start from here:-

 

CASE 1

USE Northwind
go
SELECT CompanyName
FROM Customers
WHERE Country = 'Mexico'
--Results--

/* When the Query Optimizer receives this request it analyzes the statement and determines how the data should be accessed (the execution plan). The Query Optimizer can decide to do one of three things to determine which rows have
Mexico’ in the Country column. It can do a table scan, which looks at all the rows
in the table and examines the contents of Country ; it can use a clustered index  scan; or it can use an index that references Country . If the Query Optimizer cannot choose a specific index for an operation, it will always choose to use a clustered  index scan if one exists on the target table. When a clustered index is not present,  a table scan is used. In order for the Query Optimizer to choose the third option, the column on which the comparison operation is being performed must have an associated index.*/

/*In order for the Query Optimizer to use an index with a statement the comparison
operation must be able to be resolved into a search argument (SARG). To
produce a valid SARG, the comparison operation must be of the form shown here.
Column ComparisonOperator Constant/Variable*/

If either the Column or Constant/Variable is acted on by a function (e.g., SUBSTRING) a valid SARG is not
produced.

CASE 2

The following example uses the SUBSTRING function on the Country column, so a SARG cannot be produced and an index cannot be used.

USE Northwind
go
SELECT CompanyName,
Country
FROM Customers
WHERE SUBSTRING(Country,1,1) = 'A'


---Adding an Index to Reduce Query Costs The SELECT statements executed thus far have referenced the
-- Customers table in  the Northwind database. You can use the system stored procedure sp_helpindex
-- to see the existing indexes on Customers.

CASE 3

USE Northwind
go
sp_helpindex 'Customers'

--Results--
index_name index_description index_keys
------------- ------------------------------------------------- -------------
City nonclustered located on PRIMARY City
CompanyName nonclustered located on PRIMARY CompanyName
PK_Customers clustered, unique, primary key located on PRIMARY CustomerID
PostalCode nonclustered located on PRIMARY PostalCode
Region nonclustered located on PRIMARY Region

CASE 4
---What is all about Lets  delve into the details of the estimated execution plan,back to PPt --Hope you know to use (:):):))

SELECT CompanyName
FROM Customers
WHERE Country = 'Mexico'

--The Estimated Cost for the Clustered Index Scan is .0392 seconds; this makes
--up 100 percent of the time required to execute the statement.
--Create a non-clustered index on Country and then re-examine the estimated execution plan.

CREATE NONCLUSTERED INDEX VEERSQL_CustomersCountry
ON Customers (Country)


SELECT CompanyName
FROM Customers
WHERE Country = 'Mexico'

-- Two operators are used in the new execution plan. The first is Index Seek and the second is Bookmark Lookup.
-- Notice that the cost to perform the Index Seek is 34 percent of the total costs and the Bookmark Lookup is 66 percent of -- the total costs.

-- When you add the Estimated Cost for each operator (you can’t see the value
-- for the Bookmark Lookup) you get .018918.
-- When the costs for pre- and post-index queries are compared, you see that
-- the query that used the index realized a 52 percent reduction in costs
Simple  Select ((.0392-.0189)/.0392)*100=51.78)
---  If this type of query is performed on a regular basis and Customers
---  is going to contain a large number of rows, then the index should remain in place.
---  On the other hand, if the query is only executed periodically or
---  Customers is not going to contain a larger number of rows, the index should be deleted.

/* Creating Valid Search Arguments BUt I ll come back on Indexes again

VALID SEARCH ARGS is */
Column ComparisonOperator Constant/Variable
/* In order for the  Query Optimizer to use an existing index, however, the comparison operation
must be able to be resolved into a valid SARG.

SELECT
presented in the previous section is affected when the SARG requirement is not met.

*/

SELECT CompanyName
FROM Customers
WHERE SUBSTRING(Country,1,1) = 'A'

/*The SUBSTRING function caused the Query Optimizer to ignore the index and
use a Clustered Index Scan. This caused the estimated cost to revert back to .0392,

But I doubt it wont here because the Data is less
and the OPtimizer will take Cache Plan*/

SELECT CompanyName
FROM Customers
WHERE Country LIKE 'A%'

--WRONG QUERRY

SELECT CompanyName
FROM Customers
WHERE Country LIKE '_A%'

IMPORTANT CASE 5

--Let us try to determine the most Effective Querry

--Query 1 -- LEFT JOIN
SELECT CompanyName
FROM Customers a
LEFT JOIN ORDERS b ON a.CustomerID = b.CustomerID
WHERE b.CustomerID IS NULL
--Query 2 -- NOT EXISTS
SELECT CompanyName
FROM Customers a
WHERE NOT EXISTS (SELECT *
FROM Orders b
WHERE a.CustomerID = b.CustomerID)
--Query 3 -- NOT IN
SELECT CompanyName
FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID
FROM Orders)

Check the batch

Now what I am doing is loading the Data

SELECT *
INTO Orders2
FROM Orders
go
INSERT Orders2
SELECT CustomerID,
EmployeeID,
OrderDate,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry
FROM Orders

-- Now let me run teh First THree querries...
-- and see the difference by modifying the
--

SELECT CompanyName
FROM Customers a
LEFT JOIN ORDERS2 b ON a.CustomerID = b.CustomerID
WHERE b.CustomerID IS NULL
--Query 2 -- NOT EXISTS
SELECT CompanyName
FROM Customers a
WHERE NOT EXISTS (SELECT *
FROM Orders2 b
WHERE a.CustomerID = b.CustomerID)
--Query 3 -- NOT IN
SELECT CompanyName
FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID
FROM Orders2)


The relative cost for Query 3 increased to over 44 percent. This is a good
example of how the size of the database affects a poorly written query.
Wat is good  today may not be tomorrow..

CASE  6

--We will Use profiler and trace the events and then Querry analyser to suggest for Index Tuning wizard
--I wil use the same Orders2 ans see what it sugegst

USE Northwind
go
SELECT *
FROM Orders2
Go
SELECT *
FROM Orders2
WHERE CustomerID = 'CENTC'
Go
--Some updates
SET ShipName = 'El Centro Rocket Shop'
WHERE CustomerID = 'VICTE'
GO
SELECT *
FROM Orders2
WHERE CustomerID = 'VICTE'
Go

DEMOnstrate how to use Profiler tracing
---Create the Index and then we can drop it
-- check for the Estimation after index is created..do map
-- the performanmce Benefits
/*Beware SQL Profiler trace files grow at an alarming rate when they capture data on
a server that generates a lot of activity. Once you create a trace file, make
sure you monitor its growth rate for the first few hours so you can ensure
that you have plenty of available disk space to hold the contents.*/

--Dropping Index  Foo
DROP INDEX Orders2.Orders21

--Use ITW via Query Analyzer to analyze the following SELECT.
SELECT *
FROM Orders2
WHERE ShipName LIKE 'A%'
ORDER BY ShipName

/*
we can analyse more with Set Statistics IO
It is very importantcommand
LETS take a tour of recommendatiosn on Query Writimng ..A very important tour..*/

SOME RECOMMENDATIONS


1. operate on Small result Sets
Limit the numebr of Columns in select list

set statistics IO on
select productId,SupplierId from products where supplierid = 1
--Table 'Products'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

select * from products where supplierid = 1
---Table 'Products'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.

2. Use Highly Selective Where Clause

-- A column has a high degree of selectivity
-- If it is likely to return a small number of rows
-- for a given value specified in a predicate argument.
-- The distribution statistics can be used to estimate the degree of selectivity:
-- As they say selectivity governs the rules of  chossing Non clustered indexes also
3. Using Indexes Effectviely as we did some query on this feature already.
4. Important is to avoid Non INdexable Search COnditions

Example INdexable = , > , <=, >= , Between , and some like conditions
Non indexable Exclusion != , !> and ,!< , Not Exists, not in , not like , or ,like '%%'

Select * from [Order Details]
where OrderId In (10300, 10301,10302,10303)
---Table 'Order Details'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0.
-- A clustered Index Seek wovv then whats the issue:):):) Just check the Argument and scan count
--HERE WE HAVE MORE THAN FOUR sCANS HAPPENING
Select * from [Order Details]
where OrderId between 10300 and 10303
--Table 'Order Details'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

5. LETS CHECK lIKE CONDITION

SELECT * FROM pRODUCTS WHERE pRODUCTNAME LIKE 'S%'
--Table 'Products'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0.

--IT CONVERTS THE LIKE CONDITION INTO sEARCHABLE INDEX CHECK TH >= COPNDITIOPN

SELECT * FROM PRODUCTS WHERE PRODUCTNAME >='s' AND pRODUCTNAME <'t'
--Table 'Products'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0.
THUS LEADING CHARACTERS IN liKE OPTIMIZE THE sEARCH TO ALLOW THE USAGE OF InDEX

6. AVOID ARITHMETIC OPERATORS

Select * from orders where orderid*2 = 22000
--Table 'Orders'. Scan count 1, logical reads 5, physical reads 3, read-ahead reads 2.
--See the Execution Plan which prevented the use of index on the column
Select * from orders where orderid = 22000/2
--Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

7. Sunstring vs  like

select *  from orders where substring(Shippostalcode,1,1)='V'
--Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 20.
--see the Execplan where index on ShipPostalcode index is not used and Clustered is used
--Now lets see with LIke
select *  from orders where Shippostalcode like 'V%'
--Table 'Orders'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0. and a
-- also see the Index Selectivity Shippostal code is used

8 always use datepart queries for Date opperators
A known truth ..dont cast them


9 Avoid Optimizer hints lets see how they can mislead us

---JOin Hint , Index hint and Firceplan hint let Optimizer do it

Select * from [order details] Od join Orders O
On Od.Orderid = O.Orderid
where O.shipCountry = 'spain'

54 row(s) affected)

--Table 'Order Details'. Scan count 23, logical reads 46, physical reads 0, read-ahead reads 0.
--Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.
--it went for nested Loop with Inner Join HInt with Order Details outside and Order PK inside

--So if we suppose it will use this as hint we are wrong Lets see how

Select * from [order details] Od inner loop join Orders O
On Od.Orderid = O.Orderid
where O.shipCountry = 'spain'

---Table 'Orders'. Scan count 2155, logical reads 4504, physical reads 0, read-ahead reads 0.
---Table 'Order Details'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0.

-- THus Join Hint costed us more here OrderDetail is inside and Orders is inside as such the Scans where more
-- because the JOIN hint forced the Optimizer to use the first table
-- So you must check the iterative and come to the conclusion by Analysisng the Hash Mapping of  Joins
10 Dont use INDEX HInts  unless you are prety Sure

 Select * from Orders With (Index(Pk_orders))Where OrderId * 2 = 2200
--Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.

 Select * from Orders Where OrderId * 2 = 2200
--Table 'Orders'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

--Check the Displyed Subtree Costa nd also see the NOnClustered Index selectivity

11 Avoid Implicit Conversion
For Example

Declare @int INT , @flt FLOAT
Set @int = 10
Set @flt = 10

Select * from products where SupplierID = @int -- Int
Select * from products where SupplierID = @flt -- Int
/*
(1 row(s) affected)

Table 'Products'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

(1 row(s) affected)

Table 'Products'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

*/ All same from Statis IO

---But See th Disply Estims Exec pla and you will see how Implicti Conversion Behaved in Case of Float ..
-- We must understand the Data TYpe Precedence
/* Implicit conversions are those conversions that occur without specifying either
the CAST or CONVERT function. Explicit conversions are those conversions that
require the CAST (CONVERT) function to be specified. This chart shows all
explicit and implicit data type conversions allowed for SQL Server
system-supplied data types, including bigint and sql_variant. */

--Column Type is kept as it is and the Constant for comparison is
--Implicted COnverted  with  disregard to Precedence

-- I will show more on thsi SQL Help File where 
--we Have precedence Chart Lower to upper is Implicited Converted

12   QUESTION what is Better UNION or UNION All

select * from products where SupplierID  = 1
union
Select * from products where SupplierID   =2
0.0311
--Sp_help Products
--Table 'Products'. Scan count 2, logical reads 16, physical reads 0, read-ahead reads 0.
--Merge Join cost 18%
select * from products where SupplierID
 = 1
union all
 Select * from products where SupplierID
 =2
0.0254
--Table 'Products'. Scan count 2, logical reads 16, physical reads 0, read-ahead reads 0.
--THis uses Concatenation whose cost is 0
--Both return Same rows???
--Use UnION All as it avoids the Detecting of removing of duplicates


13  Use of Subqueries over Joins

Select *  into  OD  from [Order Details]
Create Clustered Index ProdId On OD(ProductID)

Select * into Prod from products
create Nonclustered Index SuppId On prod(SupplierId)

-------------------------
Select Od.* from OD where OD.ProductID in  (Select  p.productId from Prod P
Where P.SupplierId = 10)
--0.0311 -- 61%
--Table 'OD'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
--Table 'Prod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Select Od.* from OD , prod P
where Od.ProductiD = P.ProductId
and SupplierId = 10
--0.0197 -- 38%
--Table 'OD'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
--Table 'Prod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.


--Now if  I create the a Non clustered Index ProdId on Prod to avoid this Sort in the Middle cabn the Query behave better

Create Unique NonClustered Index ProdID On Prod(ProductId)
Now we execute thequery again
Select Od.* from OD where OD.ProductID in  (Select  p.productId from Prod P
Where P.SupplierId = 10)

--Table 'OD'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
--Table 'Prod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
-->>>>>>>>>>.But what has changed then Cost has come to 0.0197 from  0.0311

--> No more SORT for Distinct Selects as such Query has improved


14  In Sps

  Set NOCOUNT ON
--Batch Querries
 Set noCOunt OF

 

 

 

 

Posted on Monday, May 2, 2005 12:56 PM CHECK it | Back to top


Comments on this post: Optimization Examples I used in my recent Seminar (Unedited and Without Formating)

# re: Optimization Examples I used in my recent Seminar (Unedited and Without Formating)
Requesting Gravatar...
Handy and indepth
Left by Saravanan on Mar 17, 2006 11:00 PM

# re: Optimization Examples I used in my recent Seminar (Unedited and Without Formating)
Requesting Gravatar...
Very well indepth understanding.

Parveen Barath
Left by Praveen Barath on May 09, 2008 6:47 AM

# Yo Everybody!
Requesting Gravatar...
Hey!
New here and first post.
I am woman, 29 years old from Ireland
I enjoyed here.

Botijos
Left by Beeniashems on Oct 29, 2009 8:51 AM

Your comment:
 (will show your gravatar)


Copyright © Veer Ji Wangoo | Powered by: GeeksWithBlogs.net