SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-19-2008, 08:50 AM
| ||||
| ||||
 Re: single transaction vs multiple transactions Sven Geisler wrote: > I have to refactoring a 'DELETE FROM x WHERE y IN (...)' because IN got > to much parameters. => 'stack depth limit exceeded' > I don't want to increase just the parameter for max_stack_depth. It is > better to refactoring because the number of arguments to IN may increase > in the future. > > My approach is to do multiple 'DELETE FROM x WHERE y=...'. You could also do something in between, issuing the deletes in batches of say 100 deletes each. But using a temporary table is much better. > My question is now, what is better for PostgreSQL from a performance > perspective? > 1. all multiple deletes in one transaction > 2. each delete in its own transaction All in one transaction is definitely faster. > The number of arguments is around 10,000. > > BTW: The arguments are generate in the application tier. I would have to > create a temporary table which I can use in 'DELETE FROM x WHERE y IN > (SELECT z FROM tmp)'. I think that's exactly what you should do. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org      |
|
04-19-2008, 08:50 AM
| |||
| |||
| Re: single transaction vs multiple transactions Hi Heikki Heikki Linnakangas schrieb: > Sven Geisler wrote: >> I have to refactoring a 'DELETE FROM x WHERE y IN (...)' because IN got >> to much parameters. => 'stack depth limit exceeded' >> I don't want to increase just the parameter for max_stack_depth. It is >> better to refactoring because the number of arguments to IN may increase >> in the future. [...] >> >> BTW: The arguments are generate in the application tier. I would have to >> create a temporary table which I can use in 'DELETE FROM x WHERE y IN >> (SELECT z FROM tmp)'. > > I think that's exactly what you should do. I have to insert my arguments to a temporary table first, because the arguments are only known in the application tier. Is a multiple insert to a temporary table and a final 'DELETE FROM x WHERE y IN (SELECT z FROM tmp)' faster than multiple deletes? Sven. ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate |
|
04-19-2008, 08:50 AM
| ||||
| ||||
| Re: single transaction vs multiple transactions Sven Geisler wrote: > I have to insert my arguments to a temporary table first, because the > arguments are only known in the application tier. > Is a multiple insert to a temporary table and a final 'DELETE FROM x > WHERE y IN (SELECT z FROM tmp)' faster than multiple deletes? If the number of records is high, it most likely is faster. You should try it with your data to be sure, but in general doing all the deletes in one batch is faster when the number of records is high because it allows using efficient merge joins or sequential scans. Populating the temporary table with no indexes should be quite inexpensive if you make sure you don't do it one record at a time. Use the COPY command or batched inserts instead. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
Linear Mode
