July 31, 2008 at 12:13 am
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_EMPCODE
INDEX-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)
July 31, 2008 at 12:28 am
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_EMPCODE
INDEX-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)
Quick solution is, just use union all statement. check out the query plan now and let me know.
----------------------------------------------
SELECT * FROM EMPLOYEEINFO_INDEX
WHERE Department = 'ABC'
Union All
SELECT * FROM EMPLOYEEINFO_INDEX
WHERE Department = 'XYZ'
-------------------------------------------------
karthik
July 31, 2008 at 12:44 am
karthikeyan (7/31/2008)
Quick solution is, just use union all statement. check out the query plan now and let me know.
----------------------------------------------
SELECT * FROM EMPLOYEEINFO_INDEX
WHERE Department = 'ABC'
Union All
SELECT * FROM EMPLOYEEINFO_INDEX
WHERE Department = 'XYZ'
-------------------------------------------------
Thanks kartikeyan for u'r reply, but still it is not working.
In u'r query, for department 'ABC', SQL optimizer does a Index Seek but for 'XYZ' Table scan. It becomes worst when i have check it against SELECT * FROM EMPLOYEEINFO_INDEX WHERE DEPARTMENT ='ABC'
OR DEPARTMENT = 'XYZ'. For this query, query cost is 35% and for your query query optimizer shows 65% query cost. This might because, Query optimizer does Index seek for department 'ABC' & then Table scan for 'XYZ'.
Any other solution??
July 31, 2008 at 12:46 am
or... you can use force the index.
SELECT * FROM EMPLOYEEINFO_INDEX (index INDEX-2)
WHERE Department = 'ABC' OR Department = 'XYZ'
Check out the query plan and let me know.
karthik
July 31, 2008 at 12:54 am
karthikeyan (7/31/2008)
or... you can use force the index.SELECT * FROM EMPLOYEEINFO_INDEX (index INDEX-2)
WHERE Department = 'ABC' OR Department = 'XYZ'
Check out the query plan and let me know.
Hi karthikeyan ,
It's not working, SQL server 2000 gives error as
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'INDEX-2'.
July 31, 2008 at 12:57 am
modify the statement as
(index = INDEX-2)
karthik
July 31, 2008 at 12:58 am
Hey..finally it works...
Query should be..
SELECT * FROM EMPLOYEEINFO_INDEX (index = INDEX-2)
WHERE DEPARTMENT ='ABC' OR DEPARTMENT = 'XYZ'.
But basic problem remain as it is. For above query query cost is 73% but for SELECT * FROM EMPLOYEEINFO_INDEX WHERE DEPARTMENT ='ABC' OR DEPARTMENT = 'XYZ'. only 27%.
July 31, 2008 at 12:58 am
Try it out and Let me know.
karthik
July 31, 2008 at 1:01 am
Try to force index for the second statement which is written in UNION ALL.
karthik
July 31, 2008 at 1:05 am
If the problem exists still, better post the table details and query plan details. You will get good replies from this site.
karthik
July 31, 2008 at 1:08 am
karthikeyan (7/31/2008)
Quick solution is, just use union all statement. check out the query plan now and let me know.
----------------------------------------------
SELECT * FROM EMPLOYEEINFO_INDEX
WHERE Department = 'ABC'
Union All
SELECT * FROM EMPLOYEEINFO_INDEX
WHERE Department = 'XYZ'
-------------------------------------------------
Thanks kartikeyan for u'r reply, but still it is not working.
In u'r query, for department 'ABC', SQL optimizer does a Index Seek but for 'XYZ' Table scan. It becomes worst when i have check it against SELECT * FROM EMPLOYEEINFO_INDEX WHERE DEPARTMENT ='ABC'
OR DEPARTMENT = 'XYZ'. For this query, query cost is 35% and for your query query optimizer shows 65% query cost. This might because, Query optimizer does Index seek for department 'ABC' & then Table scan for 'XYZ'.
Any other solution??
Execute each statement seperately and paste the query plan here.
karthik
July 31, 2008 at 1:09 am
I have run following queries simultaneously in query analyser.
1. SELECT * FROM EMPLOYEEINFO
WHERE DEPARTMENT ='ABC'
OR DEPARTMENT = 'XYZ'
Query Cost : 15%
2. SELECT * FROM EMPLOYEEINFO (index = TEMP)
WHERE DEPARTMENT ='ABC'
OR DEPARTMENT = 'XYZ'
Query Cost : 41%
3. SELECT * FROM EMPLOYEEINFO WHERE DEPARTMENT ='ABC'
union all
SELECT * FROM EMPLOYEEINFO (index = TEMP) WHERE DEPARTMENT = 'XYZ'
Query Cost : 44%
On which basis query optimizer gives above results?? As we know if it uses indexes properly it shoul cost less. M I right or wrong??
Do query analyser always work better?
July 31, 2008 at 1:17 am
Execute each statement seperately and paste the query plan here.
what is the output ?
karthik
July 31, 2008 at 1:20 am
SELECT * FROM EMPLOYEEINFO WHERE DEPARTMENT ='ABC'
what is the query plan ?
SELECT * FROM EMPLOYEEINFO WHERE DEPARTMENT ='XYZ'
what is the query plan ?
karthik
July 31, 2008 at 1:22 am
what is the total rowcount without any conditions?
what is the total rowcount when Dept = 'ABC' ?
what is the total rowcount when Dept = 'XYZ' ?
karthik
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply