Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
iansr
Starting Member
23 Posts |
 Posted - 2001-12-25 : 22:56:33
|
| Greetings!I need to display a heirarchy of menus (3 levels). I use SQL server 7 and ASP.The first thing i did was set 3 recordsets corresponding to each level with 1 stored procedure each. then loop across the three of them to display the table.The second thing i did was set up 1 recordset with 1 stored procedure. They just display the recordset.On performance, Both of them appears to be the same perceptively, half a second. But when I check the profiler, the first method had around 10 duration for each stored proc, the second method, had its duration reach something like 200.My question is, which is better?Have a very simple stored proc and do the loops in ASP, or Create a complicated stored proc, and just let ASP display it (even though its duration according to profiler is 8 times longer). Also, which will perform better come simultaneous users?Thanks! |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-26 : 00:01:44
|
| For Any Database Related Activity using Stored Procedure is recommended.There has been a great discussion on Stored procedure , follow this link.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10964HTH-------------------------Graz's Baby is my Master:) |
 |
|
|
iansr
Starting Member
23 Posts |
Posted - 2001-12-26 : 00:15:17
|
| On both cases, i do use stored procs.. but to what extent do i use them? Is is always the case that i should exhaust stored procs to the utmost? |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-26 : 00:32:07
|
| iansr i dont think u have followed the link properly. go thru it and all your doubts will be cleared.yeah , you can use in every database related activity (search,update,delete,insert) stored procedure's. with good performace gains. coz stored procedure's make use of Cached Plan.i again suggest you to follow this link.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10964-------------------------Graz's Baby is my Master:) |
|
|
|
iansr
Starting Member
23 Posts |
Posted - 2001-12-26 : 00:42:39
|
| im sorry didnt see that... hehehe... i thought it was a signature or something...ok thanks alot! gd day! |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-26 : 12:36:36
|
| The link Nazim has provided is a great discussion of Pros/Cons (mostly pros) of Stored procedures. In your example, in particular, I think one of the biggest advantages to putting everything in one stored procedure is that if/when you create a second ASP page that needs to do the same thing, you don't have to copy the logic into it, because it is all contained in the one stored procedure that you just call from your new page. And later, when you need to enahnce/fix that procedure (and you know you will at sometime) it's easier if it is only in one place (a single sproc) instead of multiple places (many ASP's). |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
|
|
|
|
|
|
|
Stored Procedure Performance Question