Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Dsum might be reason to slow down ODBC record performance

    To have a stock value, I have written following VBA. It was working perfectly with very good speed but since I put the tbls on SQL Server, performance is very slow. (takes around 30 seconds to process)

    Me.ProductCode.SetFocus
    Dim OBPurTrans As Long
    Dim OBPrice As Double
    Dim OBValue As Double
    Dim PRetQty As Double
    Dim PurRetValue As Double
    Dim ActualPurQty As Double
    Dim ActualPurValue As Double
    Dim PurQtyValue As Double
    Dim SRetQty As Double
    Dim SalesRetValue As Double
    Dim ActualSalesQty As Double
    Dim ActualSalesValue As Double
    Dim MaintQtyValue As Double
    Dim TotValue As Double
    Dim CurrStock As Long
    Dim AvgPrice As Double

    Me.ProductCode.SetFocus
    OBPurTrans = DCount("*", "T_PurInvFoot", "ProductCode=" & Me.ProductCode.Text) + 1
    OBPrice = Nz(DLookup("OldPurPrice", "Product_master", "ProductCode=" & Me.ProductCode.Text), 0)
    OBValue = OBPrice * Nz(DSum("Openingbal", "Product_master", "ProductCode = " & Me.ProductCode.Text), 0)

    PRetQty = Nz(DSum("PurRetQty", "T_PurInvFoot", "Productcode = " & Me.ProductCode.Text), 0)
    PurRetValue = Nz(DSum("PurRetAmt", "T_PurInvFoot", "Productcode = " & Me.ProductCode.Text), 0)
    ActualPurQty = Nz(DSum("PurQty", "T_PurInvFoot", "Productcode = " & Me.ProductCode.Text), 0) - PRetQty
    ActualPurValue = Nz(DSum("Amount", "T_PurInvFoot", "Productcode = " & Me.ProductCode.Text), 0) - PurRetValue

    'PurQtyValue = Nz(DSum("Amount", "T_PurInvFoot", "Productcode = " & Me.ProductCode.Text), 0)

    SRetQty = Nz(DSum("SalesRetQty", "T_SalesInvFoot", "Productcode = " & Me.ProductCode.Text), 0)
    SalesRetValue = Nz(DSum("OrigRetAmt", "T_SalesInvFoot", "Productcode = " & Me.ProductCode.Text), 0)
    ActualSalesQty = Nz(DSum("SalesQty", "T_SalesInvFoot", "Productcode = " & Me.ProductCode.Text), 0) - SRetQty

    ActualSalesValue = (Nz(DSum("OrigSalesAmt", "T_SalesInvFoot", "Productcode = " & Me.ProductCode.Text), 0) - SalesRetValue)

    MaintQtyValue = Nz(DSum("Amount", "T_MInv_Foot", "Productcode = " & ProductCode), 0)

    TotValue = (OBValue + ActualPurValue) - (ActualSalesValue + MaintQtyValue)

    I tried then using below (2 code line to test only) but it says type mismatch.

    Dim db As DAO.Database
    Dim OBPurTrans As Long
    OBPurTrans = CurrentDb.OpenRecordset("select * from T_PurInvFoot where Productcode=" & ProductCode)
    ' here is the error of type mismatch

    OBPrice = CurrentDb.OpenRecordset("select OldPurPrice from Product_master where Productcode=" & ProductCode)

    Actually I nened to count record and some value based on the productcode criteria.

    Any idea?

    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Is ProductCode text or numeric?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    It is numeric data type...

    I am very much concern to processing speed. Is it the way ODBC performs?
    Last edited by Ashfaque; 05-21-07 at 01:12.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you have 13 domain aggregate functions - there is no slower way to fill your form!!

    my approach to this is not 100% conventional, but it works for me.
    i would have a stored procedure on the server to do the calculations. the sp returns the results using the same names as your form fields. a temporary qdef fills the form:

    Code:
    Dim recs As DAO.Recordset
    Private Sub FillFormAbc()
        Dim dabs As DAO.Database
        Dim qdef As DAO.QueryDef
        Dim rfld As DAO.Field
        On Error GoTo err_FillFormAbc
        Set dabs = CurrentDb
        Set qdef = dabs.CreateQueryDef("")
        qdef.Connect = YourConnectionString
        qdef.ReturnsRecords = True
        qdef.SQL = "USE YourDatabase EXEC ash_StoredProcXyz " & [Forms]![YourParameterForm]![YourParameterField]
        Set recs = qdef.OpenRecordset(dbOpenSnapshot)
        With recs
            If .EOF Then
                MsgBox "Can't get data for " & [Forms]![YourParameterForm]![YourParameterField], vbExclamation, "Error: FillFormAbc"
            Else
                For Each rfld In recs.Fields
                    Me(rfld.Name) = rfld
                Next
            End If
        End With
    exit_FillFormAbc:
        On Error Resume Next
        Set rfld = Nothing
        Set qdef = Nothing
        Set dabs = Nothing
        Exit Sub
    err_FillFormAbc:
        MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error: FillFormAbc"
        Resume exit_FillFormAbc
    End Sub
    recs is declared module-wide as part of my opportunistic locking scheme.

    your second attempt
    long = ...openrecordset...
    fails because the recordset is an object, not a long.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Dsum is a powerfull tool, liek the other domain functions. but used inappropriately, like any tool, it can fail to do the job adequately. if you are doing that number of domain functions I suspect you may getaway with doing more of the grint work yourself, rather than , possibly being lazy and, letting the domain functions do it for you.

    The performance saving can be significant. I stripped out the domain functions form a colleagues piece of work.. was taking around 3..5 minutes to refresh a form, which the users would barely tolerate, down to around 3..5 seconds, similarly a report came down form more than 10 minutes to generate to sub 30 seconds.

    In my view use Domain functions sparingly.. they can be your friend, used extensively they can be a problem.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Izy, I never used sp. I copied your code a new sp on Server, it saved as templet. Now where and how can I reopen it to update it.

    Secondly, I m getting the data (inputing productcode and getting the recordset on sub form. Even if I adopt your sp method, it would get the recordset only. How about the calculation I was doing with DSum?

    Healdem, do you think my Dsum functions are not used sparingly? If not then what is the other way to use them?

    Please advice.

    With kind regards,
    Ashfaque

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Ashfaque
    do you think my Dsum functions are not used sparingly? If not then what is the other way to use them?
    All at once in query (perhaps as part of a sproc or view as per Izy's suggestion).

    T-SQL Code (although you'll need to do something to filter by the productcode - all depends on the method you settle on e.g. linked tables, recordsets etc):
    Code:
    SELECT PRetQty = Sum(PurRetQty)
    , PurRetValue = Sum(PurRetAmt)
    , ActualPurQty = Sum(PurQty) - Sum(PurRetQty)
    --And so on....
    FROM T_PurInvFoot
    WHERE Productcode = " & Me.ProductCode.Text
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Pootle,

    I will try to update accordingly and come back to you soon.

    With kind regards,
    Ashfaque

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi ashfaque,
    my previous code is just access code. you need to make the sp separately on the sql-server. here's an example how: if you run something like this in sql-server query analyser (suitably editing YourDatabase and maybe the datatype int of course), it should make an sp on the server that pulls everything you need from the table T_PurInvFoot.

    Code:
    USE YourDatabase
    GO
    CREATE PROC ash_StoredProcXyz @ashProd int
    AS 
    SELECT Count(*) AS OBPurTrans
         , Sum(PurRetQty) AS PRetQty
         , Sum(PurRetAmt) AS PurRetValue
         , Sum(PurQty) AS ActualPurQty
         , Sum(Amount) AS PurQtyValue
    FROM T_PurInvFoot
    WHERE ProductCode = @ashProd
    RETURN
    GO
    ...you EXEC the sp and use it's result set with the code in my previous post.



    although the above sp only talks to T_PurInvFoot, it should be radically faster than the 5 domain aggregates it replaces because all the work happens on the server: your client sends a much smaller query and receives only the 5 calculated results that you wanted.

    also - it should be possible to obtain ALL the data you wanted from one sp EXEC by combining a series of queries in the sp.

    unfortunately for you, 99.999% of my forms (actually, subforms) that need to get filled are intentionally mono-table forms, so i never bothered to mess with the OUTPUT and DECLARE stuff that seems to be necessary to get a 'one row' return from queries to multiple different tables by sp ...i'm sure there's an sp-guru out there who will tell us how.

    izy
    Last edited by izyrider; 05-21-07 at 12:10. Reason: duhhhh! forgot the aggregates - should be OK now
    currently using SS 2008R2

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A further reason it is more efficient is because it requires a single scan of the table rather than n scans. As it happens, DCount, DSum does just send an aggregate SQL query to SQL Server to execute so it is actually executed server side in any event (I know only 'cause I just checked )

    Quote Originally Posted by izyrider
    unfortunately for you, 99.999% of my forms (actually, subforms) that need to get filled are intentionally mono-table forms, so i never bothered to mess with the OUTPUT and DECLARE stuff that seems to be necessary to get a 'one row' return from queries to multiple different tables by sp ...i'm sure there's an sp-guru out there who will tell us how.
    .... uh? Do you mean outputting multiple result sets from a sproc?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - I think I know what you mean. Using output parameters if you need to output totals from several different sources? Personally I would not do that. Lots of fiddly code, both server and (especially) client side.

    I would just output however many resultsets you need and then use the very handy NextRecordset method of the recordset. I think this is ADO specific, not DAO. You would probably know better than me... I believe it is slightly less efficient to do things this way than use output variables but it would be such a marginal difference I would not worry about it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    exactly pootle. OP has several aggregates on several tables plus a DLookup which could be probably be fake-aggregated with Max. might be nice to roll them up into one sp with a single row result set holding all needed stuff (i.e. aiming for zero client-side complications ...unlike the multiple-result-set game which is a client-side PITA)

    i'll probably have a play with it in the next couple of weeks - could be useful one of these days i suppose, and it MUST be possible.

    interesting your comment that Dxxx() is well treated and aggregates at the server... i hadn't checked, and feared the worst: all rows back to the client for aggregation.
    what was your scenario?
    ODBC?
    linked tables? (obstinately, i don't use)
    saved queries or inline?

    izy
    Last edited by izyrider; 05-21-07 at 14:53. Reason: doh! linked (obviously) and inline (obviously)
    currently using SS 2008R2

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    forgot to mention: DAO/ODBC also does .NextRecordset (not that i use it)

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by izyrider
    interesting your comment that Dxxx() is well treated and aggregates at the server... i hadn't checked, and feared the worst: all rows back to the client for aggregation.
    what was your scenario?
    ODBC?
    linked tables? (obstinately, i don't use)
    saved queries or inline?
    linked table, ?DCOUNT(...., ...) in the immediate window.
    That is for the aggregates that SQL Server supports. Try running
    Code:
    SELECT LAST(MyCol) AS MyCol_Last FROM MyTable
    and the code submitted to SQL Server is a bowel loosening
    Code:
    SELECT MyCol FROM MyTable
    I will whip up a couple of alternative examples but personally I have no problem with multiple recordsets (but then I use them regularly and am fairly comfy with them now).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
     
    --SET UP THE DATA
    create table dbo.mytable
     (
      a int
      , b int
      , c int
     )
     
    insert into dbo.mytable (a, b, c)
    select 1, 23, 45
    union all
    select 2, 10, 110
    union all
    select 3, 9, 209
    union all
    select 4, 38, 863
    union all
    select 5, 23, 1
     
    create table dbo.myothertable
     (
      a int
      , b int
      , c int
     )
     
    insert into dbo.myothertable (a, b, c)
    select 1, 9, 48
    union all
    select 2, 7, 26
    union all
    select 3, 2, 76
    union all
    select 4, 5, 52
    union all
    select 5, 5, 38
    go
    --END SET UP THE DATA
     
    --PROC #1
    create proc dbo.aggs_single_select
    as
     
    declare @l_a_count_o as int
      , @l_b_sum_o as int
      , @l_c_max_o as int
     
    select @l_a_count_o = count(a)
      , @l_b_sum_o = sum(b)
      , @l_c_max_o = max(c)
    from dbo.myothertable
     
    select count(a) as a_count
      , sum(b) as b_sum
      , max(c) as c_max
      , @l_a_count_o as a_count_o
      , @l_b_sum_o as b_count_o 
      , @l_c_max_o as c_count_o 
    from dbo.mytable
    go
     
    --CALL PROC #1
    exec dbo.aggs_single_select
    go
     
    --PROC #2
    create proc dbo.aggs_output
     @l_a_count as int  OUTPUT
     , @l_b_sum as int  OUTPUT
     , @l_c_max as int  OUTPUT
     , @l_a_count_o as int OUTPUT
     , @l_b_sum_o as int OUTPUT
     , @l_c_max_o as int OUTPUT
    as
     
    select @l_a_count_o = count(a)
      , @l_b_sum_o = sum(b)
      , @l_c_max_o = max(c)
    from dbo.myothertable
     
    select @l_a_count = count(a)
      , @l_b_sum = sum(b)
      , @l_c_max = max(c)
    from dbo.myothertable
    go
     
    --CALL PROC #2
    declare @l_a_count as int  
      , @l_b_sum as int  
      , @l_c_max as int  
      , @l_a_count_o as int 
      , @l_b_sum_o as int 
      , @l_c_max_o as int
     
    exec dbo.aggs_output 
      @l_a_count  = @l_a_count OUTPUT
      , @l_b_sum  = @l_b_sum OUTPUT
      , @l_c_max  = @l_c_max OUTPUT
      , @l_a_count_o = @l_a_count_o OUTPUT
      , @l_b_sum_o = @l_b_sum_o OUTPUT
      , @l_c_max_o = @l_c_max_o OUTPUT
     
    select @l_a_count as a_count_o
      , @l_b_sum as b_count_o 
      , @l_c_max as c_count_o 
      , @l_a_count_o as a_count_o
      , @l_b_sum_o as b_count_o 
      , @l_c_max_o as c_count_o 
     
    --CLEAN UP
    drop table dbo.mytable
    drop table  dbo.myothertable
    drop proc dbo.aggs_single_select
    drop proc dbo.aggs_output
    Obviously the first is the most easy to use.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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