How can Function return multiple values and not be re-run in Query

[Rx_]

Can anyone suggest other efficiencies?
Trying to run a query where each 4 fields calling a custom function will not just re-run the same custom function over and over again for each field in a single record.

A Function has a huge amount of multiple queries and logic to perform.
The Function returns a Integer, Integer, Integer, and optional Integer.
Each integer requires a DLookup to lookup a String description value for each individual integer (in each of 4 fields).

The problem is, the DLookup in each column that runs against each of the integers re-run the same function.
The result is that a single record, each of the 4 columns returning a single of the 4 values, the complex function is re-run 4 times.

The function is huge, part of a Business Rules Engine. Depending on the Rule-Meta data - it might launch up to a dozen queries and perform logic steps for each record. This is not the ordinary SQL Query.
Imagine if one record (for 1 field) takes 0.1 second to run. By referencing the function in 4 columns, this same function is re-run 4 times (0.4 Seconds) Against 50,000 records - this duplication of re-running the function for each column can really add up.

Possible Solutions:
Researched Class Modules - There is a comment that the property Get, Let actually reduce performance. There are huge advantage of code documentation, documentation and centralization.
It doesn't claim class modules reduce execution as each propery is returned. It also describes that Class Modules can't be called directly in a Query - unless each property is wrapped in a function.

Function Returns one String with delimiters: e.g 34;54;55;1
This single column goes into a Make Table (runs function one time per record) Then the D-Lookup is run against static local data. This pevented the function from being run over and over across the network linked data.

Final Solution:
Eventually, the many hundred lines of VBA code for the Rules Engine will be converted into SQL Server T-SQL Functions on the server.
For a Rule Engine development, Access has been great for a rapid protoype development and testing. The TSQL will be a final big step requiring re-coding. It is not currenty my option for the delivery time frame.

 

[WayneRyan]

Rx,

If the logic of the function can't be "compartmentalized" such that it doesn't
reprocess the same queries to produce the individual results, then you have to
ensure that it only does the queries once.

Given that, here's two very simple scenarios:

Option 1: (Evaluate at query-time)
=========
A very simplistic short-term solution would be for the function to store its "one-time"
calculation in Global Variables --> InputArg(s) and R1, R2, R3, R4.

If it is called with the "Same Input argument"; Don't Calculate, Return (R1, R2, R3 or R4).

If it is called with a "New Input argument"; Calculate, Set Globals, Return R1

This is very simple, but it will stop the redundant calculations.

Usage:
======
GetR1: fnYourFunction(PK_Info, 1) <-- will set globals and return R1
GetR1: fnYourFunction(PK_Info, 3) <-- will NOT calculate/Set globals and return R3


Option 2: (Evaluate before query)
=========
Pre-calculate the entire result set into a table.
The table has PK (Input args) and a column for each result.
Then just join to that table in your final query.

Usage:
======
Just Join to this table by PK(s) and select the Result Columns.

The last is very similar to your second solution.

Hopefully, it at least gives you some ideas.

Wayne

 

[Rx_]

Yes, I have the rules in a Matrix for rule development.
In general, you are absolutely correct. It would have been a lenghty question to explain the whole process and the layers of logic grouping.
Your right, it works from a development point. But takes a long time to run.

For now, if I could just run a any function once, but return several values from that function to multiple fields, I would be home free.

Will post some code to show a failed approach. This also shows that Microsoft is wrong about the Access Query sequence.

I tried your global variable in terms of a Query (see code below). Turned out the Query doesn't give me control of the execution order.
That is fine, now I know one more method that won't work LOL. At least for a query.

Sometimes, I wish the Query object wouldn't help so much!

 

[Rx_]

Read that MS Access claims the Query Execution takes place from Left to Right - top to bottom.
This proved that it doesn't execute the functions in a query field in that order.

My goal - run the big validation Function once! The function returns a -1 (true), a 0 (false but no reason given) or an integer.
The Integer returned can be looked up in a table to describe why the row failed validation.

My logic - Make a Public Variable.
In Column 1 - call the validation function (rule) and return the integer. During the funciton, use that number to look up the description string, put that string in a Public variable.
In Column 2 - call a Function that retrieves the Description string.
This would prevent a DLookup(Function...) that called the validaiton function a 2nd time.

Results - no matter what order these two funtions are placed in the Query columns, the 2nd one below always runs first!

Public RuleResultStr As String ' Populated with first function, retrieved by 2nd function
Public Function R_Evaluation2(ID_Wells As Integer, ID_WellStatus1 As Integer) As String
      'APDApproved test first
      Dim RuleResult          As Integer ' returned from R_APDApproved function
      Dim RuleResultString    As String ' use integer to look up Description
10    RuleResult = R_APDApproved(ID_Wells)
20    If RuleResult > 0 Then ' returns -1 if rule passed (true)
30        RuleResultString = DLookup("Description", "R_Rules", "ID_Rules = " & RuleResult)
40        RuleResultStr = RuleResultString ' pouplate Public variable above
50        R_Evaluation2 = RuleResultString
60    Else
70        R_Evaluation2 = ""
80    End If
End Function
' Problem - this function always runs first - no matter where it is placed in a query
Public Function GetRuleString() As String
10        GetRuleString = RuleResultStr  ' Get Public Variable from above
End Function

Problem in a Query that caused formula to be called 2 times: If the 2nd query column performs a DLookup on the result of the 1st Query Column that contains the large validation function; the large validation function is run a 2nd time because of the DLookup.

In Query fields:
First Field: MyValidation: R_APDApproved(ID_Wells)
Second Field: MyValidationDesc: DLookup("Description", "R_Rules", "ID_Rules = " & [MyValidation])
- the Second Field implicity runs the validation function R_ADPAPPROVED again.

Does this remind anyone of a Class Object?
A Class can not be called from a Query unless it is in a function.
However: If the order of the functions can not be controlled, then how can a class object be updated at the beginning of a new record in a Query?

 

[WayneRyan]

RX,

You can code it so that the order of the call is not important.

Public gblCriteria As String
Public gblResult1 As String
Public gblResult2 As String
Public gblResult3 As String
Public gblResult4 As String

Public Function fnTest(strCriteria As String, WhichResult As Integer) As String
'
' Only calculate on first call.
'
If strCriteria <> gblCriteria Then
   '
   ' Perform Extensive SQL Here ...
   '
   gblResult1 = strResult1
   gblResult2 = strResult2
   gblResult3 = strResult3
   gblResult4 = strResult4
   gblCriteria = strCriteria
End If
'
' Return Result 
'
Select Case WhichResult
   Case 1
      fnTest = gblResult1
   Case 1
      fnTest = gblResult2
   Case 1
      fnTest = gblResult3
   Case 1
      fnTest = gblResult4
   End Select
End Function

Wayne

 

[CJ_London]

I won't pretend to fully understand what you are trying to achieve but in the context of using dlookups have you considered using recordsets instead?

Not sure what the function R_APDApproved actually does (presumably another lookup) but you might be able to combine it with the DLookup a couple of lines further down to return a recordset giving you the result required?

Depending how big your rules matrix is you could consider loading it all into memory as a recodset and running from that rather than the tables

 

[Rx_]

Bingo! After evaluating things on Saturday, back Sunday morning to do just that.
For ease of troubleshooting, will create a small local table and populate it with a recordset. Then, will have options to join or use code to pdate the results. Once confident that the recordset structure provides the desired results, will probably short-cut to the recordset.
The large validation function will be run to populate that. The recordset will then perform the DLookup based on a number argument rather than a function argument.
Then the code for the next record will clear the data in the recors and start again.

Your post was really appreciated. Working independentlly with nobody else to run an idea past can lead to falling into a rut.

 

[Galaxiom]

I strongly suspect your problem starts with the choice of a DLookup applied over and over again. Having to resort to this instead of using a join suggests a structural anomaly

Running the same function against multiple fields further suggests a structural mistake. It probably should be a single field.

Cn you post a sample of your database?

 

[Rx_]

Will try to get to a release to show you. Spent Sunday converting it to a VBA Recordset soltuion.
Clear Table, reset autocounter, run VBA.
Even with the DLookup in the VBA Recordset code, the query ran about 7 times faster. Eventually, this code must be converte into SQL Server TSQL functions.
Once the DLookup was coded (then commented out of code) it didn't appear to make a huge difference in the VBA Recordset code.

After rethinking this, my thought is to drop Dlookup and go for a SQL update query solution once the VBA Recordset populates the table.

It is probably worth mentioning, this is a linked SQL Server DB. DLookup might behave differently with a Jet DB.

Attached: the result for a query for 4 (of 30) Status Validations.
All of the zero's now return a number from 1 to 340 and the Dlookup was used for each of them. (e.g. why did this status fail the rule)
This Access query result (in this example) is put into Excel so Subject Matter Experts (SME) can evaluate the logic down to the individual rule.
So, each column is one function. These get wrapped up into rule groupings. A DLookup re-ran the rule in a query.
Today, I am finishing a VBA recordset soltuion. Now a DLookup (or a sql join) can just get the numereic value.
The Excel is just for testing and planning with SMExperts.
The final code will eventually change the actual status in a database once the key conditions take place.

Very long-winded. But, it might provide a small insight to managing metadata.