May 14, 2004 at 8:12 am
Hello, i'm new to programming with sql server. I've heard alot about the good things of stored procedures. my question is when is the right time to use stored procedures when building an application? I'm using visual basic 6.0 to create the application.
Thank you before
May 14, 2004 at 8:23 am
i'm working in Access for 2 years.
All hard proccess i made with stored procedure. you can solve a complex problem with it. You can select, insert data into new table , you can update table . so, you can program all you want with this. you can use parameter in or out.
I saw aplication in C and VB with the same tehnology.
Costica
May 14, 2009 at 10:07 am
IMHO, in your mixed language environment, I'd do sprocs when you have control flow to further cook you data. If the cooking is done in VB, you're bringing lots of raw data off your server then reducing it down to the answer you want. If you do this in a sproc, only the answer comes off the machine.
On larger projects, I've found sprocs are useful if you have a dedicated DBA who can spend time tuning sprocs without wading through your VB code.
For simple queries, I dont find that much performance improvement by moving it to a sproc. I tend to use exec('') to get a new plan each time so being precompiled doesnt help me much. For these, I'd look at a view before jumping into a sproc.
May 14, 2009 at 11:18 am
Hi
The right time for stored procedures? After the connection opened in your application.
It seems to be an overkill at the beginning to do any data-access (SELECT/INSERT/UPDATE/DELETE) within procedures. Some reasons:
1.)
Keep things together. Since you will need them when your queries become more complex it makes sense to keep all in database instead of hybrid solutions.
2.)
Performance. If your application creates dynamic statements they have to be compiled (validation, analysis, execution plan, ...) every time you send a new statement.
3.)
Better IDE. SQL string concatenation in front-end languages as VB6 (C#, VB.NET, ...) is quiet ugly (in my opinion).
4.)
Flexibility. If you need to change some database structures (for scaling, ...) you don't have to change all your source code. Only change the procedures.
5.)
Much more source code to write. Erm... this seems to be a contra :-D. Not, really. To avoid to write all the standard procedures for INSERT/DELETE/UPDATE(partial) you can use a simple VB-app or a SQL script to automatically generate them.
The most common reason people use against stored procedures is "They are not portable to any other DBMS".
That's correct. Anyway, if you try to limit your SQL statements to ANSI standard you will not be able to use the special features of any database system. Your database will never have the best performance, flexibility, scaling, ... That's like buying a scientific calculator and use only +/-* (thanks to Jeff for the example ;-)).
Greets
Flo
May 14, 2009 at 1:18 pm
The right time to switch to stored procedures is as soon as you determine that it is going to be an actual application with actual users (as opposed to a utility or tool that only you will use).
Why? Well in addition to Florian's many fine reasons, the big one is: Security. Using stored procedures gives you an order of magnitude improvement in security design and security options. Even post-delivery, having all database access go through stored procedures will give you (or your DBA's) many more possibilities for dealing with security issues. There is nothing else that even comes close when it comes to facilitating security implementation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2009 at 1:45 pm
OP dated 04/2004 ?? 😉
another nice resource: http://sqlblog.com/blogs/paul_nielsen/archive/2009/05/09/why-use-stored-procedures.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 14, 2009 at 1:51 pm
ALZDBA (5/14/2009)
OP dated 04/2004 ?? 😉
😀
Didn't notice.
May 14, 2009 at 2:27 pm
Heh. Nice...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply