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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-16 : 11:54:06
|
| Tomaz writes ""Applications that access SQL Server databases should make extensive if not exclusive use of stored procedures for any statement, from a very simple one-row select to a complex cascaded delete. Applications that paste together SQL statements are more difficult to manage and result in less secure databases than applications that exclusively use stored procedures."Hi guys,I was told by an SQL consultant that from performance point of view usage of stored procedures should be minimised and one should use as many simple SQL queries as possible.Any comments on that one,what happens to your theory when one deals with millions of records, do you still preach usage of stored procedures in such case and why?Sincerely,Tomaz" |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2001-12-16 : 12:20:44
|
| Hi TomazFrom my experience, you should use stored procedures in most cases. In the case of a query returning only a few rows, but which is called many times, you get a performance win concerning for example network traffic. The call of a stored procedure is almost always shorter than a query would be. In the case of advanced queries which return lots of rows, the procedure is often faster also, because the query plan can be determined beforehand by the server.Another big advantage is the ability to disallow users to access tables directly, only allowing them to use stored procedures or views. This enhances your control over the availability of data both for selects, updates and deletes. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-16 : 14:00:02
|
| Andraax is 100% correct, and if you find that SQL consultant, smack him/her/it in the face repeatedly with a blunt object!ANY ADVICE AGAINST USING STORED PROCEDURES IS BAD ADVICE!!!!! (I can't believe how many times I've had to say this!)Here's some more discussion on stored procedures, all of them supportive:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10964My 2¢ |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2001-12-16 : 19:04:06
|
As for blunt objects, I would take Izaltsman's advice and use a copy of Inside SQL Server. This book is nice and heavy with a good solid cover. It also contains reasons for why the consultant (send the idiot here and we can deal with him) is wrong.Then go have yourself a  for a job well done.Damian |
|
|
|
|
|
|
|
|
Using stored procedures - implications on performance?