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 |
|
suhasckanade
Starting Member
8 Posts |
 Posted - 2008-07-31 : 02:18:11
|
| Hello Guys, We are facing some indexes related problem in SQL server 2000. The scenario is as follows... We have a table EMPLOYEEINFO having around 32150 rows. Indexes on this tables are INDEX-1 : nonclustered, unique located on PRIMARY : IND_EMPCODEINDEX-2 : nonclustered located on PRIMARY : DEPARTMENT, EMPSTATUS Problem is that when we are firing a query like "SELECT * FROM EMPLOYEEINFO_INDEX WHERE Department = 'ABC'", it uses INDEX-2 for index scan to retrieve results. It's fine, as we want exactly same. But for query "SELECT * FROM EMPLOYEEINFO_INDEX WHERE Department = 'ABC' OR Department = 'XYZ'", instead of doing index scan of INDEX-2, it does table scan taking large time. We are unable to figure it out why SQL Query Optimizer chooses Table Scan instead of Index scan which was faster than former??Is there is any other way to overcome this problem, as we are using this query frequently?? (This occurs only when we are using 'OR' in query) |
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-31 : 04:39:45
|
| A few things first:How come no clustered index?Why are you using SELECT *? Do you know this is typically poor practice?I would expect, also, your first query to elicit an index seek then a lookup - have you just written it down wrong?In short - SQL Server evaluates the relative costs of various options. The more rows returned by the query, the more lookups (expensive operation) on the base table. Eventually you reach a tipping point where it is cheaper to scan the table than use the non clustered index and perform all those expensive lookups. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-31 : 16:32:34
|
| Add a clustered index as pootle_flame said and also add covering index for other necessary columns to avoid BookMarklookup. |
|
|
|
|
|
|
Indexes related problem in SQL server 2000