Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
BRH 6
Index 6
Non-Clustered Index 5
Performance 4
#SQLServer 3
#TSQL 2
SQLServer 2
Clustered Index 2
Filtered Index 2
SSMS 2

Archive · View All
April 2010 7
March 2010 4
August 2010 2
May 2010 2
October 2011 1
September 2010 1

Usage of Indexes - 2

Mar 29 2010 2:40AM by Dinesh Asanka   

We discussed little about indexes last time and as promissed this is the next step into it.

For this article, we are using Sales.SalesOrderDetail table in the AdventureWorks Database.

 sp_helpindex 'Sales.SalesOrderDetail'

 Following are the existing indexes.

 

You can see that there is one clustered index for SalesOrderID , SalesOrderDetailID and three other non-clustered indexes.

-- Usage of index
SELECT SalesOrderID, SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 58950

SELECT SalesOrderID, SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 68531

Let me ask a simple question from you. From the above two queries, what is the best efficient query. If you are novice on indexes, you might say that both queries will incur same cost.

 We will use Actual Execution Plan which can be enable from following icon.

This is a toggle icon which means that by clicking this again display of Actual Execution Plan will go off.

Following is the execution plan you will get.

 It is clear that first query is much faster than the second one by comparing the query cost, 1 to 99.  This is becuase first query using Index seek as it has the SalesOrderID which is the first column of the index. Second query is using index scan which means that it cannot use index seek.

Next we analyse following query.

 -- Using full index

SELECT SalesOrderID, SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 68531

SELECT SalesOrderID, SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 68531
AND SalesOrderID = 58950

Many beleive that second will execute in more time reason being that second query has two parameters to evaluate. But is it the case. Let us see this by examining the execution plan.

 So execution plan saying that second query is much faster. Simple because it can use the full index. So if you have parameter value which will use the index, provide them to the query so that it will use the index inturn it will enhance the performance of your query.

In next, article we will discuss further more cases for indexes.

 

Tags: Index, Non-Clustered Index,


Dinesh Asanka
117 · 1% · 444
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]