Re: Range query optimization help?

From: Steve Kass (skass_at_drew.edu)
Date: 07/13/04 

Date: Tue, 13 Jul 2004 03:27:01 -0400

Mischa,

It's not easy to get the optimizer to understand things like
non-overlapping, no matter how
you index things and phrase queries, so it can sometimes be best just to
provide a hint, since
data with special characteristics like this might lead to bad query cost
estimates. On the other
hand, there may be no quick solution here...

I played with this using only a couple thousand rows of sample data,
without finding any obvious
solution. In some cases, forcing a different join type looked like it
might help. If your query plan
shows a nested loop join, try forcing a hash join, or vice versa, so
you'll have one or the other
hint where I have HASH here.

select asof, MeterEvent.src, stdval stdamp
from MeterEvent
inner hash join StdAmp
        on StdAmp.src = MeterEvent.src
        and MeterEvent.amp >= StdAmp.minamp
        and MeterEvent.amp < StdAmp.maxamp

It didn't look like a merge join would help, though maybe it would with
the right
indexes, but if changing loop to hash or vice versa doesn't help, it
might be worth adding
an index to help implement the join condition better,
MeterEvent(src,amp). Either add that
as a nonclustered index, or change the clustered index key to (src, amp) or
(src, amp, asof) - with the latter, you can make it a unique clustered.
The other one
that ought to help (but didn't seem to) is StdAmp(src, maxamp) or (src,
maxamp, stdval)

Let us know how this works out (or doesn't).

Steve Kass
Drew University

Mischa Sandberg wrote:

>SUMMARY
>I have a transaction table requiring a major batch conversion.
>For each row in 'MeterEvent', I need to look up its recorded Amp value
>in a static table of nonintersecting ranges (one set of ranges per Meter).
>There are 1-3000 ranges >>>per Meter<<<, and about 100K meters.
>
>The naive query below slams the CPU into the wall.
>
>I tried a few tricks with linear interpolation, but the ranges are often
>a nasty mix of very big and very small.
>
>Help?
>-----------------------------------------------------------------------
>NAIVE QUERY
>select asof, MeterEvent.src, stdval stdamp
>from MeterEvent
>join StdAmp
> on StdAmp.src = MeterEvent.src
> and MeterEvent.amp >= StdAmp.minamp
> and MeterEvent.amp < StdAmp.maxamp
>
>DDL
>table MeterEvent(asof datetime, src varchar(20), amp int)
>unique clustered index asof_src on MeterEvent(asof,src)
> About 50M rows
>
>table StdAmp(src varchar(20), minamp int, maxamp int, stdval int)
>unique clustered index src_minamp on StdAmp(src,minamp)
>
>

Relevant Pages

  • Re: strcpy() - dangerous? [Was Re: gets() - dangerous?]
    ... >>>It's more easy to enforce a coding rule regarding string copying ... >>>strcpy correctly I will need to program a call to strncpy anyway ... >>>if the length of src gets ... Here's the corrected version that still uses int. ...
    (comp.lang.c)
  • Re: Cant find PInvoke DLL - BUG?
    ... return uncompressCE(dest, ref destLen, src, srcLen); ... int srcLen); ... I am getting same error when using another dll - the usage is ... Is there any difference when loading library from app. ...
    (microsoft.public.dotnet.framework.compactframework)
  • Automatic way to test performance optimizations
    ... But I would also like to test, that the optimization is used in the ... Does Sun remove all log statements from its JDK src before ... Objectdest, ... int high, ...
    (comp.lang.java.programmer)
  • Inefficient query looping over cursor
    ... ID int (Primary key, clustered index) ... AMOUNT_PAID smallint ...
    (microsoft.public.sqlserver.programming)
  • RE: What is the wrong with this SQL?
    ... When I was having the problem with previous SQL, ... (col1 int ... alter table src drop column col2 ...
    (microsoft.public.sqlserver.programming)
Loading