SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Simple index question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 05/17/2013 :  16:36:04  Show Profile  Reply with Quote
For this query...


SELECT NULL AS [EMPTY]  
         FROM [dbo].[Certificates] AS [t20]  
         WHERE ([t20].[StudentsID] = ([t0].[StudentsID]))  
        AND ([t20].[CourseID] = @p42)  
        AND ([t20].[Expires] IS NOT NULL)  
        AND (([t20].[Expires]) < @p43)


Do I create 3 simple indexes - one for StudentsID, one for CourseID, and one for Expires. Or do I create a composite index with the fields in the same order as they are for the where clause. Or do I create a composite index with the columns ordered according to which field reduces the result set the most first?

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/17/2013 :  17:55:00  Show Profile  Visit russell's Homepage  Reply with Quote
you have something weird here. Either it's a typo and the entire line is unnecessary, or, there is another table being joined to and you didn't include it in the query.
WHERE ([t20].[StudentsID] = ([t0].[StudentsID]))


If it's the latter, probably an index on StudentID (in both tables) and for the certificates table, have the index INCLUDE courseid, and expires. Or over all 3.

If the former, I'll put one index over courseID and expires.

In short, one index will likely perform the best for this query, but you need to consider all queries, not just a single one.

But, try various combinations and see what works the best.
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 05/17/2013 :  18:04:09  Show Profile  Reply with Quote
Hi russell, thanks for the reply. The entire query is massive. It is part of an EXISTS...


CASE  
      WHEN EXISTS 
         (  
         SELECT NULL AS [EMPTY]  
         FROM [dbo].[Certificates] AS [t20]  
         WHERE ([t20].[StudentsID] = ([t0].[StudentsID]))  
        AND ([t20].[CourseID] = @p42)  
        AND ([t20].[Expires] IS NOT NULL)  
        AND (([t20].[Expires]) < @p43)  
         ) THEN @p44  
      ELSE CONVERT(NVarChar(10),@p45)  
   END)) AS [Status],  


An index scan is being performed with an excessive number of reads on the certificates table. That is my motivation for the index.

Since it is joining on students via studentsid I would think I would need that field in the index as well. My understanding of "included" columns was that they are for selected fields not for fields used in the WHERE clause.

I think I will create a single index...

studentsid,courseid,expires

without any included columns.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/17/2013 :  18:05:20  Show Profile  Visit russell's Homepage  Reply with Quote
That is probably your best course of action for this query.
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 05/20/2013 :  08:36:42  Show Profile  Reply with Quote
Indexes can't be created based on single query. Look for most recently fired queries and based on the queries, we can go for a solution on what column we should go for Index and what type of Index. For e.g. if Student id is mostly queried in where condition, I would prefer to go with Clustered Index on Studentid, and rest of the columns with NCL. Having said that, maintenance of Indexes is a pain though for DBA's.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/20/2013 :  11:11:52  Show Profile  Visit russell's Homepage  Reply with Quote
quote:
Originally posted by srimami

maintenance of Indexes is a pain though for DBA's.


I disagree with that statement. It is an ongoing task, but hardly a pain.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 05/20/2013 :  16:05:39  Show Profile  Reply with Quote
quote:
Originally posted by ferrethouse
...
My understanding of "included" columns was that they are for selected fields not for fields used in the WHERE clause.

I think I will create a single index...

studentsid,courseid,expires

without any included columns.



I believe included columns can still help in a where clause. If your index was StudentID, including CourseID and Expires - SQL server could locate the students and since CourseID and Expires are part of the index, it would not need to go to Disk to find matches for CourseID and Expires.

In terms of your original question, it is hard to say. Indexes are a trade-off, they speed up selects but use Memory and add a little extra time for inserts, updates and selects.

In this case, yes an index comprised of all 3 columns would make the select the fastest. SQL server can use only one index per search so if you made 3, it would choose only one. But there could be an advantage to making 3 simple indexes. If you have another query based on courseid only, your index would not help since it begins with studentid.

Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 05/21/2013 :  11:26:06  Show Profile  Reply with Quote
quote:
I believe included columns can still help in a where clause. If your index was StudentID, including CourseID and Expires - SQL server could locate the students and since CourseID and Expires are part of the index, it would not need to go to Disk to find matches for CourseID and Expires.


Good point. That makes sense especially if StudentID reduces the result set greatly such that finding courseid and expires required few reads
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/21/2013 :  12:29:40  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
INCLUDING the additional columns will still remove the costs of an Clustered Index loopup (assuming there is a clustered index) -- if not then you'd avoid a RID lookup (gah!)

But if you are getting a scan then either no index exists that will help you or the engine isn't selecting if for some other reason (too many other indexes?)

Guessing the data in the [dbo].[Certificates] table, an index over (StudentsID, CourseID) may be more than enough to get you on your way.

As I said, adding the other columns as an INCLDE will only remove any CI lookup, not replace a table scan.



Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 05/21/2013 :  15:16:25  Show Profile  Reply with Quote
Just to update...

I added an index on studentsid,courseid,expires (no includes). My waits monitoring software had indicated that this query was responsible for 52% of all monitored waits. After the index was added it is 2%. Other variations on this index probably would have helped too.

Thanks for all the info and suggestions.

Edited by - ferrethouse on 05/21/2013 15:17:24
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000