Re: tables with 300+ partitions

[Pablo Alcaraz <pabloa@xxxxxxxxxxxxxxxxxxxx>]

Steven Flatt wrote:

On 10/30/07, Pablo Alcaraz <pabloa@xxxxxxxxxxxxxxxxxxxx> wrote:

I did some testing. I created a 300 partitioned empty table. Then, I
inserted some rows on it and the perfomance was SLOW too.

 

Is the problem with inserting to the partitioned table or selecting from it?  It sounds like inserting is the problem in which case I ask: how are you redirecting inserts to the appropriate partition?  If you're using rules, then insert performance will quickly degrade with number of partitions as *every* rule needs to be evaluated for *every* row inserted to the base table.  Using a trigger which you can modify according to some schedule is much faster, or better yet, use some application-level logic to insert directly to the desired partition.

 

Steve

I was a program inserting into the base table. The program ran in 200+ threads and every thread insert data on it. Every thread inserts a row every 3 seconds aprox.(or they used to do it), but when I put more partitions the insert speed went to 1 insert every 2 minutes.

The selects that need to evaluate all partitions were slow too, but I think I can wait for them. :D

I wonder if the update are slow too. I do not know that.

Do I need to do a trigger for insert only or I need a trigger to update and delete too?

To modify the appilication logic for this is not an options because they need to open the program, modify it and retest. All because an implementation problem. I prefer to try to solve it at the database level because the database need this table partitioned.

Thanks for your help

Regards.

Pablo