trying to understand ms sql/asp performance

[bbolte]

ok, here goes. i've got an asp script that makes three calls to the db, no records are returned, just 2 insert and delete sql statement. the only thing returning is an @@identity for the second insert. would combining these into 1 or maybe 2 stored procs actually benefit performance? do i really save any server resources by building one big stored proc? aside from modularizing my application, what do i gain?

[cmorbutts]

Generally, you do get better performance if you use a stored proc as opposed to several calls to the database.

Stored procs are processed on the database server. Doing it your current way, you will have to grab the results, then let your ASP script figure out what to do and send another call to MSSQL. If you use a stored proc, all the work is left to MSSQL, which generally process database stuff faster since it's a database server.

However, if you're doing something simple like a "SELECT user_id FROM tbl_Users", the performance gains you get from using a stored proc is negligable.

In most of my projects, if the client knows that MSSQL is the database server of choice, I tend to whack everything into stored procs. Especially in .NET this makes life easier. You don't have to rebuild your solution when you make a change.

There is one more thing you might want to consider before using stored procs. Is your web application sticking with MSSQL Server forever? Is there a possibility that you might want to change it to Oracle of MySQL later on? If you whack all your SQL into stored procs, you'll practically have to rewrite all your database code when you change databases.

According to the J2EE specification, Sun recommends that standard SQL be used directly in code instead of employing stored procs. So you are free to change databases without code changes.

However, why would you shell out for an expensive Oracle solution and not use any of its cool features that are non-standard?