Re: How to efficiently make an "history" ?

From: kesako <kesako_at_mail.com>
Date: Thu, 30 Aug 2001 19:06:12 +0200
Message-ID: <9mlrq5$99u$1_at_news1.span.ch>

Theo,

I follow you now: your point was related to my missing related entity.

I did some testing on SQL Server with the data structure you mentionned with 600'000 rows.

If I do queries like "Give me the last values of attributes Name, City, ... (6 fields) for Entity E1 at time T1", I get (on a not very impressive PC) about 9-15 queries per second.*

If I do queries like "Give me the last values of attributes Name, City, ... (6 fields) for Entities E1, E2, ... (5 entities) at time T1", I get about 2-4 queries per second.**

Cheers,

                        Sacha

*)
SELECT DISTINCT EA_Detail.*

FROM         t_ENTITY_ATTRIBUTES EA_Detail INNER JOIN
                          (SELECT     MAX(DateVersion) AS MaxVersion,
AttributeName AS RelatedAttribute
                            FROM          t_ENTITY_ATTRIBUTES EA_Group
                            WHERE      EA_Group.DateVersion < '1995-1-7
22:41:00' AND (EA_Group.EntityID = 1)
                            GROUP BY AttributeName
                            HAVING      EA_Group.AttributeName IN ('City',
'Name', 'Lastname', 'Firstname', 'Phone', 'Country')) DERIVEDTBL ON
                      EA_Detail.DateVersion = DERIVEDTBL.MaxVersion AND
DERIVEDTBL.RelatedAttribute = EA_Detail.AttributeName
WHERE     (EA_Detail.EntityID = 1)

**)
SELECT DISTINCT EA_Detail.*

FROM         t_ENTITY_ATTRIBUTES EA_Detail INNER JOIN
                          (SELECT     MAX(DateVersion) AS MaxVersion,
AttributeName AS RelatedAttribute
                            FROM          t_ENTITY_ATTRIBUTES EA_Group
                            WHERE      EA_Group.DateVersion < '1995-1-7
22:41:00' AND (EA_Group.EntityID in (1, 3, 5, 6, 8))
                            GROUP BY AttributeName
                            HAVING      EA_Group.AttributeName IN ('City',
'Name', 'Lastname', 'Firstname', 'Phone', 'Country')) DERIVEDTBL ON
                      EA_Detail.DateVersion = DERIVEDTBL.MaxVersion AND
DERIVEDTBL.RelatedAttribute = EA_Detail.AttributeName
WHERE     (EA_Detail.EntityID in (1, 3, 5, 6, 8))

"Theo Peterbroers" <peterbroers_at_floron.leidenuniv.nl> wrote in message news:39bb2c10.0108300644.5ff9cf75_at_posting.google.com...
> "kesako" <kesako_at_mail.com> wrote in message
news:<9ml0jg$ojd$1_at_news1.span.ch>...

> > Hello Theo,
>
> <SNIP>

...
> Question: Did John change his name to Adam or is Adam a new person? ...

> Aha, John did change his name.
> (Id003, 'Name', 'Adam', 'Nice name',2000083000990etc.)
> Now there are two Adams.

Received on Thu Aug 30 2001 - 19:06:12 CEST