Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2006
    Posts
    15

    Unanswered: performance tuning for selecting arbitrary range

    I have two tables:

    Headers(id int, time datetime)

    Data(id int, product_id int, property_id int, value float)

    Data.id references Headers.id

    Headers.id is a primary key,
    Data has clustered index (id, product_id, property_id)

    Headers has several thousand rows, Data several million. I want to return all rows from Data for a given product_id and a given property_id such that Header.id is in a given range.

    Right now I am doing

    SELECT id, time, value
    FROM Headers H, Data D
    WHERE
    H.id = D.id AND
    H.time >= @StartTime AND
    H.time <= @EndTime AND
    D.product_id = @ProductID AND
    d.property_id = @PropertyID

    This query can take 10+ seconds to run, though once I run it for a given product_id, queries for different values of property_id are much faster. Try a different product_id, and it takes longer. Given that there are millions of records in Data, is it reasonable for it to take this long? The index was suggested by Query Analyzer's Index Tuning Wizard, and I tried a couple variations on the query without any noticeable performance improvement. But, I'm no DBA...anyone have any tips? I googled a bit but couldn't figure out the right way to phrase my question to find any good info...thanks in advance

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    How many different ID's are there in Data? If I pick an ID at random, what percentage of the table has that ID? that tells you how selective an index on ID will be.

    If it's highly selective (say you get 1% or less of the Data table for each ID) then I would try a clustered index on Headers.ID only, not the triplet (id,product_id, property_id).

    also, what does the query plan look like?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •