Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access data on a Server

Posted on 2013-12-05
13
Medium Priority
?
191 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
[X]
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
  • 5
  • 4
  • 4
13 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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 1000 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

636 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