Performance question: Stored Procedures vs. Recordsets

NoVA101 · May 21st, 2009, 12:21 PM

I don't know if this is an appropriate place to ask this, but here goes:

I read this whole book now, and I am very surprised there is no real mention of using Stored Procedures when using SQL.

The last time I built a web app was with classic ASP and SQL 7, and the slowest part of the app was the SQL stuff. And Classic ASP allowed you to bring a Recordset in and manipulate the data in VBScript, and output the Presentation Layer using that Recordset, etc -- all the while the connection to SQL remained opened. This killed performance!

So what I did was absolutely 100% of all calls to SQL were done to Stored Procedures, which then did return a Recordset object in VBScript, BUT... then what I did was called .GetRows() to copy the data into an Array, then immediately destroy the Recordset, closing the connection to SQL. Then I built the Presentation Layer by iterating through the array.

This massively improved the performance. AND I optimized the execution plans for most Stored Procedures. The final result was an extremely fast and high-performance commercial web application that can support thousands of simultaneous users, and is still running today.

So I am surprised to see what ASP.NET 3.5 seems to be doing -- the way you put it is that SQLDataSource and LINQDataSource send actual SQL statements to the SQL server, rather than calling Stored Procedures. And also it seems as if the data-bound controls are using the data that is returned from SQL (Recordsets??) and maybe even leaving the connection to the database open the whole time while building the presentation layer?? If so, then Ug!

I also looked through the Wrox Professional ASP.NET 3.5 book and there is a brief mention that you can call Stored Procedures, but no mention as to optimizing your web app's performance, especially as related to SQL.

All seems a bit strange to me, just wondering if you had any thoughts. Is there something going on in .NET 3.5 that massively improves performance even though SQL statements are being sent to the database? In your examples you seem to just toss new connections to the database onto pages willy nilly, with no consideration as to what that might do to performance. Would you do something different on a commercial web app? Is there Wrox book that goes over optimizing the performance of your ASP.NET 3.5 web app??

Thank you very much... oh and BTW... great book!!

Imar · May 24th, 2009, 10:41 AM

Hi there,

A couple of notes on what you said:

1. I didn't discuss stored procedures as they're too advanced in my opinion for a true beginners book. Many other books discuss them and I personally use them a lot.

2. In-line SQL isn't necessarily slower that using a stored procedure. Both query types can be compiled and can result in the same performance. There's a lot of debate about this, but generally it turns out there's no noticeable performance difference. Clearly, stored procedures are often easier to maintain than in-line SQL statements, but that's a different discussion.

3. Results sets / recordsets aren't really related to the discussion about in-line SQL versus stored procedures. Whatever you pass to SQL Server (inline or a procedure) results in the same type of result set being returned. That is, there's no difference (performance-wise or anything else) between a sproc and in-line SQL from the client's perspective.

4. The fastest way to get data out of SQL Server is by using a SqlDataReader. Some of the data controls understand how to work with it directly. It's what they use internally to build up the UI.

5. If you want disconnected data to do slow UI stuff, look into DataSets which are disconnected from the datasource and require no open connection.

6. LINQ to SQL has some smart optimization and caching mechanisms, often resulting in excellent performance.

7. All my LINQ examples in the book wrap the DataContext in a using block. This ensures the connection is closed when it's no longer needed.

Hope this sheds some light on some of your concerns. Yes, taking care of good query code and connections are still important. However, I often find that writing proper select statements, placing the right indexes on the right tables or employing proper client caching and paging often offers a much better improvement on performance that worrying about looping (relatively small) results sets with an open connection. After all, GetRows or whatever you used, needed to loop through the result set as well.

Cheers,

Imar