The need for speed

[WhizzkidWallace]

Hi,

I have a growing Access database in a multi-user environment over a 10/100Mb network. The database is all in one file at the moment, on a shared directory of our XP-Pro 'Server', and the workstations have a mapped drive to it and are W98SE machines. All the machines are 1.2Ghz Fujitsu Siemens machines.

It is still under development, but is also in constant use, and I therefore have to develop on a copy, then get everyone out so I can copy in the changes. I would love it to be a client/server setup and split the db to Tables only backend on the server and progams on the client, but when I tried, the result was a dramatic slow-down in the system...it became unusable.

I do have a budget for this, and could get a proper 'server' or maybe an Ethernet Disk, but what is the best config for speed and admin purposes. Anyone doing something similar??

Thanks

 

[Birdy]

We use a mix of Win98 & XP builds here and we don't seem to have that much of a problem with our client \ Server databases. What memory is in the W98 pc's?

It might be worth while considering a SQL Server box if the data in the database is going to grow to a large amount.

 

[WhizzkidWallace]

They are all 256Mb. When I have local client progs and remote server tables, even the Switchboard takes ages to load when you select an item. Maybe that is down to the Switchboard Items table being on the server? I don't know how to configure for the best.

 

[Birdy]

I would suggest having JUST the DATA TABLES on the Server. That way, any processing will be done through the client and not going through links to the server.

Give it a try and let us know.

 

[ghudson]

Windows 98/98SE does not benefit from more than 128 MEGs of RAM. RAM is very important when working with Access databases.

Once your users are upgraded to Windows XP and have at least 512 MEGs of RAM you will see a huge increase in speed with your applications.

I know that does not help you now but I just wanted to point that out for I remember the old days when we had Windows 98 and Access 97.

Your db should be split. The front end should be on the users hard drive. All front ends should be linked to the one back end. The back end should only contain the shared data tables. Your forms should only be displaying [pulling] the data the users need to see. Use SQL as the record source of your forms and set criteria to only pull the records the users need to see.

There will be a slight hit on speed when the db is split but the safety of the data will be better and the chance of data and/of db corruption will be lower when the db is split.

HTH

 

[John.Woody]

The problem may be sharing violations on the xp pro server see my post
http://www.access-programmers.co.uk/forums/showthread.php?t=85461
I have created a registry update file for this here
Its made a big difference to numerous clients.

 

[WhizzkidWallace]

Thank you all for your comments.

We had actually downgraded our machines to W98 due to a dos based legacy system, which is what has been replaced by the new Access db, so I guess now would be a good time to get XP back on the workstations, and bump the memory up to 512Mb. I can then do the split.

What about network speed. Is 100Mbit fast enough? and what about the server. Would I see a speed increase by getting a faster machine with more memory (currently a 1.2Ghz machine with 128Mb RAM running XP Pro), and should I really be running Windows Server, not XP?

 

[Bat17]

My PC is 128 MB @ 400Mhz ! I would not recomend that

Peter

 

[WhizzkidWallace]

Actually I mis-stated the specs. The machines are 2.6Ghz Celeron with 256Mb of RAM. I think my best plan is to re-install XP on one of the machines, and try the various configs of client/server location etc to see if something comes up fast enough.

Did anyone have any thoughts about the Windows Server 2003 question?

Thanks

 

[ghudson]

That is better since your applications need more that 128 megs of RAM when running Windows XP. You will see and feel a difference if you simply up the RAM to 512 megs with Windows XP and Access.

 

[KenHigg]

I'd push for a SQL Server backend if you can swing it.

 

[WhizzkidWallace]

Thanks Ghudson. I will be trying it tomorrow with an XP machine with 512MB RAM and will post the results. As a bench, when I ran a particularly complex query I timed the following:

1. With file ALL IN ONE, and stored on the server....24 seconds.
2. With file SPLIT Client Local and Backend on server.....gave up after 3 mins.

Lets see how it goes. I may need to look further into creating a 'proper' server rather than just an XP shared drive on a peer to peer, but before I spend the dosh it would be nice to know I will get a result.

 

[jrjr]

Just for comparison, try it split with both the FE and BE on the server.

 

[KenHigg]

How complex is the table / relationship schema?

 

[WhizzkidWallace]

Just for comparison, try it split with both the FE and BE on the server.

Thanks JrJr...already tried that...was slower as I recall.

 

[WhizzkidWallace]

How complex is the table / relationship schema?

Its not that complex...there are approx 80 tables, very few queries (all done on the fly with SQL in forms and reports), but the relationships are quite complex, and the particular bit of SQL that takes so long involves a lot of opening and closing of recordsets.

 

[WhizzkidWallace]

I'd push for a SQL Server backend if you can swing it.

Could I use my access front end with a SQL backend, and is it a lot faster than the Jet4.0 engine?

...and is it expensive...

 

[KenHigg]

Much faster. Cost? Depends on how many users you would have in it at one time?

 

[WhizzkidWallace]

Maximum ten users...

....and all my forms, reports etc would work ?

 

[KenHigg]

You may be able to get away with using msde. You can google it for more info...

Everything should work as it does now. All you are basically doing is attaching to the tables.

As far as performance, the main upside is that you would be using a database server instead of a file server. In a nutshell, what that means is this; Say you have a straight MS Access db loaded on a server and you have a table that has 10k rows and you do a query that will filter those records and give you back say 15 rows. In the scenario, Access must bring over all 10k rows over the network to the local computer and execute the query there.

However, if the table was in a database server like SQL Server, the query would execute on the database server and then send back over the network just the 15 rows. World of difference.

Hope this makes sense...