| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2013-05-17 : 16:36:04
|
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
5072 Posts |
Posted - 2013-05-17 : 17:55:00
|
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. |
 |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-05-17 : 18:04:09
|
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,expireswithout any included columns. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-17 : 18:05:20
|
| That is probably your best course of action for this query. |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-05-20 : 08:36:42
|
| 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. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-20 : 11:11:52
|
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. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-05-20 : 16:05:39
|
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,expireswithout 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. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-05-21 : 11:26:06
|
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 |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2013-05-21 : 12:29:40
|
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 CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-05-21 : 15:16:25
|
| 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. |
|
|
|
|
Simple index question