Re: Setting bind variables or defines from applications?

"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. In a 1,000 record benchmark it took 26 minutes and pegged the CPU on the database server with an average CPU usage of 85% for 26 minutes. We spent 4 hours changing their code to use bind variables. (no change in the SQL, no change in database schema, just bind variables.) We reran the same benchmark with the same hardware and dataset etc. The whole import tool just under 6 minutes, and averaged 3% CPU usage with 10 % CPU usage peaks. Now customers could use the system and not notice any impact when the import ran. (the import was very important and an integral to the system and had to occur on a timely basis.)

Notice it took 1 developer 4 hours to change the code to use bind variables. The impact was to save hundreds hours of time for customers. If they had done it correctly the first time it might have taken a couple of hours extra in the first place. Instead, it took many more hours to document, test, discuss, and finally fix.

So if Agile programming eschews best practices I am against it. Maybe you only worked on small trivial applications, I don't know, but any database application of any reasonable size (OLTP) should be using bind variables for whatever RDBMS they are using. (I don't know of any RDBMS that doesn't support bind variables. it might be known with another name, host variables, parameterized queries, etc.) THe problem is more likely that the developer just doesn't know best practices.

Jim Received on Sat Apr 22 2006 - 11:01:19 CDT