User defined function slowing query :(

[munkee]

I am using the following function which basically queries a table to find out when a record was moved from being in one status, to a new status.
The status' are there to monitor work flow through the system.
My function is:

Function DaysInStatus(IncidentTyping As Integer, HSE As Integer) As Variant
On Error GoTo breakdown
Dim innards As String
Dim rs As Dao.Recordset
Dim strsql As String
Dim counter As Variant
counter = Null
    Select Case IncidentTyping
    Case 1
        strsql = "SELECT Date() - [Date_reported] AS TESTING FROM tblhselog WHERE (((tblhselog.HSEID)=" & HSE & "))"
    Case 2
        strsql = "SELECT Date() - [AcceptingStart] AS TESTING FROM tblhselog INNER JOIN tblStatusTiming ON tblhselog.HSEID = tblStatusTiming.HSEIDLINKING WHERE (((tblhselog.HSEID)=" & HSE & "));"
    Case 3
        strsql = "SELECT Date() - [ActionStart] AS TESTING FROM tblhselog INNER JOIN tblStatusTiming ON tblhselog.HSEID = tblStatusTiming.HSEIDLINKING WHERE (((tblhselog.HSEID)=" & HSE & "));"
    Case 4
        strsql = "SELECT Date() - [ReviewStart] AS TESTING FROM tblhselog INNER JOIN tblStatusTiming ON tblhselog.HSEID = tblStatusTiming.HSEIDLINKING WHERE (((tblhselog.HSEID)=" & HSE & "));"
    Case 5
        strsql = "SELECT Date() - [ClosedStart] AS TESTING FROM tblhselog INNER JOIN tblStatusTiming ON tblhselog.HSEID = tblStatusTiming.HSEIDLINKING WHERE (((tblhselog.HSEID)=" & HSE & "));"
    Case Else
        GoTo completedyo
    End Select
    
   
 'Debug.Print strsql
 
Set rs = CurrentDb.OpenRecordset(strsql, dbOpenSnapshot)
If rs.RecordCount <> 0 Then
counter = rs("TESTING")
Else
counter = Null
End If
 
DaysInStatus = counter
'Debug.Print DaysInStatus
completedyo:
rs.Close
Set rs = Nothing
Exit Function
breakdown:
MsgBox Err.Number & " - " & Err.Description
Resume completedyo
End Function

The function checks the status ID # passed to it then using a select case statement picks out the required sql string to execute in order to bring the number of days the record has been in that status.

The table that the sql is running agains is tblStatusTiming:

DaysInStatusID HSEIDLINKING InitialNotificationStart InitialNotificationEnd AcceptingStart AcceptingEnd ActionStart ActionEnd ReviewStart ReviewEnd ClosedStart UserIDMovedAwaiting
1 90 27-Jan-11 21-Feb-11 21-Feb-11 21-Feb-11 21-Feb-11     2

I have a dashboard page which shows the number of items within each status which I can then click on to bring up those items in a sub form. My subform is based on a query which is filtered on demand. This query contains the function in my first code snippet:

SELECT tblhselog.HSEID, tblhselog.Time_reported, tblhselog.Date_reported, tblhselog.Owner, tblhselog.Forename, tblhselog.Surname, tblhselog.Manager, tbldept.Department, tblIncidentType.IncidentType, tblhselog.Incident_date, tblhselog.Incident_time, tblhselog.Incident_location, tblhselog.Incident_description, tblhselog.Intervention, tblhselog.Intervention_description, tblIncidentCat.IncidentDescripCat, tblInjury.InjuryType, tblhselog.Person_Injured, tblStatus.StatusType, [DateToBeCompleted]-Date() AS DaysToComplete, Last([CorrectiveCompletedDate]-Date()) AS DaysTillLastAction, DaysInStatus([Status],[HSEID]) AS DaysInStatus
FROM (tblStatus INNER JOIN (tblInjury RIGHT JOIN (tblIncidentType RIGHT JOIN (tblIncidentCat RIGHT JOIN (tbldept RIGHT JOIN tblhselog ON tbldept.DeptID = tblhselog.Department) ON tblIncidentCat.IncidentDescripID = tblhselog.Incident_subtype) ON tblIncidentType.IncidentTypeID = tblhselog.Incident_type) ON tblInjury.InjuryID = tblhselog.Injury_type) ON tblStatus.StausID = tblhselog.Status) LEFT JOIN tblCorrective ON tblhselog.HSEID = tblCorrective.CorrectiveID
GROUP BY tblhselog.HSEID, tblhselog.Time_reported, tblhselog.Date_reported, tblhselog.Owner, tblhselog.Forename, tblhselog.Surname, tblhselog.Manager, tbldept.Department, tblIncidentType.IncidentType, tblhselog.Incident_date, tblhselog.Incident_time, tblhselog.Incident_location, tblhselog.Incident_description, tblhselog.Intervention, tblhselog.Intervention_description, tblIncidentCat.IncidentDescripCat, tblInjury.InjuryType, tblhselog.Person_Injured, tblStatus.StatusType, [DateToBeCompleted]-Date(), DaysInStatus([Status],[HSEID]);

I know there are a ton of joins there but I use those to ensure the data displayed is the actual text I want and not just an ID number.
Anyone able to take a look at this and help optimise?
The code actually ran very fast up until I added the function

 

[MarkK]

Your table StatusTiming has too many columns. Typically a row in a database table performs best when it defines one discrete object, and then any list of those items you implement is a vertical list. In your table one record is a horizontal list, which is why you need to address it's data with a function rather than the much more efficient SQL links you've used with other tables.
Consider...

tStatusTiming
StatusTimingID
StatusID
CaseID
StatusDate

Cheers,

 

[munkee]

Thanks for the reply. I already took a look at the overall problem again and it was definitely the overhead in calculating using a function within a query. Funnily this is something I have read about numerous times but for some reasons blindly thought I would be different and not hit the same issues HAH!

I reconstructed my table and decided to make sure that any calculations would be done on my form that writes to this table. The query would then be pulling out precalculated fields so there would be no overhead in calculating within the query.

StatusTimeID HSELINKID StatusType Day Entered Days Within Status
26 120 Initial Notification 21/02/2011 3
27 120 Awaiting Acceptance 21/02/2011 0
28 120 Action Being Taken 21/02/2011 0
29 120 Awaiting Review 21/02/2011 0
30 120 Closed 21/02/2011 0

The above is a sample of the table for 1 record with ID number 120.

I will just adjust my overall query to look at the current status using a where clause to pick out the desired value.