Bulk Insert performance

I have a situation where I need to do multiple inserts into the sql mobile db at one time. I am wondering what would be the most efficient method to do this. Right now I am just doing many inserts, but the performance is lacking. I tried to wrap all the inserts into 1 sql command and process it like that, but it does not seem to want to execute. Any help would be appreciated.

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Darren,

What is the best method to import a large amount of data into SQL Mobile? Are you saying that Batching isn't supported? Also, SQL Server will not be installed on our servers because of licensing costs. So this takes out the replication and RDA methods for imports.

In a C# application, I'm reading ASCII files and need to add to three seperate tables. It takes hours to process 100,000+ records. BTW - I need to support over 2 million records. The total file size will be around 1 gig. Since SQL CE supports files up to 4 gig, I'm not expecting this to be a problem. I'm I right here?

I'm creating an Item Lookup function that will run stand alone in our retail chain. The data (SDF file) The data will be refreshed nightly on the server and will be copied down to the device daily. If you have any suggestions you can email me directly @. jhoran@.fheg.follett.com

Here is an example of my code to add to the vendor table. I'm only using 2 fields in this example.

Why is it so slow?

.......

string p1, p2;

// loop through Vendors

int Len = 0,i=0;

try

{

using (StreamReader sr = new StreamReader("VENDOR.EXP"))

{

string line;

Status = "Exporting Vendors";

while ((line = sr.ReadLine()) != null)

{

Len = line.Length;

p1 = line.Substring(0, 9);

p2 = line.Substring(9, (Len - 9));

i = i++;

this.vendorTableAdapter.Insert(p1,p2);

}

catch (System.Exception ex)

{

MessageBox.Show(ex + " Vendor File could not be read");

}

Darren Shaffer wrote:

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Let me see if I can answer your questions:

1. SQL Mobile/Everywhere/Compact Edition databases have a 4GB limit. If you plan to grow one of these databases larger than 128MB (the default maximum), then you need to set the max database size in your connection string to something larger.

2. In terms of your insert statement - using the TableAdapter is equivalent to using one insert statement after the next - you would be able to realize faster insert time (30-50%) with a parameterized insert statement (be sure to call Prepare() on the command one time and then change the parameter values and reuse the command).

3. If you are loading up this large database with vendor lookup data, you might ask yourself if you could perform this load on the desktop versus on device and then deploy the resulting sdf file to device. For databases as large as yours, you will get the sdf file loaded much much faster on the desktop given the CPU and disk speeds available there.

4. With a database of this size on device, be aware that you should have free storage memory in an amount at least equal to the database size to realize an effective tempdb and hence decent performance.

-Darren

|||This was convered a couple of says ago :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=923795&SiteID=1

Using the resultset makes no difference to the insert performance either|||

Thanks Darren,

Pardon my ignorance, but I'm new to C#, VS2005 and SQL, but I've been writing code for decades.

2. Can you send me an example or point me to one, of how to avoid using the tableadapter? I was thinking that it was passing parameters.

3. The largest table will be the item table, but I'm using the same technique on it. We have less than 1000 vendors, but we could have 2.8 million items.

I agree about creating the file on the server and sending it to the device. That is in the design. Is activesync the best way for this or would we be better off to "roll our own"?

4. we are planning on at least 2 gig of memory.

p.s. I liked your Webinar on Mobile device development. It gave me a good jump start on this project. I've been working with mobile computing using C++ v1.52 for a long time, and don't expect that it will take all that long to get up to speed with this.

Thanks again for your help!

|||

Jack,

Instead of using the table adapter, try a parameterized insert. Here is the "pseudo code" to do this:

SqlCeCommand cmd = null
SqlCeParameter param = null

try

cmd = _mysqlceconnection.CreateCommand()
cmd.CommandText = "INSERT INTO tablename VALUES (@.Param1, @.Param2, @.Param3)"

param = New SqlCeParameter("@.Param1", SqlDbType.UniqueIdentifier) // set each parameter's type to match the column in the database
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param2", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param3", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)

cmd.Prepare() // this allows SQL CE to predetermine an execution plan for the insert and cache it

// note you do this one time and it is outside the insertion loop below

While ' loop through your input file line by line, one row per line

cmd.Parameters("@.Param1").Value = firstColumnValueInYourInputFile

cmd.Parameters("@.Param2").Value = secondColumnValueInYourInputFile

cmd.Parameters("@.Param3").Value = thirdColumnValueInYourInputFIle

etc until you have set all the params in the INSERT statement

cmd.ExecuteNonQuery()

End While

I recommend that anytime you are inserting or updating data in SQL Server you use parameterized SQL - the first time your data contains an apostrophe, a comma, an ampersand, etc, you'll see why.

-Darren