Need help choosing Server Hardware

Hi,
I'm ussually just told what I will be working with, but today I was asked to
choose the hardware myself.
First off, it's a small company, so a $10,000 server is not an option. But
also, a $500 box isn't going to cut it either.
Basically, I am builing a web app which generates from 10-100 million
records per calculation (yes, that's the spec). I'm using SQLServer 2000
(possibly 2005 if my employer wants to risk it), and ASP.NET. The
calculations are all done in stored procedures.
Comments, suggestions, and URLs are much appreciated.
Thanks in advance,
John
huh ? well barnes and nobles thought it was good enough + 2 years ago
when it was still in early Beta so risk wich risk ? ,,,,,
Well first tip use a seperate Web and SQL server this is much better
scalable
for business servers i would go for the poweredge series of DELL maybe two
2850 `s ' one with 2 gb ( for the webserver ) and the other one with as
much memory as fits in the budget for the sql database
regards
Michel Posseth
"John" <pls@.dontspamme.com> wrote in message
news:eo1Vabg5FHA.2628@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hi,
> I'm ussually just told what I will be working with, but today I was asked
> to choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option.
> But also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>|||Your not going to get a one line answer, as database sizing and web server
sizing are entirely different things and you've not hinted at whether you
expect different servers, raid clusters, resilience, failover, SAN/NAS
storage, lots of web traffic - only that you expect the database to be quite
busy. You've neitehr said if your 100 mill records are 1 bit large, or 1
meg each split across many tables, 1 user per hour or 100,000 per second -
and of course it all counts.
This is not a simple job, and if I was you I would be going back to whoever
asked you to chose the hardware and tell them you need to speak to the
hardware vendor, as they can normally help you make some initial estimates
and suggest appropriate hardware. Dont be fooled by your company being
small, for a high performance database you will easily spent $10,000.
A bit of light reading to get you started
http://www.sql-server-performance.c...re_planning.asp
Regards
John Timney
ASP.NET MVP
Microsoft Regional Director
"John" <pls@.dontspamme.com> wrote in message
news:eo1Vabg5FHA.2628@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm ussually just told what I will be working with, but today I was asked
> to choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option.
> But also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>|||"John Timney ( MVP )" <timneyj@.despammed.com> wrote in message
news:uZnMnzg5FHA.3188@.TK2MSFTNGP15.phx.gbl...
> Your not going to get a one line answer, as database sizing and web server
> sizing are entirely different things and you've not hinted at whether you
> expect different servers, raid clusters, resilience, failover, SAN/NAS
> storage, lots of web traffic - only that you expect the database to be
> quite busy. You've neitehr said if your 100 mill records are 1 bit large,
> or 1 meg each split across many tables, 1 user per hour or 100,000 per
> second - and of course it all counts.
> This is not a simple job, and if I was you I would be going back to
> whoever asked you to chose the hardware and tell them you need to speak to
> the hardware vendor, as they can normally help you make some initial
> estimates and suggest appropriate hardware. Dont be fooled by your
> company being small, for a high performance database you will easily spent
> $10,000.
> A bit of light reading to get you started
> http://www.sql-server-performance.c...re_planning.asp
> --
> Regards
>
Thanks, I will look at the URL.
BTW-There will probably be about 10 users, who will all together, probably
run less than 20 different scenarios per week. So not a lot of load. And
the records will be over 4 tables.
Thanks again,
John|||My 2 cents:
1. By the fastest disk you can find.
2. A single processor will obviously be slower than 2, 4, etc. This
factors out to exponentially slower calculations.
3. $10,000 isn't even nearly adequate unless your willing to wait, and
wait, and wait, and wait.....
4. Given the limited information you've provided I'd assume a 4 processor
machine with 4 GB of ram would still be painfully slow.
5. I agree with waiting on SQL 2005. Let everyone else experiment for 6
months. Microsoft has done their due dilligence but I'd wager there are
things out there waiting to nail people. I'm already seeing things in the
news groups about why things aren't working (Security is enable by default),
patches bringing servers down (Saw that one today in the setup newsgroup),
etc.
"John" wrote:

> Hi,
> I'm ussually just told what I will be working with, but today I was asked
to
> choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option. Bu
t
> also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>
>

Need help choosing Server Hardware

Hi,
I'm ussually just told what I will be working with, but today I was asked to
choose the hardware myself.
First off, it's a small company, so a $10,000 server is not an option. But
also, a $500 box isn't going to cut it either.
Basically, I am builing a web app which generates from 10-100 million
records per calculation (yes, that's the spec). I'm using SQLServer 2000
(possibly 2005 if my employer wants to risk it), and ASP.NET. The
calculations are all done in stored procedures.
Comments, suggestions, and URLs are much appreciated.
Thanks in advance,
John
[vbcol=seagreen]
huh ? well barnes and nobles thought it was good enough + 2 years ago
when it was still in early Beta so risk wich risk ? ,,,,,
Well first tip use a seperate Web and SQL server this is much better
scalable
for business servers i would go for the poweredge series of DELL maybe two
2850 `s ? one with 2 gb ( for the webserver ) and the other one with as
much memory as fits in the budget for the sql database
regards
Michel Posseth
"John" <pls@.dontspamme.com> wrote in message
news:eo1Vabg5FHA.2628@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm ussually just told what I will be working with, but today I was asked
> to choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option.
> But also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>
|||Your not going to get a one line answer, as database sizing and web server
sizing are entirely different things and you've not hinted at whether you
expect different servers, raid clusters, resilience, failover, SAN/NAS
storage, lots of web traffic - only that you expect the database to be quite
busy. You've neitehr said if your 100 mill records are 1 bit large, or 1
meg each split across many tables, 1 user per hour or 100,000 per second -
and of course it all counts.
This is not a simple job, and if I was you I would be going back to whoever
asked you to chose the hardware and tell them you need to speak to the
hardware vendor, as they can normally help you make some initial estimates
and suggest appropriate hardware. Dont be fooled by your company being
small, for a high performance database you will easily spent $10,000.
A bit of light reading to get you started
http://www.sql-server-performance.co...e_planning.asp
Regards
John Timney
ASP.NET MVP
Microsoft Regional Director
"John" <pls@.dontspamme.com> wrote in message
news:eo1Vabg5FHA.2628@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm ussually just told what I will be working with, but today I was asked
> to choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option.
> But also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>
|||"John Timney ( MVP )" <timneyj@.despammed.com> wrote in message
news:uZnMnzg5FHA.3188@.TK2MSFTNGP15.phx.gbl...
> Your not going to get a one line answer, as database sizing and web server
> sizing are entirely different things and you've not hinted at whether you
> expect different servers, raid clusters, resilience, failover, SAN/NAS
> storage, lots of web traffic - only that you expect the database to be
> quite busy. You've neitehr said if your 100 mill records are 1 bit large,
> or 1 meg each split across many tables, 1 user per hour or 100,000 per
> second - and of course it all counts.
> This is not a simple job, and if I was you I would be going back to
> whoever asked you to chose the hardware and tell them you need to speak to
> the hardware vendor, as they can normally help you make some initial
> estimates and suggest appropriate hardware. Dont be fooled by your
> company being small, for a high performance database you will easily spent
> $10,000.
> A bit of light reading to get you started
> http://www.sql-server-performance.co...e_planning.asp
> --
> Regards
>
Thanks, I will look at the URL.
BTW-There will probably be about 10 users, who will all together, probably
run less than 20 different scenarios per week. So not a lot of load. And
the records will be over 4 tables.
Thanks again,
John
|||My 2 cents:
1. By the fastest disk you can find.
2. A single processor will obviously be slower than 2, 4, etc. This
factors out to exponentially slower calculations.
3. $10,000 isn't even nearly adequate unless your willing to wait, and
wait, and wait, and wait.....
4. Given the limited information you've provided I'd assume a 4 processor
machine with 4 GB of ram would still be painfully slow.
5. I agree with waiting on SQL 2005. Let everyone else experiment for 6
months. Microsoft has done their due dilligence but I'd wager there are
things out there waiting to nail people. I'm already seeing things in the
news groups about why things aren't working (Security is enable by default),
patches bringing servers down (Saw that one today in the setup newsgroup),
etc.
"John" wrote:

> Hi,
> I'm ussually just told what I will be working with, but today I was asked to
> choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option. But
> also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>
>

Need help choosing Server Hardware

Hi,
I'm ussually just told what I will be working with, but today I was asked to
choose the hardware myself.
First off, it's a small company, so a $10,000 server is not an option. But
also, a $500 box isn't going to cut it either.
Basically, I am builing a web app which generates from 10-100 million
records per calculation (yes, that's the spec). I'm using SQLServer 2000
(possibly 2005 if my employer wants to risk it), and ASP.NET. The
calculations are all done in stored procedures.
Comments, suggestions, and URLs are much appreciated.
Thanks in advance,
John>> (possibly 2005 if my employer wants to risk it),
huh ? well barnes and nobles thought it was good enough + 2 years ago
when it was still in early Beta so risk wich risk ? ,,,,,
Well first tip use a seperate Web and SQL server this is much better
scalable
for business servers i would go for the poweredge series of DELL maybe two
2850 `s ' one with 2 gb ( for the webserver ) and the other one with as
much memory as fits in the budget for the sql database
regards
Michel Posseth
"John" <pls@.dontspamme.com> wrote in message
news:eo1Vabg5FHA.2628@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm ussually just told what I will be working with, but today I was asked
> to choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option.
> But also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>|||Your not going to get a one line answer, as database sizing and web server
sizing are entirely different things and you've not hinted at whether you
expect different servers, raid clusters, resilience, failover, SAN/NAS
storage, lots of web traffic - only that you expect the database to be quite
busy. You've neitehr said if your 100 mill records are 1 bit large, or 1
meg each split across many tables, 1 user per hour or 100,000 per second -
and of course it all counts.
This is not a simple job, and if I was you I would be going back to whoever
asked you to chose the hardware and tell them you need to speak to the
hardware vendor, as they can normally help you make some initial estimates
and suggest appropriate hardware. Dont be fooled by your company being
small, for a high performance database you will easily spent $10,000.
A bit of light reading to get you started
http://www.sql-server-performance.com/rc_hardware_planning.asp
--
Regards
John Timney
ASP.NET MVP
Microsoft Regional Director
"John" <pls@.dontspamme.com> wrote in message
news:eo1Vabg5FHA.2628@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm ussually just told what I will be working with, but today I was asked
> to choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option.
> But also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>|||"John Timney ( MVP )" <timneyj@.despammed.com> wrote in message
news:uZnMnzg5FHA.3188@.TK2MSFTNGP15.phx.gbl...
> Your not going to get a one line answer, as database sizing and web server
> sizing are entirely different things and you've not hinted at whether you
> expect different servers, raid clusters, resilience, failover, SAN/NAS
> storage, lots of web traffic - only that you expect the database to be
> quite busy. You've neitehr said if your 100 mill records are 1 bit large,
> or 1 meg each split across many tables, 1 user per hour or 100,000 per
> second - and of course it all counts.
> This is not a simple job, and if I was you I would be going back to
> whoever asked you to chose the hardware and tell them you need to speak to
> the hardware vendor, as they can normally help you make some initial
> estimates and suggest appropriate hardware. Dont be fooled by your
> company being small, for a high performance database you will easily spent
> $10,000.
> A bit of light reading to get you started
> http://www.sql-server-performance.com/rc_hardware_planning.asp
> --
> Regards
>
Thanks, I will look at the URL.
BTW-There will probably be about 10 users, who will all together, probably
run less than 20 different scenarios per week. So not a lot of load. And
the records will be over 4 tables.
Thanks again,
John|||My 2 cents:
1. By the fastest disk you can find.
2. A single processor will obviously be slower than 2, 4, etc. This
factors out to exponentially slower calculations.
3. $10,000 isn't even nearly adequate unless your willing to wait, and
wait, and wait, and wait.....
4. Given the limited information you've provided I'd assume a 4 processor
machine with 4 GB of ram would still be painfully slow.
5. I agree with waiting on SQL 2005. Let everyone else experiment for 6
months. Microsoft has done their due dilligence but I'd wager there are
things out there waiting to nail people. I'm already seeing things in the
news groups about why things aren't working (Security is enable by default),
patches bringing servers down (Saw that one today in the setup newsgroup),
etc.
"John" wrote:
> Hi,
> I'm ussually just told what I will be working with, but today I was asked to
> choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option. But
> also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>
>|||"John" <pls@.dontspamme.com> wrote in message
news:eo1Vabg5FHA.2628@.TK2MSFTNGP11.phx.gbl...
> Hi,
...
> Comments, suggestions, and URLs are much appreciated.
Hi,
One of the partners has decided to go with hosted server.
Thanks again for the input.
John|||The choices remain the same, whether you host it yourself or not. You will
still need to identify the correct specifications of the hardware you
require to manage the transaction quantity you will generate.
--
Regards
John Timney
ASP.NET MVP
Microsoft Regional Director
"John" <pls@.dontspamme.com> wrote in message
news:eR$HxWl5FHA.2020@.TK2MSFTNGP10.phx.gbl...
> "John" <pls@.dontspamme.com> wrote in message
> news:eo1Vabg5FHA.2628@.TK2MSFTNGP11.phx.gbl...
>> Hi,
> ...
>> Comments, suggestions, and URLs are much appreciated.
> Hi,
> One of the partners has decided to go with hosted server.
> Thanks again for the input.
> John
>sql

Need help choosing front end for SQL Server

I've been working on an Access 97 database that's pretty much reached
it's limit in terms of performance and reliability. Although it supports
relatively few users (5-10 concurrent) it contains a lot of data (around
30 tables, some with several million records) plus a lot of forms and
reports.
The company has recently purchased an MS SQL server and it's my job to
migrate the back-end as well as re-write the front-end (the front-end
needs re-writing anyway).
My question is do we keep Access as the front end (we'd probably upgrade
to 2003) or would we get better performance using a different solution
(VB.Net for example) ?
If Access, would it be best use MDB's or ADP's?
The main consideration is that we need to perform some complicated
queries on large datasets, which ideally should happen on the SQL server
(I'm currently learning about stored procedures and views etc).
Thanks,
PSB
I see no reason why you can't continue to use access as the front end.
If you have the budgets, and time to re-write the application, then I guess
you
can choose anything you like.
However, if you wish to keep the application, and not re-write, and not have
to re-train the users, then why not continue to use access as the front end?

> My question is do we keep Access as the front end (we'd probably upgrade
> to 2003)
This is going to be up to you. I see no reason why not to continue to use
ms-access. I mean, you got to write the application in something.

> or would we get better performance using a different solution
For the number of users you have, I see no reason not to use ms-access.

> (VB.Net for example) ?
It is a great platform. Do you have the developers...or perhaps you will
spend the time and learn .net? (you will need a few months of time). I mean,
how long did it take the people to learn ms-access? (it took me more then a
year to be become productive in ms-access). And, the sample applies to any
platform you will adopt.

> If Access, would it be best use MDB's or ADP's?
It is by far and away best to keep it as a MDB (and, of course, you used a
split database..and always distributed a mde to each user anyway..right?).
I would only choose a ADP for a brand new from scratch product (and even
then...likely I would not use a adp). So, for a existing application, it
makes NO
sense to use a ADP..as you can't use any of your DAO code.

> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL server
> (I'm currently learning about stored procedures and views etc).
Yes, for processing intensive things, you certainly want that to run on the
server, but you still need to choose a programming environment that lets you
write code, and create the application (and sql server does not do that!!).
If you got the time,a nd resources, and have the developer(s) to re-write,
then you can do such. It really comes down to budgets, and what benefits
you get by re-writing the application.
The skill level of the developer will make or break the project regardless
of the "conversion" issues.
I am a better hockey coach when Wayne Gretzky is on the team.
Hence the #1 consideration is at what level the developer is at. There are
certainly more levels then just "trained" or "not trained". Generally there
are a "lot" of skill levels, but the following breakdown is sufficient. **
Stage 1 Innocent (never heard of the product)
Stage 2 Aware (Has read an article about X)
Stage 3 Apprentice (has attended a three-day seminar)
Stage 4 Practitioner (ready to use X on a real project)
Stage 5 Journeyman (uses X naturally and automatically in his job)
Stage 6 Master (has internalized X, knows when to break the rules)
Stage 7 Expert (writes books, gives lectures, looks for ways to extend x)
One should NEVER attempt a project with a team consisting with Stage 3 or
lower people. This is a sure fire formula for failure. The team can consist
of stage 4's, but they should have at least access to Stage 5, or 6.
So, if your developers are skilled in the new platform, then that is
certainly a possible here to re-write.
However, I see no reason why you can't continue to use ms-access as the
front end to sql server. I mean, if your developers don't know how to use
sql server, then you can get a garbage and slow application if it is written
in VB, vb.net, or ms-access. The performance here is not going to be the
fact that you used VB or ms-access, but in fact that your developers know
how to utilize sql server.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal
|||For that number of users, Access should be able to handle if designed
properly (Seperated FE and BE and BE file size is not too big, say close 2G
limit). However, now you have already bought SQL Server, and as you stated
"the front-end needs re-writing anyway", so, there is doubt of moving to SQL
Server.
As for front end, to use Access, or not to use it, lisenceing Access itself
would not be a big problem here. That is, if you use Access FE, all users
must have it installed. for $300 of each, the cost does not weigh too much
in the entire situation. And for your situation (fairly small number of
user, and I'd imagine they are in the same office, so deploy/update your app
wouldn't be a serious issue), exactly what type of FE is not that important.
I do not know your skill level, but programming a non-Access front end (Win
App ot wep app) is a lot more different from Access programming and requires
quite different skill and knowledge. Whether such en expertise is available
or not would make quite big difference on development cost.
If you go with Access, probably ADP is the option (But you must use
Access2K or later, not Access97). And many experienced Access developers
claim they can produce Access solution a lot faster than other type of FE.
"PsychicStickleBrick" <sp@.m.me> wrote in message
news:421a3178$0$75435$ed2619ec@.ptn-nntp-reader01.plus.net...
> I've been working on an Access 97 database that's pretty much reached
> it's limit in terms of performance and reliability. Although it supports
> relatively few users (5-10 concurrent) it contains a lot of data (around
> 30 tables, some with several million records) plus a lot of forms and
> reports.
> The company has recently purchased an MS SQL server and it's my job to
> migrate the back-end as well as re-write the front-end (the front-end
> needs re-writing anyway).
> My question is do we keep Access as the front end (we'd probably upgrade
> to 2003) or would we get better performance using a different solution
> (VB.Net for example) ?
> If Access, would it be best use MDB's or ADP's?
> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL server
> (I'm currently learning about stored procedures and views etc).
> Thanks,
> PSB
>
>
|||Now there's the hundred thousand dollar question. The answer is it depends.
Moving the back-end to Sql Server is a good idea. And you can do this with
very little re-writing of the front end.
As for the front-end, it depends on how much of it needs re-written. If it's
just a little, then I'd probably stay with Access. You would get a little
performance hit, but not much if the queries ran server side.
If it's a lot of re-write, then I would go with VB.NET (or ASP.NET if you
want to make it a web app). You get a lot more functionality with these.
One word of note. Running a VB.NET app requires the Microsoft Framework also
be installed. This is a bit resource heavy and will run slowly on older
machines.
Good Luck.
Tom Collins
"PsychicStickleBrick" <sp@.m.me> wrote in message
news:421a3178$0$75435$ed2619ec@.ptn-nntp-reader01.plus.net...
> I've been working on an Access 97 database that's pretty much reached
> it's limit in terms of performance and reliability. Although it supports
> relatively few users (5-10 concurrent) it contains a lot of data (around
> 30 tables, some with several million records) plus a lot of forms and
> reports.
> The company has recently purchased an MS SQL server and it's my job to
> migrate the back-end as well as re-write the front-end (the front-end
> needs re-writing anyway).
> My question is do we keep Access as the front end (we'd probably upgrade
> to 2003) or would we get better performance using a different solution
> (VB.Net for example) ?
> If Access, would it be best use MDB's or ADP's?
> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL server
> (I'm currently learning about stored procedures and views etc).
> Thanks,
> PSB
>
>
|||"PsychicStickleBrick" <sp@.m.me> wrote in message
news:421a3178$0$75435$ed2619ec@.ptn-nntp-reader01.plus.net
> I've been working on an Access 97 database that's pretty much reached
> it's limit in terms of performance and reliability. Although it
> supports relatively few users (5-10 concurrent) it contains a lot of
> data (around 30 tables, some with several million records) plus a lot
> of forms and reports.
> The company has recently purchased an MS SQL server and it's my job to
> migrate the back-end as well as re-write the front-end (the front-end
> needs re-writing anyway).
> My question is do we keep Access as the front end (we'd probably
> upgrade to 2003) or would we get better performance using a different
> solution (VB.Net for example) ?
> If Access, would it be best use MDB's or ADP's?
> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL
> server (I'm currently learning about stored procedures and views etc).
> Thanks,
> PSB
My immediate reaction would be to stay with Access as your front-end,
and use an MDB for it. Some aspects of the application may need to be
rewritten, but in my experience that will probably be much quicker than
rewriting the whole thing in another devleopment environment. Moving
complicated queries to the server, though, is likely to take a fair
amount of work, and you have to budget for that. If the queries don't
use VBA functions or the First() or Last() functions, I don't think
you'll have too many problems, but if they do you'll have to figure out
how to accomplish the same function using SQL Server features and T-SQL.
Incidentally, one thing to watch out for -- and I know because it's
bitten me a few times -- is that your queries and application logic may
currently make assumptions about the order in which records are returned
in the absence of an ORDER BY clause. For example, in Jet SQL the GROUP
BY clause implicitly orders the records, but in T-SQL it doesn't. I
mention this only as something to keep in mind as you transfer queries
to SQL Server.
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|||Dirk Goldgar wrote:
> in Jet SQL the GROUP
> BY clause implicitly orders the records, but in T-SQL it
> doesn't.
Jet implicitly orders using what criteria?
Jamie.
|||onedaywhen wrote:
> Dirk Goldgar wrote:
> Jet implicitly orders using what criteria?
> Jamie.
Alphabetical/Numercal IOW "GroupBy SomeField" implicitly adds an "ORDER BY
SomeField" in Access/Jet.
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
|||"onedaywhen" <jamiecollins@.xsmail.com> wrote in message
news:1109076635.869509.10730@.g14g2000cwa.googlegro ups.com...
> Dirk Goldgar wrote:
> Jet implicitly orders using what criteria?
As I recall Jet orders the result set by the table's PK.
Steve
|||When you use GROUP BY in the absence of an ORDER BY clause Jet will sort by
the columns used in the GROUP BY clause, from left to right. For example,
this query ...
SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID;
... is equivalent to this query ...
SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID
ORDER BY tblTest.TestText, tblTest.TestNumber;
... however in this query ...
SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID
ORDER BY tblTest.ID;
... the explicit ORDER BY clause over-rides the implicit ordering of the
GROUP BY clause.
Brendan Reynolds (MVP)
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:Oco9FqOGFHA.1392@.tk2msftngp13.phx.gbl...
> "onedaywhen" <jamiecollins@.xsmail.com> wrote in message
> news:1109076635.869509.10730@.g14g2000cwa.googlegro ups.com...
> As I recall Jet orders the result set by the table's PK.
> Steve
>
|||Brendan Reynolds wrote:
> When you use GROUP BY in the absence of an ORDER BY clause Jet will
sort by
> the columns used in the GROUP BY clause, from left to right.
Thanks everyone. I got round to testing and I concur the following:
- the implicit ORDER BY is by columns left to right, ascending;
- the process occurs in Jet rather than MS Access;
- it is unaffected by clustered index, primary key, date/time entered
order etc.
Thanks again,
Jamie.