SEO

I'm developing an application that must support connecting to SQL
Server 2005 as well as SQL Server 2000. Which driver should I use for
the connection?

1. Use SQL Native Client driver for both 2005 and 2000.
2. Use SQL Server driver for both 2005 and 2000.
3. Use SQL Native Client driver for 2005 and SQL Server driver for
2000.

My experimentation has shown that option #3 gives the best performance
for my application but it introduces some extra complications during
setup.

Any thoughts? Any documentation that details the differences between
these drivers?

Mark
Atlanta GA

You are over-thinking here. First, you should think about finishing your
application instead of trying to *optimize* it when it's not even finished.

Second, trying to evaluate the relative performance of different drivers is
a much more difficult task than you think. The results that you are now
getting could be totally different on another system or on the same system
but with a different level of load or of pattern of usage.

Third, even if you could evaluate some difference between their performance,
there are things more important than that: first, the real load on a
sql-server is made by executing queries, not by returning the final result
to the client and second, the added functionality and security provided by
the latest driver if probably much more important than the possibility of
saving a few microseconds here and there.

Finally, if you absolutely want to save a few microseconds, add more memory
or wait a few weeks before buying your next computer.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"John Bigbooty" <BOGUSevileye0702BOGUS@bellsouth.netBOGUS> wrote in message
news:48085c58.91582765@news1.newscene.com...
> I'm developing an application that must support connecting to SQL
> Server 2005 as well as SQL Server 2000. Which driver should I use for
> the connection?
>
> 1. Use SQL Native Client driver for both 2005 and 2000.
> 2. Use SQL Server driver for both 2005 and 2000.
> 3. Use SQL Native Client driver for 2005 and SQL Server driver for
> 2000.
>
> My experimentation has shown that option #3 gives the best performance
> for my application but it introduces some extra complications during
> setup.
>
> Any thoughts? Any documentation that details the differences between
> these drivers?
>
> Mark
> Atlanta GA

On Sun, 20 Apr 2008 18:38:08 -0400, "Sylvain Lafontaine" <sylvain aei
ca (fill the blanks, no spam please)> wrote:

>You are over-thinking here. First, you should think about finishing your
>application instead of trying to *optimize* it when it's not even finished.
>
>Second, trying to evaluate the relative performance of different drivers is
>a much more difficult task than you think. The results that you are now
>getting could be totally different on another system or on the same system
>but with a different level of load or of pattern of usage.
>
>Third, even if you could evaluate some difference between their performance,
>there are things more important than that: first, the real load on a
>sql-server is made by executing queries, not by returning the final result
>to the client and second, the added functionality and security provided by
>the latest driver if probably much more important than the possibility of
>saving a few microseconds here and there.
>
>Finally, if you absolutely want to save a few microseconds, add more memory
>or wait a few weeks before buying your next computer.

Was there some reason you felt compelled to reply and act like a total
ass? Before you go making blanket assumptions about me or my
application perhaps you should look in the mirror and ask yourself "am
I really as high and mighty as I think I am?"

First, my application is basically complete. We are just finishing the
final details and making sure we support both the SQL 2000 & 2005
platforms using the most efficient driver for client connections.

Second, although I'm concerned about server-side performance, that
wasn't the issue at hand. Since I need to support both 2000 and 2005
running mostly the same queries, I wanted to know what was the
recommended client side driver to use for an application that needs to
support both platforms.

Finally, we aren't talking about microseconds here. Our application
frequently runs complex queries against tables involving more than
10,000,000 rows. Adding memory or buying a new computer is not a
decision we make. We leave that up the hundreds of users purchasing
the software.

Mark
Atlanta GA

Ah, now we are beginning to see a little clearer into your real problem. In
your original post, you have expressed the point that your main concern was
to only get the best performance possible in all cases; even if this was to
give you some extra complication in the setup process but now, you are
speaking more about the possibility of installation problems because of the
need to support hundreds of customers, with many different versions of
Windows and SPs.

On the blog for the SQL Native Client (SNAC), you will find a lot of useful
information about its differences with the other ODBC or OLEDB drivers for
SQL-Server and its support for various OS:

http://blogs.msdn.com/sqlnativeclient/

The problem with the SNAC is that you will probably have to distribute it
with your installation setup (or at least, provide the link to download it).
However, its biggest advantage is that unlike the MDAC, not only that you
will be sure to not have any problem because of a difference of version of
the installed MDAC but also, you can also be sure (up to 100%) that you
won't break anything on the user's machine; something that you cannot say if
you take the precaution (?) of installing the latest available version of
MDAC on the client's machines.

Probably that there are still a lot of clients around here with older
machines and versions of MDAC (I still have some clients with Access 97).
They won't be happy if you break something on their machine by installing
the most recent version of MDAC (or worse, an older version). However, by
installing the SNAC (if they don't have it), you can be sure - probably with
100% of certainty - that you won't mess anything on their machine and even
more, you can even garantee that to your clients.

It will also be easier to upgrade your application in the future with new
functionalities if you start by using the SNAC right now. If you start with
a mix of MDAC (many versions) and SNAC (version 9) from the beginning, it
won't be so easy.

My opinion: your application is a new application and whenever it's
possible, a new application should always use the lasted driver if it can do
so.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"John Bigbooty" <BOGUSevileye0702BOGUS@bellsouth.netBOGUS> wrote in message
news:480dad59.7866937@news1.newscene.com...
> On Sun, 20 Apr 2008 18:38:08 -0400, "Sylvain Lafontaine" <sylvain aei
> ca (fill the blanks, no spam please)> wrote:
>
>>You are over-thinking here. First, you should think about finishing your
>>application instead of trying to *optimize* it when it's not even
>>finished.
>>
>>Second, trying to evaluate the relative performance of different drivers
>>is
>>a much more difficult task than you think. The results that you are now
>>getting could be totally different on another system or on the same system
>>but with a different level of load or of pattern of usage.
>>
>>Third, even if you could evaluate some difference between their
>>performance,
>>there are things more important than that: first, the real load on a
>>sql-server is made by executing queries, not by returning the final result
>>to the client and second, the added functionality and security provided by
>>the latest driver if probably much more important than the possibility of
>>saving a few microseconds here and there.
>>
>>Finally, if you absolutely want to save a few microseconds, add more
>>memory
>>or wait a few weeks before buying your next computer.
>
> Was there some reason you felt compelled to reply and act like a total
> ass? Before you go making blanket assumptions about me or my
> application perhaps you should look in the mirror and ask yourself "am
> I really as high and mighty as I think I am?"
>
> First, my application is basically complete. We are just finishing the
> final details and making sure we support both the SQL 2000 & 2005
> platforms using the most efficient driver for client connections.
>
> Second, although I'm concerned about server-side performance, that
> wasn't the issue at hand. Since I need to support both 2000 and 2005
> running mostly the same queries, I wanted to know what was the
> recommended client side driver to use for an application that needs to
> support both platforms.
>
> Finally, we aren't talking about microseconds here. Our application
> frequently runs complex queries against tables involving more than
> 10,000,000 rows. Adding memory or buying a new computer is not a
> decision we make. We leave that up the hundreds of users purchasing
> the software.
>
> Mark
> Atlanta GA
>
>

Thanks for the information you provided, especially the blog link.
Sorry if I wasn't clear in my original post. Performance is always an
issue but I certainly understand that there are tradeoffs when dealing
with a multiple configuration environment.

I'd love to go SNAC only. I just need to be certain that using it
won't cause our customers using SQL Server 2000 to take a performance
hit. Guess I'll put some people to work benchmarking that scenario.

Thanks again
Mark
Atlanta GA




On Mon, 21 Apr 2008 15:42:12 -0400, "Sylvain Lafontaine" <sylvain aei
ca (fill the blanks, no spam please)> wrote:

>Ah, now we are beginning to see a little clearer into your real problem. In
>your original post, you have expressed the point that your main concern was
>to only get the best performance possible in all cases; even if this was to
>give you some extra complication in the setup process but now, you are
>speaking more about the possibility of installation problems because of the
>need to support hundreds of customers, with many different versions of
>Windows and SPs.
>
>On the blog for the SQL Native Client (SNAC), you will find a lot of useful
>information about its differences with the other ODBC or OLEDB drivers for
>SQL-Server and its support for various OS:
>
>http://blogs.msdn.com/sqlnativeclient/
>
>The problem with the SNAC is that you will probably have to distribute it
>with your installation setup (or at least, provide the link to download it).
>However, its biggest advantage is that unlike the MDAC, not only that you
>will be sure to not have any problem because of a difference of version of
>the installed MDAC but also, you can also be sure (up to 100%) that you
>won't break anything on the user's machine; something that you cannot say if
>you take the precaution (?) of installing the latest available version of
>MDAC on the client's machines.
>
>Probably that there are still a lot of clients around here with older
>machines and versions of MDAC (I still have some clients with Access 97).
>They won't be happy if you break something on their machine by installing
>the most recent version of MDAC (or worse, an older version). However, by
>installing the SNAC (if they don't have it), you can be sure - probably with
>100% of certainty - that you won't mess anything on their machine and even
>more, you can even garantee that to your clients.
>
>It will also be easier to upgrade your application in the future with new
>functionalities if you start by using the SNAC right now. If you start with
>a mix of MDAC (many versions) and SNAC (version 9) from the beginning, it
>won't be so easy.
>
>My opinion: your application is a new application and whenever it's
>possible, a new application should always use the lasted driver if it can do
>so.