question

Runner-2178 avatar image
0 Votes"
Runner-2178 asked thedbguy commented

MIcrosoft Access SQl Speed problem

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;


office-access-dev
image-1.png (56.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertKallal-4360 avatar image
0 Votes"
AlbertKallal-4360 answered thedbguy commented

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello you can see the answer from an Oracle expert here
you can find my databsae and download it , i am trying find way for Access

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


0 Votes 0 ·

Ok, not looked much here. But as noted, the ONE thing missing in Access SQL is a efficient means of having running totals.

Hence, you had to resort to using dsum(), and as I stated, they HAVE to go and be removed if you want perforamnce here.

I had uggested a sub-query, but even that will not help in this case.

I think the only practial way? output the table based on the 2nd query, but with the dsums() removed. You then have to run a VBA process on that data. This should be able to get you performance, and the whole process down to say sub 1 second time to execute.

So, just looking at this? Hum, yes, output the data, and then process the rows with VBA. This would be some work and code on your part. However, with such extra code and work, then as noted, the whole process should allow sub 1 second time for this process, it just a question of how much work and code you want to put into this. Another possible? Use Access + free edition of SQL server on your computer, and that would also provide a solution. So, it either you roll up your sleeves and write VBA code to process + create the running totals, or you consider using free SQL server.

1 Vote 1 ·
thedbguy avatar image thedbguy AlbertKallal-4360 ·

Hi Albert,

In case it can provide additional details, you can review the MVP group thread I started about a month ago (Aug 13) regarding this particular topic. I even attached a sample DB from the OP for testing. A few of us tried different methods, including VBA, and discussed the results of those trials. Cheers!

1 Vote 1 ·
thedbguy avatar image
0 Votes"
thedbguy answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Runner-2178 avatar image
0 Votes"
Runner-2178 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Runner-2178 avatar image
0 Votes"
Runner-2178 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.