Vertica slow performance

naresh_way2

We are using Vertica for 1 month. Initially, it gave me analytics are so fast while data is updating. But since 7 days vertica giving a slower performance in analytics while data is updating. Updates are performed continuously (that is every time bunch of ids updates will take place) although Initially, my vertica gave a faster performance. How to resolve this problem. @Jim_Knicely

Jim_Knicely

HI,

When you perform an UPDATE in Vertica you are actually running a DELETE followed by an INSERT. So, UPDATEs are generating DELETE vectors which have the potential to degrade query performance!

Why?

"To eliminate rows that have been deleted from the result, a query must do extra processing. If 10% or more of the total rows in a table have been deleted, the performance of a query on the table degrades. However, your experience may vary depending on the size of the table, the table definition, and the query. If a table has a large number of deleted rows, consider purging those rows to improve performance."

See:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AnalyzingData/Optimizations/PerformanceConsiderationsForDELETEAndUPDATEQueries.htm

You may need to periodically purge deleted data manually!

See:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AdministratorsGuide/BulkDeletingPurging/PurgingDeletedData.htm