• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

Access data on a Server

How can you establish whether a server has sufficient band width and hub or switch set-up to run an application?.

Can you get any equipment or is there a test that you can run to show the transfer rate of a server?
0
DatabaseDek
Asked:
DatabaseDek
  • 5
  • 4
  • 4
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
There's really no way to do that other then running the app itself.

Access apps vary so widely in terms of how they are developed and used, it's impossible to estimate.

I've seen poorly written Access apps fall over with 4 or 5 users and well written ones work with 30 or more without issue.

I've also seen apps with databases having tables with hundreds of records, and then those with hundreds of thousands.

 Of course there are tests out there where you can get the raw speed, but that really doesn't tell you much and how an app will react.   But in general, the faster the network, the faster the app will be.

Jim.
0
 
Eric ShermanAccountant/DeveloperCommented:
I agree with Jim ...

Something a simple as a poorly constructed SQL query can make an application run very inefficiently giving the impression that your server is slow or not working properly.  If you are having problems ... Unless you have some enormous number of users and records, a properly structured Access application will perform will highly acceptable results.  I would look at the application first, IMO.

It's like buying expensive marble tile ... the end result will only be a good as the contractor who cut and installed it.

ET
0
 
Eric ShermanAccountant/DeveloperCommented:
In addition to the comments above ... This is one of the performance issues I ran into a few times.  I found that in some client environments, SQL Queries with Sub-Queries against large tables (100k records or more) genreally ran very slow and became inefficient.  I then changed the logic and code to use Recordsets with Filters and Processing Loops and the performance was 100 times better.  I'm not at all saying the Recordset approach is better than the Sub-Query method but you just run into cases like this depending on the client's environment and how their network is setup.  A good developer will test several methods and select the best performance especially when dealing with large tables and multi-users.

ET
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
In regards to ET' points, I guess one way to sum that up is that performance is only as good as your weakest link and it's always something your chipping away at.

As a little more direct answer to your question, I would put a few users on and then monitor bandwidth on the server right from task manager.   I'd also probably check and make sure the server has free physical memory.

 In short, with a half dozen or so users, you should be able to get a fair idea of how things will run just by looking at task manager.

Jim.
0
 
DatabaseDekAuthor Commented:
I rarely use sql code. I find the access QBE grid almost always gets the result that I want.

Here is something I gleaned from my brother. (Hardware fanatic)

Old servers are 10MGb (0.8Mbite)
Average Servers are 10 times as fast
Modern servers are 10 times faster than that.

But a server can be likened to a motorway with 6 lanes. If set up badly, traffic can be directed mainly through one lane, with inevitable results. Also if using a hub (whatever that is) to send data to different computers where there maybe say 5 end users, with each packet of data sent the server asks each computer if it is the one it should be sending data to. However if a switch is used then the switch learns which computer is which and avoids this waste of time. So if like myself you are installing an app and wonder what the hell is going wrong it's worth noting that a lot of other stuff has to be right before you can expect a smoothly opperating application.
0
 
Eric ShermanAccountant/DeveloperCommented:
<<I rarely use sql code. I find the access QBE grid almost always gets the result that I want.>>

Using the QBE grid still generates the SQL code needed to run your queries in Access.  It's just a more user friendly interface.

You can still design inefficient queries using the QBE grid.  

As mentioned earlier there's a lot of variables that can determine how an Access app functions in a networked environment.

ET
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Modern servers are 10 times faster than that.>>

  I was going to add to my last comment but didn't that with modern hardware, it's almost always the app that's an issue rather then the hardware.  That's an over generalization to some extent, but mostly true.

  <<Also if using a hub (whatever that is) to send>>

 and that's a good example; a hub and a switch are almost identical except for the fact that you noted (a hub sends traffice to every port, needed or not - a switch only sends the traffic to the port that should get it).

 Years ago, switches were expensive, so often you use a switch only at critical points and hubs everywhere else.

 But today, switches are common place and it's rare to find a hub any more as you can get a 16 port GB switch for $125.  

 Unless your talking hundreds of users or tables with millions of rows, even entry level hardware is not going to be your problem.

 What you do in development will have far greater impact.

Jim.
0
 
DatabaseDekAuthor Commented:
Thanks ET

This slow server (Or my App) happened recently where I had no problems with other servers.

All that was required from the Data was enough info to fill a combo box. I have checked the sql and it looks pretty straight forward.

Here it is:
SELECT FrameType.ID, FrameType.FrameMaterial AS Material, FrameType.Supplier, FrameType.FrameType, FrameType.FF, FrameType.Depth AS Width, FrameType.TotalFT AS Thickness, FrameType.TimberMargin AS MarkUp, FrameType.FrameAssembly AS [Plus/Mtr], FrameFR.FireRating, (Round(((1+([Waste%]/100))*[TimberThick]*[TimberWidth]*[TimberCostM³])/10000)/100)+[FrameAssembly] AS Cost, Round((100+[TimberMargin])*[Cost]/100,2) AS Sell
FROM FrameType INNER JOIN FrameFR ON FrameType.FrameMaterial = FrameFR.FrameMaterial
WHERE (((FrameType.FrameMaterial) Like nz([Forms]![ProjectForm]![Details].[Form]![FM],"*")) AND ((FrameType.Supplier) Like nz([Forms]![ProjectForm]![Details].[Form]![FSupplier],"*")) AND ((FrameType.FF) Like nz([Forms]![ProjectForm]![Details].[Form]![FF],"*")) AND ((FrameFR.FireRating) Like Left([Forms]![ProjectForm]![Details].[Form]![FireRating],4) & "*"))
ORDER BY FrameType.FrameMaterial, FrameType.Supplier, FrameType.Depth, FrameType.TotalFT, (Round(((1+([Waste%]/100))*[TimberThick]*[TimberWidth]*[TimberCostM³])/10000)/100)+[FrameAssembly];

Can you see any obvious problem here?
0
 
Eric ShermanAccountant/DeveloperCommented:
I can see where the Calculations, Join and Wildcard Conditional Parameters can take a while to fill a combo box.  Is this a Front-End / Back-End setup where the tables only reside on the server?  If so, is it a Access Back-End or SQl Server Back-End??

Here is an easy test ... Take the query above and change it to a Make Table query creating TblTemp1.  Then change the query of your Combo Box to use TblTemp1 and see how the response is over your network.  This will confirm if the delay is created by waiting for the SQL to run or if it is due to network traffic.

ET
0
 
DatabaseDekAuthor Commented:
This is an Access (I believe Jet engine) back end on server front end on local pc.

The maximum number of records is likely to be well below 3000

That test is a good idea.
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, with 3000 records you should not see the type of slow response.  Your query for the combo box has a lot of Wildcards which will perform slower ... it has to look through everything.  Going back to what we were discussing earlier relating to how you SQL is structured can mak a lot of difference.  

I use a lot of temp tables and clear them out when finished to avoid that specific issue.

ET
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<The maximum number of records is likely to be well below 3000>>

Like I said, what you do in development will have the greatest impact.

Any of todays servers/networks will be more then capable of handling that.

Jim.
0
 
DatabaseDekAuthor Commented:
Great help thank you both.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now