4 ms·
Inserting 100k rows 66 times faster
- throwaway143829 3y agoTLDR to get better performance, use bulk inserts instead of inserting one row per query. And (unrelated to DB perf) don't use the spread operator in JS for passing function args (the author says not to use the spread operator at all but you don't need to go that far)
- ak39 3y agoThank you. Also, what database was in use?
- throwaway143829 3y agosqlite3, but I think bulk inserts would improve perf for other DBs too.
- kikimora 3y agoPG has “copy” which would blow away bulk inserts. Other DB vendors have similar stuff for high-performance inserts. You would be much better off using those than using bulk inserts.
- dopp0 3y agosqlite
- vbezhenar 3y agoJDBC has "batch updates" feature. You can use single inserts but they're sent as a batch of multiple statements to the database. Not sure about JS libraries, but this feature also significantly improves performance and probably easier to use and maintain. For super fast inserts you can use postgres COPY feature. But it has to be implemented in the driver.
- deleted 3y ago[deleted]
- NathanaelRea 3y agoI wonder how `.import` would compare vs creating a huge batch insert string in memory.
- kikimora 3y agoI remember SQLite have been using inserts to implement import under the hood.
- lmz 3y agoI wonder if they tried prepared statements - parsing 100k identical insert statements surely is avoidable.
- xnacly 3y agoI didn’t even think of that, I‘m gonna have to try them out and compare against the two other implementations.
- avinassh 3y agoyou might like my post - Towards Inserting One Billion Rows in SQLite Under A Minute [0] [0] - https://avi.im/blag/2021/fast-sqlite-inserts/ https://avi.im/blag/2021/fast-sqlite-inserts/
- deleted 3y ago[deleted]