Rabu, 11 April 2012

RE: [MS_AccessPros] Re: Concurrent users causing MASSIVE latency issues

 

Lee-

When one user is in the database, getting a pointer to CurrentDb of the back end
and opening a "thread" (connection) is pretty quick. When someone else already
has a connection open, Access has to jump through hoops to ensure it's getting a
"clean" copy of CurrentDb that hasn't been modified during the process by that
other user. It takes seconds instead of milliseconds. If you already have a
recordset open, Access will re-use the thread that already exists, and that
takes milliseconds.

In your case, if you're running a series of queries and / or opening-closing
recordsets to open this "report," each query execution of open of a recordset
takes measurable seconds – sometimes a minute each! Creating a permanent
"thread" to the back end avoids all of that.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

---------------------------

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lee
Sent: Wednesday, April 11, 2012 10:40 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Concurrent users causing MASSIVE latency issues

 
Hi,
The report is not a report on the report tab, it's actually a series of queries,
code, calculations, etc. that runs through an absurdly complex set of business
rules.

One reason it's so slow to start with is because the back end (despite my
protests) is buried about 10 levels deep in the folder structure. Add to that
the level of complexity involved in the report (it took me 3 straight months of
40 hour weeks to get it working correctly) and the amount of data it has to go
through, and it takes a while to run.

I just pushed the recordset solution recently so we'll see what kind of effect
that has. I just don't understand why ONE extra person in the database would
slow things down so much. =/

Thanks
-Lee

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Lee-
>
> Opening a recordset and leaving it open on startup should make a world of
> difference. But you also need to take a look at the report. Even 15 minutes
> seems excessive. What recordsets are being used by the report and why are they
> so slow? Do you have appropriate indexes defined?
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----------------------------------
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lee
> Sent: Wednesday, April 11, 2012 5:58 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Concurrent users causing MASSIVE latency issues
>
>  
> Hi,
> I have a front end/back end database with about 5 active users, usually no
more
> than 2 are in the file at one time. for single row transactions there are no
> issues, however, for some of the larger reports/queries, if ONE extra person
is
> in the file, it causes HUGE latency issues.
>
> One report that normally takes about 10 minutes to run was abandoned after 90
> minutes. I found 1 extra user in the file, kicked them out, and re-ran the
> report and it took 15 minutes (which is the slow side of normal).
>
> Is there a setting somewhere I can check? This is causing some big problems
and
> it's causing my other projects to get scrutinized, ONE user should NOT be
> causing this much delay, but every time the file is running slow if I kick
> everyone out and change nothing else, the performance is back to normal. (I
now
> have a routine that permanently opens a recordset for each user since someone
> advised that, but I don't have enough feedback to know what difference, if
any,
> it's making)
>
> Thanks
> -Lee
>

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar