As I mentioned in a previous post, I have a macro that takes forever to run. In fact, it takes about 270 seconds.
The odd thing is that FillInvoiceClass and FillInvoiceLineClass are pulling data from linked tables, while other more deeply nested classes (that loop more often) pull from native tables. It would appear that it takes longer to get data from linked tables. So I tested it. First, I ran a make table query from invoicelinedetail (a linked table) to create tblInvoiceLineTest (an identical native table). Then I ran this code:
PerfMonStartMonitoring
PerfMonProcStart “VBAProject.Module1.LinkedTableTest”
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sqlLink As String, sqlNative As String
PerfMonProcStart “VBAProject.Module1.Setup”
sqlLink = “SELECT * FROM invoicelinedetail;”
sqlNative = “SELECT * FROM tblInvoiceLineTest;”
Set cn = New ADODB.Connection
cn.Open gsMRPConn
PerfMonProcEnd “VBAProject.Module1.Setup”
PerfMonProcStart “VBAProject.Module1.OpenRSNative”
Set rs = cn.Execute(sqlNative)
PerfMonProcEnd “VBAProject.Module1.OpenRSNative”
PerfMonProcStart “VBAProject.Module1.LoopRSNative”
With rs
If Not (.BOF And .EOF) Then
.MoveFirst
Do
Debug.Print .Fields(“TxnLineID”).Value
.MoveNext
Loop Until .EOF
End If
End With
PerfMonProcEnd “VBAProject.Module1.LoopRSNative”
PerfMonProcStart “VBAProject.Module1.CloseRSNative”
rs.Close
Set rs = Nothing
PerfMonProcEnd “VBAProject.Module1.CloseRSNative”
PerfMonProcStart “VBAProject.Module1.OpenRSLinked”
Set rs = cn.Execute(sqlLink)
PerfMonProcEnd “VBAProject.Module1.OpenRSLinked”
PerfMonProcStart “VBAProject.Module1.LoopRSLinked”
With rs
If Not (.BOF And .EOF) Then
.MoveFirst
Do
Debug.Print .Fields(“TxnLineID”).Value
.MoveNext
Loop Until .EOF
End If
End With
PerfMonProcEnd “VBAProject.Module1.LoopRSLinked”
PerfMonProcStart “VBAProject.Module1.CloseRSLinked”
rs.Close
Set rs = Nothing
PerfMonProcEnd “VBAProject.Module1.CloseRSLinked”
PerfMonProcEnd “VBAProject.Module1.LinkedTableTest”
PerfMonStopMonitoring “C:PM_TestLinkedTable2.txt”
End Sub
It’s a bit hard to read with all the Perfmon calls, but it basically opens a recordset containing the whole table and loops through it. Then it opens a recordset for the other table and loops through it. I got this result:
Not convinced I knew what I was doing, I switched the order of the tables, but got the same results.
I can’t say I’m shocked that there’s more overhead with linked tables, but I am shocked that the degree. Look at the time differences to close the recordset.
I have a number of options to speed this up. First, I have access to the mdb to which these tables are linked. I could establish a new ADO connection to that database and read them natively. They live in an mdb that’s populated with Quickbooks data by another program. This mdb gets corrupted occasionally and it’s easier to swap things out when that happens. So I keep my contributions in a separate mdb and link back to the Quickbooks one.
Second, I could query every record one time instead of a small subset multiple times. My preferred method is to bring in child records as I’m filling the parent. So I would bring in all the Invoice records, then loop through them. In each loop, I create a recordset to bring in InvoiceLines that relate to Invoice. Then in each InvoiceLine iteration, I bring in only related RepLines. Then in each RepLine iteration, I bring in only related RepComms. Yeah, that’s four deep nesting. But the FillRepLineClass which is called 1,077 only took 2 seconds. When it goes that fast, I opt for clean, readable code over slight improvements in performance. Conversely, when FillInvoiceLineClass takes 126 seconds to iterate 449 times, it’s time to rethink.
Third, I could reduce the number of records I retrieve. For my application, I shouldn’t need to get records more than about six months old. So to be safe, I get the last 13 months. Cutting this procedure in half does not get me to an acceptable speed, but that’s still an option if I need it.
I haven’t implemented any of those three yet. I kind of wanted to post this first and see what you had to say.
I’d like to perform some of same type of tests you have on a macro of mine that does a lot of interaction with SQLite. What reference do I need to add to my VBA project to be able to use the perfmon calls? I did some Google searches and it looks like I also need perfmon.dll and perfmonoffice.dll. Do you know where I can get these DLLs? Thanks for your help, I’ve been reading your site and have learned a lot from your posts.
Interesting, this hasn’t been my experience. If you don’t mind, I’d be interested in seeing the linked table’s connection properties. Easiest way to get these is open the linking database in Access, Alt-F11 to the VBA environment, press Ctrl-G to get the immediate window and paste the following line:
?currentdb.TableDefs(“linkedtablename”).Properties(“Connect”).Value
Be sure to replace “linkedtablename” with the name of the linked table (e.g., “invoicelinedetail”). Then press Enter from the end of the line.
Might also do the same with the “Attributes” property.
Its an interesting article. i am very thankful as i have learn a lot from the from this article. this post is worth referring to.
I also would like to know more about the performance testing with perfmon you did. May you could make a small how-to.
Mike, Perfmon is one of the utilities included with “Professional Excel Development”. Buy the book, get the tools!
I’d bought the book after reading about it on your site, I arrived yesterday, can’t wait to dig into it…
Did you take a look at using pass through queries instead of linked tables? Would be interesting to see those results too.
;DATABASE=S:AIMUSAAccountingAIMQBData.mdb
?currentdb.TableDefs(“invoicelinedetail”).Properties(“Attributes”)
1073741824
I’ve never done a pass through query from Access to Access. When I tried, I got “You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database.” when I tried to select the DSN.
I created a new ADO connection directly to the source database. I run queries against the invoice and invoicelinedetail tables using this connection and queries against tblRepLine and tblRepComm using the original connection. Twelve seconds is good. My goal is four seconds, but this gets me a chunk of the way there.
Perfmon is a great utility. It’s worth the price of the book alone (and the book is worth more than the price of the book, so it’s a killer deal). But I do have one complaint: It doesn’t respect my indents.
If I have an Exit Function statement, it dutifully puts and ‘end proc’ statement before it. But when it removes the ‘end proc’ statement, my Exit Function statement is all the way over to the left, rather than indented to its original position.
I minor annoyance and one I’m sure they’ll have fixed in the next version.
Try a DAO connection directly to the source database just as you did with ADO. You’ll have to bring in a reference to DAO, but it may be a bit faster.
Thanks for the additional details. That verifies that you do have a native JET link, as opposed to an ODBC JET link.
So is the database with which you ran the tests above, and in which you created the native tblInvoiceLineTest table, also on the “S:” drive?
Yes, they’re all in the same folder on the same network share.
The coding style on this is so different from mine that I can’t really figure out what happens with the cursor. But it is of so great importance that imo it should be explicitly coded. Effect is not critical with Jet, but the minute data and connection is ported to SQLserver or some other “pro” database, you really wonder what went wrong; Cursor adOpenForwardOnly is far faster than the others when looping from bof to eof. Cursors http://msdn.microsoft.com/en-us/library/aa140098(office.10).aspx
Dick,
Some suggestions to try:
Use a client-side cursor.
Disconnect the recordset (rs.Connection = Nothing) after the results have been fetched; preumably you’d take the hit at this time, rather than when the recordset is closed.
Rather than testing for EOF on every row, which takes a small hit for each test, use the .Recordcount and an iterator (For i = 0 to .RecordCount – 1 : Do stuff : .MoveNext : Next).
You shouldn’t ever need to test for BOF :)
Use the recordset’s GetRows (or GetString) method to get the data out of the recordset in one hit: GetRows returns an array that must be ‘transposed’ to be read into an Excel.Range, BTW.
Rather than nested *loops* and the resulting database round-trip on every ‘parent’ row, try getting the entire resultset in one big query with multiple *joins*; you can then use the .Filter method (e.g. using the ‘parent’ identifier) if you need to walk the results.
If you like multiple recorsets (and why not), fetch a hierarchical recordset e.g. here’s a simple Northwind example to return all the products a customer has ordered over time:
SHAPE {
SELECT DISTINCT C1.CustomerID
FROM Customers AS C1
INNER JOIN Orders AS O1
ON C1.CustomerID = O1.CustomerID
{
APPEND ({
SELECT DISTINCT C1.CustomerID, D1.ProductID
FROM (Customers AS C1
INNER JOIN Orders AS O1
ON C1.CustomerID = O1.CustomerID
) INNER JOIN [Order Details] AS D1
ON O1.OrderID = D1.OrderID
} AS chapProducts RELATE CustomerID TO CustomerID)
This returns a recordset with two Fields: CustomerID and chapProducts, one row for every customer (those who has ever ordered anyting). The chapProducts Field is actually a recordset itself, so for each row (for each customer) you will have a ready-made recordset of all the product they have ordered. Best if you can use the hierarchical recordset as teh data source of a MSHFlexGrid control on a userform ;-)
Jamie.
Harald: Can you point me to an example of your coding style so I can compare?
Great points, Jamie. Thanks. My Recordcount always says -1, presumably because my cursor is server side(?). My assumptions (incorrect though they may be) was that server side cursors and forward only recordsets were the best for performance, but that you couldn’t get a recordcount because that would involve moving backwards. So I test for BOF and EOF to avoid empty recordsets. Obviously if I can get a recordcount, I’d just use that.
I’ve never used GetRows, but I’m going to try it. I also hadn’t considered Filter, but that would keep my code clean if I bring in one big recordset.
IIRC, the RecordCount property is useless in Excel VBA.
@JKP
That is certainly my experience also.
Thanks for the extra details. That rules out one performing better than the other due to location.
I would suggest ruling out the possibility that the database having the local and linked tables is being opened exclusively (or in read-only) mode while the attached table’s database is being opened for shared (and/or read-write) mode, thus causing the latter to perform slower. You didn’t specify an ADODB.Connection object Mode, so it’s set to adModeUnknown (0) by default. Adding the following between the Connection Set and Open lines and re-running your tests would rule this out as the cause:
Set cn = New ADODB.Connection
cn.Mode = adModeShareDenyNone
cn.Open gsMRPConn
Finally, would you mind checking the version of the two databases? To do so, simply open each in Access, skip back over to the VBA Immediate window, then paste the following and press Enter:
?DBEngine.Version, CurrentDb.Version, CurrentDb.Properties(“AccessVersion”).Value
@JKP and @Gordon: If I know the number of rows returned then I can use this knowledge in accommodating the resultset: limit an iterator, resize an Excel.Range, ReDim an array, etc. Ditto the number of columns (Fields.Count). I can find uses for the RecordCount property in Excel VBA, so why do you guys think it is without use? TIA.
Because it does not work, that’s why.
not sure if it’ll help with the speed issue but in terms of doing a pass-through from Access to Access, you can do it in a normal query with the following syntax:
SELECT * FROM [pathtootherdatabasedatabasename.mdb].tableorqueryname
Rob
linked
?dbengine.Version, currentdb.Version, currentdb.Properties(“AccessVersion”)
3.6 4.0 08.50
source
?dbengine.Version, currentdb.Version, currentdb.Properties(“AccessVersion”)
3.6 4.0 08.50
Thanks for that added detail. Btw, the reason I’m persisting is because this isn’t my experience. Except where the conditions I’ve asked you to test above have been found to be true, I have not found measurable differences in the performance of JET databases accessed directly with ADO or via links in other JET databases accessed via ADO. I’ll encourage you to try one more test, then I’ll beg off. :)
I would like to suggest you rule out the possibility that the performance difference is caused by the inefficient structure and/or size of the linked database. To do this, relink the native table to the native table’s database (or copy the database with the native table in it and link the table from the copy) so that the native table and linked table used in the timing tests above are in the same database (or in a copy of the same database).
Good luck!
@JKP: What, you think an ADO Recordset’s RecordCount property is somehow disabled in Excel VBA? That is not the case: quick proof:
.Fields.Append “col1”, adInteger
.Open
MsgBox .RecordCount ‘ Should be zero.
.AddNew “col1”, 1 ‘ Add a row…
MsgBox .RecordCount ‘ …should be one.
End With
Jamie:
Your example is for a disconnected recordset built in memory. If I run this:
Dim oADOconn As ADODB.Connection
Set ors = New ADODB.Recordset
Set oADOconn = New ADODB.Connection
oADOconn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=”“” & “c:data
orthwind.mdb” & “”“;”
With ors
.Open “SELECT * from customers”, oADOconn
MsgBox .RecordCount
MsgBox .EOF
End With
I get a msgbox with -1 (recordcount), followed by a msgbox with False (EOF). The actual number of records returned is 90.
Sorry the connection string was wrecked. the VB tags don’t do it all properly yet :-)
@Jan Karel: you are using a cursor type that does not support the RecordCount property! Try using a static cursor, then your Recordset should support RecordCount e.g. amendment to your code:
.ActiveConnection = oADOconn
.CursorType = adOpenStatic
.Source = “SELECT * FROM Customers;”
.Open
MsgBox .RecordCount
MsgBox .EOF
End With
Jamie:
Well what do you know, learnt something new today! Thanks.
A good feature of RecordCount is that is affected by the Filter property e.g.
.ActiveConnection = oADOconn
.CursorType = adOpenStatic
.Source = “SELECT * FROM Customers;”
.Open
MsgBox .RecordCount ‘ Returns 91 for me
.Filter = “CompanyName = ‘Around the Horn'”
MsgBox .RecordCount ‘ Returns 1 for me
.Filter = “”
MsgBox .RecordCount ‘ Back to 91
End With
That’s an eye opener, thanks Jamie.
But RecordCount seems to be limited. What if the task is to find the last record in a table?
Using OLEDB on a standard Excel wsheet where the UsedRange last cell = AF50918 against a real last cell of S98 it doesn’t work as expected.
Query1 = “SELECT COUNT(*) FROM Sheet1$;”
rsData.Open Query1, rsCon, adOpenStatic
rsCount1 = rsData(0)
rsCount2 = rsData.RecordCount
returns rsCount1 = 50917 rsCount2 = 1
Neither function returns the real answer of 97 but then from my research so far I haven’t seen anything that does.
Is this a ‘non feature’ of Jet in that COUNT(*) only works when the last cell of UsedRange matches the real last cell?