Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

sqlcode vs sp Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 8:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540
hi friends i have small doubt in sql plese tell me
how to Determine when to use stored procedures vs. SQL in the code
Post #1428033
Posted Thursday, March 7, 2013 8:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 2,873, Visits: 5,190
If you don't use any ORM in our application, then you better place all your SQL code into stored procedures.
There are no standard rules which specify where to use stored proc or application embedded SQL code. However it's considered by many to be the best practice to use stored procs!



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1428037
Posted Sunday, March 10, 2013 4:45 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 18, 2014 5:35 AM
Points: 647, Visits: 764
Using stored procedures has also the following benefits.

Security : dba can determine who can execute what
Tuning : mostly stored procedures are written by sql developpers who mostly know to find the most performant ways to manage the objective at hand.
Modify : when bussiness logic changes there is no need to wait for the application developper but can be handled by all sql developpers
and in most cases because stored procedures are compiled code there could be performance benefits..

Hopes this helps you in your way of thinking.
Wkr,
Van Heghe Eddy
Post #1428972
Posted Monday, March 11, 2013 5:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 13, 2013 2:18 AM
Points: 12, Visits: 22
My suggestion is: avoid stored procedures at all, unless you have very special needs of performance on some complicated operations.

I know that here, at SQLServerCentral, many people are skilled DB administrators, and they like s.p. that give them much power. But if you are a developer, like me, it is better not to use them for the following reasons:

- Portability: there are many types of DB out of there, tomorrow you could find a customer who wants to run the application with Oracle, MySql, Db2, Access, ..., or even your single customer can change his mind about which is the best Db suitable for his needs.
There is a myth around that tells "use SP as much as you can" but I think this is just propaganda by the database producers. If you use s.p. then you can't change Db type easily, and this is exactly what they want.
SQL is a standard, s.p. are not.
There are some subtle differences in SQL syntax among different producers, but if you program in a smart way, you can work with them. S.p. instead must be completely rewritten.

- Maintenance: if business logic change, you probably need to change something into the code and something into the DB structure; it is easier to have only one place where to look for, the application code. If you use s.p. you should always check all s.p. code if you need to make a change.

- Security: the customer decides who can execute what, by assigning each user to one or more usergroups. You, the developer, decide which usergroups can execute what, by identifying the user.
I suggest you keep tables for users, usergroups, and rights, and avoid using DB users for anything other than distinguish db administrators from normal db users.
When the user is logged in you know which rights he has: for example, if your application has a pushbutton for "Delete all records" and you want that only some users can push it, you will define a right for this and your code will disable or hide that button if the user hasn't the right.
Someone says "dba can determine who can execute what", but what does this means ? That if you, by mistake, let a user run a procedure that it is not allowed to, the user will receive a difficult to undestand error. This is not the best solution, the best solution is that the application does not allows users to do things they are not allowed to.

I definitely think that s.p. should be reserved to very special needs, and I think this happens not more than 1 % of the times you need to do a Db operation from a normal application.

Bye

Francesco Muzul

P.S. 1: I am a C++ developer with 25 years of experience, working with SQLServer since 1995, but also with Oracle, Access, SQLite and others.

P.S. 2: sorry for my imperfect english, I'm italian.
Post #1429188
Posted Monday, March 11, 2013 5:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:29 PM
Points: 2,078, Visits: 3,616
Well, I think you've got both ends of the argument there in the last couple of posts!

But what's with the paranoia over DBA's Francesco? We're not all bad
Post #1429202
Posted Monday, March 11, 2013 6:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 2,873, Visits: 5,190
Van Heghe Eddy (3/10/2013)
Using stored procedures has also the following benefits.

Security : dba can determine who can execute what
Tuning : mostly stored procedures are written by sql developpers who mostly know to find the most performant ways to manage the objective at hand.
Modify : when bussiness logic changes there is no need to wait for the application developper but can be handled by all sql developpers
and in most cases because stored procedures are compiled code there could be performance benefits..

Hopes this helps you in your way of thinking.
Wkr,
Van Heghe Eddy


Security - can be also achieved without use of stored procedures (eg. via table & vieww access)
Tuning - the way you've put it is very optimistic! In my experience I've found too many cases where stored procedures were written by non-sql developers, without any consideration for performance...
Modify - that is interesting one! One of the reason ORM is used for system/application development is to avoid additional resources (eg. sql-devloper) required for making changes.
and the last bit:

and in most cases because stored procedures are compiled code there could be performance benefits..

Ad-hoc queries also can be compiled and cached...

So, all of the above alone cannot be reason for using stored procedures.
Nowadays, it's really depends on the chosen system/application architecture.




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1429207
Posted Monday, March 11, 2013 6:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 2,873, Visits: 5,190
fmuzul (3/11/2013)
My suggestion is: avoid stored procedures at all, unless you have very special needs of performance on some complicated operations.
...


Calling statement!

I'm afraid you will not find many SQL specialists agreeing with the above...

Actually first few paragraphs from BOL tell well enough:
Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.
Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server...




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1429212
Posted Monday, March 11, 2013 7:00 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
In addition, if the underlying table structure changes but the output of the stored procedures remains the same, you isolate the changes to the database. You modify the stored procedures to use the new table structures ensuring that the input and output of the procedures remains the same, the application does not need to change.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1429242
Posted Monday, March 11, 2013 7:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 13, 2013 2:18 AM
Points: 12, Visits: 22
Gazareth (3/11/2013)
Well, I think you've got both ends of the argument there in the last couple of posts!

But what's with the paranoia over DBA's Francesco? We're not all bad


I don't think I'm paranoic over DBA's.
I just think that sometime they overestimate the benefits of using s.p., and underestimate the problems they can give.
Post #1429254
Posted Monday, March 11, 2013 7:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
fmuzul (3/11/2013)
Gazareth (3/11/2013)
Well, I think you've got both ends of the argument there in the last couple of posts!

But what's with the paranoia over DBA's Francesco? We're not all bad


I don't think I'm paranoic over DBA's.
I just think that sometime they overestimate the benefits of using s.p., and underestimate the problems they can give.


The biggest problem with having the SQL code embedded in the application code comes to tuning the code. Any rewrites of the embedded code require a redeployment of the application. If the code is contained inside of a stored procedure, only the stored procedure needs to be redeployed, not the entire application.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1429258
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse