1

Possible Duplicate:
How do you do Load Testing and Capacity Planning for Databases

I have a database set up with MS Access 2007 front ends and an SQL Server 2005 back end. At the moment, all the queries are saved in the front end as I've only recently moved to an SQL Server backend. I'm wondering how much of those queries I should save as stored procedures/views on SQL Server.

About the system

The number of concurrent users is only a handful, though it could be as high as 25 at one time (very unlikely).

The average computer has an Intel i3-2120 CPU running at 3.3 GHz, which gets a PassMark score of 3,987, whilst the server has an Intel Xeon E5335 running at 2.0 GHz, which gets a PassMark score of 2,637. Always an awkward situation when an i3 outperforms a Xeon... though the i3 is from Q1 2011 and the Xeon is Q2 2009.

There is potential for a server upgrade in the future, though it wouldn't come easy.

I'm inclined to move the queries to the back end, as they are beginning to take noticeable time and I figure that is a better way of doing things. I like the idea of throwing everything at the server, then pushing for a server upgrade. It makes more sense in my mind to be upgrading one server rather than 30 PCs.

Or am I being overzealous?

Why my question isn't a duplicate

It seems that my question has been misinterpreted and labelled a duplicate of quite a different question, one about testing and capacity planning. I'll try explain how my question is very different from the linked question.

The crux of my question is something like "Even though my server is technically slower, is it better to have it doing more of the queries?"

There's two ways that people could have answered this:

  1. I agree the server is going to be slower, but the extra benefits of such and such (like the less Access the better) means you should move most to the server anyway. (OR no it doesn't outweigh the benefit, keep them in Access)
  2. Actually the server will be faster because of such and such.

I'm hoping that people out there could provide some answers like this, and the question in the dupe link doesn't really provide either of these answers.

Ok sure, I suppose I could do extensive performance testing to compare Access queries running on a local machine to SQL Server queries running on the server, but that sounds like a very hard task (particularly performance testing of access) compared to someone giving some quick general guidance, and again, my question is looking for a lot more than immediate performance benefit.

Community
  • 1
andrewb
  • 151
  • 8
  • 3
    My first thought is, the more you move out of Access, the better. – Michael Hampton Dec 04 '12 at 02:17
  • That is a good point. If I ever wanted to fully leave Access, it would be a lot easier if most my queries were already on SQL Server. – andrewb Dec 04 '12 at 02:35
  • Move as much as you can out of Access, but first you probably want to profile your database and find out why it's so slow. The dupe link may help you there... – voretaq7 Dec 04 '12 at 03:30
  • @voretaq7 I'm currently working on getting some stored procedures and views happening on SQL Server. I think it will be a case of do one, test it, then do some more. Access isn't that slow, it's just that one of my queries has to churn through a fairly large table, so it's not instant. Though I think I can improve upon my method to make it faster. Anyway, speed is currently quite decent. – andrewb Dec 04 '12 at 03:45
  • Stored procedures and views are not magic band-aids - you have to apply them judiciously based on solid design reasons (which you may be doing, but I've seen them slapped around as band-aids far too often). Microsoft makes a number of good profiling/analysis tools for SQL Server -- you can also ask on [dba.se], if you provide enough detail about your environment/schema they may be able to give you some advice... – voretaq7 Dec 04 '12 at 03:55
  • @voretaq7 I'm surprised this question was closed as a duplicate, the one you link is very different in purpose to mine. In my question, I've stated that the server being slower is a given - the question is whether to move queries to the server regardless because of other benefits, e.g. good practice/reliability/ease of future expansion. You seem to be interpreting my question as "Help my database is slow" or "Is my server going to be faster?", which is more in line with the question you linked. The link you provided doesn't really help much. – andrewb Dec 04 '12 at 04:13
  • @andrewb I don't think anyone is going to advise you to remain on Access (for the reasons you've pointed out, and are obviously aware of - I don't think you need us to repeat them to you). With that established your problem is down to one of performance (SQL server shouldn't be that much slower) - fixing that means measuring, profiling, and adjusting iteratively until you get the performance you want out of the system, using the techniques from the dup link. Again, for more information on profiling and performance [dba.se] is probably the best resource. – voretaq7 Dec 04 '12 at 07:34
  • @andrewb All that said, if you really think I've missed the mark please [open a discussion on Meta](http://meta.serverfault.com) -- If you can convince a few other people that this question shouldn't be closed I've got no problem re-opening it. – voretaq7 Dec 04 '12 at 07:36
  • @voretaq7 Ok thanks, that makes more sense. That deduction of yours is probably what I needed to hear, as for me the link between my question and your performance guide question wasn't immediately obvious. I'm only new to all this. It sounds like I should work to move many of my queries to SQL Server, but you're right, I shouldn't mindlessly move them all there. I'd rather this question not be closed as this means I'm still stuck on 1 rep point, but you already have a few people agreeing with your move, and I got work to do, so I'll just leave it. – andrewb Dec 11 '12 at 01:02

0 Answers0