Effiency Problem

  • Thread starter Thread starter Jean-Francois Bouthillier
  • Start date Start date
J

Jean-Francois Bouthillier

Hi all,

I have a database that has a table containing completed
steps with corresponding dates. In other words, this
table has records containing for example:
Step A -> 11-Oct
Step B -> 28-Oct
etc.

In order to see how much time a particular step takes, I
built a function that creates a recordset with
ADODB.Recordset and ADODB.Connection and makes the
difference between the two completed dates.

There is a query that calls the above function for all
entries. As you probably guessed, this query is very
slow.

Would you have any recommendations? Should I close the
recordset or the connection at the end of the function,
so that it is closed every function execution?

Thank you very much for your help.
Jean-Francois
 
Hi,

If the steps are all in the same record, then using DateDiff() in a query
expression would be MUCH faster than a function.
 
Hi,

Thank you for your response...

They are in deed in different records (unfortunately).

Thank you.
JF
 
Hi,

You're welcome. :-)

Question1: Is the data laid out like this?

Job 1 -> Step A -> 11-Oct
Job 1 -> Step B -> 28-Oct
etc.

Question2: Are the steps *ALL* filled in before your audit attempt?

If so, then you could group by the job number and possibly use an expression
like this:

ElapsedTime: DateDiff("h",Min([DateColumn]),Max([DateColumn]))

May not be the best solution, but that's what popped in mind first at
4:17am. <g> Off to bed for me.
 
Back
Top