Copying 30M rows (across 15 tables) from SQLite into a MariaDB store
Original python DB-API code did single inserts, got 5.2K per second
overall.
Found some bulk insert ideas on stack exchange, finessed an
implementation and got over 50K inserts per second on some data!
"TABLEONE: 1.50 secs to post 75545 rows (50353 per sec)"
With the new code the avg for all 30M rows across all tables was around
16K per sec.
Worked on the code for some hours to save 30-45 minutes in data loading time... but 50K inserts/sec is pretty darn good for my 11-year-old
i5-750 8GB RAM system.
Raise a glass!
Very understandable. Each INSERT must be parsed and executed by the RDBMS. Parsing a text SQL statement and determining how to execute it is very time consuming, much more than actually doing the SQL statement.
That's why enterprise-level databases like IBM's DB2 have a means to preprocess static SQL statements and build access plans to do the work. The resultant code doesn't actually submit the SQL statement but instead calls a function which executes the pre-built access plan.
On 26.08.2021 01:26, Jerry Stuckle wrote:
Very understandable. Each INSERT must be parsed and executed by the RDBMS. >> Parsing a text SQL statement and determining how to execute it is very time >> consuming, much more than actually doing the SQL statement.
That's why enterprise-level databases like IBM's DB2 have a means to
preprocess static SQL statements and build access plans to do the work. The
resultant code doesn't actually submit the SQL statement but instead calls a >> function which executes the pre-built access plan.
That's a typical Stuckle post. MySQL introduced prepared statements in version 4.1. That was somewhere around 2005. Whopping 16 years in the past. Yet still he tries to sell it as a feature specific to enterprise RDBMS...
On 26.08.2021 01:26, Jerry Stuckle wrote:
Very understandable. Each INSERT must be parsed and executed by the RDBMS. >> Parsing a text SQL statement and determining how to execute it is very time >> consuming, much more than actually doing the SQL statement.
That's why enterprise-level databases like IBM's DB2 have a means to
preprocess static SQL statements and build access plans to do the work. The
resultant code doesn't actually submit the SQL statement but instead calls a >> function which executes the pre-built access plan.
That's a typical Stuckle post. MySQL introduced prepared statements in version 4.1. That was somewhere around 2005. Whopping 16 years in the past. Yet still he tries to sell it as a feature specific to enterprise RDBMS...
On 8/25/2021 2:07 PM, DFS wrote:
Copying 30M rows (across 15 tables) from SQLite into a MariaDB store
And BTW - with so many rows you'd probably get even better response by DROPping any indices before the INSERT and recreating them after the
INSERT.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 47:04:03 |
Calls: | 6,648 |
Files: | 12,198 |
Messages: | 5,329,919 |