I just finished reading 'The Art of SQL' by Stephane Faroult who has a
chapter (ch 6) discussing this very topic. I strongly recommend any
developer dealing with databases take a few days to read this
narrative.
A solution would seem to depend on whether you have many objects which
change in measurement only occasionally or if your have very few
objects whose measurement change very frequently. If you have a
chance, go to your local big name bookstore who allow you to sit and
take a read of the book, jump to ch6 (page 156 or so) and get a good
understanding of some various techniques to take into consideration.
A good suggestion (imo) is the composite key already mentioned. If I
can give an example from the book in the case you have many objects
whose measurements change occasionally:
select whatever
from object_val as outer
where outer.item_id = someValueForObjectX
and object_val_type_id = someValueForTypeA
and outer.observation_date = ( select max(inner.observation_date)
from object_val as inner
where inner.item_id =
someValueForObjectX
and
inner.object_val_type_id = someValueForTypeA
and
inner.observation_date <= yourReferenceDate )
Hopefully, I haven't completely mis-understood the author's intentions
and gave you some silly query. Some may argue that the inner.item_id
could be correlated to the outer.item_id, but then the inner query
would need to be run multiple times. If used as presented, the inner
query fires only once.
I'd be rather interested in knowing if the above query (or similar)
works in practice as occassionally can sound good on paper, but doesn't
work in the real world - I'd like to know if in your situation it
provides a good execution time. I would take the step of creating that
composite key as suggested by Ragnar first.
Good luck,
am.
"Silvela, Jaime (Exchange)" wrote:
> This is a question on speeding up some type of queries.
>
> I have a very big table that catalogs measurements of some objects over
> time. Measurements can be of several (~10) types. It keeps the
> observation date in a field, and indicates the type of measurement in
> another field.
>
> I often need to get the latest measurement of type A for object X.
>
