Very Computer

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
Server tables.

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
down.
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
Server.

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?

Thanks

Jonathan

Test against what you are actually going to deliver to the client.
Benchmarking can be skewed depending on factors like hardware (as
you've seen) so that the results are virtually meaningless in any
real-world context.

I'd also recommend getting a good book on SQL Server, like "Inside SQL
Server 2000" by Kalen Delaney. That will serve you better in the long
run when it comes down to actually learning SQL Server.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Fri, 21 Dec 2001 23:34:06 +0200, "Jonathan Black"

I run Large databases, over the internet, using ADP interface.  As you have
found out the Lumpsum size of the data transferred will effect the update.
However if you are running Queries thru ADO instead of executing a procedure
that returns the data, you have put in another delay.
the Query is Compile on access so if you run in on data in the access, it
will execute quicker.
if the Query is sent the SQL server it had to be parsed, compiled then run.

--
Host and Webmaster
Developer.
I also sweep floors.

Quote:> 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
> Server tables.

> 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
> down.
> 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
> Server.

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

> Thanks

> Jonathan