Query speed across network. Eek...Access97!!

[Paul_in_NZ]

I posted this is Small Business Server forum too, as I'm not sure where it
should belong. Please read on....

I have a client running a legacy inhouse app developed by another party.
Developed using VB, it runs on Access97. This app is split into
client(frontend) and server(backend).

Up until recently they were running everything on a shoestring WindowsXP
peer2peer. As the machine handling "server" duties was suffering increasing
BSOD due to hardware issues, it was decided a "proper server" be put in place.

Citing performance, security and email prospects I sold them SBS 2003 R2
Premium. After an initial glitch with a RAID controller, the system has
settled down to be rock solid. Except for one major issue: network speed when
running a query on the backend of the legacy app they have.

On the p2p network, the query would provide a response within 3-5 seconds.
On the SBS 2003 server network, delivering from a network takes anything from
5 seconds to 2 minutes.

This is where I need some guidance.

I need to get this app stable and operating at 'normal' speed. Only then can
I then focus on getting a migration to a SQL based backend running.

Help...!

 

[Tom Wickerath MDB]

Access MDB should have be used

you need to move to SQL Server

Access MDB has been obsolete for a decade

 

[Paul_in_NZ]

Access MDB should have be used
???

you need to move to SQL Server

Read my last paragraphs. It is a work in progress. I'm having to filter
through a lot of debris in the BE.

 

[Todos Menos [MSFT]]

eat a mother fucking dick cock sucker

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

Seehttp://www.QBuilt.comfor all your database needs.
Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
Blogs:www.DataDevilDog.BlogSpot.com,www.D....QBuilt.com/html/expert_contributors2.htmlfor contact
info.








- Show quoted text -

 

[Todos Menos [MSFT]]

im not attempting to impersonate anyone

I am using an alias-- a nickname

 

[Albert D. Kallal]

While the following list is for a2000, there is some suggestions for a97.

a few things:

First, upgrading to sql sever often does not speed these applications up.
(in fact, sometimes they slow down). Simple knee jerk solutions like
replacing the server, or adding sql server are not going to fix these
problems (and, as you seen, your actually experiencing a slow down right
now).

You don't mention if the application is split or not (I hoping it is
split -- however, lets leave this issue out for the time being, but it is a
important one, and unfortunately you failed to mention this detail (the fact
that you NOT mentioned this detail speaks volumes about the skill level and
support your exercising right now - not good!!).


First, with the real server, issues such as file permissions, security, and
a host of other issues now come into play

However, here a few things:

keep the back end "low" in the file hierarchy (if you go deep in terms of
folders, things can slow down). So, keep the back end mdb LOW in the folder
system. (one deep at most).

Since access 97 is very old, then stick to 8.3 file naming conventions.
(and, keep folders 8 or less characters in length, and don't use spaces in
the file names. (there is some "gear" switching that the network has to do
when you don't use legacy file naming here).

The next thing is to try a persistent connection. (this will requite
developer knowledge of ms-access). To "test" this connection idea, you can
simply open up a table in the application (on a workstation), and then
minimize this table. now, while in the appcation, launch that form ..it
should run fast. The "persistent" connection concept is simply something
that forces (keeps) open the file at all times, as there is a RATHER LARGE
overhead WHEN a file is being opened and closed repeat and triggers all that
extra processes and security stuff that the new box has.

And, while were at this, ensure that virus protection software is not
interfering (on both client and server sides). I would consider disable all
virus stuff during testing...

 

[Paul_in_NZ]

Albert, I appreciate your wisdom. It is your advice, amongst others that I am
seeking.

I did state at the outset that this is a split database situation
client/server or fe/be. There has not been any migration to SQL as yet.

All that has happened is that the file providing the BE has been copied from
a WindowsXP PC, named "Server", to a SBS2003 box called "Thoroughbred"(*).
The file is stored in a folder c:\ABCDEF and shared as such. There is a
requirement in the app that this be mapped as drive k:\ on the workstations
(not a biggie as there are only 10, all in the same office. This can also be
done through login scripts)

All that has changed is the servername. I'm not sure if "Thoroughbred" is
too long. I didn't think it'd be a problem when we're dealing with a 'mapped
drive'.

In the meantime I have moved the BE to a workstation on the network to allow
the users to enjoy marginally better performance. This also gives me some
room to try apply the "persistent" connection table you suggest.

Also, new data can be entered into the system without hindrance. It is only
when performing a query does performance get hammered.


Thanks
Paul

 

[Tony Toews [MVP]]

im not attempting to impersonate anyone

I am using an alias-- a nickname

Rubbish.

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Except for one major issue: network speed when
running a query on the backend of the legacy app they have.

On the p2p network, the query would provide a response within 3-5 seconds.
On the SBS 2003 server network, delivering from a network takes anything from
5 seconds to 2 minutes.

Ah, SBS? SMB signing.

See the Disable Server Message Block (SMB) Signing section at
http://www.granite.ab.ca/access/performancefaq.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I have a client running a legacy inhouse app developed by another
party. Developed using VB, it runs on Access97.

What you mean is that it uses a Jet 3.5 back end.

Access is not involved at all, except insofar as perhaps the
original MDB was created in Access.

 

[David W. Fenton]

You don't mention if the application is split or not

He *did* mention it. Access is not involved -- it's a VB app with a
Jet 3.5 back end.

 

[David W. Fenton]

I need to get this app stable and operating at 'normal' speed.
Only then can I then focus on getting a migration to a SQL based
backend running.

Go here:

http://www.granite.ab.ca/access/performancefaq.htm

and look for the things that are server-based and network issues.

Also, you might want to check if there's AV software involved.

 

[David W. Fenton]

Read my last paragraphs. It is a work in progress. I'm having to
filter through a lot of debris in the BE.



mmm....

You are responding to a troll, Aaron Kempf, who is amusing himself
today by impersonating respected Access MVPs. Aaron's Leitmotif is
"switch to ADP," whether that advice makes any sense at all. Beyond
that, Aaron also specializes in providing completely wrong advice on
any number of subjects about which he knows nothing (this would seem
to be all things Access- and database-related).

 

[Todos Menos [MSFT]]

OH RUBBISH

shut the **** up pansy boy

im not
impersonating anyone

I'm using various nicknames until I find one I like

unfortunately; all of these names remind me what MDB crack-baby
pussies you guys are

anyone using access should be fired and then spit upon

 

[Todos Menos [MSFT]]

you niggers should stop blaming everything else and find a real
database

He wouldn't be having any problems if he was using the SQL Server that
comes included with his SBS


Access MDB is worthless and anyone using it for anyhting shoudl be
fired and then spit upon

 

[Todos Menos [MSFT]]

you're right kid

my mantra is 'move to adp'

BECAUSE IT IS SUPERIOR TECHNOLOGY WITH A SUPERIOR SERVER DATABASE

stick a fork in it you fucking ****

 

[Todos Menos [MSFT]]

'whether it makes any sense at all'?

what the **** are you talking about dipshit

of course it makes sense


sorry that you cocksucker niggers can't argue a fair match

**** you piece of shit and **** your stupid little crack-baby database

SQL Server made you all obsolete a decade ago; and now mySql has
stolen the thunder from SQL Server

either way-- not using a server-based database is just fucking
laughable

ROFL

you dipshits still keep your DATA in Excel?


move on kids

nothing to see here but a bunch of MDB crack-babies


eat a fucking dick faggot

 

[Tom Wickerath MDB]

you don't need to filter through a lot

you just need to learn how to use a where clause, kid


what is SQL too hard for you ?

WAAAHHHHH!!!

cry me a river, bitch

there's some reddot raghead ready to take your job for $10/hour and he
speaks SQL Server

 

[Tom Wickerath MDB]

moving to SQL Server _WILL_ fix your problem
especially if you want to use MS Access over wireless, wan or VPN

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.