Performance Issue To Append 3 Millions Records From Access Table To SQL Server Table (1 Viewer)

[arjun5381]

I have to append 3 millions of records from MS access table to SQL Server Table, below is the VBA code for the same, This code is correct but it is taking lot of time (2000 Records Per Minute) to append 3 Millions records.

can you please help me to reduce processing time.


Private Sub cmdMoveToServer_Click()
Dim cmd As New ADODB.Command, RSdao As DAO.Recordset, RecordsCounts As Long
RecordsCounts = 0

Dim con As ADODB.Connection
Dim SQLStr As String

cmd.ActiveConnection = "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText

cmd.CommandText = "Truncate Table SQL_Server_Table" '--clear out contents of my table in Sql Server DB
cmd.Execute

Set RSdao = CurrentDb.OpenRecordset("Local_MS_Access_Table") '--local MS Access Table

DoEvents
Do While Not RSdao.EOF
cmd.CommandText = "Insert Into SQL_Server_Table Select '" & RSdao(0) & "', '" & RSdao(1) & "', '" & RSdao(2) & "', '" & RSdao(3) & "', '" & RSdao(4) & "', '" & RSdao(5) & "'"
cmd.Execute
RSdao.MoveNext
RecordsCounts = RecordsCounts + 1
Me.lblQueryCount.Caption = RecordsCounts & " Records Moved."
DoEvents
Loop

RSdao.Close
cmd.ActiveConnection.Close
Debug.Print "Done"

End Sub

 

[static]

Doevents and forcing the form to repaint will slow your code down quite a lot.
So you tend to only do it after so many loops instead of each one.

dim RecordsCounts As Long
Dim con As ADODB.Connection
Dim SQLStr As String

con.open constr

con.Execute "Truncate Table SQL_Server_Table"

with CurrentDb.OpenRecordset("Local_MS_Access_Table") 
	Do While Not .EOF
		con.Execute SQLStr
		.MoveNext
		RecordsCounts = RecordsCounts + 1
		if RecordsCounts > 10 and RecordsCounts Mod 10 = 0 then
			Me.lblQueryCount.Caption = RecordsCounts & " Records Moved."
			DoEvents
		end if
	Loop
end with
con.Close
Debug.Print "Done"

 

[CJ_London]

unless you need to see the progress, why not just do as a single append query - looping through code 3 million times is slow as well.

I can't remember the exact format but it would something like

INSERT INTO Sql_Server_Table IN (Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";")
SELECT Fld1, fld2, fld3, fld4 FROM Local_MS_Access_Table

Other things to do, remove all indexing from the sql table, then reinstate once the append is completed - updating indexes one at a time is also slow

another option if you want to see progress is to upload say 1,000 records per loop based on the above code

 

[arjun5381]

Doevents and forcing the form to repaint will slow your code down quite a lot.
So you tend to only do it after so many loops instead of each one.

dim RecordsCounts As Long
Dim con As ADODB.Connection
Dim SQLStr As String

[B]con.open constr[/B]

con.Execute "Truncate Table SQL_Server_Table"

with CurrentDb.OpenRecordset("Local_MS_Access_Table") 
	Do While Not .EOF
		con.Execute [B]SQLStr[/B]
		.MoveNext
		RecordsCounts = RecordsCounts + 1
		if RecordsCounts > 10 and RecordsCounts Mod 10 = 0 then
			Me.lblQueryCount.Caption = RecordsCounts & " Records Moved."
			DoEvents
		end if
	Loop
end with
con.Close
Debug.Print "Done"

Thanks a lot static Can you please help me to provide complete VBA Code according to you
con.open constr & con.Execute SQLStr


Here i just changed my code DoEvents every 1000 records it little bit fast ~ 8000 Records Per Second, previously it was 2000 Records Per Second.

 

[static]

The strings are whatever you used before. I just cut them out to make the code look neat.

 

[arjun5381]

INSERT INTO Sql_Server_Table IN (Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";")
SELECT Fld1, fld2, fld3, fld4 FROM Local_MS_Access_Table

Dear CJ_London, i am mot able to use your query code in my application, can you please help me to provide complete VBA code as per my requirement.

 

[arjun5381]

In my Local_MS_Access_Table & Sql_Server_Table
there are only 4 columns, one is primary key rest are text only.

I want to Append 3 Millions of Records from Local_MS_Access_Table To Sql_Server_Table

This will be run every day.

 

[arjun5381]

The strings are whatever you used before. I just cut them out to make the code look neat.

If i just use my code and DoEvents every 1000 records it is taking time ~ 8000 Records Per Second nothing much,

I have 3 Millions of records if i go with this speed then [3000000/8000/60 =~ 6.5 Hours]]/B]

 

[arjun5381]

My mistake it is ~ 8000 Records Per Minute

 

[stopher]

Looping like this is just plain slow. For one thing on every pass of the loop, all the statements in the loop have to be processed (except the on inside the IF statement).

If you are not convinced, then run the loop without the query and you will see it is still slow.

Just run a bulk query like CJ_London said.

 

[CJ_London]

Dear CJ_London, i am mot able to use your query code in my application, can you please help me to provide complete VBA code as per my requirement.

Sorry, no. Pretty much whatever you do with your vba is not going to be fast enough for your purposes. I do not have the time right now to work out what the connection string in the remote query actually would look like - you will need to investigate yourself, but it won't be that much different in run time than the below.

Your easiest solution is to create a linked table to your source data using the ODBC (no vba required) and then a straight forward append query.

Try that and see how long it takes - that is the benchmark for the fastest time the query will take to run - some simple code

debug.print "Started " & now()
currentdb.execute "myAppendquery"
debug.print "Finished " & now()

means you can go for a cup of coffee whilst it is running

If that is still too long, you need to look at alternatives - have the data loaded direct to SQL server rather than Access, run the query every hour/3 hours, put it on a timer and run at some point overnight whatever.

You need to understand the limitations before designing your solution. You seem to have started with the solution (which is the slowest way by far for your purposes) and now working backwards to get a decent performance which you won't do if you stick with current method.

For testing purposes, try selecting say top 10000 or top 50000 and time it. You will find as the sql server table grows the time gets longer because of the overhead of updating the index.

 

[The_Doc_Man]

Along the lines of CJ's comment, if you have the ability to do this, then you can get some speed by dropping the index, doing the SQL load, and then reasserting the index. If the database has to still be shared with others, specifically including the table you are updating, then you won't be able to drop the index, so in that case forget I suggested it.

CJ's suggestion to build a Linked Table description for the SQL backend is correct because SQL is compiled down to machine code. Access VBA (which is what you use when you apply recordset methods) uses semi-compiled code that is interpreted - which means that every iteration of the loop is interpreted rather than executed. The best interpreters I've ever seen are at the level of 50 native instructions per interpreted instruction, so right there your 3.2 GHz computer starts operating at speeds equivalent to a 64 MHz computer. 'bout like cold molasses.

 

[static]

In the time it's taken to discuss this the records could have been uploaded using the slowest method possible.

If an upload of 3 million records from Access to SQL server isn't a one off event, something is wrong with the process.

 

[The_Doc_Man]

Very good point, static. Regularly loading 3 million records to a server is a LOT of traffic if it isn't a one-off.

 

[jdraw]

Further to the questions and suggestions from others, how did the 3 million records get into Access?
Have you cleansed and validated the records you have?
Just curious.

 

[arjun5381]

Thanks a lot of you guys for your support, here i just test simple wizard query and trying to append record 3 million record, on this process it it is taking 1.5 hr time.

In my server table only one field have Indexed because it is a Primary key of this table.

Here i'm trying to Alter Server Table (Delete Primary Key before appending records And Adding Primary Key post records appended) programmatically but no luck.

can you help me to create/delete primary key on the server table using VBA code.

 

[arjun5381]

Further to the questions and suggestions from others, how did the 3 million records get into Access?

There are 0nly 4 Text columns where i just update current outstanding and age of network of customer, here i just want to append daily updates on sql server.

Have you cleansed and validated the records you have?
Just curious.

i did't get your question if you taking abut validation and rules.... There are no validation on text.

 

[arjun5381]

In the time it's taken to discuss this the records could have been uploaded using the slowest method possible.

If an upload of 3 million records from Access to SQL server isn't a one off event, something is wrong with the process.

I have a customer table where 3 million records are there, here we need to update customer details like [Current Outstanding & Ageing] on daily basis.

If i choose option "Update Table using VBA/Query Wizard" then it is not possible to update 3 million records every day because In my process to calculate Ageing & Outstanding there are multiple validations and multiple query's.

Here i just run all query's on ms access table and final result will append to a separate sql table.

 

[arjun5381]

Guys, which method is best for 3 Million Record (1) Append Query (2) Update Query.

 

[DavidAtWork]

If it's a one-off process or very infrequent process, have you considered using SSMS and import the data into the database direct.
Even if you pull it into a temp table and then write some SQL to append/transform from the temp into the destination, it will be so much quicker
David