Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access data on a Server

Posted on 2013-12-05
13
185 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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
 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

837 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