mercredi 30 mars 2016

Performance issue with primary key

I am populating a medium-sized table (60GB, 500 million rows). The process completes reasonably fast if the table has no primary key (~1 hour using bulk insert), but it takes ~10 times longer if I create that table with the primary key. I assume this is because it takes time to verify the uniqueness constraint and also update the index at each insert.

I thought a good workaround would be to add the primary key later, since indexation on the table that's already populated should be much faster compared to incremental indexation. But sqlite doesn't seem to have the option to add primary key after the table is created (not sure why?).

I guess I could just not use a primary key at all, and instead just add a unique index after the table is populated. Is there any disadvantage to that?

Or any better solution recommended?

Aucun commentaire:

Enregistrer un commentaire