When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
Technology Jobs
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
Print this page.
Published: Saturday, July 03, 1999

System DSN or DSN-less Connection?


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.

- continued -

'

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.

The results...
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:

  Dim Conn, dbPath
  dbPath = "d:\myaccessfile.mdb"
  Set Conn = Server.CreateObject("ADODB.Connection")
  Conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath

I have noticed rather large performance gains, both in server resources and browsing speed on the client with just this change in the code.

Happy Programming!

Software Developer / Programmer - Distributed Systems (NYC)
Next Step Systems
US-NY-New York

Justtechjobs.com Post A Job | Post A Resume


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article