Most programmers eventually encounter performance tuning issues with their Web applications. That's especially true for very large Web applications that do intense CPU processing along with large SQL queries traversing thousands or millions of records. Increased processor time increases response time, making your application less available to users at peak usage times.

I've experienced this situation personally, and I am here to tell you how valuable stored procedures are. They decrease CPU cycles significantly, when executing SQL queries, and they give your application a huge performance boost. Because stored procedures are already pre-compiled and cached, they're much faster to execute, with better memory management than in-line SQL queries.

Stored procedures do have a drawback. Only basic loops and conditions are supported, when you use T-SQL to code stored procedures at the database server level. You can't do complex nesting, or anything really fancy, as you can with a high-level language such as C#. Microsoft intends to change this with SQL Server 2005; T-SQL will become as powerful as any high level language, because it will support all the .NET languages! But, as with any new feature, you need to learn how to use it.

In this article, I show you how to use stored procedures for some common database operations using C# and ADO.NET. By learning how ADO.NET works with stored procedures, you'll get a better handle on ways to improve your applications using SQL Server 2000 today, and later with SQL Server 2005.