On May 20, 2009, at 7:17 PM, James B. Byrne wrote:
> Looking at this I have to wonder what will be the effect of having
> tens of thousands of rate-pairs on file. Would this query be
> improved by first doing a sub-query on base/quote pairs that
> returned DISTINCT pairs and then do the IN condition using that?
If it turns out to be a problem a way around is to keep a reference to
the 'actual' conversion rates from another table. Which ones are
'actual' would be updated by an INSERT trigger on your rates table.
The amount of data in the new table (and subsequently the index on
it's PK) would be far smaller and therefore likely a lot faster to
query.
I've done something similar in a database where a history of states
about records was kept around. Determining the 'actual' state was
relatively slow because it was difficult to determine a method to
uniquely point to it (the same issue with determining the latest
timestamp of a group of records for the same data).
Adding an FK from the record to it's 'actual' status record improved
things a lot and had the added benefit that other derived information
(detailed user information) was still easy to obtain with a simple join.
But as people often say here, premature optimisation is a waste of
time, so don't go that route unless you have a reason to expect
problems in that area.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4a15269c10092027810544!