473,327 Members | 1,997 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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.
Jul 20 '05 #1
3 2168
aroy (an*********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
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)
Jul 20 '05 #3
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" <an*********@rave-tech.com> wrote in message
news:be**************************@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.

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: andy.rich | last post by:
I am getting the following error and I do not know why. Can anyone help? -------------------------------------------------------- this is what appears on the screen...
5
by: AC Slater | last post by:
Whats the simplest way to change a single stored procedures query optimization level? In UDB8 that is. /F
1
by: Sean C. | last post by:
Helpful folks, I have recently migrated our test server, which runs Win NT 4, from V7.2 FP11 to V8.1.3. Just about everything works wondefully, except I am having major problems getting the...
2
by: Eugene | last post by:
I am trying to set query optimization class in a simple SQL UDF like this: CREATE FUNCTION udftest ( in_item_id INT ) SPECIFIC udftest MODIFIES SQL DATA RETURNS TABLE( location_id INT,...
12
by: WantedToBeDBA | last post by:
Hi all, db2 => create table emp(empno int not null primary key, \ db2 (cont.) => sex char(1) not null constraint s_check check \ db2 (cont.) => (sex in ('m','f')) \ db2 (cont.) => not enforced...
6
by: UnixSlaxer | last post by:
Hello, Running a query for the first time on DB2 takes a fixed amount of time. But when query is executed for the second time, the amount of time is usually less since the query is (most...
4
by: Bernard Dhooghe | last post by:
To retrieve data from a query where multiple rows can be returned, a cursor can be used. Different programming interface exist for cursors: embedded SQL, CLI, SQL PL, SQLJ, JDBC. I we look at...
3
by: manning_news | last post by:
Using A2K. I've been asked to modify a report currently requiring only one date parameter to now accept a date range. The main report has 2 subreports and is not bound to a table or query. The...
29
by: Steve R. Hastings | last post by:
When you compile the expression for i in range(1000): pass does Python make an iterator for range(), and then generate the values on the fly? Or does Python actually allocate the list and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.