[thelist] SQL & Access database Speed

[Ken Schaefer]

There are quite a few things you need to consider here:

a) Speed -vs- scalability. SQL Server is generally faster (and no slower)
than Access, but SQL Server is also much more scalable. This means that you
can execute many more queries simultaneously with SQL Server than with
Access. Many new devs will start out by testing an app on their own dev
machine, but fail to test the app under load.

b) Speed - SQL Server has a query optimiser built in. This is an intelligent
engine that examines incoming queries and determines the best way to deliver
the results. It might use an index, it might perform a join, it might do a
table scan. Access has nothing like this

c) Do not use 4 connection objects. When programming ADO (or directly against
OLEDB) you absolutely must understand OLEDB Resource Pooling (also commonly
referred to as Connection Pooling) to be able to get scalable code. OLEDB
maintains a pool of persistent physical connections. When you do Set objConn
= Server.CreateObject("ADODB.Connection") you are merely handed a stub. Only
when you call .Open is a connection given to you from the pool. When you
dispose of the connection the connection is returned to the pool. So you want
to ensure that you are pooling connections from the pool, and you want to
ensure that connections are being returned to the pool.

The following article is the best one on connection pooling I have seen:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/
pooling2.asp
I *strongly* urge you to read it. There's a lot of really good information
there.

The following is worth reading as well:
INFO: Connection Pool Management by ADO Objects Called From ASP
http://support.microsoft.com/?id=191572

d) I'm not sure what you mean by 4 SQL statements (I haven't looked through
your existing SQL statement), but if you going to be opening recordsets
within a loop, do not do this. Use a JOIN instead (as you are probably
doing). If you need to return 4 distinct sets of information that are not
related to each other, then by all means use 4 SQL statements.

Cheers
Ken

: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of j s
: Subject: [thelist] SQL & Access database Speed
: 
: Hello,
: 
: I have another question.  I'm working with an Access database and asp.  I
: was kind of thinking it was faster if you have 1 giant sql statement with
: 1 adoconn.  But would it be faster if I broke it down to 4 sql-s and 4
: adoconn-s?
: 
: Jess