Code Efficiency

**NeoPa** · Jul 11 '07, 11:31 PM

You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.

MODERATOR.

**NeoPa** · Jul 11 '07, 11:37 PM

This is an interesting question (well pair of them).
Unfortunately I don't know what 'efficiencies' are applied by the VBA compiler. I know that some compilers do apply such, but I've no idea if VBA does (I suspect not though).

As to your data (query) efficiency question, I can only suggest that you set up a test using a large number of records to find out what the situation is for your size of database and table. I imagine it varies with different parameters, but your test would give you a general ball-park feel for the issue anyway.

**JustJim** · Jul 12 '07, 02:50 AM

You might want to check out this post in the articles section about timing of processes in Access.

Jim

**OldBirdman** · Jul 13 '07, 08:18 PM

I'm sorry for posting in the wrong section. Thanks for re-posting correctly.

Thank you for the nice tool, timeGetTime(). It seems to allow almost anything inside a test loop. So I thought I would write a general program to compare 2 (or more) variations for efficiency. So I wrote the code:

Code:

    iiUpperLimit = strUpperLimit 'Inputted value by User
    iiMaxTime = strMaxTimeInSeconds * 1000 + iiStartTime 'Inputted value by User    iiStartTime = timeGetTime()

    For iiCounter = 1 To iiUpperLimit 'Do 1,000,000 times per User
        If timeGetTime() > iiMaxTime Then
            'Avoid infinite loop
            TimeTest = "Loop exceeded " & strMaxTimeInSeconds & _
                    " seconds.  Iterations = " & iiCounter
            Exit Function
        End If
        
        Select Case (iCase)
        Case 1
            wk = Sqr(iiCounter)
        Case 2
            wk = iiCounter * iiCounter
        Case 3
        Case 4
        Case 5
        End Select
    Next iiCounter
    
    iiEndTime = timeGetTime()
    Result = "Time (Seconds) = " & (iiEndTime - iiStartTime) / 1000 & "; Iterations = " & _
            Format(iiUpperLimit, "###,###,###,###")

I have disconnected my connection to the internet, and I (think that) I don't have anything running in background.

Then I thought I should "Calibrate" this, to determine the overhead time for the "TimeOut" test and the "Select Case". So I removed the 2 tests at Case 1 and Case 2 (Commented out). This left nothing to do no matter which case was done. The differences in time should only be the overhead in progressing further through the Select List. (Select case seems to be equivalent to If 1 then ... elseif 2 then ... elseif 3 then ... etc.).

I have run this routine many times, and have not been able to determine the overhead in the loop.
Here are the results of 6 consecutive tests, in milleseconds:
Test1: 2324 2654 6589 6870 7190
Test2: 3244 3425 6259 6850 7120
Test3: 5098 6108 6540 6850 4046
Test4: 3234 3906 5658 5989 9493
Test5: 5098 6108 6540 6850 4046
Test6: 5098 5498 7200 11256 12358
These are 6 consecutive test runs, no run was deleted to give maximum deviation results, or in any way to distort my results I would expect the first column to be the true overhead, and columns 2-5 incremental difference to be identical as VBA tests each "Case". In no case would I expect a column value to be less than the preceeding column value, as in rows 3 and 5.

So now I don't think the problem is Access, but something to do with Windows, i.e. what is running in background and that I don't know about. I am completely disconnected from the internet (wireless card is removed). I have re-booted, and Access is the only program running.

Yeah, I could have pasted code from the previous answer into a program, and not questioned the logic/method presented. That is not my way, as those who have read my previous posts know. I tested and came up short.

I will have questions as a result of my testing. How can I stop an infinite loop in the procedure that calls the above test code, or even display the progress of a loop.??.

Now let's go forward and help me understand the above table of results.

Old Birdman

**NeoPa** · Jul 13 '07, 09:39 PM

Breaking into code is usually done from the keyboard with Ctrl-Break (where Break is the Pause/Break key, normally over at the top-right of a keyboard somewhere).
There are a number of ways to display the progress of a loop, but probably the most accurate way (that effects the timing of the loop the least), would be using Debug.Print to display something to the Immediate Pane of the Debugging window. This cannot be seen while the code is executing though, and has a limited buffer for showing data.

**ADezii** · Jul 14 '07, 01:47 AM

Originally posted by OldBirdman

Efficiency

I've never stumbled on any discussion of efficiency of various methods of coding, although I have found posts on various forums where individuals were concerned with efficiency. I'm not concerned when dealing with user typing, but I am if a procedure is called by a query.

Does the VBA compiler generate "in-line" code for some apparent function calls? For example, y = Abs(x) might be compiled as y = x & mask. The string functions Left, Right, and Mid might also be compiled into compact code, and not be functions at all. Seems like y = x and y = Left(x, 4) might compile to Move Addrss x, Lenth = Len(x) to Address y or Move Address x, Length = 4 to Address y. No time/efficiency difference.

Second efficiency question. I have a table with 100K records, one field is "Selected", a boolean field. To clear all selected items, I run an update query. Which is better? If one is better, where is the break point. Obviously if Selected is 99% of the table size, it is better to just change all to false. Is there a method to determine any of this for Update Queries?

Code:

UPDATE Table SET Table.Selected = False WHERE (((Table.Selected)=True));
UPDATE Table SET Table.Selected = False;

1st case tests all 100K records, but only changes those that are =True. The 2nd case doesn't need to test for any condition, but must change all 100K records to False.

When VBA compiles code, it stores a tokenized version of the original text and a compiled version. What exactly, this tokenized version consists of, I'm not really sure. When Access prepares to run previously compiled code, it loads just the compiled version into memory and runs that compiled code. For uncompiled code, which can result from a variety of changes to Objects and their encapsulated code, Access must load the original version into memory and then compile the code as needed.

**OldBirdman** · Jul 19 '07, 08:56 PM

I have not responded fully to the ideas presented in response to my questions. Apparently, something is running in my computers that I cannot control. I've been running spyware detection programs, virus scans, etc.

Preliminary results show that the string-handling functions mid, left, right are true functions, called each time, even if all the parameters are constants, i.e. x = Mid("ABCDE",2,4 ). I haven't got any further as I want to know what is running on these computers.

About queries, I created a table of 10,000 rows, each about 2K so that everything could not be done in memory. Preliminary results are that there is hidden indexing, and therefore SELECTing the records to change first is more efficient than just changing all records. If any are interested, I should be able to report on both these issues within a week.

Thanks for all who responded. I needed all of the help I got.

I will get back to this issue soon.

Old Birdman

**NeoPa** · Jul 19 '07, 09:20 PM

Thanks for your update.
I, for one, am interested in anything you find out about Access performance.

**ADezii** · Jul 19 '07, 11:04 PM

I'll second that, I too am also interested in anything that you may turn up.