Re: Select whole "Objects"

"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message news:dt1vl0$h1d$1_at_nntp.fujitsu-siemens.com...
> Jim Kennedy schrieb:
> > "Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message
> > news:dsvecd$m5v$1_at_nntp.fujitsu-siemens.com...
> >> jlaffer schrieb:
> >>> Thats true.. i generate it automatically.
> >>> Unfortunately all these subqueries have to be configured first (in
> >>> worst case for every single Attribute of my Object)
> >> What do you mean by configuring first?
> >> If you want to do all that bind variable stuff, forget it for that
> >> kind of query. Every query is different anyway and binds make lots
> >> more sense for mass inserts.
> >
> > You are very wrong here. Read the application developer's guide.
> > Use binds or your application won't scale at all.
> > Not using binds means you are
> > using excessive CPU and excessive latching. (a latch is alow level
lock)
> Yes, but before you blindly repeat a mantra, please try to quantify that
> effect. Since almost each query will be different, parsing has to be done
> anyway, right? And how often per second, if a user sits in front of his
> editor? How often will a query be reused? Once? Twice?
> I mean if we were talking about the Lufthansa booking system here, yes,
> it might make sense to make that effort, but in that case the OT wouldn't
> have had to ask here.
>
> Lots of Greetings!
> Volker

If it is a data wherehouse then yes, binds aren't that big a deal for queries that are going to take minutes. (loads, binds yes, definately, 2x or more increase using binds on loads) For other purposes use binds. No really. It is rare that each query is really different. (unless you have a poor design, which is possible) Certainly, there are going to be many different queries, but it would be highly unusual that each query would be different. By not using binds you guarantee that scalability and performance suck. By using binds you increase the probability that you will scale. (it is a best practice; with the caveat I mentioned earlier about data warehouses.)

Having seen many a software engineer ignore the advice - because they knew "better" - and having to clean up the mess and show them that actually using bind variables does increase performance. Then finally dawn breaks over Marblehead. One particularly memorable experience was another engineering group that ignored the advice, because "the queries were all different", and the benchmark of 1,000 records to process caused the server to stop processing anyone else's requests for 26 minutes. (CPU usage was 85 to 100 % for 26 minutes). Once we saw that in fact they had about 40 different queries and 4 hours of retro fitting the code it made a huge difference. (same exact test now took under 6 minutes with 3% average CPU usage and peaks of CPU usage of 15%) But hey I guess mantras are meaningless.
Jim Received on Thu Feb 16 2006 - 20:12:15 CST