Calculation based on recordsets taking too long (1 Viewer)

PeterOC

Registered User.
Local time
Tomorrow, 06:09
Joined
Nov 22, 2007
Messages
80
I’ve got a problem with certain fields on a data entry form that are taking too long to calculate. Users are reporting delays of 8 to 15 seconds whilst these calculations are happening which doesn’t sound that long but it’s happening for every record and they’ve to enter a lot of records each day.

The database is Access 2007 format. It’s a split database that user’s access over citrix.

On the data entry screen there’s a field where a machine number is selected which populates a machine start hours field (HoursStart).

It’s when the user tabs out of the HoursStart field the following code runs:

Code:
Private Sub HoursStart_LostFocus()
On Error Resume Next
Me.txtClockHours.Value = Me.HoursFinish.Value - Me.HoursStart.Value
On Error GoTo 0
Call calc_totalhours
Me.HoursFinish.SetFocus
End Sub
 
Private Sub calc_totalhours()
On Error GoTo Err_MyProc
        Dim db As Database
        Dim rst1 As DAO.Recordset
        Dim SQLVar As String
 
        Set db = CurrentDb()
 
        SQLVar = "SELECT DISTINCTROW SUM (tblDOD.HoursFinish-tblDOD.HoursStart) AS Sum_ClockHours FROM tblJobs INNER JOIN " _
       & "(tblDC INNER JOIN tblDOD ON tblDC.DiaryID = " _
       & "tblDOD.DiaryID) ON tblJobs.JobID = tblDC.JobID " _
       & "WHERE tblDOD.MachineNumber= " & Me.ctlMachineNumber.Value _
       & " AND tblJobs.JobID = " & Form_frmSiteDataEntryMain.JobID & " AND tblDC.OperationDate <= #" _
       & Format(Form_frmSiteDataEntryMain.OperationDate, "m/d/yy") & "# AND tblDOD.HoursStart <= " & IIf(IsNull(Me.HoursStart), 0, Me.HoursStart)
 
       Set rst1 = db.OpenRecordset(SQLVar, dbOpenSnapshot)
        Me.txtMachineTotalHours.Value = rst1!Sum_ClockHours
        'Useful code here.
        rst1.CLOSE            'Close what you opened.
 
Exit_MyProc:
        Set rst1 = Nothing    'Deassign all objects.
        Set db = Nothing
        Exit Sub
 
Err_MyProc:
        'Error handler here.
        Resume Exit_MyProc
 
End Sub

Is there anything wrong with this code that would be causing the delay?
 

spikepl

Eledittingent Beliped
Local time
Today, 23:09
Joined
Nov 3, 2010
Messages
6,144
Put indexes on alle the fields in the WHERE clause.
Your IIF is a bit overkill, since you can submit appopriate SQL, instead of letting SQL deal with something you know

Update
Bla bla: If I could learn how to read, I wouldn't come up with nonsense like the last sentence :confused:
 

PeterOC

Registered User.
Local time
Tomorrow, 06:09
Joined
Nov 22, 2007
Messages
80
Thanks for the suggestion spikepl but all the fields in the WHERE clause are indexed bar HoursStart.

Opening a recordset in the way I've done isn't likely to put locks on the db table is it?
Also is there any difference in performance using ADO recordsets instead of DAO?
 

DCrake

Remembered
Local time
Today, 22:09
Joined
Jun 8, 2005
Messages
8,632
You could try and change...

This
Code:
IIf(IsNull(Me.HoursStart), 0, Me.HoursStart)
To

Code:
Nz(Me.HoursStart),0)


IIf reads both conditions then makes a decision which one to use, whereas Nz() doesn't
 

spikepl

Eledittingent Beliped
Local time
Today, 23:09
Joined
Nov 3, 2010
Messages
6,144
#6 LOL - did you fall into the same trap as me? The IIF is NOT in the SQL :)
 

PeterOC

Registered User.
Local time
Tomorrow, 06:09
Joined
Nov 22, 2007
Messages
80
I've not got exclusive access to the db at the moment so I'll get back to you and let you know.
 

spikepl

Eledittingent Beliped
Local time
Today, 23:09
Joined
Nov 3, 2010
Messages
6,144
#3 So did you put index on HourStart then? If not why not? I suspect you are dealing with many records - are you?
 

PeterOC

Registered User.
Local time
Tomorrow, 06:09
Joined
Nov 22, 2007
Messages
80
I'll implement your suggestion this evening and get back to you.

There's 250,000 records in the main table 'tblDOD'.

Thanks for your help,

P
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 22:09
Joined
Sep 12, 2006
Messages
15,614
sometimes you can't always index the fields. in those cases access has to check all 250K records, and there will be a performance hit.

so one alternative is to look at the bisiness process involved, and see if there is a way of modfiying the process that leads to this.

eg - maybe you could store the machine hours in a table, and generate the values as a one-off process at the end of each day. Or maybe delete old machine records, so you have a lot less than 250K records.

It's things like this that make the mantra "don;t store calculated values" one that in practice needs to be broken.
 

PeterOC

Registered User.
Local time
Tomorrow, 06:09
Joined
Nov 22, 2007
Messages
80
A quick update for the people that helped me:

User have reported a a slight improvement after I added a couple of extra indexes and added the nz. But I still need to speed things up further. I said the db table had 250k records in it. That was my test db. The Live one has closer to 700k so I'm going to look into archiving this and bring the running totals / into a new table.

I know you're not supposed to have running totals or anything that could be calculated saved in a table but this is the system I've inherited and dem boss want to keep it that way.

Thanks again,

Peter
 

Users who are viewing this thread

Top Bottom