- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Well, once you start using stored procedures and foreign keys and triggers and other fancy bells and whistles like that, you run a real risk of actually enforcing data integrity, and nobody wants that as far as I can tell.
Admin
In order for a performance optimization to be considered a hack, it would have to be implemented in a way that somehow detracts from the intended design. Building an optimized data storage engine for a project would usually be considered a hack unless there are really strange circumstances.
However, a covering index has no hack elements to it. It performs its job without any adverse effects (there are zero non-performance side effects of implementing covering indexes). It is totally reversible in seconds even while the system is running. It is transparent to the application. It does not introduce any new maintenance issues. It is widely understood. It is how the database vendors intended the solution to be designed (the performance tuning courseware from both Oracle and Microsoft teach covering indexes, I don't know if IBM does, but it is on their website).
Indexes are performance tools, you include what gives the best performance. There is no concept of "what you should include" other than what works best. A database that tends to be read more will (and should) have more indexes that include more columns. I could understand your point if there were even a single downside other than the incredibly obvious and well-known downside of indexing in general -- it slows down data manipulation.
Do you consider the fact that you have to turn your steering wheel counterclockwise in order to turn your car left a hack? That is how it was designed, but I feel "I shouldn't have to do it". Therefore, it is a hack. Brillant.
Admin
Yes, it does. It, by definition, contains more information than is required to index the table.
First, you're wrong; second, that's irrelevant to deciding whether it's a hack or not.
Wrong, situation dependent. Index builds on new tables may take hours.
Wrong, depending on the data, adding new indexes may extend maintaince time or cause other issues. PostgreSQL for example, requires periodic reindexing that takes time, especially on large indexes. Most other databases have other such pitfalls.
And? Irrelevant to determining whether it's a hack or not.
No, they didn't. If that were true, we'd use covering indexes all the time and we don't. It is a solution.
No, that is not there sole purpose in many RDBMS. They enforce data integrity in several.
There are, you seem to be quite igorant of the realities of DB maintance and planning. Except this isn't a comparable scenario. Indexes were meant to index, not store data for retreival. They're mearly lists of pointers to the actual data, ordered in some useful fashion. They're addtionally used for relational integrity in certain situations, though not all DBMS use them for that, and it's not the only way to enforce uniqueness.
Admin
Do you two want a ruler?
Admin
The old "should we have stored procedures debate" misses one important thing out.
Sometimes it is the only way to get something done with out granting excessive rights to a user. For example, we had a situation where one user needed to be able to truncate the table in another user's schema. To do this the user needs to be able to DROP the table. But, Oracle doesn't allow you to drop another user's objects, so the only way to do this is to grant DROP ANY rights to the object needing to do the truncate.
Or, you can write a stored procedure which the dropping user calls and only has the rights to run that. Safe, secure, no problem.
Admin
....we learned how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven ...
BINGO
That was quick
Admin
Yes, but...
shouldn't these "constants" be read from a config file?
Just in case some DB starts to use "GET" instead of "SELECT".
just my 2ct.
Happy Hacking!
Admin
Well, SQL-implementations (so to speak) are quite good. I'm not sure it's really great as language but that's what we have.
Database (or other) vendors propably don't want to go through pains of standardizing any radically different language.
Admin
Shouldn't this be EnterpriseConstants.IDIOTS ?
Admin
Just in case "IDIOTS" ever gets redefined as "marketing visionaries"...
Admin
never mind all else..that person can't spell the word "PRODUCTS"
"public const string SELECT_ACTIVE_PRODCUTS "
Admin
I'm still a student and surely have produced a lot of WTFs myself, but I can't believe that people who produce such unbelievable crap actually are allowed to stay in their job.
Admin
No decent DBA would do that, as it is an application developer's decision whether or not to embed.
My argument (accepted most places) is that there should be an API to the database, and the middle tier should be a client to the database. In fact, it should be impossible to connect as a user with generic 'select' privileges, much less insert or update.
That way, the database developer can ensure that all the selects which are required are (a) efficient, (b) auditable and (c) EFFICIENT.
Justin.
Admin
Well, here in the wholly-owned subsidiary of ENGLISH_DEFS.whatAClockMeasures + " " + ENGLISH_DEFS.personWhoIssuesACaution, we have a fun mix of DBAs, often in the same groups, who either demand or will never use STPs.. That one's funky. I've seen apps need to be competely rewritten when the backend support eprson changes because they will no longer allow direct sql, and I've seen the opposite.
Admin
You mean DBAs that refuse to do any work?
Admin
Well... at least they declared those as 'const' and not 'static readonly'...
Admin
Quality suggestion, which begs the questions:
- Why on Earth would you ever want to embed SQL statements within your source code?
- Does it really have any place in there at all?
- Surely it gets in the way of any surrounding logic?
- Why is embedding the SQL into source code favourable to reading in from a config file or a resource bundle (or similar)?
- Is it wrong of me to want to invite unholy retribution upon all those that embed SQL within their source code? :D
Fantastic debate BTW, it's been good reading!Kind Regards.
Admin
Allow me...
public class SqlBits
{
public const int SqlBitZero = 0;
public const int SqlBitOne = 1;
public const int SqlBitFileNotFound = 2;
}
public class SqlBytes
{
public const byte SqlByteZero = (SqlBits.SqlBitZero << 8) | (SqlBits.SqlBitZero << 7) | (SqlBits.SqlBitZero << 6) | (SqlBits.SqlBitZero << 5) | (SqlBits.SqlBitZero << 4) | (SqlBits.SqlBitZero << 3) | (SqlBits.SqlBitZero << 2) | (SqlBits.SqlBitZero << 1) | (SqlBits.SqlBitZero << 0);
public const byte SqlByteOne = (SqlBits.SqlBitZero << 8) | (SqlBits.SqlBitZero << 7) | (SqlBits.SqlBitZero << 6) | (SqlBits.SqlBitZero << 5) | (SqlBits.SqlBitZero << 4) | (SqlBits.SqlBitZero << 3) | (SqlBits.SqlBitZero << 2) | (SqlBits.SqlBitZero << 1) | (SqlBits.SqlBitOne << 0);
// ...snip...
public const byte SqlByteTwoHundredAndFiftyFive = (SqlBits.SqlBitOne << 8) | (SqlBits.SqlBitOne << 7) | (SqlBits.SqlBitOne << 6) | (SqlBits.SqlBitOne << 5) | (SqlBits.SqlBitOne << 4) | (SqlBits.SqlBitOne << 3) | (SqlBits.SqlBitOne << 2) | (SqlBits.SqlBitOne << 1) | (SqlBits.SqlBitOne << 0);
}
public class SqlChars
{
public const char SqlCharAsciiSpace = (char) SqlBytes.SqlByteThirtyTwo;
public const char SqlCharAsciiA = (char) SqlBytes.SqlByteSixtyFive;
public const char SqlCharAsciiB = (char) SqlBytes.SqlByteSixtySix;
// brain explodes
}
Admin
1. To access the database ;-)
There are better alternatives like Hibernate, but sometimes they are an overkill. It's arguably a bad idea to bury SQL deep inside the business logic but it's IMO ok to do it in a source module dedicated to database access. Some people have a tendency to add more and more levels of indirection but this only complicates things and (in many cases) gains nothing.
4. a) It's easier to understand the code if you don't have to lookup the config files.
b) If a statement needs to access a lot of host variables (like an insert or update statement), putting it into a config file doesn't improve anything.
c) If you use a precompiler, it can check the SQL code during compile time.
5. Yes
Admin
Er, spot the deliberate mistake. Yeah, deliberate. Honest.
Admin
How about creating a code generator to generate the data access layer for the database? Although you will spend a lot of time implementing such a generator, you may save a lot of time when the database is big or when its schema changes. And of course you can reuse such a tool for more than one application.
Admin
"Java in oracle,"
hahhahahahahahah...
you were joking right? oh...
Admin
Java stored procedures (in Oracle) are usefull for ... well, those things you probably wouldn't do in the database anyway. Like file system and network operations. For business logic, PL/SQL is the better choice.
Admin
I like how you state the any use outside of "indexing rows" is a hack, but down below you mention indexes can be used to implement data integrity (which is more of a hack than covering indexes could ever be). Just because it's outside your definition of what indexes should do, doesn't make it so.
Covering indexes simply index better than non-covering indexes. By that logic clustered indexes and fillfactors are beyond the basic concept of indexing, so they are hacks too. Unless, of course, you apply recursive logic so that anything you approve of isn't a hack simply because you approve of it.
So how would you define a hack? Something that annoys you?
Sure index builds can take hours. But, I said "reversible". Removal of non-clustered indexes happens in seconds on even the largest tables. Only non-clustered indexes can be covering indexes, so my statement is correct.
I said "new" maintenance issues. All DBMSs require index maintenance, but DBAs already know that. Also, indexing strategies change often so any DBA worth his paycheck will have an automated process of some sort that finds and rebuilds all indexes. In MSSQL, it's a freakin' wizard.
Back to what is a hack. Now you have rejected "has weird side effects" and "obscure" from the definition of hack. What's left?
Like all indexes, they introduce overhead. So, you never always use any indexes. If you never use them, then you are leaving a lot of tuning on the table. Since we often use them, when appropriate, they are a ligitimate solution just like defaults, check constraints, stored procedures, and 500 other solutions.
Ahhhh... so indexes have a side purpose other than performance. So, we don't implement them for best performance. Instead, we implement them according to our philisophical opions about how Dr. E. F. Cobb would have used them in the 1970s. So, how do you feel about JOINs?
Nope. It happens to be something I do rather well.
Covering indexes are being used for data retrieval. Just optimized one step beyond non-covering indexes. It's like the fundamantals of Object Oriented Programming: it doesn't matter how an ArrayList is implemented, what matters is what it's does. As long as you use it in appropriate situations, it will work for you. The writers of your chosen framework will evolve the internal implementation of ArrayList in future versions, and they won't even tell you they did it. Indexes are the same way -- every major database vendor has evolved indexes to the point where they will shortcut the table lookup step if possible. So, we take advantage of that by sometimes creating indexes that encourage shortcutting. Having some sort of chip on your shoulder about doing so is a bit weird.
Admin
All right, I'll give you that one about a source module dedicated to database access, but I've seen so many bad examples where inexperienced programmers litter their code with SQL statements, where it would be far more readable to lookup the sql from some place else, especially if (something common to all coding debates, the dreaded 'if') the statement is itself well-named. I guess this is the "levels of indirection" you talk about? Arguably, a source module dedicated to database access/statements is a form of indirection, but any full sql statement embedded along side logic is a maintenance nightmare, especially when SQL statements need to be re-used across an application. Couple this with the inherent "Copy & Paste Inheritance" used by the general OO software-engineering public (ok, VB programmers ;P) and such a simple "Why not embed?" turns into a "I'm sure I've updated all the copies of that SQL... Where's the last one/ten?".
So, I'd ask you to pick from one of the follwing:
Snippet one:
<font size="2">// Lots of logic
String mrQuery = "SELECT wibble FROM wobble WHERE bounce = ?";
// Do thngs with mrQuery</font>
Snippet two:
<font style="font-family: Courier New;" size="2">// At top of class, file or whereever constants are declared
public static final String mrQuery = "SELECT wibble FROM wobble WHERE bounce = ?";
// Later in file
// Do things with mrQuery</font>
Snippet three:
<font size="2">public interface MrQueryAndOtherAnimals
{
// other queries
String mrQuery = "SELECT wibble... you get the picture";
// other queries
}</font>
<font style="font-family: Courier New; color: rgb(0, 100, 0);" size="2">// At some other place:
// Do something with MrQueryAndOtherAnimals.mrQuery;</font>
Snippet four:
<font size="2">// ok, I'm getting board, so assume the lookup set up is done elsewhere
String mrQuery = someLookupMechanism.find("mrQuery");
// Do things with mrQuery that mrsQuery may find disturbing</font>
Do you see my point? There's creeping indirection, but arguably snippet one leads to much more of a maintenance headace than say snippet 3, and snippet four is bordering upon the pathalogical.
The goal for me here is to formulate a strategy that:
- Isn't a complete pain in the rump to implement.
- Isn't a complete pain in the left ventrical to maintain.
- Encourages developers to think of the "right" solution, rather than just the "quick" solution (which may involve shooting your line manager if he's a complete smacktard, or finding a better job/line manager. I;ve worked for Line Manager who's philosophy was always "Do it the quick way". He's caused so many problems that he surely must be punishing us all for some indiscretion during a former life)
We've got three large systems here, one in C++ and two in Java. The C++ project and one of the Java projects use embedded SQL and are both complete nightmares to maintain. The amount of effort it takes to make seemingly simple changes are now immense. The second Java project has its SQL embedded into a suite of configuration files which gives us:- Ability to review SQL without reviewing the source code.
- Abilty to review the source code without the SQL.
- Review both together (you obviously need to!) :)
- A step in the right direction for database vendor independence (theoretically) without the need to recompile.
- Changes to the SQL are not spread across multiple files across multiple packages, they are neatly contained together, giving benefits for source control as the number of affected files for a given change can be small, again without having to recompile.
This is not the first time this has happened. Unless you are bloomin careful and repeatedly beat your developers with the correctness-stick, emdedding your SQL in anything other than a mechanism that supports simple and striaghtforward re-usablility is the equivalent of sticking forks in your legs and wondering why you are bleeding. Anyhoo, I'll not convert any of you, Software Engineers by their very nature are stubborn beasts, but it's fun to soapbox :DKind regards
Admin
Wouldn't you then want the statements defined in a properties file?
Anyways, I can think of at least one reason for doing that. Maybe they got tired of people making typos and not catching them in unit testing. Now when they misspell 'SELECT' they will just generate a compile error...
Admin
OMG I can't believe my eyes...
Tell me where you work... I never ever ever want to work there...
Admin
If you have to do that you elsewhere made a BIG design error. It is a hack...
Admin
First of all can I say how much I enjoy this forum and how much I am learning!
In my organisation (I'm quite new), we LOVE stored procedures. Rather than running an update statement, we'll create a stored procedure that takes all the variables we need and create an update statement using them. None of these is ever reused or anything, we make a new one with table names hardcoded into the procedure each time.
We also wrap up all sorts of processes that would be much nicer in a "real" programming language. For example today my task is to write a new stored procedure which writes data to a file, calls a shell command to combine that with some formatting information and write the result to another file, call another shell script to turn that result into a PDF and then send an email with the PDF file attached.
What do you guys think about doing things in this way? It "smells" wrong to me, I don't really want to be operating on files from inside a stored procedure, but I'm not sure what I'm basing this on. The star programmer says its always the right way so that we can change programming languages in the future, but I'm not sure we're using the right tools for the right jobs. Any thoughts? I'm calling the procedure from a programming language that is quite capable of doing what is needed!
Admin
MrGoaty, why would I want to use the same SQL-Statement (as a string) in different places? Why not have exactly one method that does that query, and call that method whereever necessary?
Problem of the config file method:
Let's say you have a query that can be retrieved by calling
<font size="2">String query = someLookupMechanism.find("mrQuery");</font><font size="2"></font><font size="2">How do you find all parts of the code that use mrQuery? Can be difficult if a wise guy writes code like that:
</font>
<font size="2">String query = someLookupMechanism.find((person.isMale()?"mr":"ms")+"Query");</font><font size="2"></font><font size="2">
</font>If you can't easily find the places where a query is used, changing a query becomes dangerous.
(Of course the same can happen with methods called by the means of reflection, but that seems rather unlikely to me)
Admin
I'd say your instincts are good :) However, unless you have the political and persuasive skills of a social genius, then you will be stuck whilst the "star programmer" maintains any political power. I've tried to convince a manager that reinventing J2EE wheel was a bad idea, that nearly cost me my job (I didn't realise he was on £100 an hour and thus needed an incredibly long task to perform).
The database is there to databasey things, like store information so it can be read back later. Can I think of another analogy? Yeah, why not. It's like cooking all your food on the hot engine block of your car, you can do it, sure it may even look cool, but the kitchen cooker is designed for that job and would be a far better tool to use. Safer too :)
Kind regards,
Admin
Unless you have most or all logic in stored procedures, I would not recommend writing files from a stored procedures. Oracle's DBMS_FILE has some limits and there are also security and performance issues. For example, the formatting script is called with the OS privileges of the database process, so it has the necessary rights to destroy the database.
Admin
I'd say you are technically correct, which we all know is the best kind of correct :) TBH I'd already described snippet 4 as bordering upon the pathalogical and the method by which I'd chosen to advocate its use does open itself to this kind of retort. I should've been more robust.
Anyway, I believe we are beginning to argue a very similar philosophical point, which is good. I think we are both in agreement that randomly shoving SQL statements here, there and everywhere is tantamount to sociopathic behaviour, and that an organised approach is far superior. We merely differ in the means by which we'd choose to organise them. Thank you, this has been enlightening :)
Admin
TBH, my view on the problem is strongly influenced by my history of C programming.
In C (using Oracle's preprocessor), it's eigher
or two pages of complex code to create the statement "object", assign the host variables, execute the statement, read the results etc.
It requires a certain amount of masochism not to embedd SQL in that situation.
Admin
I would say that treating the entire database as an object, and then having stored procedures being treated as accessor methods is probably the best thing you can do.
Application developers should look at the database as an amorphous blob of data, and use the given methods to access that data.
Stored procedures tie you to a vendor. True. OTOH, not having stored procedures is worse.
Lack of version control -- not too difficult to fix.
Application logic duplicates data logic. I would say that this is a pretty small thing to worry about. It violates the DRY principle, and I would remove that logic from the application itself.
Admin
This "object" is the reference implementation of the God Object antipattern, right?
;-)
Admin
In the company I work for we mostly use MS-SQL Server for our application, then we had a customer come along, a far far bigger customer than all the existing ones combined. They wanted to use Oracle, no way they'd use SQL Server. We could do this very simply because we had almost no code that was SQL Server specific. We got a several million pound contract and completed the conversion very smoothly. Now we can run our application on any combination of AIX / Windows and Oracle / T-SQL.
Don't be so short sighted.
I don't get the WTF in this article. It looks like it was code from a DB back end that no application programmer should have to touch - there are probably two or more layers of code on top of it. I would much rather use something like this using recordset objects than directly embedding SQL everywhere. Far easier to maintain.
Admin
You are right, if you make some kind of standard software (for customers yet to come), DB independence is a good thing.
For in-house development or customized software, database switches are very unlikely.
Admin
At first I couldn't tell which side of this discussion you were supporting. However, even in using "select *", your code is still likely to break even if you are only accessing the columns by column number (shudder), because now you're putting "project_id" in the variables for customer name...
Admin
If you're talking about the ninth bit, don't worry about it - that will come in handy for parity checks![:D]
Admin
That's a fair point. I suppose it's horses for courses isn't it? In my experience, the embedding in Java and C++ has caused an awful lot of avoidable pain, but it depends very much on how the SQL is used and with what library or mechanism. Fascinating! :) However, if anyone does it here, POW!!!! ;)
Kind regards.
Admin
So basicly you mean something like Middlegen?
http://sourceforge.net/projects/middlegen
Admin
No, they don't. Once I have all the information needed to index the database, any other data is excessive.
Rather, they are implementation details. However, you're correct in that they're beyond the basic concept.
Any usage that abuses the abstract data structure. For a relational database, that's probably reasonable enough. And they're all over the place. Your problem is that you're assuming hack == bad, but that's no tthe case.
And who cares? The entire point of an RDBMS is that most operations are indeed reversible.
Compouding existing issues is an isuse in and of itself.
Which is a far cry from saying their the vendor's intended solution. They are one solution of many. There are many other solutions, all legimiate, all recommended, to RDBMS performance problems.
And that optimization changes how the index is being used. It's no longer merely a pointer.
Ahh, but we frequently do care about both. We care about all sorts of semantics about an ArrayList's implementation.
I don't in the least, beyond the normal early optimization one.
Admin
They accomplish "genericity" (don't you just love coining your own terms? I do!) by doing exactly what I suggested: by providing an interface and knowing about and dealing with all of the issues with all of the databases that they support behind the scenes. As another poster said in response to your original question "with a lot of code". Even then, Hibernate still lets you get platform specific by allowing you to do direct queries on the database in a platform-specific way, essentially defeating the concept of a generic data access layer.
Still, that's a moot point, because Hibernate is not a generic access layer, it is an object-relational mapping framework.
Admin
This qualifies as a textbook example of "WTF". And I agree with the trend of recent postings - that "enterprise" is the new rallying cry of clueless managment. I guess they finally retired "Object Oriented Land Ho!"
And I'm so happy to see "Object Oriented" being retired. This week I'm asked to add some features to a java component that we're licensing. The features are important to our business - and these changes affect the UI, the network security, and some of the runtime behavior. So, when I started looking into the code base, its 500 classes. Many many situations are simply object-oriented-silliness: Class A derives from Class B which implements Interface C, D and E. Seems straightforward enough, but, there are no other instances of ClassB - not instantiated, not derived from... its just an mental-abstraction placeholder! Sure, maybe someone was planning to use 'class B' elsewhere. Maybe there are other code bases that use 'class B'....but, given this repeated pattern, i conclude that most likely, its just OO run amok.
What are other signs of OO silliness are to be found? Entire class declarations that are essentially constants. The idea of over-riding static values in a class architecture is clever, and I can appreciate its usefulness. But, not as a design paradigm. Especially not for primitive data types like INTs and STRINGs.
What kind of silliness can we expect from the "enterprise" cry ? So far, I've seen blind allegiance to "it must be XML" - to the point of utter stupidity. In the 90s, it made sense to marshall data from native data to stream an object from one process to another and do so in a standard format (call it COM, CORBA, RMI - whatever flavor you like), and there was the silliness of making anything and everything 'COM' compatible. There was inherent waste and overhead, and when applied apporpriately, those drawbacks were overcome by great gains in interoperability. Now, we take the same concept and add the bloat of text representation of data, and forced inclusion of structure.
Again, there will be gains in interoperability between disconnected processes. But, already, I'm seeing code where parameters are being passed from a process to another via an XML tag set! A simple string user-id being passed as an xml node selection. Rediculousness. But its got enterprise written all over it.
Taking that a step further, it would seem that "enterprise" silliness will lead to entire XML schemas, XSLT transforms, and XML datasets simply to exchange runtime values between processes.
Admin
You can write a database and SQL interpreter in one of them fancy OOP langauges. You can use data structures and even indexes into collections of objects. Your claim is quite silly. OO does not imply iterating linearly over a list of rows/objects/whatever.
Admin
No logic in the application? What, do I query the database everytime the user fills out a field to make sure they have done something consistant? Do I just spout out errors from the database when their stored procedure fails? How the hell are you supposed to do object oriented development with NO LOGIC in the OBJECTS, and instead put into COBOL like stored procedures?
Admin
Biff -- I thought we were discussing ways to use existing database products. If you want to go ahead and write your own database engine from scratch, by all means go for it.
Admin
Have you ever heard of ODBC? Or, perhaps, ADO / ADO.NET?
You are on a road to maintenance hell. If you are in the business of creating custom software, then unless you Invent Everything, you will depend on something else, be it a PDF-creator, Active Directory, File System, etc.
What you *should* be doing is designing your system so external dependency are kept in one logical layer: for example, the data access layer. This way, if you ever need to support a different vendor, it's simply a matter of editing the classes in your DAL and changing this. The time involved in doing this is magnitudes smaller than the time trying to invent some "generic data access layer".
What are you going to do when you need to optimize a query? You can't -- everyone does it differently. Orable uses comments, SQL Server uses keywords, DB2 uses [I have no idea]. So now your entire "generic layer" is useless.
This is yet another reason you need to be using stored procedure. Every real database [*] has them, and they all function the same way when you use something like ODBC or your own invented "abstract data layer." That's the whole point of stored procedures -- simplifed access to a complex database system.
[*] I do not acknowledge MySql as anything more than a toy. See http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/26/428527.aspx.
Admin
This is quite possible, exactly like that. The performance is better than you think.
At least in Oracle, stored procedures are not COBOL-like, rather Pascal-like (or ADA-like, to be more precise).
This kind of development is not OO.