too slow on getting calc done (1 Viewer)

[TipsyWolf]

hey guys,
i almost finished my db, extracting all attachments out of db, making it lose from 200Mb down to 21Mb and it thought its gonna be much smother, but i was wrong.
i dont remember, but someone here told me that im gonna face with similar problems.

so my db is 21Mb. network works fine. up to 100Mbit\sec.

but it takes almost 1 minute or a bit longer to get these calculations done. but why ? if i open db from network does it completely download a file, i think it does. so why it takes so long to get it calculated ? and it doesn't use net traffic much in a process of calc.. i have no idea why it takes so long.

in average every cell u see has calls on queries like this one.

=IIf(DCount("site";"QddallA";"site='site5' and status='in progress' and duedate<date() and isactive=false")+DCount("site";"QddallA";"site='site5' and status='new' and duedate<date() and isactive=false")=0;"";DCount("site";"QddallA";"site='site5' and status='in progress' and duedate<date() and isactive=false")+DCount("site";"QddallA";"site='site5' and status='new' and duedate<date() and isactive=false"))

and dbl click event

="RiskID In(SELECT RiskID FROM QddallA WHERE (status='in progress' and Site='site5' and duedate<date() and isactive=false) or (status='new' and Site='site5' and duedate<date() and isactive=false))"

so i have 9 types of calc and bunch of sites that i need to show statistic in this dashboard.

is there a way to structure this dashboard properly ? keeping this UI preferably

 

[TipsyWolf]

hm.. very weird. now it recalc within 1sec.
either i re-open or hit f5

upd:
after my 5-6th refresh (f5) in a row on my dashboard, it starts to re-calc very slow.
when i close db and reopen - it calcs all cells within 1 sec.

so inconsistent

 

[TipsyWolf]

so i would like to hear some advices how to properly structure such type of form \ dashboard so it shows data instantly especially if db is used in local network

 

[TipsyWolf]

no, its consistent now. just checked - it calc very slow on 4 PCs and it doesn't matter if i re-open

 

[ebs17]

IIf(DCount("site";"QddallA";"site='site5' and status='in progress' and duedate<date() and isactive=false")
   +DCount("site";"QddallA";"site='site5' and status='new' and duedate<date() and isactive=false")=0;
   "";
    DCount("site";"QddallA";"site='site5' and status='in progress' and duedate<date() and isactive=false")
   +DCount("site";"QddallA";"site='site5' and status='new' and duedate<date() and isactive=false"))

This is dramatically complicated and therefore fundamentally a performance problem.

1) Counting results in a number. Because you don't want the 0, you first create the "necessity" of the IIF expression. The empty string then introduces text as the data type for the contents.

2) In the worst case, you execute 4 DCounts per cell, which are 4 individual queries with their own individual access to the data source. In your case, this is a query, which in the worst case means that it is executed again each time, which in turn means a lot of work. Errors and problems there would also be multiplied.

3) Counting steps could be reduced by logically linking criteria:

status IN ('in progress', 'new')

General recommendation:
Instead of calculating individually for "cells" with DCount continuous fire, create ONE REAL COMPREHENSIVE query that delivers all results in one go. This is applied database technology and is much faster if done correctly.
The second step (display) can then also be simple and quick, just think of a bound form.

 

[TipsyWolf]

create ONE REAL COMPREHENSIVE query that delivers all results in one go

okay, will it be possible to create dbl click events on numbers using query ?
in my case, dbl click event opens a master list with those specific items. its really helpful and must have feature in my dashboard

 

[ebs17]

The controls in a form have at least the same events in a bound form as in an unbound form, so yes, a double click is usable (in form, not in query).

 

[TipsyWolf]

hm... is there a way to insert # of records from query into txtbox ?
i just created some simple query that goes along with calc of cell. is there a way to put a number of records of that query into that txtbox (cell) ?
and most importantly - will be it easier for access to process it instead of 2-4 Dcounts ?

 

[ebs17]

is there a way to insert # of records from query into txtbox ?

No. A TextBox wants a single value as its content. A query returns a recordset, which is an object with rows and columns, and even with only one column and one value, a recordset is still an object and not a single value.

 

[TipsyWolf]

i can now definitely tell u why sometimes its within 1 second and 1 minute.
i was testing back and forth and ...
it only depends if access opened on another PC or not

so. IF noone is in the db and im opening it as 1st user - it shows all numbers within 1 sec
IF at least one PC has already opened the db - the 2nd will be having too long calculations.

why do u think it is so ? i dont think PC or access can't handle to calc 100-200 Dcounts. it can - i just proved it. but it gets super slow if acccess opened already on another PC

it must be somehting with access itself or its structure

 

[gemma-the-husky]

Might the adverse performance be caused by having multiple concurrent users? In which case you might need to look at all users establishing a persistent connection to the backend database.

I think that's what you've described in your last post.

Each user needs to maintain a connection to the backend - personally I open a record set on a dummy table which is just there for that purpose - otherwise windows needs to negotiate your connection each time.

 

[Minty]

If you used a single query to gather most of your data, you can specify to use a snapshot recordset which would reduce any locking on the underlying data, which might assist.
Switching to 1Gb LAN will also make a surprising difference.

200 + DCounts() on the same data seems very unnecessary.

 

[The_Doc_Man]

it only depends if access opened on another PC or not

If the database file that contains the computations is being shared then you have not split your database. This is a formula for inviting database corruption, which makes the file unusable. Make good backup copies frequently if you are going to do that.

The part about bad performance when someone else has the file open is a consequence of locking, the mechanism by which Access (and, for that matter, Windows itself) prevents two people from touching the exact same thing at the exact same time. Lock collisions at the file level are a small annoyance because whole-file collisions can be resolved quickly by the Windows file system. However, Access uses a protocol that allows it to get to a piece of a file without locking the whole file. Normally, in a split database situation, it manages data updates by locking only the part it needs at the moment. But when you haven't split the database and two users are opening the same form at the same time, you have lock collisions on file parts, and the bad news is that Access must interact with the .LDB or .LACCDB file (lock file) before allowing the action. This interaction will take a lot of time since the ONLY way Access CAN resolve this is through the lock file being read and updated and then (when the requested micro-operation is complete) update the lock file again to reflect lock release so that whoever else is waiting can get there.

I don't understand the logic of your form. It SEEMS like you are asking someone to double-click into one of those boxes to get it to compute something. But somehow it involves a DCount. Every DCount you trigger is another implied SQL query "behind the scenes." You SHOULD be able to generate all of those counts in a limited number of simple aggregate queries and just fill in those blanks almost immediately. Since I don't read Russian, I have no idea what is going on. My comments are based on the questions and comments in this thread.

In words, how do you use that form and what happens when you double-click one of the controls you were asking about? What is the end goal of the form?

 

[Pat Hartman]

Given the language issue, I can't tell for sure but I would guess that each box could be a continuous subform bound to a single totals query rather than hundreds of separate queries.

 

[Isaac]

hey guys,
i almost finished my db, extracting all attachments out of db, making it lose from 200Mb down to 21Mb and it thought its gonna be much smother, but i was wrong.
i dont remember, but someone here told me that im gonna face with similar problems.

so my db is 21Mb. network works fine. up to 100Mbit\sec.

but it takes almost 1 minute or a bit longer to get these calculations done. but why ? if i open db from network does it completely download a file, i think it does. so why it takes so long to get it calculated ? and it doesn't use net traffic much in a process of calc.. i have no idea why it takes so long.

View attachment 114791

in average every cell u see has calls on queries like this one.

=IIf(DCount("site";"QddallA";"site='site5' and status='in progress' and duedate<date() and isactive=false")+DCount("site";"QddallA";"site='site5' and status='new' and duedate<date() and isactive=false")=0;"";DCount("site";"QddallA";"site='site5' and status='in progress' and duedate<date() and isactive=false")+DCount("site";"QddallA";"site='site5' and status='new' and duedate<date() and isactive=false"))

and dbl click event

="RiskID In(SELECT RiskID FROM QddallA WHERE (status='in progress' and Site='site5' and duedate<date() and isactive=false) or (status='new' and Site='site5' and duedate<date() and isactive=false))"

so i have 9 types of calc and bunch of sites that i need to show statistic in this dashboard.

is there a way to structure this dashboard properly ? keeping this UI preferably

doing tons and tons of dcounts is notoriously slow. what you need to try to do is convert some of that stuff into aggregate queries instead

 

[TipsyWolf]

In words, how do you use that form and what happens when you double-click one of the controls you were asking about? What is the end goal of the form?

please have a look at my screen recording i just made. its 7 min. sorry, its quite long

2024-06-29 10-58-20.mkv

drive.google.com

 

[TipsyWolf]

If you used a single query to gather most of your data, you can specify to use a snapshot recordset which would reduce any locking on the underlying data, which might assist.
Switching to 1Gb LAN will also make a surprising difference.

200 + DCounts() on the same data seems very unnecessary.

i wii try, thank you for advice

 

[TipsyWolf]

Given the language issue, I can't tell for sure but I would guess that each box could be a continuous subform bound to a single totals query rather than hundreds of separate queries.

is it possible to set up a dbl click event ?

 

[TipsyWolf]

convert some of that stuff into aggregate queries instead

how i would do this ? i tried queries before and i couldnt find a way to set up a dbl click event

 

[TipsyWolf]

then you have not split your database

no, i didn't. i tried, but faced with some problems after split access was saying x32(86) \ x64 capability, although all PC are on x64 systems
i'll try to investigate more and try again in coming business week