> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
>
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
>
> However there is no relation whatsoever between the KEY and the
> from/to range.
>
> We need to find matching records where Key is found between the from
> and to range :
>
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range

On 29 Jul 2004 00:43:27 -0700, aroy wrote:

>Hi,
>
>Need help in optimizing a query in SQL Server.
>Following is the problem statement.
>
>There are two tables;
>1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
>this is not the primary key. The table can have billions of records;
>in test environment, we are having 3,000,000 records
>
>2nd table (t2) has two columns a from_Range and to_Range, both
>char(8). this table has lesser number of records, in thousands.
>Clustered index is on the primary key.
>
>However there is no relation whatsoever between the KEY and the
>from/to range.
>
>We need to find matching records where Key is found between the from
>and to range :
>
>select t1.id, t2.id from t1, t2
>where t1.KEY between t2.from_range and t2.to_range
>
>( The ids form part of primary keys in both tables. )
>
>The plan shows a loop, with t1 using clustered index of KEY and t2
>using clustered index of the primary key.
>
>This query is taking around 14 seconds on SQL server 2000 in win2kpro
>with P4 and 512 MB RAM.
>
>Is there any way this can be reducd to a subsecond performance ? This
>query forms the core of most of the processing, and any reduction here
>will have recursive effect all over.
>
>Thanks in advance,
>roy.

"aroy" <anindya.roy@rave-tech.com> wrote in message
news:be87bcc0.0407282343.1bbda35f@posting.google.com...
> Hi,
>
> Need help in optimizing a query in SQL Server.
> Following is the problem statement.
>
> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
>
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
>
> However there is no relation whatsoever between the KEY and the
> from/to range.
>
> We need to find matching records where Key is found between the from
> and to range :
>
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range
>
> ( The ids form part of primary keys in both tables. )
>
> The plan shows a loop, with t1 using clustered index of KEY and t2
> using clustered index of the primary key.
>
> This query is taking around 14 seconds on SQL server 2000 in win2kpro
> with P4 and 512 MB RAM.
>
> Is there any way this can be reducd to a subsecond performance ? This
> query forms the core of most of the processing, and any reduction here
> will have recursive effect all over.
>
> Thanks in advance,
> roy.