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

updated.

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!