| Author |
 Topic Search  Topic Options
|
Gullanian 
Senior Member
Joined: 04 January 2002
Location: England
Status: Offline
Points: 4373
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Stored procedures
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?
|
 |
fernan82
Mod Builder Group
Joined: 17 November 2002
Location: United States
Status: Offline
Points: 362
|
Post Options
Thanks(0)
Quote Reply
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
|
|
Gullanian
Senior Member
Joined: 04 January 2002
Location: England
Status: Offline
Points: 4373
|
Post Options
Thanks(0)
Quote Reply
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?
|
|
fernan82
Mod Builder Group
Joined: 17 November 2002
Location: United States
Status: Offline
Points: 362
|
Post Options
Thanks(0)
Quote Reply
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
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
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
|
|
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 December 2003 at 4:53pm |
|
The biggest advantage with the stored procedures and SQL Server is the ability to return multiple recordsets
|
|
|
|
psycotik
Groupie
Joined: 27 November 2003
Status: Offline
Points: 73
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
