The WebWeekly I sent out on June 23, 1999 discussed just how one should connect to a database to acheive maximum performance. In that article I said that folks should use a System DSN over a DSN-less connection. I feel like our President, because I now must admit that I was wrong. However, let me assure you my incorrect statements were not my fault (now I really sound like a politician!). Before I delve into my miscarriage of communication, let's take a step back and see how this whole thing started.
The date was June 21, although at 60 degrees and raining here in sunny Seattle it felt like March. I had just read an email from a 4Guys visitor who was wondering what type of database connection approach to use. This confused surfer had just read an article on another ASP web site that detailed the various types of database connections, and this poor soul wondered if he should use a DSN-less connection or a System DSN connection. A good question. A very good question.
I didn't know the answer. I usually use System DSNs, but does it really matter at all what approach a developer chooses? I decided to find the answer to my question, so onto the net I jumped and searched extensively for articles comparing the two database connection approaches.
I soon found an article titled Maximizing the Performance of Your Active Server Pages on Microsoft's MSDN site. In this article, under the ASP Tips and Top 10 List, Tip #4 read:
Use system DSNs, not file DSNs or DSN-less DSNs
So, figuring Microsoft knew what it was talking about, I authored a WebWeekly encouraging developers to use System DSNs to maximize the performance of their database connections. Shortly after sending out the WebWeekly I received a number of emails from readers who had previously read that DSN-less connections were more efficient, and they referenced various Wrox titles as their sources.
Hmmmm... so who was right, Microsoft or Wrox?
I checked the references given to me (Wrox's ADO 2.0 Programmer's Reference) and found pages of performance tests done with System DSN and DSN-less connections. There were fanciful charts and some good information showing that DSN-less connections were superior to System DSN connections. A lot more convincing that Microsoft's one sentence claiming System DSNs were the way to go. I decided that it was time to take advantage of the fact that I am interning at Microsoft, and contacted the gentleman who's presentation was the reference for the performance article on MSDN that I had referenced in the WebWeekly article.
I soon received a reply, informing me that the tests he ran to come to the "System DSN is better" conclusion were run a long time ago, and that they needed to be rerun. He directed me to another developer who was working on running such tests. That developer directed me to another developer, and then to another. I was becoming frustrated, but I was determined to find an answer. After being referred to a number of developers here, I finally was shown some concrete data on tests that were run in June, '99.
These tests showed that DSN-less connections were slightly faster than System DSN connections. The increase in performance was nothing monumental; the greatest performance boost was a mere 13% faster with 64 concurrent requests. For one, two, or four concurrent requests, there was virtually no performance improvement. In fact, no noticeable improvement is seen in a DSN-less connection over a System DSN until there are 10 or more concurrent connections.
I can hear what you're saying: "Great, but why!?" Well, the reason is that when ADO attempts to connect to a database using a System DSN is must perform a lookup in the registry. This lookup, while not overly expensive, does add up, especially if there are many concurrent connections.
So what do these results mean? If you expect a high concurrent load on your server, a DSN-less connection will outperform a System DSN. If you run a smaller site, and only get a few hundred hits a day, you're not going to see a difference between the System DSN and DSN-less connection, so do whatever floats your boat.
OK, now that I've spent all this time discussing why to use a DSN-less connection, I'm now going to tell you not to use one. Rather, connect directly through the OLEDB layer. Hehe, I bet I have you really confused now! Don't worry, there is a great article explaining what OLEDB is and how to connect through it. Go on, read the article!
From alert 4Guys reader Donovan B.|
Here, you point out how DSN-less connections are faster than System DSN connections because DSN-less avoids doing a registry lookup. This is true; furthremore you can receive additional performance benefits by directly using the OLEDB layer
Here is an example:
I have noticed rather large performance gains, both in server resources and browsing speed on the client with just this change in the code.