Complex Queries on Terminal Services

#1

I have a client with Windows 2003 Server running Terminal Services through
FortiClient (VPN).
My application is Access 2002 with split front / back end.
Front ends are loaded separately in each user's workspace on the same drive
as the backend.
Recently we have experienced unacceptable and intermittent delays when
running two queries in the program. The queries haven't been changed, but
both include sum() functions.
Using the same set of data the queries can run from as fast as 1 second up
to 62 seconds during a 10 minute testing period. There is no pattern to the
differences. If they were always slow, I could accept the need to look at
the design of the queries, but that isn't the case.
The underlying tables contain around 40,000 records, but the queries filter
back to less than 30.
I'm monitoring the times by writing lines to a text file at various check
points in my code.

The behaviour is the same regardless of how many users are logged on at the
time.
The queries run fine on my test environment using a basic cat 5 LAN cable
with the front and back ends on different PC's!
I also have the program running at another client site using LAN without the
TS and they have no problems at all.
The Jet files are the latest updates.
There seems to be a problem on either the client's server or TS setup.
Has anyone else experienced similar problems?
Any ideas will be welcome.

--
Bob Darlington
Brisbane

#2

"Bob Darlington" <bob (AT) notheredpcman (DOT) com.au> wrote

Quote:

I have a client with Windows 2003 Server running Terminal Services through
FortiClient (VPN).
My application is Access 2002 with split front / back end.
Front ends are loaded separately in each user's workspace on the same
drive as the backend.
Recently we have experienced unacceptable and intermittent delays when
running two queries in the program. The queries haven't been changed, but
both include sum() functions.
Using the same set of data the queries can run from as fast as 1 second up
to 62 seconds during a 10 minute testing period. There is no pattern to
the differences. If they were always slow, I could accept the need to look
at the design of the queries, but that isn't the case.
The underlying tables contain around 40,000 records, but the queries
filter back to less than 30.
I'm monitoring the times by writing lines to a text file at various check
points in my code.

The behaviour is the same regardless of how many users are logged on at
the time.
The queries run fine on my test environment using a basic cat 5 LAN cable
with the front and back ends on different PC's!
I also have the program running at another client site using LAN without
the TS and they have no problems at all.
The Jet files are the latest updates.
There seems to be a problem on either the client's server or TS setup.
Has anyone else experienced similar problems?
Any ideas will be welcome.

--
Bob Darlington
Brisbane

Further to my previous post, all users are using TS connections to access
the program.
And this morning I noticed a 'Not Responding' message in the application
title bar. It goes away when the process eventually completes.
--
Bob Darlington
Brisbane

#3

"Bob Darlington" <bob (AT) notheredpcman (DOT) com.au> wrote in
news:4dd6f698$0$15070$afc38c87 (AT) news (DOT) optusnet.com.au:

Quote:

Further to my previous post, all users are using TS connections to
access the program.
And this morning I noticed a 'Not Responding' message in the
application title bar. It goes away when the process eventually
completes.

Sounds like a memory/caching issue.

I assume the fields being selected on are appropriately indexed?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#4

"David-W-Fenton" <NoEmail (AT) SeeSignature (DOT) invalid> wrote

Quote:

"Bob Darlington" <bob (AT) notheredpcman (DOT) com.au> wrote in
news:4dd6f698$0$15070$afc38c87 (AT) news (DOT) optusnet.com.au:

Further to my previous post, all users are using TS connections to
access the program.
And this morning I noticed a 'Not Responding' message in the
application title bar. It goes away when the process eventually
completes.

Sounds like a memory/caching issue.

I assume the fields being selected on are appropriately indexed?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Thanks David.
I don't know if a table can be 'over indexed' but if so, I've done it.
And I've even tested with most of the indices removed.
Could you give me a pointer to where to look for a solution regarding the
'memory/caching' issue?

--
Bob Darlington
Brisbane

#5

On Fri, 20 May 2011 17:55:35 +1000, "Bob Darlington"
<bob (AT) notheredpcman (DOT) com.au> wrote:

Quote:

Recently we have experienced unacceptable and intermittent delays when
running two queries in the program. The queries haven't been changed, but
both include sum() functions.
Using the same set of data the queries can run from as fast as 1 second up
to 62 seconds during a 10 minute testing period. There is no pattern to the
differences. If they were always slow, I could accept the need to look at
the design of the queries, but that isn't the case.

What I have noticed many years ago was a difference in timing in
running reports. The variable was closing and opening Access and
running the report again in which case the first time you opened it
took 40 seconds then the second and subsequent times it took 20
seconds. Or something like that.

Or maybe the variable was opening and closing the report. Of course I
was designing this complex report so I really noticed the difference
in times it took to open the report.

Just thinking out loud here if this behavior is somehow relevant to
your case.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

#6

"Tony Toews" <ttoews (AT) telusplanet (DOT) net> wrote

Quote:

On Fri, 20 May 2011 17:55:35 +1000, "Bob Darlington"
bob (AT) notheredpcman (DOT) com.au> wrote:

Recently we have experienced unacceptable and intermittent delays when
running two queries in the program. The queries haven't been changed, but
both include sum() functions.
Using the same set of data the queries can run from as fast as 1 second up
to 62 seconds during a 10 minute testing period. There is no pattern to
the
differences. If they were always slow, I could accept the need to look at
the design of the queries, but that isn't the case.

What I have noticed many years ago was a difference in timing in
running reports. The variable was closing and opening Access and
running the report again in which case the first time you opened it
took 40 seconds then the second and subsequent times it took 20
seconds. Or something like that.

Or maybe the variable was opening and closing the report. Of course I
was designing this complex report so I really noticed the difference
in times it took to open the report.

Just thinking out loud here if this behavior is somehow relevant to
your case.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

Thanks Tony.
I've noticed the same thing when opening forms. But in this case the 'hang'
is occurring when a recordset is created.
It might be on the first call or not until a 4th or 5th call that it stalls.
On other occasions it runs fine.

--
Bob Darlington
Brisbane

#7

Further to my original post, the problem is occurring when opening a
recordset using the query rather than 'running the query' as I indicated.

--
Bob Darlington
Brisbane
"Bob Darlington" <bob (AT) notheredpcman (DOT) com.au> wrote

Quote:

I have a client with Windows 2003 Server running Terminal Services through
FortiClient (VPN).
My application is Access 2002 with split front / back end.
Front ends are loaded separately in each user's workspace on the same
drive as the backend.
Recently we have experienced unacceptable and intermittent delays when
running two queries in the program. The queries haven't been changed, but
both include sum() functions.
Using the same set of data the queries can run from as fast as 1 second up
to 62 seconds during a 10 minute testing period. There is no pattern to
the differences. If they were always slow, I could accept the need to look
at the design of the queries, but that isn't the case.
The underlying tables contain around 40,000 records, but the queries
filter back to less than 30.
I'm monitoring the times by writing lines to a text file at various check
points in my code.

The behaviour is the same regardless of how many users are logged on at
the time.
The queries run fine on my test environment using a basic cat 5 LAN cable
with the front and back ends on different PC's!
I also have the program running at another client site using LAN without
the TS and they have no problems at all.
The Jet files are the latest updates.
There seems to be a problem on either the client's server or TS setup.
Has anyone else experienced similar problems?
Any ideas will be welcome.

--
Bob Darlington
Brisbane

#8

I've seen incredibly slow results on large recordsets using aggregate
functions.

Functions running against a large number of records will be slow.

Try breaking your query into several queries. First get the data, then run
your sum() function. The difference can be astounding. I remember 1
developer building a complex query that took 5 minutes to open a report.
After I broke the query into 2 pieces, the time reduced to 4 seconds. In the
first case she ran an AVG() against 158,000 records. After breaking it into
2 queries, she was running against 30 records.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://access.mvps.org
Co-author: "Access Solutions", published by Wiley

"Bob Darlington" <bob (AT) notheredpcman (DOT) com.au> wrote

Quote:

I have a client with Windows 2003 Server running Terminal Services through
FortiClient (VPN).
My application is Access 2002 with split front / back end.
Front ends are loaded separately in each user's workspace on the same
drive as the backend.
Recently we have experienced unacceptable and intermittent delays when
running two queries in the program. The queries haven't been changed, but
both include sum() functions.
Using the same set of data the queries can run from as fast as 1 second up
to 62 seconds during a 10 minute testing period. There is no pattern to
the differences. If they were always slow, I could accept the need to look
at the design of the queries, but that isn't the case.
The underlying tables contain around 40,000 records, but the queries
filter back to less than 30.
I'm monitoring the times by writing lines to a text file at various check
points in my code.

The behaviour is the same regardless of how many users are logged on at
the time.
The queries run fine on my test environment using a basic cat 5 LAN cable
with the front and back ends on different PC's!
I also have the program running at another client site using LAN without
the TS and they have no problems at all.
The Jet files are the latest updates.
There seems to be a problem on either the client's server or TS setup.
Has anyone else experienced similar problems?
Any ideas will be welcome.

--
Bob Darlington
Brisbane

#9

Thanks Arvin,
I'll give it a run.

--
Bob Darlington
Brisbane
"Arvin Meyer" <arvinm (AT) invalid (DOT) org> wrote

Quote:

I've seen incredibly slow results on large recordsets using aggregate
functions.

Functions running against a large number of records will be slow.

Try breaking your query into several queries. First get the data, then run
your sum() function. The difference can be astounding. I remember 1
developer building a complex query that took 5 minutes to open a report.
After I broke the query into 2 pieces, the time reduced to 4 seconds. In
the first case she ran an AVG() against 158,000 records. After breaking it
into 2 queries, she was running against 30 records.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://access.mvps.org
Co-author: "Access Solutions", published by Wiley

"Bob Darlington" <bob (AT) notheredpcman (DOT) com.au> wrote in message
news:4dd61e7a$0$13392$afc38c87 (AT) news (DOT) optusnet.com.au...
I have a client with Windows 2003 Server running Terminal Services through
FortiClient (VPN).
My application is Access 2002 with split front / back end.
Front ends are loaded separately in each user's workspace on the same
drive as the backend.
Recently we have experienced unacceptable and intermittent delays when
running two queries in the program. The queries haven't been changed, but
both include sum() functions.
Using the same set of data the queries can run from as fast as 1 second
up to 62 seconds during a 10 minute testing period. There is no pattern
to the differences. If they were always slow, I could accept the need to
look at the design of the queries, but that isn't the case.
The underlying tables contain around 40,000 records, but the queries
filter back to less than 30.
I'm monitoring the times by writing lines to a text file at various check
points in my code.

The behaviour is the same regardless of how many users are logged on at
the time.
The queries run fine on my test environment using a basic cat 5 LAN cable
with the front and back ends on different PC's!
I also have the program running at another client site using LAN without
the TS and they have no problems at all.
The Jet files are the latest updates.
There seems to be a problem on either the client's server or TS setup.
Has anyone else experienced similar problems?
Any ideas will be welcome.

--
Bob Darlington
Brisbane

#10

"David-W-Fenton" <NoEmail (AT) SeeSignature (DOT) invalid> wrote

Quote:

"Bob Darlington" <bob (AT) notheredpcman (DOT) com.au> wrote in
news:4dd6f698$0$15070$afc38c87 (AT) news (DOT) optusnet.com.au:

Further to my previous post, all users are using TS connections to
access the program.
And this morning I noticed a 'Not Responding' message in the
application title bar. It goes away when the process eventually
completes.

Sounds like a memory/caching issue.

I assume the fields being selected on are appropriately indexed?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

David,
When you refer to 'memory / caching' are you talking about something which
can be controlled through Access or something in the operating system or
server software?
--
Bob Darlington
Brisbane