Slow inserts with indentity column
ttvv
Community Edition User
Hi all,
I'm having issue with slow inserts from joined tables into one destination table. I'm using INSERT / SELECT, not row by row insert or similar.
My destination table has 80 columns with IDENTITY(0,1,1) as primary key.
Insert for just 400 records takes 5 seconds.
SELECT statement on joined tables is fast, but when I combine it with INSERT it takes 5 seconds for this amount of records.
Anyway, if I remove IDENTITY column from destination table, it takes just 200 ms.
Is there any proper way how to handle this, because gap between those two results is so huge.
I'm using Vertica inside Docker on Windows environment.
Cheers
Best Answer
-
Options
Jim_Knicely
- Select Field - Administrator
Hi,
The problem isn't the IDENTITY per se, it's that you disabled the cache! When you create an IDENTITY column Vertica creates a sequence to back it. You've disabled the cache on the sequence, so for each row inserted Vertica has to grab a catalog lock to generate a new sequence value!
Here is the doc link AUTO_INCREMENT and IDENTITY Sequences.
Note that the third parameter you passed to IDENTITY was 1. A value of 0 or 1 specifies to disable caching.
For further details on this topic, check out the doc page Sequence Caching!
And here's a link to a Vertica Quick Tip titled "Be Careful with the Sequence CACHE Value"...
1
Answers
@Jim_Knicely Thanks!