Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2010
    Posts
    8

    Unanswered: SQL Function Continuous recalculation upon Criteria Application

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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).
    Attached Thumbnails Attached Thumbnails ScreenShot.jpg  
    Have a nice day!

  4. #4
    Join Date
    Nov 2010
    Posts
    8

    Thanks

    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.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  6. #6
    Join Date
    Nov 2010
    Posts
    8
    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

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  8. #8
    Join Date
    Nov 2010
    Posts
    8

    Garcon Eltinty

    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.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.

    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.

    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.

    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.

    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

    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.
    Have a nice day!

  10. #10
    Join Date
    Nov 2010
    Posts
    8

    Thanks Sindho

    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....

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •