[AccessD] SQL Server Native Client

Congratulations, John.

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, February 28, 2012 9:39 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] SQL Server Native Client

Wouldn't you know, I found an install package just for that SQL Server
native Client driver.  It has 
separate x32 and x64 installers.  Given that most of us use x32 Office I
downloaded and installed 
the x32 driver on the problem machine and voila, she's a wurkin.

So now I have to decide whether it is worth while to (have to) remember to /
have handy / install 
the SQL Server Native Client driver on each and every  client machine.

I am still trying to determine why I would do that.  The professionals seem
to be split.  "Generally 
the same performance level."  Nothing I am seeing indicates it is worth the
hassle for the Microsoft 
Access arena.

I wrote a timing function to set a bunch of linked tables to "Native
client", time opening them, 
then set them back to "Sql Server" and time opening them again.  The test I
am doing is simply open 
every ODBC linked table in a DAO recordset (SELECT * FROM MyTbl) and then do
a rst.MoveLast.  Also 
at this point I don't have a bunch of tables with big data.

I am getting anywhere from 6% to 25% speed increase using the "Native
Client" drivers, with 7-10% 
speed increase being most common.  That is just doing the same set of tables
over and over.  Not 
sure what the difference is, though it could be internet overhead since I am
testing links to tables 
linked to a SQL Server via a Hamachi IP / VPN.

And finally, I tested my billing database which has the most info of
anything here at my office 
using Access and the results were truly unremarkable.  This (billing)
database is hosted on my big 
server whereas the others I was testing against were on a SQL Server on a
VM.  For the billing 
database I got results run to run literally a few ms better and a few ms
worse.

The nice thing is that my relink framework will allow me to instantly do
this test on any database 
in the future.

Anyway, it certainly looks to me like in general (for Access) the use of the
Native Client doesn't 
provide significant if any speed gains.

-- 
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com