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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Stored Procedures : When and when not

Author  Topic 

kensai
Posting Yak Master

172 Posts

Posted - 2001-11-27 : 10:14:12
Hi. I just started to write a forum software with ASP. I started to plan it and I'm considering to use SQL Server 2000 as database. I'm an intermediate SQL Server user, I have stored procedure knowledge (but not that deep though) and I'm thinking to use stored procedures for my forum. I wrote simple procedures before but I never got into something like this. I want to use stored procedures but I don't want to use them in unnecessary places. I would like to hear your opinions on when I should use them and when I shouldn't.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-27 : 10:20:19
Most people would say you should ALWAYS use stored procedures for all of your data activity. I agree 100%. It's a very good practice for several reasons:

-Easier to manage in the long run
-Easier to modify
-More secure
-Promotes modular programming and design
-Easy to recycle code between separate but similar databases

Using stored procedures forces you to clearly identify what a process is supposed to do. If you have several steps to a process, you will break them down into smaller parts. These become templates, in a way, that you can utilize for similar functions elsewhere, with little or no modification.

I've gotten to a point with a lot of my code where I can knock out an entire web-enabled database application in one day, start to finish! Trimmings take a few days longer, but it's doable.

You will also streamline the ASP code because your database actions become almost like function calls--in fact if you write your ASP pages this way they WILL be function calls, and your code will be tight and efficient and easy to read.

Edited by - robvolk on 11/27/2001 10:23:03
Go to Top of Page

nlocklin
Yak Posting Veteran

69 Posts

Posted - 2001-11-27 : 11:12:06
I agree with everything that robvolk said, and would add:

Have you looked at existing forum software? SnitzForum (the forum software we're using right now to post these messages on SQLTeam) is a great solution for ASP/SQL Server sites. It comes with all of the source code, and is pretty customizable. It's a great product, and would save you the hassle of writing your own.

Check it out:
http://forum.snitz.com/


--
"It's not that I'm lazy, it's that I just don't care."

Edited by - nlocklin on 11/27/2001 11:12:53
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2001-11-27 : 11:42:57
I'd like to second that vote for the Snitz forums. I was writing my own for a while too. There was no way I could write anything this nice without spending hundreds of hours. Very flexible, easy to configure, good support forums, large user base, FREE. Very easy to recommend them.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

kensai
Posting Yak Master

172 Posts

Posted - 2001-11-27 : 13:56:25
Thanks for the link nlocklin. I'm already aware of Snitz. But somehow I don't like it. Be it the design or the options. The boards I like are vBulletin and Ikonboard but 1)We're on w2k server and don't have MySQL, 2)I don't want it using flat files as database. So I decided to write my own. I don't have a deadline or something, so I can take my time while writing it.

I'm asking this because I want to make it as fast as possible. I don't want to use a stored procedure for something if I can make it run better with simple sql commands within the asp code. Do you have any advice for such a situation? Or can I use them everywhere as you said?

Go to Top of Page

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2001-11-27 : 14:35:54
If you write sql statments within your asp code and then execute it against the sql server, your code will never be able to generated a cache plan. I agree that stored procedures should ALWAYS be used. To address your question, by using stored procedures, you create a cached plan, therefore your will be optimized for speed rather then writing sql code in asp regardless of how simple it might be

*************************
Just trying to get things done
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-27 : 16:07:18
Thanks Roy, I forgot about that (#1 reason to use stored procedures, and I forget to mention it!)

As you might notice, Roy is 100% correct about performance: SPs will be faster than ad-hoc SQL statements in ASP. I did mention that using SPs is easier in the long run; don't let the initial learning curve put you off, it's harder at first but you WILL appreciate the benefits later on. Any time you might save now by using SQL strings in ASP will be wasted later when you want to re-write it due to performance or maintainability issues.

There's a reason people wax rhapsodic about stored procedures: THEY'RE GREAT! Once you try them you will never go back. Seriously, any advice AGAINST using stored procedures is bad advice.

In addition to Books Online, you should check out some books about SQL Server and see the chapter(s) on stored procedures, they do a good job of listing the benefits.

BTW, what exactly do you not like about the Snitz forums? As far as ease of use, maintainability and customizability, I haven't seen ANYTHING that comes close.

Go to Top of Page

kensai
Posting Yak Master

172 Posts

Posted - 2001-11-27 : 20:48:46
quote:

In addition to Books Online, you should check out some books about SQL Server and see the chapter(s) on stored procedures, they do a good job of listing the benefits.



I do have two books. I studied the stored procedure chapters lights last time, I'm gonna work on it harder now.

quote:
BTW, what exactly do you not like about the Snitz forums? As far as ease of use, maintainability and customizability, I haven't seen ANYTHING that comes close.



Don't know, the design I think. And I always wanted to write my own..

So I think I have my answer. I'm gonna use stored procedures everytime I have to deal with the database. Thanks for the all help. (But I always want to hear from you if you have any further opinions)

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-11-27 : 20:59:25
Rob did mention more secure but I thought I might elaboarate on that. If you create an SQL Server user to use in your connection string in ASP. You can grant all your stored procs to that user, but not give that user any other rights to the database.

That way, if your webserver was compromised and someone got your SQL Server Username and password all they could do is run the procedures, they wouldn't have access to anything else.

This wouldn't work if you use dynamic sql in your procs though see the articles to find out why.

As for writing a message board, go for it. snitz actually doesn't use stored procs (which I don't like, so I wrote one) and writing one of your own will be a good learning excersise.

Good luck



Damian
Go to Top of Page

kensai
Posting Yak Master

172 Posts

Posted - 2001-11-28 : 16:56:30
Thanks for the info merkin. I will definitely check the security very throughly.

One more thing. Do you think I better learn Command object or can I stick with Recordset to use stored procedures?

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-28 : 17:00:57
If the stored procedure returns results, then the recordset is a good option. If it just performs some action (like UPDATE, INSERT, DELETE etc.) then you can use Connection.Execute or Command.Execute without a recordset object. You might as well study up on all three of them so that you can choose the most efficient method for your code.

ADO documentation can be gotten here:

http://www.microsoft.com/data/download.htm

Look for the MDAC SDK package; download and install it, then look for the ADO260.CHM file, it has all of the documentation for ADO.

Wrox has a really good book on ADO 2.6, if you want to spend a little money (might even be discounted if you look in your local bookstore).

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-28 : 17:27:15
Personally, I prefer to use Command objects for any stored procedure that has parameters passed to it, which for me is every stored procedure I use. Maybe it's overkill in some cases, but that's my preference. When the sproc is returning a recordset then I do a SET RS = Cmd.Execute() to get it into a Recordset object.

By the way, have I mentioned yet that I agree 100% with Robvolk's answers above?

(okay, gotta try this out... )

-------------------
It's a SQL thing...

Edited by - AjarnMark on 11/28/2001 17:27:27
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-11-28 : 17:36:28
I agree with Mark. I use the command object, then as soon as I get a recordset I use GetRows to convert it to an array.

That is just too cool

Damian
Go to Top of Page

kensai
Posting Yak Master

172 Posts

Posted - 2001-11-28 : 18:06:36
robvolk thanks for the link. I already have ADO SDK and I've read most of it. I passed the Command object since I didn't need it that time. So you're saying Command is not that necessary..

quote:

..Maybe it's overkill in some cases...


Mark can you give an example about this?

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-28 : 18:29:53
I think what Mark means is that you could end up with a lot of command objects that aren't really necessary.

The official wisdom is that you should never reuse command or connection objects. I don't disagree with that philosophy, but IMHO if I have 10 commands to run, all through the same connection, I'm not gonna create 10 command objects to do it. If I can't combine the 10 steps into one (using a stored procedure, for example), I'll just recycle the same command object and call it 10 times with the appropriate settings. It's too easy to forget to close and destroy all of your objects properly, and you can end up with memory problems. And I'm lazy; if I can do something with 15 lines of code that SHOULD be done "properly" with 50 lines of code...well, I don't enjoy typing that much! As long as it works I get by with as little as I can.

Much of the "one command, one object" philosophy comes from scalability issues and object (mis)management. You'll find a lot of documentation about the negative effects of reusing objects. Again, I don't disagree with it, but in my experience if you know what you're doing and you write your code correctly, you don't need to do this.

There is a little extra overhead when using Connection.Execute vs. Command.Execute. It's up to you to determine if that is a problem or not (test both and compare performance). I personally don't use Command objects, but a lot of people do and think they're great. I'd recommend that you try them; you'll get experienced with them and you can always decide later whether or not they're better for you.

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-28 : 19:02:02
Yeah, what HE said!

I haven't done a lot of reading up on pros/cons of reusing Command or Connection objects. I personally prefer to re-use them, especially my Connection object because it's fewer items to keep track of, and as Rob pointed out, the fewer you have to remember to close. At this point, I pretty much have my Create/Destroy for connections in subroutines named ConnectDB and DisconnectDB, then every page calls those at the beginning/end, and I'm in good shape. I only run multiple connection objects if I'm connecting to multiple databases or servers (which would require separate connection strings).

I was also thinking in terms of the fact that you can call a stored procedure and pass it parameters in-line vs. creating a command object with a Parameters collection. I developed the habit long ago of always using a Command object, and doing various validations (such as for null) or conversions as I'm assigning values to the parameters.

Old habits die hard.

-------------------
It's a SQL thing...
Go to Top of Page

smartlizard
Starting Member

24 Posts

Posted - 2001-11-29 : 08:17:50
I have stored procedures that I have to pass variables to from my ASP page, for instance I have to search by last name and first name, so I pass the last name and first name from the asp page to the stored procedure. Some times I have to pass 5 and 6 parameters to my SP.

My concern is speed. Is this the correct way to get the results I need?

I have read that if you pass arguments to the SP it can not reuse it's previous execution plan. Is this correct, and is it still smart to use a SP in this situation? Is there something I should add to my SP to make it run faster?

Thanks!

Ron Sell
http://smartLIZARD.com
Go to Top of Page

izaltsman
A custom title

1139 Posts

Posted - 2001-11-29 : 09:20:04
quote:

I have read that if you pass arguments to the SP it can not reuse it's previous execution plan. Is this correct, and is it still smart to use a SP in this situation?



I don't know where you read it, but it is not true. The query optimizer is intelligent enough to recognize that procedure's parameters may change with every execution, and it will reuse the cached plan (unless you are using dynamic SQL in your stored procedure, which is a different story). So you should definitely use stored procedures.

Go to Top of Page

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2001-11-29 : 09:54:55
I'm in 100% agreement with Ilya. I don't know who told you that passing in parameters to a stored procedure causes the cached plan not to be used or where you read it from, but that is absolutely incorrect. I have seen significant speed changes when moving my embedded sql code from asp to stored procedures. My 2 cents on the reuse of a connection object: I always reuse my connection object. I do not use the command object, so I won't say anything about that. It doesn't make sense to me not to reuse a connection object. By reusing the connection object you obviously get speed enhancements. Robvolk, you said there are scalability/mis-management issues? I could not find anything in MSDN, is this where you were getting information from in regards to this?

*************************
Just trying to get things done

Edited by - royv on 11/29/2001 09:55:17
Go to Top of Page

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2001-11-29 : 09:55:04
Personally I always wrap each SPROC into a function and call the function to get the data I need or to perform some operation. This makes some of my includes rather large but the base code inside the HTML code very easy to read.

I would advise those using
Set [Recordset] = [Command].Execute()
against using this method. As you do not control what type of recordset you are getting. It is much more efficient to use
[Recordset].Open([Command])
and set the appropriate parameters for the recordset.. cursorlocation|cursortype|locktype|etc...

On the subject of connection reuse ... Once upon a time I stored my connection objects in the Session object ... ya I was a bad little coder I didn't know better ... but after a while I fixed that and used one connection object per page... opened at the top... closed at the bottom ... well now I hear due to the overwealming perfomance of connection pooling I now open the connection 2 or 3 lines above where I do an Execute or Open then close it within 2 or 3 lines after that. I use adUseClient for my cursor location (which is not suitable for all uses but my queries never return more than 10 rows for this particular application)

Open and closed as fast as possible to allow others a chance to execute their queries is the optimal and most scalable way to code your connections. If you keep your connection open you are consuming 1 connection from the pool while you do NON database related tasks! Think about it a little... the other users have to wait for you to release the connection so they can use it! and then people just have to wait more and more ... however if you released the connection ASAP then you will continue on processing and others will be be able to do DATABASE work ... and since connection pooling works so well (according to MS and I happen to think it does as well) you will only notice a slight lag when the initial connection is made!

Hope this helps some of you on your quest for the ultimate SCALABLE and HIGH AVAILABILITY codology. I think I just made that word up :-p

From MS Document on create COM+ applications...
quote:

Avoid Reusing ADO Connection Objects to Execute Many Commands
Avoid reusing open connection objects over multiple database commands. That is, open, use, and close a connection whenever needed.
If you have wrapped ADO code in a data-access object, this will not be a problem, since these wrappers perform in a stateless fashion. However, if you have ADO code in your objects, and especially if you have ADO connections stored at class level, please review the Knowledge Base article below. The main problem lies in keeping open database cursors while other things are being executed. If you are retrieving recordsets to send to the client, disconnect them from the RDBMS.
Implementing a way to reuse ADO Connection objects greatly increases the maintenance effort and sometimes leads to design mistakes that could be avoided by good encapsulation. The ADO-OLE DB layer implements its own internal connection pooling. This connection pooling makes the following flow the preferred way to access data on the server:
• Create ADO connection
• Open connection
• Use it
• Close connection
• Set ADO connection object to Nothing
This will be easy to develop, easy to maintain, as well as fast and scalable.
References
HOWTO: Reusing ADO Connections Within MTS Transactions (Q234218) at http://support.microsoft.com/support/kb/articles/Q234/2/18.asp



Edited by - onamuji on 11/29/2001 10:44:27
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-29 : 11:22:49
Thanks! The above quote from MS was similar to what I have read, Roy.

I also think that the advice MS gives here may be more applicable to a 3-tier environment, where your data access code is kept in a DLL. I don't use this approach, it is too cumbersome, and I don't have huge sites that need the performance improvement (not to mention that I don't feel the improvement is as big as the proponents say it is...I don't want to start an argument, just my personal opinion). Because of that, I happily run several commands through a connection while only opening it once, and so far I've been very happy with the performance.

And I totally agree with Ilya: whoever wrote that stored procedures can't reuse the cached plan was smoking crack. I said it earlier in this thread, and I'll say it again:

Any advice against using stored procedures is BAD ADVICE!

I don't care if the Pope says that stored procedures are bad, he's WRONG!

Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -