Innovation ways to get the last date less than or equal to an input da

[heyinlangley]

Hi, we've have a very/very large table indexed by STORE, ITEM, DATE and would like to reference the row with the last date less than or equal to an input date.
We currently have a subselect to find the max(date) but the query is very slow due to the large number of rows being matched.

Does anyone have an innovative solution to finding the max row less than or equal to a date without having to write a subselect?

Any help would be much appreciated.

thanks

 

[gregsimpson]

heyinlangley,

can you post your full sql?

Cheers
Greg

 

[blom0344]

Select date from table order by date desc fetch first 1 rows only.

The sort may quite demanding however...

Ties Blom
Information analyst

 

[gregsimpson]

Ties,

you've assumed the highest date on the table, where I'm interpreting the requirement to be "latest date which is less than or equal to an input to date".

That's why I asked for the query to be posted so we could see what it's doing and suggest an alternative which gets the same results.

I had same thought as youself but also using a where predicate, however as I said I thought the current SQL would act as a spec for the requirements and I was planning to take it form there.

Cheers
Greg

 

[blom0344]

Greg,

You're right. Didn't take enough time to read the requirements in full..

Ties Blom
Information analyst

 

[heyinlangley]

Here's a snippet from the code:

SELECT A.REG_PRICE
FROM PRICE_TABLE A
WHERE ITEM_ID = 40 and
STORE_ID = 992 and
EFFECTIVE_DT = (SELECT MAX(EFFECTIVE_DT)
FROM PRICE_TABLE B
WHERE B.ITEM_ID = A.ITEM_ID AND
B.STORE_ID = A.STORE_ID AND
B.EFFECTIVE_DT<={d '2005-12-31'})
The above SQL brings back the right information, the problem is that it runs very slowly.

 

[PruSQLer]

Does your platform allow you to create Global Temp Tables? If so, you could take the code from the subquery and put it in a step 1 which loads the GTT. Then in step 2 you could match Price_Table with the GTT.

Should be a lot faster. I've converted jobs with subqueries that wouldn't run at all to Global Temp Tables and they then ran in 10 minutes or less.

 

[ddiamond]

Assuming you have the proper indexes defined, the following query may run faster:

SELECT A.REG_PRICE
FROM   PRICE_TABLE  A
WHERE  ITEM_ID = 40 and 
       STORE_ID = 992 and
       A.EFFECTIVE_DT<={d '2005-12-31'} AND
       NOT EXISTS (
         SELECT *
         FROM PRICE_TABLE B
         WHERE
           B.EFFECTIVE_DATE > A.EFFECTIVE_DATE AND 
           B.ITEM_ID = A.ITEM_ID AND 
           B.STORE_ID = A.STORE_ID AND
           B.EFFECTIVE_DT<={d '2005-12-31'});

 

[ddiamond]

If you add the date criteria to Ties solution would that work?

Select EFFECTIVE_DT from PRICE_TABLE where EFFECTIVE_DT<={d '2005-12-31'} order by EFFECTIVE_DT desc fetch first 1 rows only