March 11, 2013 at 8:06 am
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.
March 11, 2013 at 8:15 am
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?
March 11, 2013 at 8:19 am
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 🙂
March 11, 2013 at 8:39 am
...
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... :hehe:
March 11, 2013 at 8:48 am
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.
March 11, 2013 at 8:56 am
...
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...
...
Are you sure?
What I can say... The red colour is nicer than blue one! Prove me wrong!
March 11, 2013 at 9:40 am
In my experience, it's the front ends that come and go, back ends tend to be more stable. Granted there has been a bit of a shake-up with the availability of free dbmss, but I don't believe that a major segment of existing installations in the RDBMS market has migrated to MySQL, other data storage technologies, or competing products.
In our case, we have 4 different front-ends, web and client-server, accessing and modifying our data. The results must be consistent across platforms. We have no interest in modifying 4 apps any more than necessary. All DB access is done via stored proc. All business logic is in stored procs, some of which may also be implemented in the front-end (app-specific input validation, for example).
And let's not forget the benefits of DRI, which front-end-centric developers love to hate.
March 11, 2013 at 10:25 am
Eugene Elutin (3/11/2013)
...
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... :hehe:
My customers didn't complain about Db performance.
They select records, insert, update and delete records, these are the most frequent actions.
SQLServer if good enough to respond quickly also when selecting 5000 records out of 5millions.
I know that the size of my applications (100k LOCs) and related DBs (around 100 tables) is far from the biggest ones, but I don't think this is the point.
The original question was "when to use stored procedures vs. SQL in the code". And I think that s.p. are rarely the best choice.
I completely disagree on your statement "there is no way to write good performing code for database without understanding how database (and its engine) works". I drive my car and my bike without a deep understanding on how the engine works. I know about fuel, oil, how to make safe breakings, what else ? About relational DB I know about keys, indexes, views, collations, and little else.
I know that I am not a Db expert, far from being, but many times you don't need to be such an expert to take a good decision.
I respect the experts in SQLServer, I like the overall ease of use of this product, far better than Oracle for instance. But I think the DB vendors try to push excessively the benefits of the s.p., and they do this because the try to tie the users to their engine. And I like freedom...
March 11, 2013 at 10:31 am
Eugene Elutin (3/11/2013)
...
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...
...
Are you sure?
What I can say... The red colour is nicer than blue one! Prove me wrong!
Haha, the colour ! No, I didn't think at colours...
But now I know that, to explain my point of view, I need to give some real example.
And now I have to pay attention to my customer...
But I will be back, maybe tomorrow.
Thanks for sharing your thoughts.
Bye for now.
March 11, 2013 at 10:42 am
fmuzul (3/11/2013)
...I completely disagree on your statement "there is no way to write good performing code for database without understanding how database (and its engine) works". I drive my car and my bike without a deep understanding on how the engine works. I know about fuel, oil, how to make safe breakings, what else ?
...
hmmm... I don't think it's a good example!
I guess it's enough for a lammer-driver to know about fuel, oil and safe breaking.
But, you will be surprised how much more professional driver must now about car, its mechanics and electronics in addition to different complex aspects of classical mechanics (physics) in order to compete in formula one, for example...
Do you want to be a lammer coder or ;-)?
March 13, 2013 at 8:54 am
Eugene Elutin (3/11/2013)
...
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...
...
Are you sure?
What I can say... The red colour is nicer than blue one! Prove me wrong!
Hello,
I promised to better explain why I think C++ is far better than T-SQL in many situations.
In a broader sense, why it is better to avoid using non-standard elements of a DBMS.
I think I could write a book on this topic, but now I will try to summarize some examples.
- Write a generic procedure for editing records in a simple table (those used for lookups).
This procedure receives only the table name as parameter, so it can be reused on different applications.
It manages inserts, updates and deletes, check if univoque keys and foreign keys are respected, do simple validations (length of text fields, valid numeric or date values).
The user lock the table and works on a copy of the records kept in RAM.
When the user confirm changes, start a transaction, replicate on the DB all the inserts, updates and deletes, then close the transaction.
Sometimes a special validation on one field is needed (example: an email address);
with C++ I just have to redefine a virtual method on the class managing this table editing.
- Give feedback to the user during a long procedure.
When one of my customer saves a complex document I need to save about 1000 records.
This can require 10 seconds or more than 1 minute, depending on the network speed and if the user is working on local or remote DB.
While the procedure saves all the records, it give a feedback to the user about what is happening.
This would be much more difficult using a s.p.
- Lock management on multiuser systems.
When I need to lock a record, a table, or an entire DB, I never use the specific locking system of the DBMS.
I use a simple table when I keep informations about who, where and when put the locks.
If user B tries to edit a record locked by user A, I can tell B exactly who, where and when locked that records.
I think it should be much more difficult using the native locks of the DBMS.
- 2 times I had to do complex calculations on 10000 or 100000 records from many tables
(not rocket-science math, just check for date ranges, and simple arithmetic operations like add or multiply).
The original procedures, doing many operation on the db, were too slow.
I reimplemented the procedure in C++ doing an initial load of most data in RAM.
I spent about 1 minute for this initial load, but then:
I reduced time from 2 hours to 0.5 seconds on the first system (order calculations on a MRP system),
and improved from 1 to 40000 calculations per second on the second system (price calculations on a complex catalog).
These are real "performance gains".
- Create a demo version of an application, using data on simple .csv or .dbf files rather than a real DB.
Using only standard SQL my application can run (most of it) also with very basic storage systems.
If I use s.p. this would not be possible.
Bye
March 13, 2013 at 9:11 am
fmuzul (3/13/2013)
Eugene Elutin (3/11/2013)
...
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...
...
Are you sure?
What I can say... The red colour is nicer than blue one! Prove me wrong!
Hello,
I promised to better explain why I think C++ is far better than T-SQL in many situations.
In a broader sense, why it is better to avoid using non-standard elements of a DBMS.
I think I could write a book on this topic, but now I will try to summarize some examples.
- Write a generic procedure for editing records in a simple table (those used for lookups).
This procedure receives only the table name as parameter, so it can be reused on different applications.
It manages inserts, updates and deletes, check if univoque keys and foreign keys are respected, do simple validations (length of text fields, valid numeric or date values).
The user lock the table and works on a copy of the records kept in RAM.
When the user confirm changes, start a transaction, replicate on the DB all the inserts, updates and deletes, then close the transaction.
Sometimes a special validation on one field is needed (example: an email address);
with C++ I just have to redefine a virtual method on the class managing this table editing.
- Give feedback to the user during a long procedure.
When one of my customer saves a complex document I need to save about 1000 records.
This can require 10 seconds or more than 1 minute, depending on the network speed and if the user is working on local or remote DB.
While the procedure saves all the records, it give a feedback to the user about what is happening.
This would be much more difficult using a s.p.
- Lock management on multiuser systems.
When I need to lock a record, a table, or an entire DB, I never use the specific locking system of the DBMS.
I use a simple table when I keep informations about who, where and when put the locks.
If user B tries to edit a record locked by user A, I can tell B exactly who, where and when locked that records.
I think it should be much more difficult using the native locks of the DBMS.
- 2 times I had to do complex calculations on 10000 or 100000 records from many tables
(not rocket-science math, just check for date ranges, and simple arithmetic operations like add or multiply).
The original procedures, doing many operation on the db, were too slow.
I reimplemented the procedure in C++ doing an initial load of most data in RAM.
I spent about 1 minute for this initial load, but then:
I reduced time from 2 hours to 0.5 seconds on the first system (order calculations on a MRP system),
and improved from 1 to 40000 calculations per second on the second system (price calculations on a complex catalog).
These are real "performance gains".
- Create a demo version of an application, using data on simple .csv or .dbf files rather than a real DB.
Using only standard SQL my application can run (most of it) also with very basic storage systems.
If I use s.p. this would not be possible.
Bye
Nothing here to work with. Many of us have optimized SQL to perform similar calculations in the database reducing processing time from hours (or longer) to subsecond. What's the point?
How much of the work are you doing in your application that could be done in the database and by doing so reduce the load on the network communicatioin between your database and your application?
We will just have to agree to disagree.
March 13, 2013 at 9:42 am
Lynn Pettis (3/13/2013)
Nothing here to work with. Many of us have optimized SQL to perform similar calculations in the database reducing processing time from hours (or longer) to subsecond. What's the point?
How much of the work are you doing in your application that could be done in the database and by doing so reduce the load on the network communicatioin between your database and your application?
We will just have to agree to disagree.
Hi Lynn, I'm pleased you find ways to optimize SQL from hours to subseconds, and I can't affirm for sure that it could not be done on the applications on which I worked on.
But I would bet that no, C++ would always have an order of magnitude of advantage over Stored Procedures.
I know it is difficult to speak about theoretical systems.
We should have a real test case to solve and compare my solution with your.
Maybe one day...
🙂
You gave me an idea :exclamation::
someone should suggest to SQLServerCentral to start some form of "competition among developers/DbAdmins". A problem of data manipulation, in a field that people can easily understand, and let people implement a solution using their preferred methods (C++, PHP, Javascript, batch files, with Stored Procedures or not, no limits to the fantasy...).
Then publish them and let people compare, which is simpler, which is faster to implement, which performs better.
It would be very easier to discuss about a real case we all know.
Maybe we could all learn something we don't know.
Bye.
March 13, 2013 at 9:47 am
fmuzul (3/13/2013)
Lynn Pettis (3/13/2013)
Nothing here to work with. Many of us have optimized SQL to perform similar calculations in the database reducing processing time from hours (or longer) to subsecond. What's the point?
How much of the work are you doing in your application that could be done in the database and by doing so reduce the load on the network communicatioin between your database and your application?
We will just have to agree to disagree.
Hi Lynn, I'm pleased you find ways to optimize SQL from hours to subseconds, and I can't affirm for sure that it could not be done on the applications on which I worked on.
But I would bet that no, C++ would always have an order of magnitude of advantage over Stored Procedures.
I know it is difficult to speak about theoretical systems.
We should have a real test case to solve and compare my solution with your.
Maybe one day...
🙂
You gave me an idea :exclamation::
someone should suggest to SQLServerCentral to start some form of "competition among developers/DbAdmins". A problem of data manipulation, in a field that people can easily understand, and let people implement a solution using their preferred methods (C++, PHP, Javascript, batch files, with Stored Procedures or not, no limits to the fantasy...).
Then publish them and let people compare, which is simpler, which is faster to implement, which performs better.
It would be very easier to discuss about a real case we all know.
Maybe we could all learn something we don't know.
Bye.
I will disagree with this statement "But I would bet that no, C++ would always have an order of magnitude of advantage over Stored Procedures." completely.
March 13, 2013 at 10:05 am
Lynn Pettis (3/13/2013)
I will disagree with this statement "But I would bet that no, C++ would always have an order of magnitude of advantage over Stored Procedures." completely.
Me too.
Isn't that a C++ vs SQL question anyway, stored procedures have nothing to do with it?
Given the description of the work being done, I'd be willing to bet the SQL was being run row-by-row rather than set-based.
A VW will beat a Ferrari if the Ferrari's not allowed out of 1st gear 🙂
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply