Den 2012-07-23 07:32 skrev Kjell Rilbe såhär:

> Den 2012-07-22 21:59 skrev Kjell Rilbe såhär:
>> Trying to figure out a way to do a search/query with decent performance.
>>
>> Table "Master" contains roughly 100 million records and table "Detail"
>> contains roughly 200 million records.
>>
>> Both have a bigint "Id" primary key and "Detail"."Master" links to
>> "Master"."Id" in a regular master-detail relationship. "Detail"."Master"
>> is indexed, but there's no FK constraint (never mind why).
>>
>> "Detail"."Removed" TIMESTAMP is nullable and NOT indexed.
>> "Detail"."RemovedBy" bigint is nullable (FK to a third table) and IS
>> indexed.
>>
>> If a detail has been removed, "Removed" is set to the time of removal
>> and "RemovedBy" is set to the id of a record containing data about the
>> removal, e.g. who did it and so forth.
>>
>> Business logic says that if a master has any details at all, at least
>> one of them should be current, i.e. NOT be removed. So, at least one
>> detail should have "Removed" and "RemovedBy" set to null.
>>
>> I've found a breech of this rule in my data and I want to find if there
>> are more.
>>
> Having solved that (after 9 hours query execution), I'm thinking "How
> can I prevent this from happening again?"
>
> So, could this have been prevented using some kind of DB constraint?
> I.e. Make it impossible to remove a details without also adding a
> current one...?
>
> The O/R mapper probably can't be guaranteed to issue the addition of a
> current detail (insert) before issuing the removal of the old one
> (update...set "Removed" and "RemovedBy" to non-null).

I note that it would have helped to split the link between masters and
details into current ones and historic ones, having two FK columns in
"Detail", one for each "state" or one for current ones and one for both
current and historic ones. That would actually be rather easily
implemented at the model level using the model driven framework, but
would require some refactoring in the application's code... But it might
actually speed things up in various other places too...

Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64