Do we need to promote SP's anymore?
-
Caveat - I am VERY pro store procs, I not allow direct string access to the database. I disagree with you're premise that they are simpler to debug in strings, this may be true if you have no TSQL experience but not if you have even a modicum of skill. If you are doing fairly standard CRUD work then your arguments are absolutely valid. However if you are doing any serious volume manipulation then procedures are easier to build and tune. I can't imagine writing a 100 line proc as strings, it just would not make sense. As for processing (complex, volume) data in the business layer this just does not work. We had a team use Enterprise library and do all the processing in the BL layer, they needed 2 application servers to match the DB server performance. I do sympathise with your stand, I recently had to get involved in PLSQL, my skills are a decade out of date, what a bloody nightmare. I tweaked my code generator to write PLSQL CRUD procs and get an Oracle specialist to do anyhting more complex. Also any (LOB) developer worth paying should know SQL and the database they are working on!
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
I not allow direct string access to the database.
Then how do you execute the procedures? :-D As to debugging the SQL, I see no difference -- either way, I try them out in SSMS.
Mycroft Holmes wrote:
writing a 100 line proc
I've never seen one of those, sounds awful. On the other hand, I've written at least one Insert like:
SetCommand (
@"
INSERT INTO tablex
(
field0
,
field1
,
...
,
fieldn
)
VALUES
(
@Param0
,
@Param1
,
...
,
@Paramn
)
"
,
value0
,
value1
,
...
,
valuen
) ;Which I guess could be a hundred lines. :-D
-
Thanks for your input. I agree there are times where they are absolutely necessary ( mostly server side heavy manipulations). My problem is they are being pushed where they aren't needed. The problem with DB's (tables structures and SP's) is that they go rigid quickly and you dare not change them, I have had clients where 1500 SP's were quite common and no one knew how they worked.
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.
Mehdi Gholam wrote:
server side heavy manipulations
All manipulations (other than formatting for display) should be server-side regardless of how the SQL gets there.
-
Mehdi Gholam wrote:
where 1500 SP's were quite common and no one knew how they worked
Thats because the stupid bastards (management) don't retain the IP (as in the business knowledge) and the skills to manage their own data. So many organisations feels they can outsource their development work, get a monkey to do the work and maintain a viable business plan - idiots. And I'm a contractor who makes a very comfortable living from this business practice. Don't push that button I can go on for hours on this subject!
Never underestimate the power of human stupidity RAH
finger poised... push... click... Come on lets have some of your war stories!
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.
-
Your thought please on this topic in the QA forum. QA : Do we need SP's anymore?
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.
About security: No real difference if you use parameterized queries. About performance: When the query is compiled there should normally be no difference. But, a stored procedure is precompiled, so the time to optimize and compile the query can be written off for the SP. This is sometimes a larger part of the execution time. (Yes, I know that most DBs nowadays cache the execution plans). On the other hand, with a precompiled SP you will always have the same execution plan. This is obviously not always optimal. Think for example of the
LIKEorINclauses. They might need very different scanning of the indexes depending on the parameters supplied. They lock you down to a vendor: So does dotnet. And a couple of pros and cons from me: Making dynamic SQL in a SP is a complete PITA IMAO. Think of the roundtrip time from your application to the DBServer. This can sometimes be a serious bottleneck -
Your thought please on this topic in the QA forum. QA : Do we need SP's anymore?
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.
Mehdi Gholam wrote:
after all the questions I have answered here I thought a more substantial one was needed here. :)
Why?
Mehdi Gholam wrote:
Your thought please
Sometimes they're an advantage, sometimes they're not. Sometimes I use a hybrid version, where I store my inline-SQL in a textfile. Sometimes it pays to have the flexibility of updating without recompiling. Promote experimenting :)
Bastard Programmer from Hell :suss:
-
Mycroft Holmes wrote:
I not allow direct string access to the database.
Then how do you execute the procedures? :-D As to debugging the SQL, I see no difference -- either way, I try them out in SSMS.
Mycroft Holmes wrote:
writing a 100 line proc
I've never seen one of those, sounds awful. On the other hand, I've written at least one Insert like:
SetCommand (
@"
INSERT INTO tablex
(
field0
,
field1
,
...
,
fieldn
)
VALUES
(
@Param0
,
@Param1
,
...
,
@Paramn
)
"
,
value0
,
value1
,
...
,
valuen
) ;Which I guess could be a hundred lines. :-D
PIEBALDconsult wrote:
I not allow direct string access to the database
Read Table/Views instead of database - my error.
PIEBALDconsult wrote:
either way, I try them out in SSMS.
Ok so you write the TSQL in SSMS and run it in the BL and yet you don't want to put the tested code into a proc but move it into the BL. I actually agree with you when it come to the CRUD procs (I use a code generator and have not written an insert procedure for many years), even some simple queries could have a life in either format. A complex query with multiple joins, using temp or var tables and with interim results just will not go into code, it belongs in a proc. As to the 100 lines crap you put up X| bleh I write rather tight code and 100 lines on a complex query is not unusual, just before it is turned into a proc I run Red-Gates formatter over the code, it blows it out as above but makes it eminently more readable (and therefore supportable).
Never underestimate the power of human stupidity RAH
-
finger poised... push... click... Come on lets have some of your war stories!
Its the man, not the machine - Chuck Yeager If at first you don't succeed... get a better publicist If the final destination is death, then we should enjoy every second of the journey.
Mehdi Gholam wrote:
Come on lets have some of your war stories!
Not a chance, both of us have better things to do :sigh:
Never underestimate the power of human stupidity RAH
-
Mehdi Gholam wrote:
after all the questions I have answered here I thought a more substantial one was needed here. :)
Why?
Mehdi Gholam wrote:
Your thought please
Sometimes they're an advantage, sometimes they're not. Sometimes I use a hybrid version, where I store my inline-SQL in a textfile. Sometimes it pays to have the flexibility of updating without recompiling. Promote experimenting :)
Bastard Programmer from Hell :suss:
Eddy Vluggen wrote:
Sometimes it pays to have the flexibility of updating without recompiling.
Perhaps for internal applications, but not when the application is sold to clients.
-
Eddy Vluggen wrote:
Sometimes it pays to have the flexibility of updating without recompiling.
Perhaps for internal applications, but not when the application is sold to clients.
I think we need to differentiate the developers here, commercial and corporate, there are completely different mindsets required for the each of these :) As a corporate developer I can understand Eddy's stance, as a commercial developer I guess you would be horrified.
Never underestimate the power of human stupidity RAH
-
Eddy Vluggen wrote:
Sometimes it pays to have the flexibility of updating without recompiling.
Perhaps for internal applications, but not when the application is sold to clients.
-
About security: No real difference if you use parameterized queries. About performance: When the query is compiled there should normally be no difference. But, a stored procedure is precompiled, so the time to optimize and compile the query can be written off for the SP. This is sometimes a larger part of the execution time. (Yes, I know that most DBs nowadays cache the execution plans). On the other hand, with a precompiled SP you will always have the same execution plan. This is obviously not always optimal. Think for example of the
LIKEorINclauses. They might need very different scanning of the indexes depending on the parameters supplied. They lock you down to a vendor: So does dotnet. And a couple of pros and cons from me: Making dynamic SQL in a SP is a complete PITA IMAO. Think of the roundtrip time from your application to the DBServer. This can sometimes be a serious bottleneckJörgen Andersson wrote:
No real difference if you use parameterized queries.
Not true. You can set a DB so that the application has access ONLY to EXECUTING the stored procedures, if you exclusively use stored procedures. So having suddenly becomes much harder. Otherwise you HAVE to give read and write access to the tables to your application - MUCH less secure.
MVVM# - See how I did MVVM my way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
Jörgen Andersson wrote:
No real difference if you use parameterized queries.
Not true. You can set a DB so that the application has access ONLY to EXECUTING the stored procedures, if you exclusively use stored procedures. So having suddenly becomes much harder. Otherwise you HAVE to give read and write access to the tables to your application - MUCH less secure.
MVVM# - See how I did MVVM my way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
Minimizing the attack surface. You're quite right, and I stand corrected. My five.