Improving the SQL is always a performance boost. Parsing is a big part of the performance hit in many applications that are written with little thought to the database performance.
To reaffirm what a previous respondent has said, significant performance increases will be seen by only preparing(parsing) the statement once. Oracle will optimize it best as possible when you make multiple calls, as Jeroen is calling with bind variables. Even so, it's still a soft parse at the least. You can run benchmarks to compare performance both with one prepare() call and one per each execution. The real payoff comes though when there are many instances of an application doing the same thing. Parsing requires latches, and not parsing will obviously require less latching, which translates to greater scalability. Jared On Thu, 2004-02-12 at 04:48, Henrik Tougaard wrote: > Lodewijks, Jeroen skrev: > > ...[snip]... > > If so, I don't expect much performance improvement as I am only > > optimising a few lines of Perl. > > In my experience you always get the greatest performance > improvements by changing your SQL instead of changing you Perl - > often several orders of magnitude greater. > Sometimes it takes database schema changes, but even that may > be worth the trouble. > > You should: > - reduce the number of round-trips to the database (as a > rule of thumb the number of prepares, executes, fetches, commits > and rollbacks - dependent on fetch-caching etc) > - reduce the number of times the query optimizer has to be > invoked (roughly either the number of prepares or the number of > executes, dependent on the driver and DBMS) > - reduce the amount of data transferred > > But of course you should *ALWAY* measure before you optimise, > otherwise you'll be doing it blindly. > Find out where your application is spending it's time - I'll > be greatly surprised if it is in the DBI code or the Perl code > surrounding it. > > -- > Henrik Tougaard
