Re: Slow delete when many foreign tables are defined

On 12/1/2014 10:21 AM, Giuseppe Sacco wrote:
> Il giorno lun, 01/12/2014 alle 09.49 -0600, Andy Colson ha scritto:
>> On 12/1/2014 9:23 AM, Giuseppe Sacco wrote:
>>> Hello,
>>> I have a main table and a lot of "details" tables that reference the
>>> main one.
>>>
>>> Every time I delete a record from the main table, a check is done on
>>> every details table that contain a foreign key toward main table.
> [...]
>> I can think of two options:
>>
>> 1) Don't use 50 different detail tables.  A single detail table with the
>> type column will work much faster.  Is there a good reason to break them
>> out?  (# rows is not a good reason, btw).
>
> Basically we do have a lot of different attributes on each details
> tables. Let's say we use 20-30 specific columns in each of them, so why
> should we waste disk space and CPU cycles for handling all these columns
> in one table? If I understand it, you are suggesting to add about 25*50
> columns in the main table and only set values for 25 columns.
>
> Moreover, our ORM would probably get crazy :-)
>
>> 2) Try inheritance.  I have no idea if it'll help, but I thought I'd
>> read someplace where the planner knew a little more about what types of
>> rows go into which tables.
>
> This would probably help, but we are blocked on ANSI SQL for easily
> porting our application to other DBMSes.
>
> Bye,
> Giuseppe
>
>
>


Oh, so the table structure of detail1 doesn't really match the structure
of detail2?  That'd be a pretty good reason to have lots of different
detail tables.

 > If I understand it, you are suggesting to add about 25*50
 > columns in the main table and only set values for 25 columns.

Nope, I didnt realize they were all different.

Hum.. how different though?  Maybe you could combine the similar one?
Maybe cutting the # of detail tables down from 50-100 to 25 would
increase speed enough?



-Andy