Vertica JDBC Connection

Raj_Murali

Hi, We are trying to integrate drools with Vertica, We have used Vertica JDBC Driver 4.1.14 for integration with the JAVA code. But we found that update query is taking so much time as compared to other databases in execution through JDBC Driver. We have to use it for around 8 million updates, so it is not possible if updates are taking so much time. For only 1,000 records,more than 1 minute is consumed. We have tried both the options( batch update and normal update). Even if we tried with HIbernate framework, it doesn't help us. It is taking time in 'session.flush()' method. Please suggest any solution to improve the performance. Thanks, Murali

[Deleted User]

Hi Raj, In Vertica and update is a delete and insert, so the performance is not that good. I don't think that the performance issues is because you are using the driver, does the performance is better when you do it directly in vsql? The slow part is the delete not the insert. In the documentation you will find a section that said how to optimize for deletes. https://my.vertica.com/docs/6.1.x/HTML/index.htm#12675.htm Take a look, Hope this helps. Eugenia

eli_revach

Query base projection can also lead to slow performence of delete , make sure that the projections you have include the where statment columns of the delete command

Raj_Murali

Thanks for the update. we tried Truncate and insert, the performance have improved.