I am a newbie to Sql Server too and am trying to persuade a client
to upgrade to Sql Server from Access 2000.(To be honest because
I want to gain more Sql Server experience)
They only have a maximum of 5 concurrent users and their largest
database table has around 350,000 records.
I have found that on tests where I have compared running the same
query from access 2000 mdb's on the clients to an Access db on the
server, compared with an access 2000 adp connecting to an Sql
Server 7 database on the same server, Access 2000 is often faster
and sometimes almost double the speed.
Specifically, on a query which returns 68000 rows, Access 2000 is
much faster on a modern pentium 2 client. On a query which returns 20 rows
Sql Server is faster.
On a client which has old hardware, Sql Server is always faster.
This all makes sense because the old client has a slower network
card so dragging all 350000 rows to the client jet engine is slow.
Similairly with queries that return a lot of rows, the advantage
of server side processing is reduced.
Please note that the same fields are indexed in both the access and Sql
If I run the query on the Server itself, Access is much faster.
This makes sense because the client based nature of jet doesn't slow it
I haven't yet tested asp based queries.
My problem is that I want to work with Sql Server, but being a man
of integrity I need to demonstrate clear and real speed superiority of Sql
With only 5 concurrent users, the picture is very mixed.
In particular the display of records in the access grid is much
faster with the access mdb than it is with the access project.
Does anyone have any tips as to demonstrate advantages of Sql Server
over access 2000 in this type of scenario?