SQL Server Speed Slow

[Bill Sturdevant]

I have converted an Access 2003 BE to SQL Server and am keeping my FE as an
MDB.

I have a form with several sub forms that use various filters and
master/child links to determine which data to display. In one particular
case, this scenario returns 52 records comprising about 25000 characters of
data total. We really are not talking very large here.

When I run this against a copy of SQL Server desktop on my own machine, the
retrieval and associated adjustment of display (conditional formatting, etc.)
takes less than 1 second.

When I run this against a copy of SQL Server desktop on a WIN 2000 laptop
connected to my small wireless network, and sitting right next to me, it
takes about 5 seconds.

When I run this against a copy of SQL Server Enterprise located inside a
company intranet on a dedicated WIN 2003 server to which I gain access via a
VPN, and my connection is through the internet using a cable modem with 5mb
download speed, the retrieval takes 51 seconds. There is currently nothing
else running on the server. I have checked the speed of my connection
immediately before and immediately after the retireval, and my speed is up in
the 5mb download range at both times.

All versions of SQL server have default settings (no tuning)

I can understand a slight slowness, but cannot understand a 10-fold slowdown
against the dedicated server.

What can I do? I have started to rebuild my app so it retireves only 1
record at a time on the main form. But when I found that the retrieval time
was still up in the 50 second range for the retrieval, I stopped because it
seems there might be something else I am missing.

 

[Albert D.Kallal]

Well, you speeds are much slower Read the following of mine:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

What this means is that you have optimize your setup to reduce the data
transferred....

 

[Arvin Meyer [MVP]]

Running against a remote server across a VPN will always be slow unless you
use a web front-end or Terminal Services (either Windows or Citrix). Even if
you could get Access to reduce the traffice by using a Pass-Through query,
it would still be slower than either of the other 2. I prefer the Terminal
Server because you can still use your Access front-end and it's far cheaper
in the long-run.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Bill Sturdevant]

OK. Where can I get some insite into this:

Do I have to set up a userID for each user on the server?

I assume I have to either have MS Access installed on the server, or I need
to include the Access runtime in my app, but does each user have his own copy
of the app or is there need for only one copy?

What about memory requirements?

What requirements are there on the user machines. A knee-jerk reaction is
that this would make my Access 2003 app executable from any machine, from Win
98 to Win2003. True?

 

[Rick Brandt]

OK. Where can I get some insite into this:

Do I have to set up a userID for each user on the server?
Yep

I assume I have to either have MS Access installed on the server, or
I need to include the Access runtime in my app, but does each user
have his own copy of the app or is there need for only one copy?

Each user of a Terminal Server has to be licensed just to connect to the server
and must also hold licenses for all software run on the server. If your Access
app is installed on the server with the runtime then that would cover your users
as far as Access licensing is concerned. They would still need server connect
licenses and licenses to any other software that your app might utilize (Outlook
to send messages for example).

What about memory requirements?

Microsoft has white papers that discuss hardware recommendations per expected
user.

What requirements are there on the user machines. A knee-jerk
reaction is that this would make my Access 2003 app executable from
any machine, from Win 98 to Win2003. True?

The user simply needs a Terminal Server client program which can run on most any
Windows box.

 

[Arvin Meyer [MVP]]

Rick has answered most of your questions, let me add:

Each user should have his/her own folder with a copy of the front-end of the
database in it as well as any other programs.

I set up a server with a dual Xeon processor and 4 GB of memory, including
all licenses for just over $8,000. I can handle as many as 31 users,
although to date I've only had about half that many concurrently logged on.
The database backend is on another server and has another 35 users connected
to it on the LAN. There is no discernable difference between the users on
the LAN and those on the Terminal Server, except when there's only a few
users. If the Terminal Server doesn't have to meter out its services, the
advantage of having more than a GB to run an app will often outperform a
local machine.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access