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 Monday, March 11, 2013 7:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 13, 2013 2:18 AM
Points: 12, Visits: 22
Eugene Elutin (3/11/2013)
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...




BOL is written by Microsoft. This is not an independent point of view.
They want to convince the reader that s.p. are good.

The statement "The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure." can be rewritten replacing "stored procedure" with "C++ procedure", or "C# procedure", or "PHP procedure" or whatever language else you use for developing the application.

And about performances, yes, I recognize that sometime you can get gains using s.p., but the most of the time we are speaking about milliseconds, so this doesn't really matter.
Post #1429260
Posted Monday, March 11, 2013 7:41 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
I think this is more a discussion with the stand from the world you belong to.
SQL DBA vs native code writers.

Well i belong to both worlds.
I used to write VB.Net and also perform SQL DBA/developer tasks.
For me the choice wass easy.
I use the world that is the most convinient.
Most cases this was SQL Side SP.
But even when i used ad-hoc sql code in my applications, i found myself writing the statements in sql mgt studio and test them on execution performance (also Redgate sqlprompt was great help writing here )

And so i have to agree with Lynn, i have found myself different times on the case that bussines logic changes so did the table structure but output in application would stay the same.
With an SP this is an easy tasks, no so in ad-hoc sql code.

With kind regards,
Van Heghe Eddy
Post #1429269
Posted Monday, March 11, 2013 7:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 13, 2013 2:18 AM
Points: 12, Visits: 22
Lynn Pettis (3/11/2013)
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.


Replace "stored procedure" with "C++ procedure" and swap "application" with "database" and you get a perfectly valid statement again.
If you work with s.p., you isolate the changes to the database. And this can be good if you don't want to change the application.
If you work with the application language, you isolate the changes to the application. And this can be good if you don't want to change the database logic (or even don't want to have logic at all in the database).

DBAs obviously tend to prefer s.p. because they are accustomed to them.
Developers should prefer to avoid s.p.
Post #1429270
Posted Monday, March 11, 2013 7:50 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 20,857, Visits: 32,877
fmuzul (3/11/2013)
Lynn Pettis (3/11/2013)
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.


Replace "stored procedure" with "C++ procedure" and swap "application" with "database" and you get a perfectly valid statement again.
If you work with s.p., you isolate the changes to the database. And this can be good if you don't want to change the application.
If you work with the application language, you isolate the changes to the application. And this can be good if you don't want to change the database logic (or even don't want to have logic at all in the database).

DBAs obviously tend to prefer s.p. because they are accustomed to them.
Developers should prefer to avoid s.p.


You are operating under a false perception of me. I am not a DBA. I am a DBA/Database Developer/Developer. I have worked on all sides of the application. My perception comes from that experience.

If your application needs to support multiple database backends, then develop those database backends separately. Support the multiple code bases needed. Make use of the capabilities of each of those DB Engines to get the most from them to improve the performance of the application using those DB Engines.

There is a fallacy of truly portable code. I don't write Standard SQL code, I write code that makes the best use of the underlying DB engine. This provides a performant system upon which an application can be built.




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 #1429274
Posted Monday, March 11, 2013 7:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 13, 2013 2:18 AM
Points: 12, Visits: 22
Lynn Pettis (3/11/2013)
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.


Maybe you live in a different world than mine. For me the redeploy of an application is not a problem, I just need to replace a .exe file, and this can happen automatically also (as many modern applications do, checking for an update somewhere into the net). Updating the code of a stored procedure could be more uncomfortable.
And using a source version control I can know exactly who and when made a change to the application code, I can get a complete history of the changes.
I know that version control can be applied to s.p. also, but I think this is more difficult.
Post #1429276
Posted Monday, March 11, 2013 8:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
fmuzul (3/11/2013)


BOL is written by Microsoft. This is not an independent point of view.
They want to convince the reader that s.p. are good.


Is wiki independent enough for you? http://en.wikipedia.org/wiki/Stored_procedure

Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures


You can contribute your opposite independent opinion there too.

I have similar sort of experience as stated by you (with a small exclusion, my first programming language was Assembler for IBM mainframes S360/370) but I have nothing against wide use of stored procedures within appropriate architectural system design. Also, I have nothing against using ad-hoc queries embedded into code for the relevant designs. Also, I have nothing against using ORMs.
It's all depends!
I don't think that universal best approach exist.
There are different options for different situations and these options have nothing to do with DBA's preferences.


_____________________________________________
"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 #1429282
Posted Monday, March 11, 2013 8:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
fmuzul (3/11/2013)

...
Maybe you live in a different world than mine. For me the redeploy of an application is not a problem, I just need to replace a .exe file, and this can happen automatically also (as many modern applications do, checking for an update somewhere into the net). Updating the code of a stored procedure could be more uncomfortable.
...


Or yes, I do, very different world, actually!
The change deployment of systems I do usually work with, requires more than one .exe file replacement. They kind of consist of many of executables and DLL's . Also, quite often changes related to system database (or quite often, databases) include not only change of some queries, but also database structural changes and data transformations. Therefore change deployment of database is still required (and better to be version controlled too).
What exactly makes T-SQL code change in stored proc more uncomfortable then c++ or c# or any other code change in your application?


_____________________________________________
"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 #1429290
Posted Monday, March 11, 2013 8:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 13, 2013 2:18 AM
Points: 12, Visits: 22
Lynn Pettis (3/11/2013)
fmuzul (3/11/2013)
Lynn Pettis (3/11/2013)
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.


Replace "stored procedure" with "C++ procedure" and swap "application" with "database" and you get a perfectly valid statement again.
If you work with s.p., you isolate the changes to the database. And this can be good if you don't want to change the application.
If you work with the application language, you isolate the changes to the application. And this can be good if you don't want to change the database logic (or even don't want to have logic at all in the database).

DBAs obviously tend to prefer s.p. because they are accustomed to them.
Developers should prefer to avoid s.p.


You are operating under a false perception of me. I am not a DBA. I am a DBA/Database Developer/Developer. I have worked on all sides of the application. My perception comes from that experience.

If your application needs to support multiple database backends, then develop those database backends separately. Support the multiple code bases needed. Make use of the capabilities of each of those DB Engines to get the most from them to improve the performance of the application using those DB Engines.

There is a fallacy of truly portable code. I don't write Standard SQL code, I write code that makes the best use of the underlying DB engine. This provides a performant system upon which an application can be built.



Your philosophy is different from mine.
A single code base is a must for me. I absolutely don't want to write 3 versions of a procedure to support 3 different DB engines. I prefer to save the time of the developer rather than the time of a PC running a piece of code. I consciously try to ignore the non-standard capabilities of the DB engines. They are not worth the time.

Also, to be able to "make the best use of the underlying DB engine" you must acquire a deep knowledge of the DB engine, and I am not interested in it.

If you like your way of living, and you can find customers that agree with it, that's good !
Be happy and have a nice day
Post #1429293
Posted Monday, March 11, 2013 8:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201

...
Also, to be able to "make the best use of the underlying DB engine" you must acquire a deep knowledge of the DB engine, and I am not interested in it.
...


That is a key!
If you want to do anything better than "poor" with your database, you should kind of develop some sort of knowledge of the DB engine you're working with.
If you have no interest in it, you either never work(ed) with serious database based systems or develop very poor code. As there is no way to write good performing code for database without understanding how database (and its engine) works.
BTW, this forum is generally for people who have at least some kind of interest in it or would like someone to share with them their knowledge of SQL Server.

I guess similar discussion could happen between VB and C++ programmer: VB-programmer could say something like: "you must acquire a deep knowledge of the memory management in PC/OS, and I am not interested in it." I just can imagine what good C++ could reply to this...


_____________________________________________
"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 #1429305
Posted Monday, March 11, 2013 8:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 13, 2013 2:18 AM
Points: 12, Visits: 22
Eugene Elutin (3/11/2013)
fmuzul (3/11/2013)

...
Maybe you live in a different world than mine. For me the redeploy of an application is not a problem, I just need to replace a .exe file, and this can happen automatically also (as many modern applications do, checking for an update somewhere into the net). Updating the code of a stored procedure could be more uncomfortable.
...


Or yes, I do, very different world, actually!
The change deployment of systems I do usually work with, requires more than one .exe file replacement. They kind of consist of many of executables and DLL's . Also, quite often changes related to system database (or quite often, databases) include not only change of some queries, but also database structural changes and data transformations. Therefore change deployment of database is still required (and better to be version controlled too).
What exactly makes T-SQL code change in stored proc more uncomfortable then c++ or c# or any other code change in your application?


If my application is used in different places, to update a s.p. I should connect to all servers to make the change. Sometime I don't have access to the server too.
If I need to replace a .exe that can be automatically updated, I just need to place a .exe on the right place.
Well, I could embed the script that updates the s.p. into the .exe, but a change to the .exe would be needed anyway.

But, the most important thing, is that making the change in a C++ procedure let me use the full power of the C++ language. Can't be beaten by any T-SQL, PL-SQL or similar...
I'm interested in being a master in C++ language, as with it I can solve many different types of problem, and I think I reached that level of mastership (but I know there's always something to learn...).
I'm not interested in being a master in T-SQL or PL-SQL, as with them I can only solve problems about manipulating data in a relational database, and only from one of the many existing vendors !

And, doing the change in the application code, the history of all changes made are saved in the source version control system, together with all the other changes to the application.
Post #1429311
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse