Very Large Insert

[sebs]

Hey Guys,

I have to populate an MS SQL database for a client, he has a database (.dbf) that his store uses and I am writing an app that is reading all of those record and insert them in a MS SQL database for his online store.

There's around 50 000 record and so far I open his .dbf database, go thru all the record and thought that it would be better to build a file with all my insert in it instead of doing it every loop.

But when it commes time to send it to the sql database, it takes very long and it looks like it hangs.

Here's my function that updates the database:

Private Sub updateWebDatabase()
  Dim sqlStatement As String
  Dim fso As New FileSystemObject
  Dim fle As TextStream
  
  lblProgress.Caption = "Saving to Web Database"
  lblProgress.Refresh
  
  Set fle = fso.OpenTextFile(sqlTxtPath)
  
  sqlStatement = fle.ReadAll
  
  Set fle = Nothing
  Set fso = Nothing
  
  Call openDB("MsSQL")
    Set rs = conn.Execute(sqlStatement)
  Call closeDB
End Sub

Is there a way to submit my file to the sql database or is there a better way than sending it 50 000 insert's ?

Thanks a lot

[DKenny]

Have you considered using a DTS package to do the load?

[sebs]

Have you considered using a DTS package to do the load?

DTS ?

Not very familiar with this, please explain...

[DKenny]

Are you using MS SQLServer?

[sebs]

Yes, I am, sorry for not specifying in first post.

[DKenny]

DTS - Data Trasformation Services is an integrated component in SQLServer and is very useful for bulk loading of data.
Have a look in BOL, there is an extensive section dedicated to DTS.

[sebs]

The problem is I don't have access to the sql server, what I mean is that the server is on our hosting company server, so I don't think I can use DTS, which is what I would need.

Heck, I can't even use BULK INSERT's

Any other ideas ?

[RobDog888]

Why cant you use the sql BULK INSERT sql statement?

[szlamany]

Probably because the "path" to the BULK INSERT file is "server centric" and with a remote server, there is no way for that server to see a "local" file.

50,000 INSERTS - done one at a time - will be slow. Maybe putting a DOEVENTS in the LOOP that does the INSERT's will make it stop appearing to freeze.

[ZenMan]

How about doing the update in a seperate thread?

Check out Multithreaded Programming

[sebs]

RobDog888: Because I don't have permission and it's soemthing they will look into when they upgrade.

I had to go to the one by one route, because I need to finish this ASAP, but now I am looking into how I can make it go faster, like using adUserClient for the cursorLocation made it at least twice as fast.

Any other ideas to make it go faster ? What kind of cursor type ? I never really paid much attention to the cursorType and lockType...

ZenMan: Thanks, but that's something I should of look into at the beginning, I don't have time anymore

[brucevde]

Some things to try.

Don't use the Connection.Execute method, use Command.Execute. It will allow you to create a prepared statement, which means the sql is only parsed once and sql server will create an execution plan. I don't think you will gain anything by creating a file of sql statements first.

Since you are doing an Insert don't create a recordset, use the adExecuteNoRecords option.

Private Sub updateWebDatabase()
  Dim sqlStatement As String
  Dim cmd as ADODB.Command
 
  Dim fso As New FileSystemObject
  Dim fle As TextStream
  
  lblProgress.Caption = "Saving to Web Database"
  lblProgress.Refresh
  
  Call openDB("MsSQL")
   
  Set cmd = New ADODB.Command
 
  With cmd
     Set .ActiveConnection = conn
     .CommandText = "Insert Into TableName Values(?,?,?,?) 'assumes table contains 4 fields
     .CommandType = adCmdText
     .Prepared = True
     'create a parameter for each field 
     .Parameters.Append .CreateParameter("@Field1", adVarChar, adParamInput,30)
     .Parameters.Append .CreateParameter("@Field2", adInteger, adParamInput)
end With
 
  For each record in DBF Table
      cmd.Parameters("@Field1").Value = data from DBF
      '..etc
      cmd.Execute lngRowsAffected,,adExecuteNoRecords
  Next

End Sub

It is probably possible to send 2 inserts at once using something like
.CommandText = "Insert Into TableName Values(?,?,?,?); Insert Into TableName Values(?,?,?,?)"

This of course means you must double up on the parameters.

Adding a Begin Trans on the connection object should speed up things as well. Maybe call commit every 1000 records or so.

[sebs]

I like that !

I can do the same with updates, right ? Just need to set two command ? cmdAdd and cmdUpdate...

Thanks, I will try that.

[sebs]

One question bruce,

What is the lngRowsAffected in the cmd.execute ?

Thanks

[szlamany]

Bruce - I've never seen that before...

Do you really just put ?-marks in the INSERT VALUES () and then refer to them as @FIELD1, @FIELD2 and so on??

[sebs]

Bruce - I've never seen that before...

Do you really just put ?-marks in the INSERT VALUES () and then refer to them as @FIELD1, @FIELD2 and so on??

Looks like it, pretty cool

[sebs]

Ok, I made it work, but I do not see a big difference compare to

Conn.Execute("INSERT INTO...")

Hmm, I thought it should be faster.

[szlamany]

Are you inserting into an existing table?

Does that table has indexes and such??

If so, INSERT into a staging table - an empty table - with no INDEXES.

That should run faster then one-by-one inserts into a production table.

Then after you load the staging table do an:

INSERT INTO REALTABLE SELECT * FROM STAGINGTABLE

Just a thought...

[sebs]

yes, into an existing table.

I did not created indexes, I created my db in access then use the upsize wizard to put it on my sql db.

the problem is I will need to to insert and updates, mostly insert for the first time, then it will be mostly updates.

It's an app to update my client online store database with his store database.

Thanks for all your help.

[brucevde]

Do you really just put ?-marks in the INSERT VALUES () and then refer to them as @FIELD1, @FIELD2 and so on??

The ? are considered parameter placeholders. The parameter names ("@Field1" etc.) can be anything you want. I usually use the name of the table column.

Note that in ADO.NET, the data provider for sql server does not support the ? placeholder but requires named parameters.

[brucevde]

What is the lngRowsAffected in the cmd.execute ?

This argument will indicate how many records the sql command modified. For inserts it should always be 1. However, for updates it could obviously be any number.

[sebs]

This argument will indicate how many records the sql command modified. For inserts it should always be 1. However, for updates it could obviously be any number.

Thanks, I just omit it since it's optional.