This code is too slow. Can you come with faster one?

  • Thread starter Thread starter Song Su
  • Start date Start date
S

Song Su

This line of code takes 20 seconds. Can anyone provide faster code? The
underline query has a calculated field Minutes:[TimeOut]-[TimeIn]. It links
to SQL server which has 30,000 records. Thanks.

=Int(Sum([minutes])/60)
 
I don't know and I would guess others would have the same problem. You
have posted no context for this.

Is this criteria for a query?
What does the query look like?
Are all the tables on the SQL Server?
What other query does this query use?

You might be able to solve the speed issue using a pass-through query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Here is the underlying query. All tables are in SQL server.

SELECT qryData.SID, DateDiff("n",[timein],[timeout]) AS Minutes,
Duration.From, Duration.To
FROM Duration, qryData
WHERE (((qryData.timeIn)>=[forms]![mainmenu].[cboFrom] And
(qryData.timeIn)<=[forms]![mainmenu].[cboto]+1));

qryData is just simple select query linking 2 tables without criteria.

I have a form based on this query. The form has a few controls display the
summary info:
From
To
=[to]-[from]
=DCount("*","DistinctCount")
=DCount("*","VisitCount")
=[TotalVisit]/[StudentTotal]
=Int(Sum([minutes])/60) 'only this one is slow
=Int([TotalHour]/[StudentTotal]) & ":" &
Int(([TotalHour]/[StudentTotal]-Int([TotalHour]/[StudentTotal]))*60)
=Int([totalhour]/[totalvisit]) & ":" &
Int(([totalhour]/[totalvisit]-Int([totalhour]/[totalvisit]))*60)



John Spencer said:
I don't know and I would guess others would have the same problem. You
have posted no context for this.

Is this criteria for a query?
What does the query look like?
Are all the tables on the SQL Server?
What other query does this query use?

You might be able to solve the speed issue using a pass-through query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Song said:
This line of code takes 20 seconds. Can anyone provide faster code? The
underline query has a calculated field Minutes:[TimeOut]-[TimeIn]. It
links to SQL server which has 30,000 records. Thanks.

=Int(Sum([minutes])/60)
Click to expand...
Click to expand...
 
Back
Top