Jared Still <[EMAIL PROTECTED]> writes: > If you are working with Oracle, or any database for that matter, > one of the cardinal performance rules for programming is to do > it in SQL in possible. It is much faster than anything you > can write in Perl, or any other language for that matter. > > This is due to the data staying in the database, and not > being sent to/from the application.
But following this advice blindly is somewhat dangerous. It strongly depends on where in your system the bottleneck is. If database CPU is your bottleneck, moving from Perl to (PL/)SQL could really cost you. What's the price of an Oracle CPU license, something like $50,000 or what? You can buy a lot of Perl processing hardware for that (assuming client is on a different machine than the DB server). Likewise, huge PL/SQL code that moves stuff back and forth between tables is bound to be more expensive than Perl code that manipulates a few small hashes. On the other hand, sometimes by moving processing from Perl to SQL, it is possible to avoid fetching most of the rows from disk, or sending most of the rows to the Perl client. For example, add a join to filter rows in a table, or use SELECT COUNT(*) rather than sending rows to Perl and counting there. That's bound to be an advantage performance-wise. My rule-of-thumb for performance is to write simple SQL that fetches rougly the minimum amount of data necessary, and allows Oracle to use an efficient execution plan (EXPLAIN PLAN is your friend here). Then send the data off to Perl for processing, leaving the Database to process other requests in the meantime. Usually, on modern hardware the overhead spent in Perl and DBI fetching rows is negligible compared to the cost of fetching from disk and sending over the network. > Use the RowCacheSize dbh attribute to efficiently fetch > rows from Oracle - empirical testing suggests that 100 > is a good starting value for that. > > Set the ora_check_sql sth attribute to 0 to avoid extra > parses in Oracle. > > *Always* use bind variable as has already been suggested. Indeed. - Kristian.
