Stored procedures
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=7942
Printed Date: 30 March 2026 at 12:16pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Stored procedures
Posted By: Gullanian
Subject: Stored procedures
Date Posted: 08 December 2003 at 12:07pm
| Just reading up on SP's, cant find the figures on google but does anyone know if it really is worth executing all queries as stored procedures, and how much power does it save? |
Replies:
Posted By: fernan82
Date Posted: 08 December 2003 at 5:55pm
|
If you just copy your queries to a stored procedure it don't really
makes a noticeable difference in performance. On a really comple query,
like for example if you got an advanced search query on access you
might have to go thru all the records and do your searching with ASP
while on SQL you can do all that on the server and have the stored
procedure just return the results, in a situation like that it makes a
huge difference, specially with ASP which is a rather slow scripting
language.
------------- FeRnAN http://www.danasoft.com/"> 
|
Posted By: Gullanian
Date Posted: 08 December 2003 at 5:56pm
| Ok cool, but even if the change is tiny, the simple queries will still run faster in stored procedures? |
Posted By: fernan82
Date Posted: 08 December 2003 at 10:33pm
|
Depending on the queries and the size of the site it might cuz it
reduces the traffic between the web server and the sql server, like for
example if you got a simple select query but quite long like sql = "SELECT tbl1.Field1, tbl1.Field2, tbl2.Field1, tbl2.Field2 FROM tbl1 INNER JOIN tbl2 ON blah, blah, blah, WHERE tbl1.Field1 = " & i & ";" All that text has to go from the web server to the sql server while with the stored procedure only the name of the stored procedure and the value for i would have to travel to the server. On most cases it won't make a difference but on a real busy site or a remote sql server it will make a difference. The other advantage of stored procedures is security as you don't have to worry about sql injections much when you use stored procedures, so it makes it easier for the coder, ASP don't have type defined variables so you can't define i as integer, etc. so you need to do a lot of checking to make sure that the user didn't changed like when they're posted on a form or on the querystring, etc. With sp's you don't have to worry that much about that but it's always a good practice still. ------------- FeRnAN http://www.danasoft.com/">
|
Posted By: michael
Date Posted: 09 December 2003 at 4:24pm
|
The traffic between web and sql server does not make a difference but the advantage of SP's are that they are already compiled so it can be executed directly as opposed to a normal sql query, it would have to be compiled before executing ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: ljamal
Date Posted: 09 December 2003 at 4:53pm
|
The biggest advantage with the stored procedures and SQL Server is the ability to return multiple recordsets ------------- L. Jamal Walton http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming |
Posted By: psycotik
Date Posted: 12 December 2003 at 10:45am
|
Stored procedures are cached on the server. If you frequently use a stored proc it will execute faster. Also it makes code read alot easier, rather than having 20-30 lines of an asp-generated sql string, you just have the proc name + parameters and can interpret it by sight alot easier. One other positive use is it allows sql code to be removed from the page. ASP should really be display-only as much as possible. If you repeat the same code 5-10 times in the site and need to change something it it's hard. If you have the code in a stored proc, you change the procedure and the rest of the site is updated automatically. |