Solved

Speed up write from Excel to Access

Posted on 2010-11-24
3
346 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
  • 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now