Query Confusion

Hello,
I have a large database and am trying to figure out why the following
queries differ so much in speed. I am selecting from two tables related by a
unique ID field. There are approximately 26mil rows of data in each table.
select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
v1.zip=90210) AND v1.uid=v2.f1;
- this query completes in about 7 seconds
select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
v1.zip=90210) AND v1.uid=v2.f1;
- this query completes in
My only guess is this. The only indexed fields in the query are v1.zip,
v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
simply a matter of the time it's taking MSSQL to fine the appropriate values
in the database file.
Can I split up my database into many filegroups and actually assign a
filegroup to specific tables?
Thanks.
and v2.f63=1;
stooky
Have you seen that query optimizer is available to use the index?
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:0A0C35BA-8FF7-4FE6-B08A-BFE78B017DA5@.microsoft.com...
> Hello,
> I have a large database and am trying to figure out why the following
> queries differ so much in speed. I am selecting from two tables related by
a
> unique ID field. There are approximately 26mil rows of data in each table.
> select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in about 7 seconds
> select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879
OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in
> My only guess is this. The only indexed fields in the query are v1.zip,
> v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
> simply a matter of the time it's taking MSSQL to fine the appropriate
values
> in the database file.
> Can I split up my database into many filegroups and actually assign a
> filegroup to specific tables?
> Thanks.
> and v2.f63=1;
|||The answer to the first query can be obtained from looking at the index
ONLY, without going to the table ... The index covers that query... The
second query includes a non-indexed column in the column list, so the rows
must be fetched - which would be much slower...
Search for 'Covering index' on google or in books on line...
Hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:0A0C35BA-8FF7-4FE6-B08A-BFE78B017DA5@.microsoft.com...
> Hello,
> I have a large database and am trying to figure out why the following
> queries differ so much in speed. I am selecting from two tables related by
> a
> unique ID field. There are approximately 26mil rows of data in each table.
> select v1.zip,v2.f1 from v1,v2 where (v1.zip=44122 OR v1.zip=56879 OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in about 7 seconds
> select v1.zip,v2.f1,v2.f63 from v1,v2 where (v1.zip=44122 OR v1.zip=56879
> OR
> v1.zip=90210) AND v1.uid=v2.f1;
> - this query completes in
> My only guess is this. The only indexed fields in the query are v1.zip,
> v1.uid and v2.f1. Due to the fact that the table v2 is quite large, it's
> simply a matter of the time it's taking MSSQL to fine the appropriate
> values
> in the database file.
> Can I split up my database into many filegroups and actually assign a
> filegroup to specific tables?
> Thanks.
> and v2.f63=1;