Indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Indexes

Author

Topic

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-16 : 14:50:01

I've created indexes for the queries below running select getdate() before and after the query to determine the time. I'm curioius as to how sql server determines and decides how and when to use the indexes for the queries and how the run times of these queries compare. I'm also curious to know what kind of difference there would be on the last two queries as the only difference is the values.
For the last two queries I don't think there would be a difference but hoping that someone would know.

Below are my results:

select count(distinct CustomerID)
from Orders
where Status = 5

SELECT getdate()
go
Create Index Orders_Index On Orders(customerID)
go
SELECT getdate()

select sum(TotalDue)
from Orders
where CustomerID = 11212

select getdate()
go
Create Index Orders_Index On Orders(customerID)
go
select getdate()

select count(distinct AccountNumber)
from Orders
where SalesPersonID = 288

select getdate()
go
Create Index Orders_Index On Orders(salespersonID)
go
select getdate()

select count(distinct AccountNumber)
from Orders
where SalesPersonID = 276

select getdate()
go
Create Index Orders_Index On Orders(salespersonID)
go
select getdate()

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-17 : 00:27:22

You can check query execution plan before and after creating index.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-17 : 10:04:05

Where would the query plan execution be ?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-17 : 21:14:07

Under query -> include actual execution plan in query window in ssms.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-17 : 22:28:00

what do u mean by execution query plan?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-17 : 22:34:25

Do you know how to use ssms? Check books online for details.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-17 : 22:37:28

I dont know about ssms. An explanation would be appreciated.
thank.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-18 : 00:11:04

??! Sql server management studio.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-18 : 01:04:01

thanks rmiao
what is an explain plan then and would it be of any use in my question?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-18 : 20:28:33

Did you ever look at books online?

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-18 : 23:05:51

yes, sometimes i find it a little confusing and would like if someone here could answer it in a more simpler form.

thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-18 : 23:23:06

You really need take basic sql training.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-19 : 19:19:13

hi there,
is this the correct way of creating the index for the first query to make it run faster?

select count(distinct CustomerID)
from Orders
where Status = 4

Create Index Orders_Index On Orders(customerID)

is it always performed on the where clause?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-19 : 19:45:08

Depending on what data you want, you might be able to get it all from the index. This depends on the selectivity of the index. For example you may be able to add an index of : Create Index IX_Orders_Status_CustomerID On Orders(Status, CustomerID)

Again, depending on the data, the query might be able to use the index to restrict (Status = 4) and get the CustomerID from the index so it does not have to do a bookmark lookup. But, that depends on the data distribution as to whether the index is good or if SQL will use it.

Try some different index combinations and see what the execution plan tells you.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-19 : 19:54:01

thank you lamphrey would you be able to guide me with the other queries and the execution plan? thanks.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-19 : 23:23:52

Sounds you just don't like to read book.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-20 : 12:01:15

thanks rmiao