Page 1 of 1

Converting Stored Procedures to Python: Issues

Posted: Tue Apr 23, 2013 6:05 pm
by clv100
We are moving from an Informix DB to Vertica and have to convert ALL our Informix stored procedures to some kind of combination of SQL and Python. However, that is not working out very well and I am at a loss on how to circumvent these issues. There is no problem converting the stored procedure to a python script BUT the time to insert/update tables through a loop is unacceptable in the python scripts. It is taking hours for a simple insert/update to complete for 10000 rows. The stored procedures are based on FOREACH loops and converting them to Python makes them virtually impossible to run time wise. I would appreciate any help and/or possible alternatives with this issue!
Thanks,
CVezza

Re: Converting Stored Procedures to Python: Issues

Posted: Thu Apr 25, 2013 6:52 pm
by doug_harmon
Hopefully this doesn't apply in your case, but I've seen very slow performance when loading records into Vertica via pyodbc due to the Python program being designed to do a commit after every record. A much better design is to commit records in batches.

Also, have you looked at Pyvertica from Spilgames?
https://pypi.python.org/pypi/pyvertica