[Zope-DB] Working with ZPsycopgDA (or any DA) - commiting

[Charlie Clark]

Am 11.08.2006, 16:56 Uhr, schrieb <krokodylek at tenbit.pl>:

> Hi
> I'm working with a Postgresql database which I have to insert a lot of  
> data to... I'm using the ZPsycopgDA database adapter, and ZSQL methods.
> I have noticed that though I am calling the zsql method no data gets  
> really commited to the database. It all gets entered to database at the  
> very end.
> This behavior seems reasonable for keeping database consistent, but in  
> my situation it is really not so good - I have couple houndreds  
> megabytes of data and as I believe it all gets stored in memory before  
> being sent to the database causing serious system lags. I could be wrong  
> though - this is only my observation... What could be another reason for  
> slooow inserts?
> Either way, it would be far better for me if every "INSERT" would be  
> commited immediatelly or better yet if I could force a "commit" within  
> my code. Is there some way I can do it?

Maciej,

if you want transactional integrity then be prepared to pay the price: do  
not adjust Zope's transaction settings. As Andreas says you can make some  
adjustments on the server to speed up the process but please note that  
Zope is infamously slow for SQL statements although this usually does not  
play an important part. If you are doing a one off import then you are  
better working directly with Python. If you do need to work within Zope  
then note that using bound parameters should give you an additional  
performance boost over anything you can set on the server.

Charlie