Re: Setting bind variables or defines from applications?

From: Jim Kennedy <jim>
Date: Sat, 22 Apr 2006 15:53:41 -0700
Message-ID: <HqCdnaHiHJv_LtfZRVn-vA@comcast.com>

"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message news:e2dntj$oda$1_at_nntp.fujitsu-siemens.com...
> Jim Kennedy schrieb:
> > "Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message
> > news:e2d9ne$6t1$1_at_nntp.fujitsu-siemens.com...
> >> Sybrand Bakker schrieb:
> >>> On 21 Apr 2006 09:06:53 -0700, "dean" <deanbrown3d_at_yahoo.com> wrote:
> >>>
> >>>> You're a (seemingly very good) dba, but clearly not a developer.
> >>> You don't know me. I have more than a decade of development experience
> >>> on my sleeves.
> >> Just out of curiosity, what did you and when was it?
> >> You really don't sound like a guy who is programming for
> >> customers talking to him. Are you in the aircraft industry or so?
> >>
> >> Me, I've also got a decade of developing but we use oracle as a better
> >> access database and anything that takes less than one second is fine
> >> by my customers. All ten of them. And hard parses aren't a problem at
> >> all.
> >>
> >>> Unlike developers like you, who only know how to click
> >>> a button
> >> Like one of those people oracle develeoped htmldb for?
> >>
> >>> and make a mess of it.
> >> A mess is when customers complain.
> >>
> >> Lots of Greetings!
> >> Volker
> >
> > I have well over a decade of developer experience and hard parses are a
> > problem unless your application minimally uses the database. (eg few
users,
> > or few queries) For example, one large software project I worked on had
an
> > engineering group that thought they new better. They decided that "it
was
> > too much trouble to use bind variables, that it wasn't important." So
the
> > applcation went out to customers. When their code ran it bought the
database
> > to its needs. It was so bad that the other people on the system
couldn't do
> > any work.
> >
> > We looked at what they were doing. We benchmarked it. Their code was
> > importing data from external systems.
> Well, *obviously* a mass insert would have to use bind variables, and
arrays
> at that. I don't think anyone disputes that.

>

> For me, it depends on the API. For a gui implementation I often use tcl/tk
> and there binds are (were?) a bit of trouble if the database used unicode.
> So, for gui intensive stuff (user types in stuff, result is one select
with
> typically less than ten rows of data) I'm not recoding the whole app in
> another programming language with a worse gui.
>
> For mass inserts like batch stuff overnight we decided against tcl
precisely
> for the bind variable issue.

>

> I mean, sqlplus allows no-bind-variable stuff too and if the usage pattern
> doesn't diverge significantly from that I have no problem with literals.

>
>

> > So if Agile programming eschews best practices I am against it.
> It doesn't. However, it *does* involve prototyping practices and
> testing. So, if those guys didn't test under realistic load conditions
> it has nothing to do with the methodology.

>

> > Maybe you
> > only worked on small trivial applications, I don't know,
> Most of the time. The only big one (data-wise) is a spatial one where
> I use sql*loader to get the data into the database.

>

> Lots of Greetings!
> Volker

My example is just a very very clear example. You encounter the same problem with GUI apps that have a lot of users. In this same application the GUI part was coded with bind variables and array fetch etc. This allowed us to scale the application to hundreds of users on very very modest hardware. (eg hundreds of users on NT on intel 486 CPU servers with SCSI drives, which was pretty standard at the time) So sure one can write ineffecient code and just make sure the hardware is big enough to handle it. I have heard the old excuse "we don't have time to write efficient code; we will fix it later." Performance and security isn't something you bolt on later. It is something you include from the begining or pay a very high price later to retrofit it.
Jim Received on Sat Apr 22 2006 - 17:53:41 CDT