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.
| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2001-12-27 : 11:43:13
|
| I have the following query. When I look at the execution plan, it does an Index scan for the first select (which is good, from what I've read). On the second select, it does a table scan?My question is this:Should I create several indexes (one for each column) or one index that contains any columns that I are in my where clause?QUERY:*********************************************SELECT TOP 1 FaxID, SiteID FROM FaxQueue WHERE ((FaxStatus = 0) OR ((FaxStatus = 3) AND (Attempts < NumberOfAttempts)))AND ((SiteID = '{38E4BE58-A5D3-4BEF-B488-61FA018971BD}') OR (SiteID = '{522F8749-D0D7-47EB-8D97-2700F374DE88}'))ORDER BY Priority, FaxIdIF @@rowcount = 0 BEGIN SELECT TOP 1 FaxID, SiteID FROM FaxQueue WHERE ((FaxStatus = 0) OR ((FaxStatus = 3) AND (Attempts < NumberOfAttempts))) ORDER BY Priority, FaxId END *********************************************INDEX (this is currently the only index on the table):*********************************************CREATE INDEX [FAXQUEUE1] ON [dbo].[FAXQUEUE] ([Priority], [FaxStatus], [NumberOfAttempts], [Attempts], [CostCenterID], [SiteID], [Cre_Date])TIA!Michael Pearson |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2001-12-27 : 12:40:18
|
| You want indexes on columns that are used to sort or search by...<edit>This is not always the case though, too many indexes will slow down DML's</edit>Edited by - onamuji on 12/27/2001 12:41:07 |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2001-12-27 : 13:20:02
|
| I have the single index that indexes all he columns I use in sorts and searches, but SQL Server uses the index for one query, but not the other. Why? |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2001-12-27 : 13:35:26
|
| Remember that most queries filter first then sort. Your first query is filtering based on FaxStatus, Attempts, NumberOfAttempts and SiteID. Your index has FaxStatus as the second field. I'm suprised it uses the index at all. I'd lean toward an index on SiteID. I'm guessing that SiteID is the most restrictive in this case.As an example let's say you have an index on FaxStatus and the table has 100,000 records. Let's say that 99,000 records have a status of 0 and 500 have a status of 3. The SQL Server optimizer will decide that the index will limit the rows from 100,000 to 99,500 and probably decide not to use the index at all. Suppose that a given SiteID has no more than 10 records at most. The optimizer will look at that and say I can limit the records I'm dealing with to 10 by using this index and then examine those further. This should generate an Index Seek. An Index seek looks for one specific value rather than searching throgh an index for a range of values. It's also important to keep an index's statistics up to date. This is how SQL Server knows which indexes are restrictive and which aren't.The second is tougher. I'd guess that an index on FaxStatus and then Priority might be your best bet.I'd also limit your indexes to 3-4 fields at most. The extra fields don't really buy you that much. If you put your entire table (or most of it) in an index you've really defeated the purpose of an index.I think indexes with multiple fields are more effective than a series of indexes each with an individual field.You might also look at this article (http://www.sqlteam.com/item.asp?ItemID=6891) where is do this type of analysis for a series of queries.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
|
|
|
|
|
Index Question