| Somewhat Confused, Aren Asks: Why would I want to use a manual DSN? Why would I want to use ODBC? In which
cases is one better than the other? Stephen Clarifies: DSNs are part of ODBC. ODBC is a set of APIs for database access. One
method of using ODBC requires a Data Source Name. Another method is a
DSN-less connection which uses a text connection string and does not require
a DSN. There is no such thing as a "manual DSN." There are two types of
DSNs--User and System. User DSNs are only available to the user context
that created them (and, thus, not much use for ASP). System DSNs are
available system-wide. The other method you have for database access is OLE DB (although,
technically, if you are using ADO, even when you use ODBC you are invoking
OLE DB to get there). OLE DB uses a connection string that looks a lot like
a DSN-less connection under ODBC. So, with that in mind, what exactly do you want to know? When should you
use a System DSN? When should you use a DSN-less connection? When should
you use OLE DB?
Michael C. Inquires Further: I recently put an ASP site on Brinkster (the free version) and had to
convert my ODBC DSN (for MS Access) connection strings to DSN-less, since
their free hosting service doesn't support ODBC. I am not dead certain, but
I think the DSN-less version is slower. It is hard to be sure, since up to
this point I was developing it on my own machine using PWS, and of course
this is going to be very responsive. So my supplemental question to the thread is, is using a DSN inherently
faster than going DSN-less? (sounds like skinny-dipping, somehow)
Michael R. Replies: If access, use the ole-db jet provider, not the odbc connector for access,
and it should performa bit better. OLD-DB DSN-Less is the fastest, but
odbc offers connection pooling, which lowers connect times.
Magnus Shares His Own Practices: Rule number one on optimization for speed:
Don't optimize for speed until the development process is done. Rule number two (which is a consequense of rule #1):
Don't optimize. The code looks better with ODBC connections, therefore I use it :) Also, when migrating your Access DB to an MS SQL-Server located on another
machine, you'll se real performance-benefits from using ODBC, because of
connection pooling.
David Rebuts: Has anyone read this: http://msdn.microsoft.com/library/techart/pooling2.htm Magnus, could you explain how your code "looks better" with ODBC
connections? If you have connection strings in more than one place (i.e.
not using Application variables or global constant), I wouldn't want to do a
global replace on that. Yes, I know you *can* do that, but that is not the
best way around it.
Anders Provides His Own Rules: Rule #1: Always optimize your code, because you'll never get back to it
later. The famous "if it ain't broken, don't fix it" mentality strikes
again. Rule #2: Keep learning how to optimize your code by reading long threads on
this list (some started by me), on important issues such as string
concatenation and the like. (See Multiple Response.Writes vs. String Concatenation.) Optimization is king!
Magnus Defends His Statements: Optimizing code mostly has a degenerating effect on code. My example below
doesn't really make sense though, as D. Penton pointed out.
I'm usually not a fan of slow websites, but I'm even less a fan of
non-standard code. A program writte without any function calls could perform a bit faster than
a program written with generalised functions. The later however saves time and money, and is therefore preferred.
Of course destroying sessions when appropriate, and always destroying
objects should be done, but that's not a matter of optimization, that's a
matter of writing robust code. My philosophy is to always write easy-to-write and easy-to-use/reuse code,
not fast code. When the two can be combined, i'm happy, when not I buy better hardware :)
Anders Adds Hardware to the Mix: You should look into the new Tyan Thunder - Dual AthlonMP (Palomino chips)
at 1.2Ghz, kicking ass to a Dual Xeon 1.7Ghz system. The database
performance is about 30% better, and that using a chip which is 500Mhz
slower. I'm building such a system for my own workstation, putting the old
dual PII 350Mhz server in the closet, doing .. server stuff, uhm ..
Michael R. Adds His Rules to the Discussion: Rule 1: Always optimize to the best of your knowledge.
It's easier to do it now, then to re-do it later. Rule 2: Always take the time to re-do certain methods if you find a better
way. First, using a connection string in an application variable, or global
include works well, only have to create it, modify it once. Second, OLE-DB
is faster than ODBC. Third, if the OLE-DB provider supports it, ADO will
automatically try connection pooling if the connection properties (string)
match. ODBC seems to manage connections a little better in some
environments, but overall performance is better going straight to OLE-DB,
which is what ODBC uses anyway.
Ken Jumps In: Are we still talking about Access/Jet here? Then you really need to do some
research:
http://www.aspalliance.com/PeterJohnson/JetAdvice.asp
http://www.adopenstatic.com/faq/whyOLEDB.asp
are good places to start. Use OLEDB with Access/Jet, not ODBC - I don't care
what your code looks like.
Resources: Here are some more resources on database connection technologies: http://www.mavweb.net/asp-samples/database-connection-strings.asp
http://www.able-consulting.com/ADO_Conn.htm
http://www.asp101.com/tips/index.asp?id=50This conversation string was taken from the 15Seconds ASP Listserv on 6/5/01. If you have an ASP-related question or would like to share some of your knowledge with others, you may join the list by clicking here.
| 
