Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

IIS Connection Pooling ? ADO Gotcha!

By Leon Platt,

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!
 

Total article views: 7454 | Views in the last 30 days: 5
 
Related Articles
FORUM

Update problem

Update problem

FORUM

Update Problem

Update Problem

FORUM

question about update record

question about update record

FORUM

UPDATE statement that creates duplicate records

Problem with an UPDATE statement that creates duplicate records

FORUM

SSIS Package Updating Records Incorrectly

SSIS Package Updating Records Incorrectly

Tags
ado    
asp    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones