How to send 1000 INSERTs to MySQL at a time?

[kwein123]

I love SimplySql for individual SQL queries. I have an application where I will be uploading millions of records across a WAN as fast as I can. What's the best way to efficiently send, say, 1000 INSERTs at a time, in one batch/transaction? I am the only one accessing this table.

[mithrandyr]

@kwein123 There are a couple of options to do this and a lot depends upon where the data is originating from and what SQL provider you are using. However, some basic considerations for performance. Leverage transactions.

#this will always be faster than
Start-SqlTransaction
Invoke-SqlUpdate -Query $insertQuery -Parameters $queryParams #first insert
Invoke-SqlUpdate -Query $insertQuery -Parameters $queryParams #second insert
Invoke-SqlUpdate -Query $insertQuery -Parameters $queryParams #third insert
# ... a bunch more
Invoke-SqlUpdate -Query $insertQuery -Parameters $queryParams #1000th insert
Complete-SqlTransaction

#this will always be slower than the above
Invoke-SqlUpdate -Query $insertQuery -Parameters $queryParams #first insert
Invoke-SqlUpdate -Query $insertQuery -Parameters $queryParams #second insert
Invoke-SqlUpdate -Query $insertQuery -Parameters $queryParams #third insert
# ... a bunch more
Invoke-SqlUpdate -Query $insertQuery -Parameters $queryParams #1000th insert

However, there are other things you can do to improve this as well. I'm assuming the data you have to insert has to be gathered somehow. Well, you can always use the SQLite provider to create a local copy of that data in the relational format. So create a SQLite database (just open a connection by passing a filepath, DB will be automatically created if the file doesn't exist), create a table that matches your destination table. Then, insert data into the SQLite database (again -- use transactions). Once that is complete, then open a connection to your destination database (provide a ConnectionName) and then use the Invoke-SqlBulkCopy to fast load data from the SQLite database to your destination.

You can optimize the above, by copying the SQLite db file across the wan first (make sure to close your SQLite connection before copying the file), and then running the Invoke-SqlBulkCopy command from a system on the other side of the wan.

Anyways, if you want to provide more details, I'm happy to brainstorm a bit on how to leverage SimplySql in a performant way.

[mithrandyr]

@kwein123 -- using SQL transaction means that the DB server will receive the result, but only in memory, not commit to writing to disk.. when you complete the transaction, it will write everything to disk at once -- ok, thats a very generic explanation.

So, your options are -- each server runs its scan and then writes inserts within the transaction and then completes the transaction. OR write to a local SQLite DB first (depending on how much data at a time, you can use the inmemory version) and then do a bulkcopy from SQLite to MySql and that should be very fast.

Does that make sense, or do you want a sample?

[mithrandyr]

@kwein123, I had some time, so here is an approach.

$searchPath = "E:\Media\videos"
$tableCreation = "CREATE TABLE inventory (server varchar(50), dirName varchar(255), fileName varchar(255), attributes int, created datetime, modified datetime, accessed datetime)"

# I'm going to use the ability to pipe objects into Invoke-SqlUpdate, so the parameter names have to match the object names exactly...
$insertQuery = "INSERT INTO inventory (server, dirName, fileName, attributes, created, modified, accessed)
                VALUES (@server, @DirectoryName, @Name, @Attributes, @CreationTime, @LastWriteTime, @LastAccessTime)"

# open SQLite in memory (because its all in memory, you don't need to wrap inserts in transactions), give it a ConnectionName so we can have multiples connections open simultaneously...
Open-SQLiteConnection -ConnectionName src
Invoke-SqlUpdate -cn src -Query $tableCreation | Out-Null #this just creates the table

# ok, '$null = ' means no output gets displayed from Invoke-SqlUpdate and its faster than 'out-null'
# because we want to tag these to a particular computer, we are extending the objects returned by Get-ChildItem with a new property 'server' that matches the parameter we expect...
$null = Get-ChildItem -Path $searchPath -Recurse -File |
    Add-Member -NotePropertyName server -NotePropertyValue $env:COMPUTERNAME -PassThru |
    Invoke-SqlUpdate -cn src -Query $insertQuery #we are piping all the objects to Invoke-SqlUpdate, each object will cause ISU to fire once, using the members of the object as parameters...

Open-MySqlConnection -ConnectionName dst -Credential (Get-Credential)
#Invoke-SqlUpdate -cn dst -Query $tableCreation  #only needed for my purposes to create a destination table

#this is the real magic, super fast loading from one database to another.. you can use a source query (instead of table) for the source data, you can determine mappings by providing a hashtable, check the help on Invoke-SqlBulkCopy
Invoke-SqlBulkCopy -SourceConnectionName src -DestinationConnectionName dst -SourceTable inventory -DestinationTable inventory
Invoke-SqlScalar -cn dst "SELECT count(1) FROM inventory" #just for my testing to verify that items got loaded.
Close-SqlConnection dst, src

The overall approach is very minimal -- use a pipeline for your scanning, modify the objects in the pipe to add the computer name, send to Invoke-SqlUpdate (data will get stored in a SQLite in memory database). Then create connection to MySql database and use Invoke-SqlBulkCopy to fast copy data from SQLite to MySql. Just a couple of lines (at the end of the day) for an efficient process to get and load data.

Hope you find this helpful!

[kwein123]

This is fantastic. Let me play around with this and see if I can wrap my brain around it. Thanks so much!

[mithrandyr]

@kwein123 -- is it working for you, did you figure it out?

[kwein123]

Late last evening I got it all to click, thanks to your detailed example! Next I'm going to do some speed tests.