A Range Query Optimization

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) writes:
> 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
In general, it it best to post the CREATE TABLE and CREATE INDEX statements
for the table, as the narrative easily can be misunderstood.
But it sounds like a problem that Mischa Sandberg ran into recently,
and you can review that thread starting on
http://groups.google.com/groups?hl=s...396%40edtnps84
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||On 29 Jul 2004 00:43:27 -0700, aroy wrote:
(snip)
See my reply in comp.databases.ms-sqlserver.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Funny enough, but this is just what I've been working on,
for lookup of about 50M rows in a table of 10M ranges.
(discussion in another thread, in m.p.s.programming).
(As an aside, "KEY" is a lousy column name; you'll be hearing from J.C.)
Given:
T1(id INT, "Key" CHAR(8), ...)
T2(id INT, from_Range CHAR(8), to_Range CHAR(8))
Gert-Jan Strik came up with the single-query answer
(paraphrased from the query framed for my problem)
SELECT T1.id, T2.id
FROM T1
JOIN T2
ON T2.from_Range= (
SELECT MAX(from_Range) FROM T2
WHERE T1.Key BETWEEN T2 .from_Range AND T2 .to_Range
)
"aroy" <anindya.roy@.rave-tech.com> wrote in message
news:be87bcc0.0407282343.1bbda35f@.posting.google.c om...
> 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.

A Range Query Optimization

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) writes:
> 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
In general, it it best to post the CREATE TABLE and CREATE INDEX statements
for the table, as the narrative easily can be misunderstood.
But it sounds like a problem that Mischa Sandberg ran into recently,
and you can review that thread starting on
dtnps84" target="_blank">http://groups.google.com/groups?hl=...
dtnps84
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 29 Jul 2004 00:43:27 -0700, aroy wrote:
(snip)
See my reply in comp.databases.ms-sqlserver.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Funny enough, but this is just what I've been working on,
for lookup of about 50M rows in a table of 10M ranges.
(discussion in another thread, in m.p.s.programming).
(As an aside, "KEY" is a lousy column name; you'll be hearing from J.C.)
Given:
T1(id INT, "Key" CHAR(8), ...)
T2(id INT, from_Range CHAR(8), to_Range CHAR(8))
Gert-Jan Strik came up with the single-query answer
(paraphrased from the query framed for my problem)
SELECT T1.id, T2.id
FROM T1
JOIN T2
ON T2.from_Range= (
SELECT MAX(from_Range) FROM T2
WHERE T1.Key BETWEEN T2 .from_Range AND T2 .to_Range
)
"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.

A Range Query Optimization

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) writes:
> 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
In general, it it best to post the CREATE TABLE and CREATE INDEX statements
for the table, as the narrative easily can be misunderstood.
But it sounds like a problem that Mischa Sandberg ran into recently,
and you can review that thread starting on
http://groups.google.com/groups?hl=sv&lr=&ie=UTF-8&selm=ZeKIc.27357%24Rf.25396%40edtnps84
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||On 29 Jul 2004 00:43:27 -0700, aroy wrote:
(snip)
See my reply in comp.databases.ms-sqlserver.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Funny enough, but this is just what I've been working on,
for lookup of about 50M rows in a table of 10M ranges.
(discussion in another thread, in m.p.s.programming).
(As an aside, "KEY" is a lousy column name; you'll be hearing from J.C.)
Given:
T1(id INT, "Key" CHAR(8), ...)
T2(id INT, from_Range CHAR(8), to_Range CHAR(8))
Gert-Jan Strik came up with the single-query answer
(paraphrased from the query framed for my problem)
SELECT T1.id, T2.id
FROM T1
JOIN T2
ON T2.from_Range= (
SELECT MAX(from_Range) FROM T2
WHERE T1.Key BETWEEN T2 .from_Range AND T2 .to_Range
)
"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.

A Range Query Optimization

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) writes:
> 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

In general, it it best to post the CREATE TABLE and CREATE INDEX statements
for the table, as the narrative easily can be misunderstood.

But it sounds like a problem that Mischa Sandberg ran into recently,
and you can review that thread starting on
http://groups.google.com/groups?hl=...5396%40edtnps84

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.

Hi Roy,

The URL Erland gives has lots of potentially useful info that might help
you. But you might also try if it helps to define an index on (from_range,
to_range) in the second table (t2). Try it with both a clustered and a
non-clustered index - I expect a performance gain in both cases, but it's
hard to predict which will be the fastest.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Funny enough, but this is just what I've been working on,
for lookup of about 50M rows in a table of 10M ranges.
(discussion in another thread, in m.p.s.programming).

(As an aside, "KEY" is a lousy column name; you'll be hearing from J.C.)

Given:
T1(id INT, "Key" CHAR(8), ...)
T2(id INT, from_Range CHAR(8), to_Range CHAR(8))

Gert-Jan Strik came up with the single-query answer
(paraphrased from the query framed for my problem)

SELECT T1.id, T2.id
FROM T1
JOIN T2
ON T2.from_Range= (
SELECT MAX(from_Range) FROM T2
WHERE T1.Key BETWEEN T2 .from_Range AND T2 .to_Range
)

"aroy" <anindya.roy@.rave-tech.com> wrote in message
news:be87bcc0.0407282343.1bbda35f@.posting.google.c om...
> 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.

a price range dimension question

I'm using sql2k.
I'm providing a simplified scenario here.
I'm trying to build a fact table on sales (ie. item, price, quantity,
price*quantity).
I'd like build a cube that I can look up the price by range ($0-$5,
$5-10, $10-$15, etc...).
What's the best way to handle this? do i need a price range
dimension? or should i keep the price range in fact table?
I can't predict what new price will be added to sales, it could be
from 1 cent to any pricing, so if I were to build a price range
dimension, how would it look like?=== Steve L === wrote:
> I'm using sql2k.
> I'm providing a simplified scenario here.
> I'm trying to build a fact table on sales (ie. item, price, quantity,
> price*quantity).
> I'd like build a cube that I can look up the price by range ($0-$5,
> $5-10, $10-$15, etc...).
> What's the best way to handle this? do i need a price range
> dimension? or should i keep the price range in fact table?
> I can't predict what new price will be added to sales, it could be
> from 1 cent to any pricing, so if I were to build a price range
> dimension, how would it look like?
>
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I believe the price range would be considered more a criteria than a
dimension. The only price-range dimension table I could come up w/
would be something like this:
CREATE TABLE PriceRange (
range_code INT NOT NULL PRIMARY KEY,
start_value DECIMAL (11,2) NOT NULL,
end_value DECIMAL (11,2) NOT NULL
)
The fact table would hold the range_code. When you made the CUBE you'd
include the range_code. It might be faster to use range_codes if all
you're doing is a retrieval of data based on ranges. Probably, you
could include both range_code and price in the cube.
But, it make more sense to only use the price in the CUBE then you could
do SUMs and change the price range criteria for each query. Using the
PriceRange dimension - what happens when you want to change the range
criteria of a query? The PriceRange dimension would have to be rebuilt,
then the cube. Are you always going to be using the same price ranges?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQuVvxYechKqOuFEgEQJxJACgwYIakvQZsqvI
WzXp1z6A6aFyMN8An3VU
aJ07XYAO+lukEymGqGnn8aQe
=E0wk
--END PGP SIGNATURE--|||Hi Steve ,
This might solve your problem
Here the gap is 10 ,You can easily make it 5 ,qty can be changed to
price
SELECT LowRange,HiRange,COUNT(*)
FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
,HiRange=((qty - 1) / 10) * 10 + 10
FROM sales) AS ds
GROUP BY lowRange ,HiRange
Please let me know if it solved your purpose
With warm regards
Jatinder