SQL Stored Procedures . Why?

[Guest]

Hi,

I'm using MSSQL2000 at the moment on a shop/cms app.

A good friend of mine (who doesnt do coldfusion at all) has told me it would be a good idea to learn about stored procedures in MS 2000/2005 because they take the workload off the web server. Until now I had been using <cfquery> and that was it, although these have been running in an MVC application structure and to some degree encapsulated.

I know nothing about Transact-SQL at all and have never used a stored procedure or a trigger because I've never had to to do what I want to do. I cache queries where I can and this seems to make things a great deal faster anyway.

Whats the consensus of opinion on here. Should I learn T-SQL somehow and start using stored procedures, with the queries stored in the db server or is this just another way of skinning a cat?

Keith

[Dan_Bracuk]

Stored procedures are pre-compiled so they will run faster. You can also put more than one query into a stored procedure which might save you some work.

[joeDangelo]

Stored Procedures are definitely recommended for anything other than very simple applications. If you have complex business rules that apply to your data, if you need to parse out data before or after it goes into the db or as it comes out, or you need to insert the same data into multiple tables... stored procedures are a terrific way to do these because, as your friend said, it takes the load off the web server. Plus, database servers are better suited to do things like manipulate data than CF is... it'll process it faster.

For smaller, simpler apps, I'd leave it up to you. Learning how to write and work with them is a good thing either way, but simpler queries can run very fast through CF if you use things like the Blockfactor attribute and use the cfqueryparam tags.

Even though there's a learning curve, it's not a very steep one to get the basics of stored procedures down. You'll find that designing your complex applications will be significantly easier. You wont have to account for the business rules in your application and that will probably reduce the complexity of both your model and controller layers of data. Plus... having something extra on your resume never hurts 😉

[Guest]

Thanks Joe & Dan...
My CMS is quite complicated and the shop element with stock control etc..... definetly could benefit from using stored procs then.