SQL2000 Chooses wrong execution plan
-
Hi, Guys!
I am running SQL2000 SP1 on Compaq 8500 with 6 processors.
Here is a problem. I have the following statement:
INSERT MyTable (col1, col2, col3......col20)
SELECT col1, value2, col3.......value20
FROM MyTable
WHERE col1 = 0x000034235F343F
MyTable has about 2 mln. records in it. There is a non-clustered index defined on col1. The index is not defined as unique, but the values in the column are almost entirely unique. This index seem to be the obvious choice for this query, but SQL Server refuses to utilize it. Instead it does a clustered index scan (which is a table scan). The reason it does what it does, is because it incorrectly estimates the number of records returned by SELECT portion of the query. The estimated execution plan shows that SELECT will return over 64,000 records -- this is wrong! SELECT only returns a single row!!! When I run SELECT separately, it correctly chooses to use index defined on col1 (and correctly estimates that only 1 row will be returned). The indexes on this table are rebuild every night, so statistics should be fine. I even run DBCC SHOW_STATISTICS on this table/index and the results are correct: the index is very, very selective. Any ideas?
Thank you!
Michael
P.S. Adding query hint to the query above (to force it to use index on col1) makes it very fast (1 sec versus 72 sec with Clustered Index scan).
-
I'll tkae a look tomorrow, but my guess is that there is a bug inthe parser for this insertr operatrion. sorry, tired.
Steve Jones
-
I am answering my own question here: Bug in SQL2000 SP1. See Q306374.
Michael
-
sql server often gets the query plan wrong on updates - but inserts are usually better.
If the select is ok on it's own you could probably get round it by
INSERT MyTable (col1, col2, col3......col20)
exec ('SELECT col1, value2, col3.......value20
FROM MyTable
WHERE col1 = 0x000034235F343F')
or as you say by using a hint.
Cursors never.
DTS - only when needed and never to control.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply