|
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
|
|
|
|
|
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: 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.
|
|
|
|
|
Mehdi Gholam wrote: Come on lets have some of your war stories!
Not a chance, both of us have better things to do 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
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.
|
|
|
|
|
Mycroft Holmes wrote: I not allow direct string access to the database.
Then how do you execute the procedures? 
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.
|
|
|
|
|
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  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
|
|
|
|
|
It is well-known that I am basically anti-stored-procedure. Use them when you must, but not when you don't. (Currently the only time I must is when the boss insists. )
In the late 90s, when I first encountered SQL Server (6), we were accessing it via ODBC, and stored procedures were the only way to use transactions, so any code that altered the contents of the database had to be in a stored procedure (queries were not in stored procedures, I still boggle at people who insist on writing stored procedures for queries). I got to the client site to install the part of the system I supported and the client asked me to add a feature (a reasonable feature). It only involved a new column in a table and a small change to a stored procedure, but I didn't want to do that in the field without testing it. Unfortunately the program manager insisted, so I walked into the server room, started up enterprise mangler, added the column, and altered the stored procedure. It worked fine. Then I had to save the a script to add the column and the new version of the stored procedure to a floppy so I could put them into version control when I got back to the office -- that was fine, the plane didn't crash on my way back and the changes got into version control. But, twice when I was still with the company, and at least once since then, the stored procedure disappeared  !
My very biggest complaint about stored procedures (or any code stored in the database (it's a database, not a codebase)) is that it is too easy to change (or delete), either by mistake or by mischief.
On the other hand, making proper changes may be more difficult to make because you not only have to deploy new executables, but also ensure that the procedures and such are updated as well.
Whereas, with all the SQL in the executable, you only have to deploy the executables (after proper review and testing), and there's nothing in the database that allows anyone to alter production code.
And if you have multiple clients with customized versions of your software (which is my experience), you don't have to track custom procedures as well as custom program code.
It is my experience that stored procedures increase maintenance costs.
Where I am now, there are stored procedures and views galore, all of which could easily be in program code. One of my first tasks is to get them all into version control (the program code is already there). As part of that, I'm comparing them between dev, test, and prod. I have already found several procedures that won't execute because the tables have been altered, and some in prod that aren't in dev. But at least it pays well  .
|
|
|
|
|
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 LIKE or IN clauses. 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
|
|
|
|
|
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.
|
|
|
|
|
Minimizing the attack surface. You're quite right, and I stand corrected.
My five.
|
|
|
|
|
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 
|
|
|
|
|
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
|
|
|
|
|
PIEBALDconsult wrote: Perhaps for internal applications, but not when the application is sold to clients.
Good point 
Bastard Programmer from Hell
|
|
|
|
|
Pls can someone direct me to where i can get tutorials for MySQL workbench 5.2.34 CE
|
|
|
|
|
See my answer I posted in the Q&A[^] you created.
0100000101101110011001000111001011101001
|
|
|
|
|
Hi wonderful experts, this problem has become a headache to me and so I consult you.
I need to generate IDs that are unique for records. I want the IDs to include the year in which the record is added to a table. So for instance, the first record will have an actual value of say
10001
but because i want the year to be part of the ID and where for instance the record is added in the year 2011, I will want the ID to be
201110001
The subsequent records to follow will have IDs as
201110002
201110003
201110004
201110005
.........
.........
.........
where the numbers have been stuck to the year to generate the IDs. Then during the next year which will be 2012, the first record in that year will start the count from 10001, and when stuck to the year, subsequent IDs will be
201210001
201210002
201210003
201210004
.........
.........
.........
I want all these records to be in the same table. I have been thinking of ways around this but I don't get a way of achieving this. Please help if there are ways to do this. I'm using Microsoft SQL Server 2005. Thanks.
|
|
|
|
|
Personally I would create a table to define the sequence, start the value at 10000, increment it as necessary, and reset it when the year rolls over.
What do you plan to do if your sequence exceeds 99999?
|
|
|
|
|
Dan_K wrote: I want the IDs to include the year in which the record is added to a table
Mistake number 1, commited by all newbie database designers, yup every one of them unless somone intervenes.
A primary key should not hold any intelligent information, if you want the year the record was created then add a datetime field for the job DO NOT USE THE PRIMARY KEY.
Piebald will tell you to use GUIDs, I like IDENTITY myself but that is just preference, neither has any intelligence in the information, it is ONLY there to identify the record and should not be used for any other reason, this includes sorting. You do not care if there are gaps or missing records or the sequence changes, it is only to identify the record.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
well, to be fair he never actually says that he is using this as a primary key. It may be something like a business transaction id, where for audit purposes there must never be a gap in the sequence. Think payments between bank accounts: if there is a gap in the sequence numbers, what happened to that missing payment? It will ring an alarm bell with the auditors and they will want an explanation.
|
|
|
|
|
Oooh I hate it when someone brings logic to a good rant 
You are quite right in that a sequence may be a business requirement, I just focussed on the IDENTITY and went into rant mode, I'll bet you a beer that is NOT what he had in mind though.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In fact the OP doesn't say that there should be no gaps, just that the id should be unique, so I think you are probably right. Even if he did want no gaps, IDENTITY would be the wrong solution since it doesn't guarantee no missing values in the sequence.
|
|
|
|
|
That's kinda what I figured. I recall back when I was using OpenVMS, support calls got an ID number of the form YYMMDDxxx where xxx began at 001 each day and incremented throughout the day (I hope they didn't get more than 999 calls in a day). It made for a more memorable ID.
And, no, it wouldn't have to be used as the primary key (but maybe it was).
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
