sql server data warehouse: Sql weird behavior -- sql server data warehouse

Sql weird behavior

csmba
5/22/2006 6:04:56 PM

sql server data warehouse: Hi all. I got the next very simple and strange problem. When running the
following 2 slqs, one runs very fast, and one very slow. I looked at my
indexes time and time again, and cannot figure out why. The table has about
8 million rows.

Very fast:

Select top 1 keyA from tableA where fieldB = 0 and keyA=keyA order by fieldC

Very slow:

Select top 1 keyA from tableA where fieldB = 0 order by fieldC

Properties:

keyA: integer unique key of tableA (clustered index)

fieldB: integer (non-clustered index)

fieldC: integer (non-clustered index)

any ideas?

Re: Sql weird behavior

Jeje
5/22/2006 10:00:47 PM

maybe SQL Server use the fieldA index in the query 1 while the query B use
the fieldB index.

take a look at your resulting index plan to see which index(es) is used.

this will not explain the result but help us to understand what's appends.

[quoted text, click to view]