Speed of Stored Procedures?

[wassup]

Speed of Stored Procedures

I am running ASP pages using IIS4 with the data in SQL Server 7

I currently have an asp script to login to the site. The script checks the user name and password are correct using an SQL select statement on the database

Would it be faster to run if I wrote a stored procedure to validate the login?

In general is it quicker to use a Stored Procedure than running ADO with an SQL query string?

 

[jnicho02]

I believe that stored procedures will generally be quicker as they are pre-compiled. My home ---->

http://www.firstlightassociates.co.uk/

visit me for Java and Data Warehousing resources

 

[foxdev]

Ditto jnicho02. The difference with a simple statement and small tables may not be noticeable, but you get the added bonus of compartmentalizing the security (you can give the IIS user access to the SP, but revoke access to the related tables). Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[djohns]

I also agree. Even though the inline SQL statements executed through ADO are ran at the SQL server, you will be missing out on some performance gains of a stored procedure. This is especially true if the table(s) you are searching through are large.

A stored procedure is compiled as mentioned before and during this an execution plan is stored.

When using inline SQL select statements, SQL Server will have to analyze it and create an execution plan on the fly.

 

[RickJ]

I'm fairly new to SQL Server and haven't noticed any performance gains by using stored procedures. (I'm using version 7.0)

The following quote from the books on line seems to back that up, if I'm reading it correctly:

"SQL Server version 7.0 introduces a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements.

SQL Server 7.0 does not save a partially compiled plan for stored procedures when they are created.

A stored procedure is compiled at execution time like any other Transact-SQL statement.

SQL Server 7.0 retains execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.

It uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans.

If SQL Server 7.0 determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan.

This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements."

 

[foxdev]

Interesting reading (the BOL quotes that SPs have lost some of their performance advantages).

Also from BOL:
Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server.

I read the above as: if you have more than one statement to be executed, especially if some statements are executed based on conditional (IF) logic, then SPs still have the advantage.

From Inside SQL Server 7 (Soukup/Delaney) comes this:

You should try using stored procedures whenever possible, rather than passing ad hoc SQL statements from your applications. (...) The picture has changed somewhat in SQL Server 7. The query optimizer now has many more processing options, and compilation and optimization time can end up being a more significant percentage of total execution time. It is even more crucial now that you know when recompilation will take place and try to avoid it, if at all possible.


a subsequent execution of the stored procedure can skip not only Step 1, parsing, but also Step 2, compiling, and go directly to Step 3, execution. Steps 1 and 2 always add some overhead and can sometimes be as costly as actually executing the commands. Obviously, if you can eliminate the first two steps in a three-step process, you've done well. That's what stored procedures let you do.
Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[markinro]

I have a question...if I have a stored procedure which does not take any parameters, would I be better off using a view ?

 

[JamesLean]

If your SP is a simple select statement that you want to SELECT FROM then yes, that's what views are for. However, if you do ANY other processing before returning the resultset then you can't do that in a view.

In SQL 2000 you also have the ability to create table-valued functions which can do processing like a stored proc but can be referenced in a FROM clause like a view.

--James