I ran across a perplexing problem last week while debugging an ASP web
application. I’m not talking about your everyday run of the mill, oops I
forgot to consider that type of error. I’m talking about the debug four
hours, bang your head against the wall, debug four hours, bang your head and
repeat for three days type of error. If you write ASP applications you’ll
definitely want to read this article. For those developers out there who
are bald or balding all ready there's not a whole lot I can do for you, but for
those developers out there that still have a full head of hair, maybe I can
prevent one or two of you from pulling it out when you run across the same
situation. I don’t know if I’d consider the problem a bug or just a
consequence of connection pooling, but either way its something you should keep
in the back of your mind while developing you web application (especially if you
or any or your developers are using the MaxRecords property of the ADO
RecordSet). Let me set the stage for this ADO Gotcha.
Being an internals type of guy from the old assembly language days, I like to
learn everything there is to know about the code I am writing. When it comes to
ADO, I am fascinated with the behind the scenes operations. I have read several
articles, a book or two and have attended a few seminar presentations on ADO
performance. Thus, I like to implement every little tidbit of info I pick up.
Well, one of the things I have picked up has to do with the RecordSet.MaxRecords
property. According to Microsoft documentation the MaxRecords property
indicates the maximum number of records to return to a RecordSet from a query.
Somewhere along the line, I read that setting the MaxRecords property to 1 for
queries that only return one record gives you a performance gain when
instantiating ADO RecordSets. So being the kind of guy that likes to save
a nanosecond here and there, I figured why not. What could it hurt right?
Wrong…. Let me explain.
First, it helps to know what goes on behind the scenes in SQL. If you run
SQL profiler and trace an application that instantiates a RecordSet and then
sets the MaxRecords property to one, you will see the following SQL command
being executed; “SET ROWCOUNT 1”. Ok, that makes sense if you think about it,
but the first GOTCHA #1 is that the MaxRecords documentation leads you to
believe that it only affects SELECT statements. Wrong, the “SET ROWCOUNT
1” command affects all queries (SELECT, UPDATE, INSERT and DELETE).
Second, it helps to know a little about connection pooling. Connection
pooling is IIS servers way of increasing connection setup and teardown times.
Remember in a web application the ADO connection is created when the page is
interpreted and then destroyed when the page is finished being interpreted.
Since connection setup and teardown is an expensive, time consuming tasks, IIS
server attempts to help us out with connection pooling. Basically, what happens
is that instead of destroying ADO connections, IIS server keeps them around by
placing them in a pool of connections. Then, the next time a user needs a
connection....just like magic its there ready to use, no setup necessary.
If you take a look at this with SQL profiler you will see “connection resets”
happening quite frequently. This is due to a pooled connection.
GOTCHA #2 occurred when the pooled connection I received still had a ROWCOUNT 1
set. I don’t know if this is a bug with connection pooling or what, but I
can vouch that it happened.
By now you probably have a good idea what happened to me. My environment
consisted of SQL server 2000, IIS server version5, Visual Basic version6 and ADO
version2.6. From my ASP pages, I was instantiating an activeX dll to
handle my business logic and data access. A good number of my SELECT
queries involve statements that return one record. Thinking that I would
save myself 10 or 15 nanoseconds, I have numerous occasions where I use the
RecordSet.MaxRecords=1 statement. You would think that when the middle
tier component was destroyed and then re-instantiated in a new ASP page, you
would get a fresh connection object with the default ROWCOUNT 0. In my
case it didn’t happen that way. Unfortunately the problem decided to show
its nasty little self during an UPDATE query. Needless to say, instead of
all the records in this particular table being updated, only the first one was
Just when you’re thinking, well that aint so bad…GOTCHA #3. For some
reason the problem only occurred while using the compiled dll version of my
middle tier component. When I ran the application through the Visual Basic
debugger interactively, everything functioned exactly the way it was supposed
to. I looked pretty bad after the third time of claiming the problem was
solved. Then I figured I'd take the easy way out, "hey it works fine on my
machine the users computer must be screwed up" ...Until I re-compiled and
ran the app using the dll instead of through the debugger. Hmmm, I'm
trapped in one of those now it works now it doesn't scenarios. My
best guess is that IIS handles connection pooling differently while running
interactively in the Visual Basic debugger. Either that or it just doesn't
use connection pooling at all (something to look into).
How did I solve this dilemma? Eventually, I realized that I could not step
through the Visual Basic debugger to solve my problem. I loaded my dll up
with a bunch of debugging code that I could turn on or off. The code
allowed my to log variables and query results to an application log.
Viewing the application log, I could see that the records affected value being
returned from the SQL update was one record, when it should have been multiple
records. Then a funny thing happened, I decided to put a Select Count(*)
statement immediately before my UPDATE so that I could verify how many records
should be updated. Wouldn’t you know it…just putting the SELECT statement
in the code caused my problem to disappear??? At that point, I went to SQL
profiler and traced the SQL that was being executed, and guess what?
Putting the SELECT statement in the code caused an unexplained “SET ROWCOUNT 0”
to be executed. Then when the UPDATE executed everything was fine.
When I removed the SELECT statement, the “SET ROWCOUNT 0” disappeared from the
SQL trace, and only one record updated. A hard lesson learned, and all
over a few nanoseconds. Now, I execute a “SET ROWCOUNT 0” every time I
open a database connection; just to be on the safe side!