More Speed for Processing info for hundreds of employees

[33216CLC]

I presently call on one subroutine from within another subrountine to process info for hundreds of employees. The rountine is call by employee for each employee. When processing for about 60 employees, speed is acceptable. However, there are companies that have over 400 employees, and it takes about 7 - 15 minutes for processing to be completed (depending on the speed and capacity of their computer). I am usine VB6 and Access 97 as my DBMS.

Is there a way I can speed up processing. I was advised to used a binary or batch method to do this (I'm not sure which was the reccommendation). I am not sure how to go about this.

Any suggestions?

Thanks

 

[chiph]

So, for 10 employees, there are 100 sunroutine calls, and for 20 employees, there's 400 calls, etc?

That would be an exponential growth rate, and 100 employees would be 100^2 = 10,000 calls. 400^2 = 160,000 calls. That might indeed take a while if you've got any database access in the function.

Using a Batch method won't actually make it run any faster -- Batch just means "non-interactive".

What's taking up the time here is the processing your code is doing. You might want to post a sample so that we can look at it and point out where you can optimize it. To make it more readable for us, surround it with [ tt] and [ /tt] tags (remove the embedded spaces).

Chip H.

 

[33216CLC]

Actually, the process for calculating info is called only once per employee. Therefore, it is called only 10 times for 10 employees.

Here's the sample code:

[If Not rs.BOF Then
Do While Not rs.EOF
Call UpdateAgeClass
n = CheckDup
If n = 3 Then
Call SingleProcess 'procedure called for each employee
ElseIf n = 1 Then
Exit Sub
End If
If CancelRun Then Exit Sub
rs.MoveNext
Loop
End If]


The UpdateAgeClass subroutine does not contribute to the slow speed. I just added it a few days ago, after testing on over 400 employees. The code above is within another subroutine that is called only once. Would you aso need the code within the Singleprocess subroutine? (I must warn you this routine has 1070 lines of code) If you do, should I send it by email or post it?

 

[MichaelRed]

33216CLC,

Wheather Chip wants to review 1K + lines of code or not, yoy should figure out how to "Profile" the process. For this routine ad your programming future. There are some third party tools which your employer should be anxious for you to acquire (read Pay for) if they help getting the production back on track. Alternative approaches do exist, which you may attempt to implement yourself.

Of these, my favorite is a "TraceLog". In this, you basically add a table for the results. Here you can include whatever info you think appropiate. I ALWAYS include the routine Name and TimeStamp as a mininum. Next, Generate a GenericFunction which accepts the as args, the parameters you want logged. It will simply "post" a new record to the TraceLog each time it is called, with the info in the Arg list. Finally, in EVERY procedure, call this TracgLog function with the routine name, the time and any parameters you have identified.

When you run the process, every call to every routine should be logged to the table. Since it is just another table in the db, you can easily do any/all standard data manipulations on it to analyse the performance. A couple of the easiest:

Count by routine name. Will tell you where you keep going.
Largest time 'Gap' between calls. Tells you where you may be spending a LOT of time. (Note, that this "Gap" should represent the execution time of the 'parent' routine, however in MANY instances this may be less than the 1 second resoloution of gereral date/time, so several 'rotines may apear to have the same 'time'. ALso, if you have omitted the call to the tracelog function in a routine, it - obviously doesn't show up, and distorts the time.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[chiph]

33216CLC -

Your loops looks OK. I suspect your time is being consumed in your SingleProcess function. That's where you ought to focus your attention. Sorry, I don't have time right now to examine 1k of code (work is busy busy busy!)

Like Michael said, when presented with a problem like this, you need to get some hard numbers as to where your program is spending it's time. You can log to an array or collection if the program isn't crashing, or to a file if it is.

For a quick & dirty test, you should try commenting out pieces of code to see what speed-up you get. The piece you commented out last is the area eating up your time.

Chip H.

 

[MichaelRed]

I would be VERY careful of 'deleting' code. Even if you are VERY famliar with the code you can easily do the wrong thing and corrupt the dataset and give misseading indicators of the problem area.

Some version of TraceLog" is almost foolproof (not necessarily ProgrammerProof).

Your trouble shooting techniques are - of course - for you to select and implement, but even VERY good programmers who were being VERY careful have corrupted a lot of data with the 'remove' code approach.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[33216CLC]

Chip, Michael,

Is there a book or books that you can recommend on fine-tuning vbcode. I have been doing some researh and so far the info I have come up with is a bit scanty.


Thank you for your response. I have gone through the routine on several ocassion in debug mode and have in fact encounter areas where moving to the next line of code took a while. I'll continue to look into it.

 

[MichaelRed]

Software Defect Removal, Robert Dunn. ISBN 0070183139 McGraw Hill

Software System Testing and Quality Assurance, Boris Bezier. ISBN 0442213069 Van Nostrand Reinhold Co.

Neither of these is 'light reading' or casual use for a specific and Immediatly vexing situation.

Another resource is:
The Design and Analysis of Algorithms, Dexter C. Kozen ISBN 3540976876 Springer-Verlag.

but, again, this is not applicable to the immediate.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[edderic]

make in the database of Access97 Query's or Pivot tables ,and open this with Visual Basic like a normal Table

You can mak Query's in Query's or Pivot with another Query etc

RE I must warn you this routine has 1070 lines of code)

This codes can you translate to Access97 and make the query at run time

Set a break on the Openrecordset and CTRL + G ,type ? and the sql statements

Go to Access and run the sql ,make then the query or inner join etc...or Pivot whatever

Eric De Decker
vbg.be@vbgroup.nl

License And Copy Protection AxtiveX.

Download Demo version on my Site:

http://www.createsoftware2000.com

 

[33216CLC]

Eric,

Thanks

 

[33216CLC]

Thanks