MIcrosoft Access SQl Speed problem

Runner 41 Reputation points
2021-09-07T18:23:51.407+00:00

i can't solve speed problem in MIcrosoft access
i want create table from xp2 but it is very slow.

xp table

130022-image-1.png

Queries

xp1 

SELECT xp.ID, xp.Date, Format$([xp.Date],"yyyymmdd") & Format([xp.ID],"00") AS ID2, xp.typeID, xp.Goods, xp.quantity, xp.Cost, xp.Cost/xp.quantity AS unitcost, IIf([typeID]="IN",[quantity],-1*[quantity]) AS rtquantity, xp.Company, xp.Cost/xp.quantity AS unitPrice, xp.[1PaymentDate], xp.[1PaymentAmount], xp.[2PaymentDate], xp.[2PaymentAmount], xp.[3PaymentDate], xp.[3PaymentAmount], IIf([typeID]="IN",-1*[Cost]+[1PaymentAmount]+[2PaymentAmount]+[3PaymentAmount],IIf([typeID]="OUT",[Cost]-[1PaymentAmount]-[2PaymentAmount]-[3PaymentAmount],0)) AS rtblanace, IIf([typeID]="IN",-1*[Cost],IIf([typeID]="OUT",[Cost],0)) AS rtpl  
FROM xp;

xp2 

SELECT xp1.ID, xp1.Date, xp1.ID2, xp1.typeID, xp1.Goods, xp1.quantity, xp1.Cost, xp1.unitcost, Val(DSum("[xp1].rtquantity","[xp1]","Goods = '" & [Goods] & "' " & "AND ID2 <= " & [ID2])) AS ∑quantity, xp1.Company, xp1.unitPrice, xp1.[1PaymentDate], xp1.[1PaymentAmount], xp1.[2PaymentDate], xp1.[2PaymentAmount], xp1.[3PaymentDate], xp1.[3PaymentAmount], Val(DSum("[xp1].rtblanace","[xp1]","Company = '" & [Company] & "' " & "AND ID2 <= " & [ID2])) AS ∑blanace, Val(DSum("[xp1].rtpl","[xp1]","Goods = '" & [Goods] & "' " & "AND ID2 <= " & [ID2])) AS ∑ProfitAndLossByGoods, Val(DSum("[xp1].rtpl","[xp1]","ID2<=" & [ID2])) AS ∑ProfitAndLoss  
FROM xp1;
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
817 questions
0 comments
Accepted answer
  1. Albert Kallal 4,646 Reputation points
    2021-09-12T03:57:05.9+00:00

    The problem here is the way you have written this. 50,000 rows export should take less then 1 second. In fact, you find Access is faster then most server based systems. However, you have a dsum() command. (how did you do that on other systems - they DO NOT have that command). Get rid of the dsum command, and this will run fast.

    So, the first query, it should run fast. But the 2nd one? No, it will run slow due to the dsum() command. You need to get rid that.

    However, if your first query is slow, then some detail is missing here. You also don't mention how long this takes (so, "slow" is a relative word in this context).

    However, just a quick glance, the first query should run ok speed wise. But the 2nd one? Nope, that's going to run slow.

    Also are the tables linked to some other data source (say a network, or some server database?).

    I mean, in general, you should get about 100,000 rows per second here easy. However, with a dsum(), then you are in slow turtle land.

    So, you might want to make a note of how long this takes, but more important give a heads up on how many rows you are dealing with.

    You could (should) try replacing the dsum() with a sub-query, and that may well fix the speed of the 2nd query.

    But it is perplexing that the first query is slow? Perhaps my quick "eyeball" is missing something. So, perhaps I would have to look "more' or "closer", but that first query should not be all that slow.

    Since it is slow as you note, then perhaps some additional detail is missing here (is a network involved, linked tables, etc?

    But, red flags for 2nd query? Yes, the dsum() has to go!

    Regards,
    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. DBG 2,301 Reputation points
    2021-09-09T00:56:01.257+00:00

    Hi. Unfortunately, that's just the amount of time it will take Access to perform all those calculations and save the results into a table. Someone had better results regarding speed when using an APPEND query instead of a Make-Table one. Also, the answer I got when I asked the experts why the Make-Table was slow as compared to a SELECT query, it was explained to me that Access only process just enough information to display on one screen. So, since a Make-Table query has to process all records, it takes a lot longer.

    0 comments
  2. Runner 41 Reputation points
    2021-09-09T05:05:27.303+00:00

    Oracle Export does it in one second in Oracle Apex with 50,000 transactions on a simple laptop, I don't understand why Access can't do this.

    0 comments
  3. DBG 2,301 Reputation points
    2021-09-10T18:44:42.04+00:00

    What can I say? Access is not Oracle. Heck, it's not even the same as SQL Server or MySQL. So...

    0 comments
  4. Runner 41 Reputation points
    2021-09-12T04:53:50.79+00:00

    I am a newbie and dont know of other ways that Dsum is the limit of my knowledge, I cannot attach the database, Oracle Expert answered with a real example and he solved it in Oracle.

    https://community.oracle.com/tech/developers/discussion/4488607/speed-problem#latest

    My database has one table and only two queries

    thanks

    0 comments