Index Scan vs Index Seek

I have a really strange problem.

I execute this query:

declare @.cid int
set @.cid = 2003227

select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @.cid

select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @.cid or @.cid = 0

3 Million rows in sales, 120000 in product.

The first does and index seek, the second an index scan.
The execution plan reports that the scan takes 99.87% of the cost, and the seek takes 0.13%

This problem obviously gets worse the bigger the dataset / query /etc.

The reason I query this, is because it never used to take this long to do index scans. Is there something i can change, something i can fix?

Any help would be appreciated.

Joshi believe that the 'OR' statement is causing a table scan(the leaf level of the clustered index)

consider this
i am going to assume that only one of these columns are indexed.
try creating an index on the other column, because if you query a table and use the 'OR' clause in your query and any of the columns are not indexed... SQL server will perform a table scan.|||Okay, If I simplify the queries to this:

declare @.c_ID int
set @.c_Id = 2003227

select * from sales where customer_id = @.C_ID
select * from sales where customer_id = @.C_ID or @.c_Id = 0

(There is a non-unique, non clustered index on the customer_Id column.)

The problem still happens. My question is - why does it use an index scan for the second query, and why does it take so damn long?

Josh

Index Building

Does SQL Server 2000 optimize indexes differently than SQL 7
It seems that SQL 2K doesn't (re)build the index until you execute a
query that will utilize the index. This is causing the first time you
execute the query to be slow. The second time it is executed it runs
fast.
We have maintenance plans to rebuild the indexes each night. This
doesn't seem to help. Instead if it seems like the index is rebuilt
when our users are executing the query which is undesirable. Does
anyone know if this is what SQL Server does? If so, how can I can this
behavior?
GregGreg,
SQL Server does not build indexes unless you actually give it the command to
do so. It does not build any on the fly. It may create statistics on the
fly but they are not indexes. Your most likely seeing the effects of two
things. One is that since you rebuild indexes each night (which probably
isn't necessary) you will invalidate the cached plan. So the next time you
run a query it must recompile the plan. The other and more likely is that
the data has most likely been flushed from cache and will need to be brought
back into cache from disk. This is a relatively slow process. But once it
is in cache the next queries will be much faster.
Andrew J. Kelly
SQL Server MVP
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||Indexes are maintained with each insert/update/delete.
with time, indexes can be fragmented, this is why we defragment them. They
are defragmented when you execute the DBCC DBREINDEX command (of whichever
method you are using).
There is no difference between SQL7 and 2000 in this regard. Possible causes
for what you see can be 1) data is not in the cache when the "first" query
hits is or 2) the optimizer has to produce a query plan, possible it think
so as the indexes has been defragmented. These are two reasons I cam up
with, there can be others as well, of course.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||The only thing that SQL might do is update statistics when looking at a
query. This shouldn't matter because statistics are updated nightly as part
of the dbreindex...
Could the issue be related to physcial IO?... First user brings data into
memory, and others benefit?
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg

Index Building

Does SQL Server 2000 optimize indexes differently than SQL 7
It seems that SQL 2K doesn't (re)build the index until you execute a
query that will utilize the index. This is causing the first time you
execute the query to be slow. The second time it is executed it runs
fast.
We have maintenance plans to rebuild the indexes each night. This
doesn't seem to help. Instead if it seems like the index is rebuilt
when our users are executing the query which is undesirable. Does
anyone know if this is what SQL Server does? If so, how can I can this
behavior?
GregGreg,
SQL Server does not build indexes unless you actually give it the command to
do so. It does not build any on the fly. It may create statistics on the
fly but they are not indexes. Your most likely seeing the effects of two
things. One is that since you rebuild indexes each night (which probably
isn't necessary) you will invalidate the cached plan. So the next time you
run a query it must recompile the plan. The other and more likely is that
the data has most likely been flushed from cache and will need to be brought
back into cache from disk. This is a relatively slow process. But once it
is in cache the next queries will be much faster.
--
Andrew J. Kelly
SQL Server MVP
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||Indexes are maintained with each insert/update/delete.
with time, indexes can be fragmented, this is why we defragment them. They
are defragmented when you execute the DBCC DBREINDEX command (of whichever
method you are using).
There is no difference between SQL7 and 2000 in this regard. Possible causes
for what you see can be 1) data is not in the cache when the "first" query
hits is or 2) the optimizer has to produce a query plan, possible it think
so as the indexes has been defragmented. These are two reasons I cam up
with, there can be others as well, of course.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||The only thing that SQL might do is update statistics when looking at a
query. This shouldn't matter because statistics are updated nightly as part
of the dbreindex...
Could the issue be related to physcial IO?... First user brings data into
memory, and others benefit?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||First use, sometimes doing an update statistics will help.
Other things I've done at some sites, with 7.0 is execute
the proc after its built before my users get in. Not the
cleanest way, but it worked. I haven't had similar
problems in 2000, but you never know.
Gary Abbott
MS-SQL Database Architect
>--Original Message--
>Does SQL Server 2000 optimize indexes differently than
SQL 7
>It seems that SQL 2K doesn't (re)build the index until
you execute a
>query that will utilize the index. This is causing the
first time you
>execute the query to be slow. The second time it is
executed it runs
>fast.
>We have maintenance plans to rebuild the indexes each
night. This
>doesn't seem to help. Instead if it seems like the index
is rebuilt
>when our users are executing the query which is
undesirable. Does
>anyone know if this is what SQL Server does? If so, how
can I can this
>behavior?
>Greg
>.
>|||UPDATE STATISTICS shouldn't be needed after DBCC DBREINDEX because the
distribution data is updated with the index rebuild...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:1213e01c3f5b8$534208d0$a401280a@.phx.gbl...
> First use, sometimes doing an update statistics will help.
> Other things I've done at some sites, with 7.0 is execute
> the proc after its built before my users get in. Not the
> cleanest way, but it worked. I haven't had similar
> problems in 2000, but you never know.
> Gary Abbott
> MS-SQL Database Architect
>
> >--Original Message--
> >Does SQL Server 2000 optimize indexes differently than
> SQL 7
> >
> >It seems that SQL 2K doesn't (re)build the index until
> you execute a
> >query that will utilize the index. This is causing the
> first time you
> >execute the query to be slow. The second time it is
> executed it runs
> >fast.
> >
> >We have maintenance plans to rebuild the indexes each
> night. This
> >doesn't seem to help. Instead if it seems like the index
> is rebuilt
> >when our users are executing the query which is
> undesirable. Does
> >anyone know if this is what SQL Server does? If so, how
> can I can this
> >behavior?
> >
> >Greg
> >.
> >