Ben wrote: > > On Wed, Nov 06, 2002 at 12:47:09PM +0000, Paul Makepeace wrote: > > On Wed, Nov 06, 2002 at 11:13:43AM +0000, David Cantrell wrote: > > > Stupid programmers forgetting to appropriately quote/escape data before > > > stuffing it into a database. > > > > > > INSERT INTO users (userid, username, passwd) VALUES > > > (usersequence.nextval, '$username', '$passwdmd5hash') > > > > Why would anyone not use bound variables? > > I've seen quite a few who honestly believed that using explicit values > was faster, because in their minds it must be easier for the DB to process. > Not only are they emphatically *not* easier or faster, but some databases > (notably Oracle) can suffer serious performance problems because of this.
Informix, in certain circumstances, appears capable of doing more subtle optimisation given literals: SELECT ID, Ego, SuperEgo FROM People WHERE Name = "Smith" ... will cause "Smith" to be checked against the distribution, and finding a huge number of smiths, a sequential scan will be used. SELECT ID, Ego, SuperEgo FROM People WHERE Name = ? ... will go for index path every time. However, I suspect that cases where the former behaviour of the optimiser is actually desirable and yields a useful performance improvement are as rare as Nigella Lawson's steak. Most of the time, the overhead of reparsing and reoptimising statements overwhelms any benefits, so I'd mostly be endorsing Ben's "Always use bind variables" with a small caveat. Another weird case: SELECT Subconscious FROM People WHERE ID IN (1, 666, 732) [where that's a variable-size but not-so-big-it-overwhelms-yer-buffer set] You don't wanna be using things like that with prepare_cached without caution, now, do you? Well do you, punk? *click* -- Tim Supposed former badass Informix DBA
