Add a lot of data into database using several stored procedures

Question

I have 3 stored procedures AddProduct, AddProductProperties and AddOffer. I need to import data from various datasources in database using this procedures. Structure of datasources is variable, so it may contains only products or offers or both. My algorithm is to read record from data source and then call necessary procedures. To call procedures I use MySqlCommand with parameters.

Questions:

1. What is the fastest way to call procedures in that manner?
2. Do I need to store 3 MySqlCommand objects and change only parameters or it doesn't matters?
3. How can I cache these calls (for each call I see record in trace log "Retreiving procedure metadata for ProcedureName")?

Answer 1

Two suggestions:

Use the Prepare method on MySqlCommand

The MySqlCommand class has a Prepare method on it. If you have to call these stored procedures repeatedly, then I would use that in order to try to minimize the amount of work that has to be done per execution. Below is roughly the steps that I think would be involved:

1. Set up the command objects (one per stored procedure) including any necessary parameters.

2. Open the connection to the database

3. Call the Prepare method for each command once

4. Iterate over your data

   4.1. Update the values of the command's parameters according to your data

   4.2. Call the appropriate Execute method on the command's

I'm not sure how much performance benefit you will really get from this, though.

If you were executing ad-hoc statements instead of procedures (or perhaps even with procedures), then I think an import thing to note is to use parameters instead of simply concatenating your values into the text of the command. If you were to use the string concatenation approach, which I think is generally a bad idea in most situations anyway, then you might not be able to take advantage of the Prepare method, as you will have to modify the command for each execution.

Is there a BulkInsert/BulkCopy option available?

I'm not as familiar with MySQL as with SQL Server; ADO.Net has bulk copy functionality that you can use to insert a large amount of data into SQL Server. I don't know if similar functionality is available with MySQL, but if it is and you have a lot of data to insert, then consider using that instead of repeatedly calling stored procedures.

Answer 2

Multi-record SQL updates are the key

Speed and individual calls to inserts are like oil and water. (oh, not a good statement to use in these times).

Strategies...

• Import the various data sources into temp tables matching their layouts. Use the db's built-in importing utilities to import the flatfiles (or tables attached to other databases, or odbc, etc - if you can get the external data source to appear as an attached table you can skip the temp table part)
• Use statements like INSERT INTO AddProduct ... (Select ... from TempProducts1) - or the linked table - will put all the records in at once
• If you have to manage collisions in the same dest table from multiple input formats, you can still do it from sql bulk queries.
  
  • You have to use temp tables then
  • If temp1 trumps temp2 then

    Delete from temp2 where temp2.id in (select id from temp1) 

    or simliar before import.
• You could do similar by updating a join of the tables, left outer joins checking for nulls or not in the right side's PK field, etc.
• Drop the temp tables. NO COMPLAINING about temp tables! You're not multiuser I'm betting. You're the DBA I'm betting. Extreme speed = extreme measures.

Once I even swung something like this where conflicting items later on in the file had to overwrite earlier items, with builk update/insert/delete queries and temp tables, from an external flatfile. Took 5+ huge ugly sql statements. Not fun but about 100x faster than code on the same box doing inserts. Which is the analoge of a single SP doing inserts (ok maybe not that bad, it was vb4 and access jet2.0:-). But, a networked client would do WORSE because of roundtrips, etc.

You can also cure cancer with Multi-record SQL updates.