Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2006
    Location
    Whittier, California, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Speed up DSum (2003)

    Hello All,

    I've created a simple form to allow my boss to set beginning & end dates and retrieve some stats. It works but is very slow. Doing a search on this site I found posts stating that DSum is very slow with large tables, so I'm pretty sure that I need to replace these but I don't know the best replacement. Should I create separate queries to replace each DSum and then join them? Create temporary tables based on the dates first and then query these? Any help would be appreciated. My current sql statement is below.

    SELECT tlstDocs.Doc, FormatCurrency(DSum("Charge","qryClinStatsCh","Dat eEnt Between #" & Forms!fpriClinStats!txtDateBeg & " # And # " & Forms!fpriClinStats!txtDateEnd & "# And Dr='" & [Doc] & "'"),0) AS Charges, FormatCurrency(Nz(DSum("Pay","qryClinStatsCh","Dat eEnt Between #" & Forms!fpriClinStats!txtDateBeg & " # And # " & Forms!fpriClinStats!txtDateEnd & "# And Dr='" & [Doc] & "'"),0)+DSum("Pay","qryClinStatsPay","DateEnt Between #" & Forms!fpriClinStats!txtDateBeg & " # And # " & Forms!fpriClinStats!txtDateEnd & "# And Dr='" & [Doc] & "'"),0) AS Payments, DCount("CaseId","qryClinStatsVisits","DateEnt Between #" & Forms!fpriClinStats!txtDateBeg & " # And # " & Forms!fpriClinStats!txtDateEnd & "# And TypeId=1 And Dr='" & [Doc] & "'") AS Np, FormatNumber(DCount("CaseId","qryClinStatsVisits", "DateEnt Between #" & Forms!fpriClinStats!txtDateBeg & " # And # " & Forms!fpriClinStats!txtDateEnd & "# And TypeId=2 And Dr='" & [Doc] & "'"),0,False,False,True) AS Visits, tlstDocs.Prof
    FROM tlstDocs
    WHERE (((DSum("Charge","qryClinStatsCh","DateEnt Between #" & [Forms]![fpriClinStats]![txtDateBeg] & " # And # " & [Forms]![fpriClinStats]![txtDateEnd] & "# And Dr='" & [Doc] & "'"))>0))
    ORDER BY CInt(DSum("Charge","qryClinStatsCh","DateEnt Between #" & Forms!fpriClinStats!txtDateBeg & " # And # " & Forms!fpriClinStats!txtDateEnd & "# And Dr='" & [Doc] & "'")) DESC;

    Thanks,

    Kevin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Speed up DSum (2003)

    I'd create one or more queries to calculate the totals, and use these in your select query.
    If that is still too slow, you could create temporary tables, but I'd only do that as a last resort.

Posting Permissions

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