Solved

Speed up write from Excel to Access

Posted on 2010-11-24
3
369 Views
Last Modified: 2012-05-10
The procedure below is run in an Excel module. In it several dependent variables which are functions of ten independent variables are calculated and written to Access. There are ten nested For loops; therefore, this procedure calculates tens of millions of values and writes millions of records to Access. This takes FOREVER so I'd like to employ the fastest way to write to Access.

I'm currently using dbs.CurrentDb.Execute "INSERT INTO ... VALUES ...".

aikimark suggested using recordsets instead of INSERT INTO. The new procedure using recordsets is in the code snippet. This procedure is MUCH slower than the same procedure using INSERT INTO ... VALUES ....

Is this to be expected or am I doing something wrong? Is there a way to speed up the code in the code snippet?

NOTE: I tried CSV files which are faster than either the recordset approach or the INSERT INTO approach but for some reason some decimal values are truncated to integer values which isn't acceptable.
Sub CostLoop()

	Set g_objConn = New ADODB.Connection
	g_objConn.Open g_sConnectionString

	Set rs = New ADODB.Recordset
	rs.Open "tbl", g_objConn, adOpenDynamic, adLockPessimistic

	For iCounter1 = 1 to (...)
		For iCounter2 = 1 to (...)
			For iCounter3 = 1 to (...)

			...

				For iCounter10 =
					
					iRecordCounter = iRecordCounter + 1
					If iRecordcounter = 1 Then g_objConn.BeginTrans
					
					
					dOutput1 = (...)
					dOutput2 = (...)
					dOutput3 = (...)
					...
					dOutput10 = (...)

    					'SETUP RECORDSET TO ADD A NEW RECORD
    					rs.AddNew

    					'ASSIGN FIELDS IN TABLE rs!<columnname in table> = <value>
    					rs!Variable1 = iCounter1
    					rs!Variable2 = iCounter2
    					rs!Variable3 = iCounter3
    					rs!Variable4 = iCounter4
    					rs!Variable5 = iCounter5
    					rs!Variable6 = iCounter6
    					rs!Variable7 = iCounter7
    					rs!Variable8 = iCounter8
    					rs!Variable9 = iCounter9
    					rs!Variable10 = iCounter10
    					rs!Output1 = dOutput1
    					rs!Output2 = dOutput2
    					rs!Output3 = dOutput3
    					rs!Output4 = dOutput4
    					rs!Output5 = dOutput5
    					rs!Output6 = dOutput6
    					rs!Output7 = dOutput7
    					rs!Output8 = dOutput8
    					rs!Output9 = dOutput9
    					rs!Output10 = dOutput10

    					'ISSUE THE ADD
    					rs.Update

					If iRecordCounter = 100 Then
						g_objConn.CommitTrans
						iRecordCounter = 0
					End If

				Next iCounter10
			
			...
	
			Next iCounter3
		Next iCounter2
	Next iCounter1

	g_objConn.CommitTrans

	rs.Close
	Set rs = Nothing

End Sub

Open in new window

0
Comment
Question by:ltdanp22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
inthedark earned 400 total points
ID: 34211838
Here are 8 suggestions to improve your code speed.
Before you do any changes start with item 1 and see how each part of your code performs. Then start to make speed mods.

Hope this helps:~)

1) Profile the phases of the code using GetTickCounter

Example:

Dim StartProc1 as Currency
Dim Proc1Time as Currency
Dim StartProc2 as Currency
Dim Proc2Time as Currency

StartProc1 = GetTickCounter

'some code here to create the values for the recordset

dOutput1 = (...)
dOutput2 = (...)


StartProc2 = GetTickCounter

Proc1Time = Proc1Time + StartProc2 - StartProc1 ' Accumulate time between start and end of phase


'some other code here to move data into recordset

rs!Variable1 = iCounter1
rs!Variable2 = iCounter2
                                  
StartProc3 = GetTickCounter
Proc2Time = Proc2Time + StartProc3-StartProc2


When the job is complete you can then see how much time was spent in each part of the code so can then be sure how much time you are saving on each part.

At the end of the sub:

sMes = "CreatingSaveData= "+cstr(ProcessXTime)+vbcrlf
sMes=sMes + "RecordUpdates="+cstr(ProcessYTime)+vbcrlf
Clipboard.Clear
Clipboard.SetText sMes

stop ' past the result into a spreadsheet


2) Excel is very slow at getting data; so pick up all of the data in a sheet in one hit:

Dim DT

' Select all data in a worksheet
EA.ActiveSheet.Cells.Select ' Where EA is your Excel.Application object

' Move the daya to an array
DT = EA.Selection.Value


The problem with the above is that you get every possible cell which is a huge array so really it is better to just pick up the data for a block of rows (say 100 at a time until you find all empty cells).

DT = EA.ActiveSheet.Range("A" + CStr(lStartRow)).Resize(lNumberOfRowsToGet, lNumberOfColumnsToGet).Value

Now when you are working you can access the cells in the sheet without needing to constantly link with the Excel Application Objects. This will make the calculation part really quick.

3) When you save the data make a field array to reduce binding to your recordset.


' After the rs.Open
Dim MyFiedlds() As ADODB.Field

ReDim MyFields(RS.Fields.Count-1)
Set MyFields(0) = rs!Variable1
Set MyFields(1) = rs!Variable2
etc.

Dim lMaxFields As Long

lMaxFields = 29 ' set up the last used element of MyFields()


' Now when you update your recordset

Dim lC as Long


' Update the recordset

CN.Begin
With RS

    For lC = 0 To lMaxFields
        MyFields(lc) = Uutput(lC)
    Next

    StartProcUpdate = GetTickCounter
    .UpdateBatch ' (or use .Update)
    ProcUpdateTime = ProcUpdateTime + GetTickCounter - StartProcUpdate

End With
CN.CommitTrans


You dont need to use an array you can use field names if you like doing lots of typing:

Dim MyFieldValueOfStuff As Field
Dim MyFieldOtherStuff As Field

Set MyFieldValueOfStuff = RS!ValueOfStuff
Set MyFieldOtherStuff = RS!OtherStuff



4) In your example you batched the updates into blocks of 100 rows but I would experiment by using the profiling becuase you may find that 1 record may work quicker then 100 - by profiling the tickcounts you will know exactly.

5) All counter fields like iCounter1 should be Dim iCounter1 as Long
Because the CPU can work faster with Longs then Integers

Also make sure that your module Declarations start with Option Explicit if it is not already there.

5) You should be using adCmdTableDirect, adLockOptimistic

6) In your example you are using ADODB but by using DAO your .update code will work about 2000 times faster into an access database. At least when I did the timings on ADODB when it first came out DAO was 2000 times faster in my insert records example.

Set a project reference to Microsoft DAO.


Open can use the open table direct and also the append only options on DAO:

Dim DB as DAO.Database
Dim RS As DAO.Recordset

Set DB = Workspaces(0).OpenDatabase("C:\MyFolder\MyDB.MDB")

Set RS = DB.OpenRecordset(SQL, dbOpenTable, dbAppendOnly + dbOptemistic)

Item 3 using the set arrayelement(x) = field will also work in DAO
   
7)  In a loop you want to avoid calling functions to get your data:

dOutput1 = SomeFunction(SomeParameters,etc.....)

Now that you have your data in an array you can put a result directly into your recordset

RS.AddNew

MyFieldValueOfStuff = DT(lMyRow, lMyColumn)

Or you may need to do calculations, do them without calling a sub or function
Dim Result As Currency

Result =DT(lMyRow, lMyColumn) * 100 /DT(lMyRow, lMyColumn+1)
MyFieldValueOfStuff = Result


RS.Update


When yoiu call a function the CPU has to do a whole bunch of stuff like pushing a whole lot of registers on the stack, then pushing pointers to parameters onto the stack and return addresses, etc. then stacking them.

You can use a simple Gosub MyInteralSub it is much faster than calling subs or functions but will not win you any lovely code points.
 
8) CSV would be the fastest method. To avoid loss of data you simply need to use Format.

' Setup

lFile = FreeFile
Open "C:\MyFolder\MyFile.csv" For Output As lFile

Dim sQuote As String
sQuote = """"
dim sComma As String
sComma = ","

' Save a record


Print lFile, sQuote;"MyStringValue";sQuote;sComma;Format(DT(lRow,lMyFieldCol,"0.00");sComma;
' More fields
Print lFile, Format(DT(lRow, lNextField);vbCRLF; ' last field needs no commad as it has CRLF

' after the loops complete close the file

close lFile

Be sure to check the output using notepad; if you view in excel excel make sure you format each column with enough decimals.










-----------------------------------------GetTickCounter function
Private Declare Function GetTickCount Lib "kernel32" () As Long

Public Function GetTickCounter() As Currency
GetTickCounter = CCur(GetTickCount)
End Function
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 100 total points
ID: 34212412
to follow up on the excellent inthedark comment above...

* start testing your commits at 500 iterations.  There likely be some point before you get to the 1000 iteration interval where you won't see much better performance.

* What are the upper limits of each of these For...Next loops?  If you are using 10 for each, then you are trying to insert 10 billion rows, not millions or tens of millions.  I'm surprised that you have exceeded one or more Access limits (including the use of a CSV intermediary file).

* WHY are you doing this?
0
 
LVL 17

Expert Comment

by:inthedark
ID: 34212927
Good point aikmark, it would seem to me that an entire spreadsheet can only be a few thousand rows which would import into a database within a few seconds, I would estimate about 200 rows per second.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question