SQL Function Continuous recalculation upon Criteria Application

accro · #1 (**permalink**) 11-29-10, 13:16

SQL Function Continuous recalculation upon Criteria Application

A better labeled version of the problem, with specifics examples:

This code seems to force the function I have below to be volatilte and to be recalled each time any query references it. The base query creates the calculated field, but any further reference to such a field, forces recalculation, how can this be avoided in this case, and controlled in others?

SELECT dailyreturn([AAP].[PRICE],[AAP].[DATE]) AS Return, AAP.Date, [Return] AS Return2
FROM AAP
WHERE (((AAP.Price) Is Not Null));

The function is simple, it takes 2 variables (but really only uses 1) and returns the calculated field:

Function dailyreturn(return2, Date2 As Date) As Double
On Error GoTo Handler
Dim DB As Database
If IsNull(return2) Then
Else
showdate = Date2
dailyreturn = log(return2 / savedreturn)
savedreturn = return2
Exit Function
End If
Handler:
dailyreturn = 0
savedreturn = return2
End Function

Thus the query returns the desired calculated field et al.

It is when I try to apply criteria to the 'Return' calculated field that everythign goes wrong. If I apply it with a simple WHERE, on that column (Return), the function is called again, but now on a smaller recordset, hence changing the calculation).

In my code, as you can see, I do not explicityl create a recordset for the function as I would do in a SUB, my undefstanding was that the function was passed info 1 record at a time and hence you could not retrieve the whole recordset with one call to the function. In any evernt, shouldnt my method be exactly the same, as all we re interested in is the calculated field to be returned and to do no further manipulations on the data?

DCKunkle · #2 (**permalink**) 12-01-10, 15:20

In this situation dailyreturn is going to be called for each record that is returned in the SELECT statement.

I would think that your query would have issues. You have two fields called Return. One that is being calculated and one that is being renamed to Return2. I am not sure how Access handles that.

To keep Access from recalculating, I would create two queries. One to calculate the results and the second one (using the first one as it's input) to filter the results using WHERE.

Sinndho · #3 (**permalink**) 12-01-10, 16:44

This question was already asked in another thread and the answer remains the same.

In Access, if you call a function in a query and pass an element of the SELECT part of the query as argument to the function, the function will be called for every row returned by the query. If you have several references to a function, whether directly or indidrectly (i.e. by referencing a colum computed with the function), the function will be called for every reference to it and for every row returnd by the query.

If you want to convince yourself of this, try the following:

1) Create a public function in an independant module:

Code:

Public Function Cut(ByVal anything As Variant) As String

    Static cnt As Long
    
    cnt = cnt + 1
    If Len(Nz(anything, "")) > 2 Then Cut = Left(anything, 2) & " - " & cnt
    
End Function

2) Create a query that will reference the function, both directly and indirectly:

Code:

SELECT Tbl_CF_DATA.Customer, Cut([Customer]) AS x, [x] AS x2
FROM Tbl_CF_DATA;

3) Open the query. You'll see that the numeric part of x and x2 is not the same in the same row (see attached picture).

accro · #4 (**permalink**) 12-02-10, 14:48

First of all thank you very much for your reply Sindho.

Regarding the previous post, I had a pooly phrased posting, so I revised and elucidated the problem, hence the reposting. Also, I had gotten that far, but I am stuck on something you brought to a head, which is:

Yes in your example I can see that the function will be called EACH TIME a fucntion related field/calc is referrenced, and that is powerful, useful and very desireable sometimes -- but other times not. The 'not' case thus is how to make it non volatile field, like in a spreadsheet referecing an array of values as opposed to a formula. Could you tell me the best way to do that in access? I think thats really what I am missing and need to find out how to do. Thanks.

Sometimes, I do want to to recalculate based on a criteria reduced set; in such a case my expectation from a logical stanpoint would have been that the code structure would work somethign like this:

SELECT Function(SELECT Function(Data) FROM Table WHERE Function(Data) > Something)

I would expect that that is how you obtain continuous calcuatlion, by delberatly asking for it.
For one reason or another, my expectation example above of how it should work, does not work at all, and thats fine its just how access (or sql rather) was written; id just like to figure out the way to get to it, i have really exhausted all ideas for achieving that other than programatically, but again, it should be a matter of a manipulaton of a simple query.

Regarding the other post by DCKunkle, additional queries to the origginal field calculation query never worked -- i explored them fully - but to Sindo's observation that every reference forces recalculation, that option is definiely not doable. But thanks very much DCKunkle.

Thanks again all!

Also one thing I noticed, is that the sort order is not merely presentational, but is actually how the query evaluates things, in that order....

Andd if anyone else wants to weight in on or point to the best method for time ahieving time series data like this it would be appreciated.

Sinndho · #5 (**permalink**) 12-03-10, 04:57

The easiest solution that comes to my mind would consist in using a temporary table to store the resulting rowset, then going on working with this temporary table.

accro · #6 (**permalink**) 12-03-10, 10:56

Thanks,

Yes but if I call the 'function calculation' query into a recorset in code, then I apply the filtering query to the recordset, wouldnt take invoke the same re-calculation that you pointed out earlier? And if I were to save it to a real physical table, wouldnt that take up enormous amounts of space, I have about 5000 20000 x 4 array tables to make and go through. that would be creating an deleting (likely one right after the other) Gigabytes worth of file data, it would take forever to run what would be such a simple query.

Anyone have any other suggestions?

Thanks

Sinndho · #7 (**permalink**) 12-03-10, 12:26

If you perform the calculation in a Recordset loop you can control when how and how many times the function will be called.

With such an amount of data the temporary table would indeed be huge. Whatever the method you choose can be (Recordset loop or QSL Query) it will need a rather large amount of time to get the resulting rowset.

Not knowing the actual structure of the tables you have to handle, I cannot be sure of this, but I would bet that the Query solution would be faster.

In any case, I would try to optimise the function in order to make the system as fast as possible (e.g. do not pass a parameter to a function is that function does not use it, prevent implicit casting, etc.). Some optimisation of the data (proper data type, indexes, etc.) is perhaps possible too.

An alternate faster and easier solution would consist in using a proper SQL Server and let it perform most of the process.

accro · #8 (**permalink**) 12-03-10, 13:00

Hi, Thanka again,

Just to be clear though, would something like this qualify as invoking a recalculation:

QueryString = SELECT dailyreturn([AAP].[PRICE],[AAP].[DATE]) AS Return ..... the query we discussed

Set Table = db.openrecordset (QueryString)

So now we have the full uncriteria-ed recordset of the calculated field.

Could I then pass any sql SELECT statement directly onto that recordset 'Table.'

Could I do somethign like:

Docmd. runsql (DELETE Return FROM Table WHERE Return > 'Something')

To get a criteria reduced recordset in code? Would that be slow over thousands of large tables?

And also, can i just clarift a fwe of your statements, you say 'but I would bet that the Query solution would be faster.' Which is that to which you refer? Cause I agree I see the record set loop manaul function calculation as being slower than just about any possibility.

Also when you say use SQL, i suppose you mean the developers edition. because I am using a Desktop SQL server engine.....

Lastly, I will of course try to optimise, the resulting tables can be very simple, just an array of dates and returns in the simple case, the dates being the index. Probably oculdnt be simpler there.

I really dont know much about programming as you can tell, any assistance is greatly appreciated.

Sinndho · #9 (**permalink**) 12-04-10, 07:40

Quote:

Just to be clear though, would something like this qualify as invoking a recalculation:
QueryString = SELECT dailyreturn([AAP].[PRICE],[AAP].[DATE]) AS Return .....
the query we discussed

The function will be invoked by the SQL engine of Access for every reference to it, whatever the way the SQL engine is called.
One can believe that Access is not "smart" enough to understand that it's not necessary to call the function several times but this is not true. This behaviour is by construction. Several years ago I read a technical paper explaining why it was so, but I cannot remember the reference of this article right now.

Quote:

Set Table = db.openrecordset (QueryString)
So now we have the full uncriteria-ed recordset of the calculated field.
Could I then pass any sql SELECT statement directly onto that recordset 'Table.'
Could I do somethign like:
Docmd. runsql (DELETE Return FROM Table WHERE Return > 'Something')

In Access you cannot create a table that way. A table in Access is a serialized (= persistent, = written into a file) object that relies on a TableDef that contains the metadata (structural definition) of that table. SQL Server allows the creation of temporary or "volatile" tables, though.

Quote:

To get a criteria reduced recordset in code?
Would that be slow over thousands of large tables?

The sooner in the process you can reduce (limit) the data set, the best.

Quote:

And also, can i just clarift a fwe of your statements, you say 'but I would bet that the Query solution would be faster.'
Which is that to which you refer? Cause I agree I see the record set loop manaul function calculation as being slower than just about any possibility.

As far as I know, executing a SQL statement will always be faster than using a loop with a RecordSet. This is aways true (almost a dogma) for a SQL Server and seems to be a correct assessment for Access too.

Quote:

Also when you say use SQL, i suppose you mean the developers edition. because I am using a Desktop SQL server engine.....

MSDE (Microsoft SQL Server Desktop Engine) for the SQL Server 200 version, SQL Server 2005 Express Edition or SQL Server 2008 Express Edition, they all are free and can all be used in such a case as yours. The Developer Edition is submitted to licence fees: Microsoft SQL Server 2005: Developer

Quote:

Lastly, I will of course try to optimise, the resulting tables can be very simple, just an array of dates

This could be the most interresting part. Please do not consider what follows as criticism, but rather as a tentative to solve the problem in a reasoned manner.
Here is a commented version of the code for the function:

Code:

Option Explicit                                             ' Should be mandatory as this eliminates many possible errors.

' Function dailyreturn(return2, Date2 As Date) As Double    ' Date2 is not used for anything useful in the function.
Function dailyreturn(return2 As Variant) As Double

'    On Error GoTo Handler                                  ' Needless if the value of return2 and savedreturn
'                                                             are tested beforee being used.
    
'    Dim DB As Database                                     ' DB is not used by the function.
    
'    If IsNull(return2) Then                                ' If Not IsNull(return2) Then ...
'    Else                                                   ' would be better (only the Else part
                                                            ' is used in the original code).
'    showdate = Date2                                       ' showdate is not declared.
'    dailyreturn = Log(return2 / savedreturn)               ' Possible error if return2 = 0 --> Log(0) !!!
                                                            ' and what happens when savedreturn = 0?
'
'   1. Nz(return2, 0) <> 0 eliminates both Null and zero values.
'
'   2. What is savedreturn? It is not declared nor received as a parameter.
'      Moreover its value should be tested against 0 before being used as a divisor.
'
    If Nz(return2, 0) <> 0 Then dailyreturn = Log(return2 / savedreturn)

' The remaining part of the code is useless.
'
'    savedreturn = return2
'    Exit Function
'    End If
'
'Handler:
'    dailyreturn = 0
'    savedreturn = return2
    
End Function

The most interesting thing here is that we come to the conclusion that, provided we can check 2 variables for possible error-inducing values (return2 Is Null, return2 = 0 and savedreturn = 0) and that the scope of savedreturn is reachable by the SQL Engine (I still don't know where it comes from), we can reduce the function to a single line of code which means that we can eliminate the function entirely and use an "in line" function in the SQL statement:

Code:

SELECT Log([AAP].[PRICE] / [savedreturn]) AS Return

Or at least something very similar.

Eliminating the possibility of having a Null value in the [PRICE] column should not be difficult: just change the definition of the column and set a Not Null constraint. This is only an incomplete and not necessarily totally accurate example (partly because I do not have enough information about your database), but it can give you an insight of how you could optimize not only the function but the whole system. Using any version of SQL Server would of course expand the possibilities of optimisation to another level.

accro · #10 (**permalink**) 12-04-10, 12:58

Really, thanks a lot. It is really not expected that you share as much of your time as you do, but it is very much appreciated.

OK, so I'll try to make this quick, so I can free you up to do your own programming

1) Yes, I agreee the code but superflous (and I left some debugging stuff in there) but the savedreturn field, is neccessary in this calculation construction as you need to save a price of a previously passed record to calculate a return (2 records price value = 1 return value), so unfortunately no, it cannot be handled in a 1 line function, I wish.

2) I have already downloaded SQL 2008 Express, lets see how that goes -- do you think that can handle the kind of SQL querying I am trying to do. I suppose I will use it from now on.

3) Regarding the Null values, I will try to define the colume as Not Null, Thanks, I wonrder what it does then with blank data?? And the NZ function is a real winner, thanks.

4) SOLUTION.... I think I have one if SQL Server cant do any better (and it doesnt use recordset loops!). Here it is:

a) We can either save the most used 'function fields' as value into Tables (we maybe doubling size of DB) and then dont need to worry about their recalcilation but also the, perhaps, more preferable:

b) We can redine the function approach, FROM one that calculates on the entire recordset passed to it (which I had though from a logical standpoint would optimise the SQL, but apparently doesnt) TO one which uses DLookup or some other lookup function, to 'find' the 'correct' other record whose value we seek to use.

So in other words, for each record we can 1) find all prices D-1 with Dlookup 2) calculate log(price / price d-1), then 3) filter down such recordset as desired, 4) find the desired price d-x with Dlookup (which may now be outside the new recordset) and 4) calculate desired return log(price / price d-x) -- which in many cases will not even be neccessary being equal to that of step 2.

The difference is since we are now passing static record values into the new function via the lookup, it will return the same thing each time.

POTENTIAL PROBLEMS are 1) maybe these queries are still to complicated and 2) are the dLookups going to take up too much time. 3) would a TOP 1 series of nested SELECT statements be quicker?

i imagine the above being somethign like:

SELECT Price(PriceField,Dlookup(PriceField,Table,Date = DATEX )) FROM Table
WHERE Price(PriceField,Dlookup(PriceField,Table,Date = Date-1 )) > 'Something'

DATEX being user supplied vaiable as desired

Function Price(Price0,Price1)
Price = Log(Price0/Price1)
End Funtion

I imagine Dlookups must be slower than nested SELECTS right....

Sinndho · #11 (**permalink**) 12-04-10, 14:05

You're welcome

If you intend to use a SQL server, let it perform all the calculations and don't bother with DLookUp and the like. A SQL server has more powerful tools (stored procedures, user-defined functions) that can do the job more efficiently.

To call a stored procedure from Access, there are many solutions. Here's one you can store as a Class Module in Access:

Code:

Option Compare Database
Option Explicit

Private m_strConnection As String
Private m_strName As String
Private m_strArgument As String
Private m_booPersistent As Boolean
Private m_booStatus As Boolean
Private m_varValue As Variant
Private m_lngErrorCode As Long
Private m_strQueryName As String
Private m_strErrorDescription As String
Private m_dbs As DAO.Database

Public Property Get Argument() As String

    Argument = m_strArgument
    
End Property

Public Property Let Argument(ByVal strArgument As String)

    m_strArgument = Trim(strArgument)

End Property

Public Property Get Connection() As String

    Connection = m_strConnection
    
End Property

Public Property Let Connection(ByVal strConnection As String)

    m_strConnection = Trim(strConnection)

End Property

Public Function Delete(Optional ByVal Name As Variant) As Boolean

    If Not IsMissing(Name) Then Me.Name = CStr(Name)
    DeleteQueryDef m_strQueryName
    
End Function

Public Property Get ErrorCode() As Long

    ErrorCode = m_lngErrorCode

End Property

Public Property Get ErrorDescription() As String

    ErrorDescription = m_strErrorDescription
    
End Property

Public Function Execute(Optional ByVal Connection As Variant, _
                        Optional ByVal Name As Variant, _
                        Optional ByVal Argument As Variant, _
                        Optional ByVal Persistent As Variant) As Boolean

    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    On Error GoTo Err_Execute
    
    ' Process optional parameters.
    '
    If Not IsMissing(Connection) Then m_strConnection = Trim(CStr(Connection))
    If Not IsMissing(Name) Then Me.Name = Trim(CStr(Name))
    If Not IsMissing(Argument) Then m_strArgument = Trim(CStr(Argument))
    If Not IsMissing(Persistent) Then m_booPersistent = CBool(Persistent)
    
    ' Concatenate procedure name and its arguments (if any).
    '
    strSQL = m_strName
    If Len(m_strArgument) Then strSQL = strSQL & " " & m_strArgument
    
    ' Delete former same QueryDef (if any) in the database.
    '
    DeleteQueryDef m_strQueryName
    
    ' Let's try to create the QueryDef.
    '
    Set m_dbs = CurrentDb
    Set qdf = m_dbs.CreateQueryDef
    With qdf
        .Connect = m_strConnection
        .Name = "Qry_" & m_strName
        .SQL = strSQL
        m_dbs.QueryDefs.Append qdf
    
    ' No VBA errror --> Good so far.
    ' Let's analyze the Querydef to know if it's usable
    ' and if the SQL Server returned status information.
    '
        If .ReturnsRecords = True And .Fields.Count > 0 Then                            ' Conditions for being able to open a Recordset on the QueryDef.
            Set rst = .OpenRecordset
            With rst
                If .Fields(0).Name = "ReturnValue" And .Fields(1).Name = "Status" Then  ' Stored procedure is managed (at least for errors).
                    m_booStatus = !Status                                               ' Status = True --> Success, Status = False --> Error.
                    If m_booStatus = True Then                                          ' Stored procedure is a "true" managed procedure and succeeded.
                        m_varValue = !ReturnValue                                       ' - Store value returned by the procedure.
                        m_lngErrorCode = 0                                              ' - ErrorCode = 0 --> No error.
                    Else                                                                ' An error occured, whatever the type of procedure can be.
                        m_lngErrorCode = !ReturnValue                                   ' - ErrorCode contains the error code returned by the procedure.
                        m_varValue = 0                                                  ' - Value = 0     --> No usable returned value.
                    End If
                Else                                                                    ' Stored procedure is not managed for success but returns rows.
                    m_booStatus = True                                                  ' - Status = True --> Success presumed.
                    m_varValue = 0                                                      ' - Value = 0     --> No usable returned value.
                    m_lngErrorCode = 0                                                  ' - ErrorCode = 0 --> No usable error code.
                End If
                .Close
            End With
        Else                                                                            ' Unmanaged procedures not returning rows are not accepted.
            m_booStatus = False                                                         ' - Status = False --> Error.
            m_varValue = 0                                                              ' - Value = Error message.
            m_lngErrorCode = 50                                                         ' - ErrorCode = 50 --> Unknown procedure type.
        End If
        .Close
    End With
    
Exit_Execute:

    ' Clean up.
    '
    If m_booPersistent = False Then DeleteQueryDef m_strQueryName
    Set rst = Nothing
    Set qdf = Nothing
    m_strErrorDescription = GetErrorDescription(m_lngErrorCode)
    Execute = m_booStatus
    Exit Function

Err_Execute:
    m_booStatus = 0
    m_varValue = 0
    m_lngErrorCode = Err.Number
    m_strErrorDescription = Err.Description
    Resume Exit_Execute
    
End Function

Public Function GetErrorDescription(ByVal ErrorCode As Long) As String

    Select Case ErrorCode
        Case 0:     GetErrorDescription = "Success: No error."
        Case 50:    GetErrorDescription = "Bad Procedure Type: Unmanaged procedures not returning rows are not accepted."
        Case 70:    GetErrorDescription = "Bad User Credentials: Permission is denied."
        Case Else:  GetErrorDescription = "Unknow Error Code: Code " & ErrorCode & "is not handled by this class."
    End Select
    
End Function

Public Property Get Name() As String

    Name = m_strName
    
End Property

Public Property Let Name(ByVal strName As String)

    m_strName = Trim(strName)
    m_strQueryName = "Qry_" & m_strName

End Property

Public Property Get Status() As Boolean

    Status = m_booStatus

End Property

Public Property Let Persistent(ByVal booPersistent As Boolean)

    m_booPersistent = booPersistent

End Property

Public Property Get QueryName() As String

    QueryName = m_strQueryName

End Property

Public Property Get Value() As Variant

    Value = m_varValue

End Property

Private Function DeleteQueryDef(ByVal QueryName As String) As Boolean

    Dim qdf As DAO.QueryDef
    
    For Each qdf In m_dbs.QueryDefs
        If qdf.Name = QueryName Then
            DoCmd.DeleteObject acQuery, QueryName
            Exit For
        End If
    Next qdf
    
End Function

Private Sub Class_Initialize()

    Set m_dbs = CurrentDb
    
End Sub

Private Sub Class_Terminate()

    Set m_dbs = Nothing

End Sub

Note that the error handling part is specific to one of my applications and you'll have to adapt or to delete it.

You can easily use it in your code, like this:

Code:

Private Function DeleteRecord()
    
    Dim cls_SP As Cls_StoredProcedure

    If MsgBox("Delete current record?", vbOKCancel, "Please confirm") = vbOK Then
        Set cls_SP = New Cls_StoredProcedure
        With cls_SP
            .Connection = c_strConnection ' String connection to the server.
            .Name = c_strProc_Delete    ' c_strProc_Delete is a constant containing the name of a stored procedure.
            .Argument = m_clsList.RowID ' ID of the row to delete.
            .Persistent = False         ' Do not keep the query after execution.
            .Execute
            If .Status = False Then     ' Parse result and handle errors.
                MsgBox "Error while executing: '" & .Name & " " & .Argument & vbNewLine & _
                       "Returned code: " & .ErrorCode & vbNewLine & _
                       "Error message: " & .ErrorDescription, vbExclamation, "DeleteRecord"
            Else
                m_clsList.Requery Row_Previous ' Move to privious row into the list.
                RowID = RowID
            End If
        End With
        Set cls_SP = Nothing
    End If
    
End Function

But once more there are many solutions, and many of then are simpler than this one.

Internet Services

Web Development

Digital Marketing

Consumer Tech