Query with Function call very slow

[pabatochy]

Running a Access query that calls a VBA function and it takes over seven hours to run. Without the Function call the query runs in 4 mins. There are four (4) tables joined together. The main table has 90k rows and the other three are outter joins. Four (4) mins is reasonable to run the query under Office on a workstation. But seven hours is not when the VBA function is called. The Function uses DAO. A Select is opened and if the row is found a column value is returned. If the row is not found the first available row is updated. Stepping thru the VBA module does not appear to show anything wrong, but letting it run takes too long. Why would the VBA module run so slowly? Running Office 2007 SP2 w/XP SP3.

Query:
SELECT DISTINCT fGetProfileName([Otrans for eLabel Temp 2].[eLabel FormNum],[Otrans for eLabel Temp 2].[eLabel Job Function],[Otrans for eLabel Temp 2].[Otran],(Val(IIf(IsNull([Otran CSI by Otran].[csi_id]),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[csi Number]),IIf([Otran CSI by Otran].[csi_id] In ('000000','999999'),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[CSI NUMBER]),[Otran CSI by Otran].[csi_id]))))) AS [Profile Name], "Form " & [Otrans for elabel Temp 2].[elabel FormNum] & "-" & [Otrans for elabel Temp 2].[elabel Job Function] & " CSI " & Val(IIf(IsNull([Otran CSI by Otran].[csi_id]),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[csi Number]),IIf([Otran CSI by Otran].[csi_id] In ('000000','999999'),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[CSI NUMBER]),[Otran CSI by Otran].[csi_id]))) AS [Profile Desc], [Otrans for elabel Temp 1].[Mdl Type], [Otrans for elabel Temp 1].[Mdl Resource], [Otrans for elabel Temp 1].[Mdl Dept/Div/Zone Desc], [Otrans for elabel Temp 2].[eLabel FormNum] AS Form, [Otrans for elabel Temp 2].[eLabel Job Function] AS [Job Function], Val(IIf(IsNull([Otran CSI by Otran].[csi_id]),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[csi Number]),IIf([Otran CSI by Otran].[csi_id] In ('000000','999999'),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[CSI NUMBER]),IIf(IsNumeric([Otran CSI by Otran].[csi_id]),[Otran CSI by Otran].[csi_id],'43458')))) AS [CSI Number], [Otrans for elabel Temp 2].Otran, [Otrans for elabel Temp 2].[Otran Resource] & [Otrans for elabel Temp 2].[Otran Owner] & ' ' & [Otrans for elabel Temp 2].[Otran] AS [Otran String], [Otran CSI by Otran].Description AS [Otran Description], (select Top 1 [eLabel Approvers].[First name] & ' ' & [eLabel Approvers].[Surname] & ' - ' & [eLabel Approvers].[SOEID] from [eLabel Approvers] where [eLabel Approvers].[Form] = [Otrans for eLabel Temp 2].[eLabel FormNum]) AS [Approver First/Last/SOEID], [Otran CSI by Otran].Usage AS [Lpar Usage], [Otran to CSI Mapping].[CSI NUMBER] AS [Otran CSI Number], [Otran CSI by Otran].CSI_ID AS [Lpar CSI], [Otrans for elabel Temp 2].[Otran Resource], [Otrans for elabel Temp 2].[Otran Owner], [Otran to CSI Mapping].Obsolete AS [Otran CSI Obsolete], [Otran CSI by Otran].[Dept Manager] AS [Dept Manager], [Otran CSI by Otran].[Appl Manager last name] AS [Appl Manager Last Name]
FROM (([Otrans for elabel Temp 2] LEFT JOIN [Otran to CSI Mapping] ON [Otrans for elabel Temp 2].Otran = [Otran to CSI Mapping].Entitlement) LEFT JOIN [Otrans for elabel Temp 1] ON ([Otrans for elabel Temp 2].[Profile Name] = [Otrans for elabel Temp 1].[Profile Name]) AND ([Otrans for elabel Temp 2].[eLabel FormNum] = [Otrans for elabel Temp 1].[eLabel FormNum]) AND ([Otrans for elabel Temp 2].[eLabel Job Function] = [Otrans for elabel Temp 1].[eLabel Job Function])) LEFT JOIN [Otran CSI by Otran] ON [Otrans for elabel Temp 2].Otran = [Otran CSI by Otran].Transaction
WHERE ((([Otrans for elabel Temp 2].Otran)<>'&&') AND (([Otran to CSI Mapping].Obsolete)='No' Or ([Otran to CSI Mapping].Obsolete) Is Null))
ORDER BY [Otrans for elabel Temp 2].[eLabel FormNum], [Otrans for elabel Temp 2].[eLabel Job Function], Val(IIf(IsNull([Otran CSI by Otran].[csi_id]),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[csi Number]),IIf([Otran CSI by Otran].[csi_id] In ('000000','999999'),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[CSI NUMBER]),IIf(IsNumeric([Otran CSI by Otran].[csi_id]),[Otran CSI by Otran].[csi_id],'43458')))), [Otrans for elabel Temp 2].Otran;

Function:
Option Compare Database
Option Explicit
Public Function fGetProfileName(FormNum, JobFunction, Otran, CsiNum As String) As String
Dim dbsMfes_Reconfig_Automation As DAO.Database
Dim rstGetProfileName As DAO.Recordset
Dim rstAddProfileName As DAO.Recordset
Dim strGetProfileNameSql As String
Dim strAddProfileNameSql As String

Set dbsMfes_Reconfig_Automation = CurrentDb

strGetProfileNameSql = _
"SELECT [Profile Name], Form, [Job Function], [CSI Number] " & _
"FROM [Profile Names] " & _
"WHERE [Profile Names].[Form] = '" & FormNum & "' " & _
"AND [Profile Names].[Job function] = '" & JobFunction & "' " & _
"AND [Profile Names].[CSI Number] = '" & CsiNum & "'"

Set rstGetProfileName = _
dbsMfes_Reconfig_Automation.OpenRecordset(strGetProfileNameSql, dbOpenSnapshot)

If rstGetProfileName.EOF = False Then
fGetProfileName = rstGetProfileName![Profile Name]
Else
strAddProfileNameSql = _
"SELECT Top 1 [Profile Name], Form, [Job Function], [CSI Number] " & _
"FROM [Profile Names] " & _
"WHERE [Profile Names].[Form] Is Null"

Set rstAddProfileName = _
dbsMfes_Reconfig_Automation.OpenRecordset(strAddProfileNameSql)

If rstAddProfileName.EOF = True Then
fGetProfileName = ""
[TempVars]![ErrorCode] = 1
Else
rstAddProfileName.Edit
rstAddProfileName![Form] = FormNum
rstAddProfileName![Job Function] = JobFunction
rstAddProfileName![CSI Number] = CsiNum
rstAddProfileName.Update
fGetProfileName = rstAddProfileName![Profile Name]
End If
rstAddProfileName.Close
End If

rstGetProfileName.Close
dbsMfes_Reconfig_Automation.Close

Set rstGetProfileName = Nothing
Set rstAddProfileName = Nothing

End Function


Profile Table:

I:\Databases\Retail\Retail MFES TSS CFEERS.mdb Tuesday, July 16, 2013
Table: Profile Names Page: 1

Properties
Attributes: Linked Connect: ;DATABASE=S:\MFES\R
etail\Access
Databases\MFES
Reconfig Retail Profile
DateCreated: 7/9/2013 1:40:50 PM DefaultView: 2
DisplayViewsOnShare 1 Filter: ([Profile Names].[Job
function] Is Not Null AND
[Profile Names].[Job
function]<>"")
FilterOnLoad: False GUID: {guid {03BF4C73-D61B-
4976-A6C2-
HideNewField: False LastUpdated: 7/9/2013 1:40:50 PM
NameMap: Long binary data OrderBy: [Profile Names].[Job
OrderByOn: False OrderByOnLoad: True
Orientation: Left-to-Right RecordCount: -1
SourceTableName: Profile Names TotalsRow: False
Updatable: False


Columns
Name Type Size
ID Long Integer 4
Form Text 4
Job function Text 3
Profile Name Prefix Text 4
Profile Name Seq Text 4
Profile Name Text 8
CSI Number Text 10
Occurance Integer 2

Table Indexes
Name Number of
CSI Number 1
Fields:
CSI Number Ascending
Form 1
Fields:
Form Ascending
ID 1
Fields:
ID Ascending
PrimaryKey 1
Fields:
ID Ascending
Profile Name 1
Fields:
Profile Name Ascending

 

[MarkK]

Your function has to run for every single row this query returns before the DISTINCT clause is enforced. What does the function do? If it's a data operation, you'll want to look at running a sub-query instead, so something like . . .

SELECT DISTINCT (
  SELECT OtherData 
  FROM OtherTable 
  ) As SubQueried, Field2
FROM tblPrimaryTable

. . . rather than writing VBA to go do this query . . .

SELECT OtherData 
FROM OtherTable

That's be the main optimization I can see, but you if you must use VBA, there might be stuff to optimize there, like, don't discard variables between function calls . . .

 

[Pat Hartman]

Since you didn't tell us what the function does, it is hard to offer suggestions. But, if the selection criteria and join reduce the recordset sufficiently and it makes sense to apply the function after that point, you can remove the function from the existing query and then make a wrapper query that selects the results of the posted query and applies the function. Doing this may allow the ACE database engine to optimize the query better.

It could be the code in the function that is simply inefficient.

 

[pabatochy]

So sorry, I left the function out. I updated original post with the Function. I tried to post at work but due to internet policy access was denied. Had to post from home. But here it is:


Option Compare Database
Option Explicit
Public Function fGetProfileName(FormNum, JobFunction, Otran, CsiNum As String) As String
Dim dbsMfes_Reconfig_Automation As DAO.Database
Dim rstGetProfileName As DAO.Recordset
Dim rstAddProfileName As DAO.Recordset
Dim strGetProfileNameSql As String
Dim strAddProfileNameSql As String

Set dbsMfes_Reconfig_Automation = CurrentDb

strGetProfileNameSql = _
"SELECT [Profile Name], Form, [Job Function], [CSI Number] " & _
"FROM [Profile Names] " & _
"WHERE [Profile Names].[Form] = '" & FormNum & "' " & _
"AND [Profile Names].[Job function] = '" & JobFunction & "' " & _
"AND [Profile Names].[CSI Number] = '" & CsiNum & "'"

Set rstGetProfileName = _
dbsMfes_Reconfig_Automation.OpenRecordset(strGetProfileNameSql, dbOpenSnapshot)

If rstGetProfileName.EOF = False Then
fGetProfileName = rstGetProfileName![Profile Name]
Else
strAddProfileNameSql = _
"SELECT Top 1 [Profile Name], Form, [Job Function], [CSI Number] " & _
"FROM [Profile Names] " & _
"WHERE [Profile Names].[Form] Is Null"

Set rstAddProfileName = _
dbsMfes_Reconfig_Automation.OpenRecordset(strAddProfileNameSql)

If rstAddProfileName.EOF = True Then
fGetProfileName = ""
[TempVars]![ErrorCode] = 1
Else
rstAddProfileName.Edit
rstAddProfileName![Form] = FormNum
rstAddProfileName![Job Function] = JobFunction
rstAddProfileName![CSI Number] = CsiNum
rstAddProfileName.Update
fGetProfileName = rstAddProfileName![Profile Name]
End If
rstAddProfileName.Close
End If

rstGetProfileName.Close
dbsMfes_Reconfig_Automation.Close

Set rstGetProfileName = Nothing
Set rstAddProfileName = Nothing

End Function

 

[Pat Hartman]

Well, you're running 90,000!!! queries plus a potential 180,000!!!! additional queries depending on the results of the first one. Perhaps that explains why it takes 7 hours. If you switched to saved querydefs rather than dynamic SQL, you would see some improvement. However, the first query is probably not necessary at all since it looks like you can use a left join to get that data.

I would rethink this process.

 

[pabatochy]

Thanks for the explanation. I do not see how joining the profile table to the main table will help.

Table T1(main table 90k rows) is left outter joined with T2, T3, T4. There is a function call to the Profile Table (P1).

Let's forget the function call for now. Also I do not want duplicates hense the use of Distinct. MS Jet engine will only go thru T1 once providing a result of 90k rows (depending on criteria; for now let's assume there are no criteria). Then the duplicates will be eliminated. Without the function call the query runs in <4min. Not an issue. And becasue the tables are on a network drive the response is different throughout the day. It sometimes runs in 30 sec without the function call.

The function uses the Profile table only. The profile Table is pre loaded with profile names AAAA thru ZZZZ + other search columns - see original post for Profile Table Def. The Function looks for a Profile name using the search columns. If found the Profile Name is returned. If not found the search colums are used to find the first unassigned profile name(search cols = blanks). The found row is updated and the profile name is returned.

The function will always return a profile Name, whether from an already assigned row or from an unassigned row (that becomes assigned). There are 456,976 rows in the Profile table (26^4). I have no issues with the Profile Table. There are appropriate indexes.

When the Function call is added to the Query there will still be 90k rows in the result with the function being called 90k times . Whether the Jet engine actualy makes 90k calls or makes less by invoking the Function after the Distinct I am not sure. So there will be approx 90k calls to the function(less if after the Distinct) and the function will make at min 90k calls to the P1 table sometimes two DAO calls to the P1 table if the Profile name is not found.

As far as I can tell there is an issue with Access calling VBA code. I have removed the VBA code from many Access apps due to the slowness. I am not sure if it has to due with being under Office 2007/SP2 and not SP3. Or some other reason, but there is definately an issue calling VBA. I have stepped thru the code and it works as coded, but very slowly.

I hope this explains in more detail what is going on. Maybe now you may be able to help me with why I am having this issue with slow processing times calling a vba module.

Thanks.

Patrick Waring

 

[Pat Hartman]

There are THREE queries in the function. The 180,000 number comes from the second and third queries. The second query is inside the If - I don't know how often it has to run and the third query needed to open the recordset to add rows is also inside an If. If they each run 90,000 times, that's an additional 180,000 queries. If they run 25% of the time, that's an additional 45,000 queries. You know, I don't. I didn't say they WOULD run 180,000 times. I said they could.

As far as I can tell there is an issue with Access calling VBA code.

Access has no trouble with VBA code. VBA is NATIVE to Access. Queries are run by the database engine which is either Jet or ACE or SQL Server or Oracle, etc. and is NOT Access. The database engine is COMPLETELY independent of Access and does not require Access to even be installed. NONE of those database engines knows anything about VBA. All they know is SQL. By including VBA in a query, you force the query engine to interrupt its normal workflow to offload work to VBA. This can be done but you may need to give the query engine help in optimizing how this happens. That's why I suggested the wrapper query. To attempt to influence Access to do the aggregating before running the query. Since the engine doesn't know what the function will return, it doesn't know if the result of the function will influence the aggregation so it will run it BEFORE the aggregation so it has to run fewer queries. Since you KNOW it doesn't YOU need to break up the query in a way that lets the database engine in on the joke.

And I do not see how joining the profile table to the main table will help.

So you think it takes less time to run 90,000 separate queries than it does to join to a table. OK. I guess we're done then.

PS - you're new here and haven't yet discovered the Go Advanced options. In the future when posting code use, code tags and when posting large SQL strings, please organize them so they are not a blob.