Access + ASP = Bad
So here's the dilemma: Windows is still the most widely-used platform out there, and Office holds the lion's share of users, due to the ease of acquisition. Most folks who buy their first machine buy it off the shelf at a retail store or an online retailer like Dell, and it comes with Windows pre-loaded. Since they figure that running a Microsoft product on a Microsoft operating system is probably a good idea, they either go ahead an purchase Office, or again get it pre-loaded. If they splurge for the extras, Access is in the bundle, and they start using it for their database work. When they start doing web programming, they naturally drop an Access .mdb file onto the web server, write their scripts in ASP since it's the easiest thing to run with IIS, and use ADODB within ASP to access the Access file. You can Google a ton of tutorials on how to put drive a web page with an Access database in this manner, which is a testament to the technique's popularity.The problem starts to appear when you start programming more complicated databases, such as multiple tables and multiple-step drill-down queries. If you think your script through procedurally, you tend to come up with a process like:
Find all records of table A, and get foreign key bID that points to ID field of table B.This works fine for small do-it-yourself websites that don't get many page hits, so a lot of web developers who are starting out tend to do it this way (guilty!). Especially if they either don't understand table joins, or would rather not pull repeated data for A in each record. After all, with joins you'll wind up getting data like this:
For each record in A:
"Select * from B where ID=" & A.bID
A.ID, A.stuff, A.otherStuff, A.bID, B.ID, B.stuff, B.moreStuffSo it looks like you're wasting data by getting the A data in each returned record. Whether or not you actually are is determined by the underlying database driver implementation, but that's a story for some other time, and possibly someone who knows more about the guts of databases.
Anywho, as you start building bigger sites that handle more traffic, you suddenly get calls from angry clients who demand to know why it's taking so long for thirty simultaneous requests to retrieve data from their 300MB Access database. At this point you start researching why Access is bad, and you may or may not come to find out the dirty little secret: Access was never intended to serve as the backend to web sites. In fact, it was never even intended to serve any multi-user role. Thus, this shared ADODB stuff is sort of like black magic. Sometimes it works, especially for under ten users and smaller amounts of data. But it doesn't scale well.
There are ways to save the day without having to totally move to a different DBMS. If you cut out those queries that loop through sub-queries, either by converting them to joins or by temporarily caching the sub IDs that you need to look up, then you're in better shape. But sometimes you're dealing with totally unrelated data tables, and you need to perform multiple queries, retain the records, and use them later. The ActiveRecord design pattern, which I'll talk about some other time, takes care of this by allowing you to create an object with data members for each database field, which can also access the database to select/insert/update/delete (CRUD). You can then stick those objects into containers for safe keeping. ASP.Net gives you a workaround with the DataSet class, which creates an in-memory version of the query result set.
But what if you need something that's faster and easier than creating a class, to get you through a pinch, like if it's gotta be done right now, but not necessarily the right way? Enter the Execute() function, which dynamically evaluates and executes a string, as though it were code. Most scripting languages have some means of doing this, so check out your language of choice - it'll usually be named something along the lines of execute, evaluate, or a shorter version like exec, eval, etc. The function takes the string and treats it exactly like real source code, but evaluates it at run time. So for example:
for each f in rs.FieldsThis will loop through each field in a recordset. For each field it sets a variable tvar equal to the field name and value, eg tvar = "ID", tval = 123. Since the value of tvar is "ID", the string being passed into Execute() is "ID=tval", which is the same as doing it in plain source code. The beauty of it is that if you have a lot of fields to account for, you don't have to code it all by hand, like:
tvar = f.name
tval = f.value
Execute(tvar&"=tval")
next
ID=rs("ID")So you get a lot of bang for your buck, especially when dealing with lots of database fields.
stuff=rs("stuff")
moreStuff=rs("moreStuff")
'etc
The most important lesson: when dealing with Access databases through ASP, the most important thing is to make sure that you're closing your ADODB.Connection and nulling it before opening a new connection, and only performing one query per connection. So rather than doing this:
set db=Server.CreateObject("ADODB.Connection")
db.open("DSN=mydb")
set rs=db.execute("SELECT ID, bID from A where x=" & x)
set rs2=db.execute("SELECT * from B where ID=" & rs("bID") )
...
rs.close:set rs=nothing
rs2.close:set rs2=nothing
db.close:set db=nothing
Make sure you do this instead:
set db=Server.CreateObject("ADODB.Connection")
db.open("DSN=mydb")
set rs=db.execute("SELECT ID, bID from A where x=" & x)
bID=rs("bID")
rs.close:set rs=nothing
db.close:set db=nothing
set db=Server.CreateObject("ADODB.Connection")
db.open("DSN=mydb")
set rs=db.execute("SELECT * from B where ID=" & bID )
...
rs.close:set rs=nothing
db.close:set db=nothing
It may seem redundant, even painful, to do so much opening and closing. The pain is an indication that it may, in fact, be time to switch to a more capable scripting system or database management system.
posted by Adam Payne @ 12:20 PM
permalink
0 comments
