|
Access Scalability Notes by Charles Carroll
This page demonstrates how many
Access connections can be opened by one script. Testing generally indicates a
single script can only initiate 64 connections. Microsoft Tech Support has
suggested a 255 limit easily disproved by this script. As a point of
reference I ran this same script against a SQL7 data source and attempted 2,550
connections. All were sucessful.
Access has the following limitations:
1. Access is STA (single-threaded-apartment), NOT
MTA or Free-Threaded (see http://www.learnasp.com/advice/threads.asp
for thread basics). What this means is that all database requests are
serialized, i.e.
user1 asks for 100 records
user2 asks for 2 records
user3 asks for 5000 records
user4 asks for 3 records
If an agile back-end (free-threaded or MTA for example SQLserver, Oracle, etc.)
was handling those requests it could send user2 and user4 records before dealing
with other requests. Access cannot. User 4 will receive their data AFTER the
other requests have completed. Requests are handled as they come in and to
completion before next low-level data access starts. So the application's
pattern of retrieval makes a huge difference in whether an Access app will
scale.
2. Since open connections are the issue and
scripts run round-robin, see: http://www.learnasp.com/advice/roundrobin.asp
(see advice about open late/close early) http://www.learnasp.com/advice/whybuffer.asp
http://www.learnasp.com/advice/whygetrows.asp
are EXTREMELY relevant in this case.
3. Access unfortunately does not support
connection pooling which can dramatically improve performance, see:
http://www.learnasp.com/freebook/asp/dbpooling.aspx
4. The free answer to this problem is MSDE, a
chopped down version of SQLserver that Microsoft gives away which can handle
much more load, see:
http://msdn.microsoft.com/vstudio/msde/
Additional Note: ODBC connections are very flaky producing a
variety of run-time error messages that OLEDB connections do not. This
sample uses an OLEDB connection hence it's "microsoft.jet.oledb...."
vs. a connection string that contains "{microsoft access...}". The
latter connection string/ODBC connection is far less reliable. See:
http://www.learnasp.com/freebook/asp/dbopen.aspx
for more connection string info. For example:
[Microsoft][ODBC Microsoft Access Driver]General error
Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xda8
Thread 0x49c DBC 0x254d024 Jet'. occurs when connecting to data with
"{microsoft access..." connection string whereas exactly the same data
can be accessed with "Jet.oledb" string fine.
filename=/learn/test/accesstest.asp
<HTML><HEAD><TITLE>accesstest.asp</TITLE></HEAD>
<body bgcolor="#FFFFFF">
<%
on error resume next
myconn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("nwind.mdb") & ";"
' the above string is recommended way to connect
' myconn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & server.mappath("nwind.mdb") & ";"
' the above connect string is VERY flaky
dim test(256)
dim testRS(256)
dim errn(256)
dim errd(256)
dim errrs(256)
maxTests=256
rsErrorCount=0
response.write "<hr>Staring Access Scalability Test<br>"
response.flush
debugThisScript=False
for counter=1 to maxtests
set test(counter)=server.createobject("adodb.connection")
set conntemp=server.createobject("adodb.connection")
set test(counter)=conntemp
IF debugThisScript=True THEN
response.write "cn-"
response.flush
END IF
goodconn=true
goodRS=true
vbsError=0
errorcount=0
conntemp.open myconn
' have to test error here because if conn cant be created
' the errcount... line will fail
vbserror=err.number
errcount=conntemp.errors.count
IF errcount>0 then
DBErrNum=conntemp.errors(0).number
DBErrDes=conntemp.errors(0).description
errn(counter)=DBErrNum
errd(counter)="ADO: " & DBErrDes
goodconn=false
IF debugThisScript=True THEN
response.write "cADOErr-"
response.flush
END IF
ELSE
IF debugThisScript=True THEN
response.write "op-"
response.flush
END IF
END IF
' VBScript Error Trap
IF vbserror<>0 THEN
errn(counter)=vbserror
errd(counter)="VBS: " & err.description
goodconn=false
IF debugThisScript=True THEN
response.write "cVBSErr-"
response.flush
END IF
END IF
If goodconn=true THEN
ConnCounter=ConnCounter+1
errn(counter)=0
success=success+1
vbserror=0
errcount=0
set tempRS=server.createobject("adodb.recordset")
set tempRS=conntemp.execute("select categoryID,CategoryName,Description from Categories")
' only a dozen records or less
vbserror=err.number
errcount=conntemp.errors.count
IF vbserror<>0 THEN
errRS(counter)="VBS Recordset Error: " & err.description & "<br>"
goodRS=false
IF debugThisScript=True THEN
response.write "rVBSErr-"
response.flush
END IF
ELSE
'errRS(counter)=""
END IF
' check for ADO error
ADOErrorCount=conntemp.errors.count
tenpADOerr=""
IF ADOErrorCount>0 THEN
response.write "rADOErr-"
response.flush
rsErrorCount=rsErrorCount+1
for counterADO=0 to ADOErrorCount-1
ADOerrornum=conntemp.errors(counterADO).number
ADOerrordesc=conntemp.errors(counterADO).description
tempADOerr=tempADOerr & "#" & ADOErrornum & " =>" & ADOErrorDesc
next
errRS(counter)=errRS(counter) & tempADOerr
goodRS=false
ELSE
IF debugThisScript=True THEN
response.write "ro-<br>Data Start:<br>"
response.flush
END IF
errRS(counter)=""
DO WHILE not tempRS.EOF
IF debugThisScript=True THEN
response.write tempRS(0) & ","
response.write tempRS(1) & ","
response.write tempRS(2) & "<br>"
END IF
Cellcounter=CellCounter+1
tempRS.movenext
LOOP
TempRS.Close
IF debugThisScript=True THEN
response.write "Data End<hr>"
END IF
response.flush
RSCounter=RSCounter+1
END IF
else
'exit for
'response.write "<br>"
END IF
IF response.isclientconnected()=false THEN
exit for
ELSE
end if
'conntemp.close()
'set conntemp=null
'response.write " *** "
'response.flush
NEXT
response.write "<hr>Closing connections and recordsets now<br>"
set RsTemp=nothing
for counter=1 to maxtests
test(counter).close
set test(counter)=nothing
testRS(counter).close
set testRS(counter)=nothing
next
response.write "<hr>Succeeded creating " & Conncounter & " Connections<br>"
response.write "<hr>Succeeded creating " & RScounter & " Recordsets<br>"
response.write "<hr>" & CellCounter & " Cells (rows x columns) Fetched<br>"
response.write "<hr>Closed all connection/recordsets<br>"
' Print Out Errors
errorcountCon=0
errorcountRS=0
for counter = 1 To maxtests
errnum=errn(counter)
IF errnum<>0 THEN
tempError=errd(counter)
If tempError="VBS: Unspecified error" THEN
unspecifiedErrCounter=unspecifiedErrCounter+1
Else
response.write "#" & counter & " connections failed:"
response.write TempError & "<br>"
END IF
errorcountCon=errorcounterCon+1
END IF
errRSDescription=errRS(counter)
If errRSDescription<>"" THEN
response.write "#" & counter & " recordset had error!"
response.write errRSDescription & "<br>"
errorcountRS=errorcountRS+1
END IF
If errorcoount=1 THEN
response.write ("<hr>Error List<br>")
END IF
next
If errorcountCon=0 THEN
response.write ("<hr>No Connection Errors!<br>")
end if
If errorcountRS=0 THEN
response.write ("<hr>No Recordset Errors!<br>")
end if
If unspecifiedErrCounter>0 THEN
response.write ("<hr>" & unspecifiedErrCounter & " Unspecified Errors opening connections!<br>")
END IF
%>
</BODY></HTML>
|