• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

Files DSN vs. System DSN, faster?

The title is probably pretty self explanatory, I have a site based on an online database (ASP, obviously, used to access an Access .mdb) and need to know whether I am better off doing a File DSN or a system DSN, is there a big difference in terms of speed?  I am really hoping for a clear-cut answer, but I know that usually doesn't happen ;)

btw, this site has gotten upwards of 2500 visitors a day, usually its around 1500 though; and I don't think I've had more than 100 simultaneous connections, if that helps.

thank you,
1 Solution
Auerelio VasquezETL DeveloperCommented:
A file dsn is visible to anyone who logs onto the machine, if your using windowns NT. a system dsn is visible only to the creator, there is not much difference in speed that i have noticed. i belive that's the only difference
Auerelio VasquezETL DeveloperCommented:
A file dsn is visible to anyone who logs onto the machine, if your using windowns NT. a system dsn is visible only to the creator, there is not much difference in speed that i have noticed. i belive that's the only difference
FileDSN = Database information stored in a file.
SystemDSN = Database information stored using Control Panel (ODBC Data Sources).
DSNLess connection = Database information stored in a connection string in an ASP.

You will get the best performance if you use a DSNLess connection.  However, if you have to use File or System DSN, then SystemDSN will be faster.  FileDSN will require your script to locate a file on the server and retrieve information (not efficient).

Hope that helps
A text from www.4guysfromrolla.com.
Sorry, probably it is not allowed to publish their materials, but if I link their community to them?
Here it goes, search www.4guysfromrolla.com for it, since there are some links in the text, I could not paste;

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.

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!

Happy Programming!
BegemotAuthor Commented:
One last question though: should I take into account only the requests for this particular database (i.e. my site) or the overall number of requests that server is getting (i.e. for all the sites that IHP is hosting on that machine), would the latter contribute to the performance difference?  After all 13% is nothing to joke about....

thank you

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now