Re: Range query optimization help?

From: Gert-Jan Strik (sorry_at_toomuchspamalready.nl)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 22:05:09 +0200

Mischa Sandberg wrote:
<snip>
> Gert-Jan Strik's solution completed on my test DB in <3hours,
> instead of the 10+ hours that the naive query took. So that's good.
> The query plan suggests that it's looking at multiple StdAmp rows
> in the subquery, instead of just homing in on the max value
> in the index as you'd expect. Will tinker.
<snip>
> I failed to mention that MeterEvents really has
> 18 other unindexed fields, all but one being NOT NULL.
<snip>

Well, since your problem is a common range lookup problem, the query was
the easy first step. Now up to the next step...

It may sound silly, but you may want to create a nonclustered unique
index on StdAmp(src,minamp), in addition to your already existing
clustered index on these columns.

If this index is not automatically used in the correlated subquery, then
add an index hint there.

Maybe this will only hurt performance, but it is worth a try.

Good luck,
Gert-Jan 

-- 
(Please reply only to the newsgroup)

Relevant Pages

  • Re: Nearest Common Ancestor Report (XDb1s $1000 Challenge)
    ... >> only one active application (Query Analyzer). ... I did notice that both the data and log files for the ... but with the options SET STATISTICS TIME and SET STATISTICS ...
    (comp.object)
  • Re: internal handling of views
    ... (because the data element is the same). ... they usually mean thats not what it truly is, or they would have use IS ... not merely merging a select query. ...
    (comp.databases.oracle.misc)
  • Re: need procedure for exporting from Access to Excel
    ... >> duplicated in Excel. ... >> 1) build and execute a SQL query that selects the record in the Access ... create an append query that appends records from the Access table to ...
    (microsoft.public.access.externaldata)
  • Re: Design advice
    ... This query will not run. ... The column name ItemID in the group by clause is ... > LEFT OUTER JOIN iTransRow AS iTranR ON iTranR.ItemID = iMtr.ItemID ...
    (microsoft.public.sqlserver.programming)
  • Re: Deleting Duplicate Records in a Query
    ... This type of query is called a Correlated Subquery. ... is that the subquery is returning the Minimum unique id for ... all the duplicated records and deletes all of those whose unique id is ...
    (microsoft.public.access.queries)