Solved

Access data on a Server

Posted on 2013-12-05
13
178 Views
Last Modified: 2013-12-07
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
Comment
Question by:DatabaseDek
  • 5
  • 4
  • 4
13 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 39698069
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39698432
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
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 250 total points
ID: 39698460
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
 
LVL 57
ID: 39698487
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
 

Author Comment

by:DatabaseDek
ID: 39699015
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39699071
<<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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 57
ID: 39699174
<<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
 

Author Comment

by:DatabaseDek
ID: 39699207
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39699366
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
 

Author Comment

by:DatabaseDek
ID: 39701465
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39701586
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
 
LVL 57
ID: 39701659
<<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
 

Author Closing Comment

by:DatabaseDek
ID: 39703322
Great help thank you both.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now