Re: More on web application performance with DBI
Hi , I have a perl script which connects to the oracle database. I want to know if i can lock the script. i.e even if there are many requests to the server for the same script there will be no concurrency update problems. Also how i implement commit rollbacks in a script. thanks for help in advance Niel __ Get Your Private, Free Email at http://www.hotmail.com
Re: More on web application performance with DBI
Greg Stark wrote: *** From dbi-users - To unsubscribe, see the end of this message. *** *** DBI Home Page - http://www.symbolstone.org/technology/perl/DBI/ *** Tim Bunce [EMAIL PROTECTED] writes: On Mon, Oct 18, 1999 at 07:08:09AM -0700, Michael Peppler wrote: Tim Bunce writes: On Fri, Oct 15, 1999 at 11:42:29AM +0100, Matt Sergeant wrote: Sadly prepare_cached doesn't always work very well - at least not with Sybase (and I assume MSSQL). Just a warning. Could you be more specific? Well I doubt it will be nearly as effective as it is on Oracle since I don't think Sybase supports placeholders at the database level. I believe the DBD driver is emulating them. Actually not - Sybase creates a temporary stored proc for each prepared statement, so it's equivalent to using stored procedures. Michael
Re: More on web application performance with DBI
Greg Stark writes: Michael Peppler [EMAIL PROTECTED] writes: Greg Stark wrote: Actually not - Sybase creates a temporary stored proc for each prepared statement, so it's equivalent to using stored procedures. Heh neat, is that DBD::Sybase or the server that's doing that? And does it only work for a single statement handle or does it keep that procedure around in case i prepare the same statement again? The prepared statement uses a stored proc built on the fly *if* your SQL statement has ?-style placeholders. With Sybase you can't have multiple statements that are active simultaneously over the same connection, so preparing a second statement will result in DBD::Sybase opening a new connection (as a side note you *can't* use this feature when AutoCommit is OFF because it would require DBD::Sybase to do two-phase commits, which I'm not prepared to code at this point...) The stored procs remain around for as long as the $sth is defined/valid. Another good point is that Sybase *knows* what types the various parameter to a prepared statement are, so you don't need to tell it that something is a VARCHAR or whatever (and I actually ignore those type params to execute() and bind_param()) That being said Sybase is pretty fast at parsing/compiling SQL, so using ?-style placeholders is really only usefull if you're going to call a particular statement more than a couple of times. And in general, with Sybase I always advocate using stored procs for all access as this allows the DBA to fine tune the queries without having to go into the perl code itself (and ensures that you don't get someone issuing a very sub-optimal query that brings a system to its knees!) Michael -- Michael Peppler -||- Data Migrations Inc. [EMAIL PROTECTED]-||- http://www.mbay.net/~mpeppler Int. Sybase User Group -||- http://www.isug.com Sybase on Linux mailing list: [EMAIL PROTECTED]
Re: More on web application performance with DBI
On Mon, Oct 18, 1999 at 07:08:09AM -0700, Michael Peppler wrote:
Tim Bunce writes:
On Fri, Oct 15, 1999 at 11:42:29AM +0100, Matt Sergeant wrote:
On Fri, 15 Oct 1999, Perrin Harkins wrote:
On Thu, 14 Oct 1999, Jeffrey Baker wrote:
Zero optimization: 41.67 requests/second
Stage 1 (persistent connections): 140.17 requests/second
Stage 2 (bound parameters): 139.20 requests/second
Stage 3 (persistent statement handles): 251.13 requests/second
I know you said you don't like it because it has extra overhead, but would
you mind trying stage 3 with prepare_cached rather than your custom
solution with globals? For some applications with lots of SQL statements,
the prepare_cached appraoch is just much more manageable.
Sadly prepare_cached doesn't always work very well - at least not with
Sybase (and I assume MSSQL). Just a warning.
Could you be more specific?
I've never looked at prepare_cached() for DBD::Sybase, and Matt tried
it out and it appeared not to work.
"appeared not to work" isn't much more specific :-)
I would guess that this is again
an issue of having to open multiple connections if you prepare more
than one statement.
Here's the code:
sub prepare_cached {
my ($dbh, $statement, $attr, $allow_active) = @_;
my $cache = $dbh-FETCH('CachedKids');
$dbh-STORE('CachedKids', $cache = {}) unless $cache;
my $key = ($attr) ? join(" | ", $statement, %$attr) : $statement;
my $sth = $cache-{$key};
if ($sth) {
Carp::croak("prepare_cached($statement) statement handle $sth is still
active")
if !$allow_active $sth-FETCH('Active');
return $sth;
}
$sth = $dbh-prepare($statement, $attr);
$cache-{$key} = $sth if $sth;
return $sth;
}
Tim.
Re: More on web application performance with DBI
On Fri, 15 Oct 1999, Perrin Harkins wrote: On Thu, 14 Oct 1999, Jeffrey Baker wrote: Zero optimization: 41.67 requests/second Stage 1 (persistent connections): 140.17 requests/second Stage 2 (bound parameters): 139.20 requests/second Stage 3 (persistent statement handles): 251.13 requests/second I know you said you don't like it because it has extra overhead, but would you mind trying stage 3 with prepare_cached rather than your custom solution with globals? For some applications with lots of SQL statements, the prepare_cached appraoch is just much more manageable. Sadly prepare_cached doesn't always work very well - at least not with Sybase (and I assume MSSQL). Just a warning. -- Matt/ Details: FastNet Software Ltd - XML, Perl, Databases. Tagline: High Performance Web Solutions Web Sites: http://come.to/fastnet http://sergeant.org Available for Consultancy, Contracts and Training.
Re: More on web application performance with DBI
Oleg Bartunov writes: On Thu, 14 Oct 1999, Perrin Harkins wrote: Date: Thu, 14 Oct 1999 17:53:15 -0700 (PDT) From: Perrin Harkins [EMAIL PROTECTED] To: Jeffrey Baker [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: More on web application performance with DBI On Thu, 14 Oct 1999, Jeffrey Baker wrote: Zero optimization: 41.67 requests/second Stage 1 (persistent connections): 140.17 requests/second Stage 2 (bound parameters): 139.20 requests/second Stage 3 (persistent statement handles): 251.13 requests/second I know you said you don't like it because it has extra overhead, but would you mind trying stage 3 with prepare_cached rather than your custom solution with globals? For some applications with lots of SQL statements, the prepare_cached appraoch is just much more manageable. Some databases doesn't support caches of prepared plans. PostgreSQL for example. Or Sybase. Though with Sybase you could open multiple connections to achieve the same result (at the risk of getting deadlocks depending on what you are trying to do!) Michael -- Michael Peppler -||- Data Migrations Inc. [EMAIL PROTECTED]-||- http://www.mbay.net/~mpeppler Int. Sybase User Group -||- http://www.isug.com Sybase on Linux mailing list: [EMAIL PROTECTED]
Re: More on web application performance with DBI
On Thu, Oct 14, 1999 at 05:53:15PM -0700, Perrin Harkins wrote:
On Thu, 14 Oct 1999, Jeffrey Baker wrote:
Zero optimization: 41.67 requests/second
Stage 1 (persistent connections): 140.17 requests/second
Stage 2 (bound parameters): 139.20 requests/second
Stage 3 (persistent statement handles): 251.13 requests/second
I know you said you don't like it because it has extra overhead, but would
you mind trying stage 3 with prepare_cached rather than your custom
solution with globals? For some applications with lots of SQL statements,
the prepare_cached appraoch is just much more manageable.
It is interesting that the Stage 2 optimization didn't gain anything
over Stage 1.
I think Oracle 8 is doing some magic by parsing your SQL and matching it
up to previous statements, whether you use bind variables or not. It may
matter more on other databases.
- Perrin
You may also find that the use of bind variables become more and more
effecient when used in complex, multi-table joins.
Tim.
--
_ _
Timothy E. Peoples |_| C o l l e c t i v e |_|
Senior Consultant |_technologies _|
[EMAIL PROTECTED] [] []
a pencom company
There is no spoon.
Re: More on web application performance with DBI
On Thu, 14 Oct 1999, Jeffrey Baker wrote: Zero optimization: 41.67 requests/second Stage 1 (persistent connections): 140.17 requests/second Stage 2 (bound parameters): 139.20 requests/second Stage 3 (persistent statement handles): 251.13 requests/second I know you said you don't like it because it has extra overhead, but would you mind trying stage 3 with prepare_cached rather than your custom solution with globals? For some applications with lots of SQL statements, the prepare_cached appraoch is just much more manageable. It is interesting that the Stage 2 optimization didn't gain anything over Stage 1. I think Oracle 8 is doing some magic by parsing your SQL and matching it up to previous statements, whether you use bind variables or not. It may matter more on other databases. - Perrin
Re: More on web application performance with DBI
Perrin Harkins wrote: On Thu, 14 Oct 1999, Jeffrey Baker wrote: Zero optimization: 41.67 requests/second Stage 1 (persistent connections): 140.17 requests/second Stage 2 (bound parameters): 139.20 requests/second Stage 3 (persistent statement handles): 251.13 requests/second I know you said you don't like it because it has extra overhead, but would you mind trying stage 3 with prepare_cached rather than your custom solution with globals? For some applications with lots of SQL statements, the prepare_cached appraoch is just much more manageable. Okay. I'll try it out tomorrow and post the results. However, my application uses this approach and I'd say it's in the 99th percentile with respect to number of different statements =:-) Regards, Jeffrey
Re: More on web application performance with DBI
"Jeffrey W. Baker" wrote: Perrin Harkins wrote: On Thu, 14 Oct 1999, Jeffrey Baker wrote: Zero optimization: 41.67 requests/second Stage 1 (persistent connections): 140.17 requests/second Stage 2 (bound parameters): 139.20 requests/second Stage 3 (persistent statement handles): 251.13 requests/second I know you said you don't like it because it has extra overhead, but would you mind trying stage 3 with prepare_cached rather than your custom solution with globals? For some applications with lots of SQL statements, the prepare_cached appraoch is just much more manageable. Okay. I'll try it out tomorrow and post the results. However, my application uses this approach and I'd say it's in the 99th percentile with respect to number of different statements =:-) I just performed this benchmark. The Stage 3 optimization using prepare_cached gets 152.72 requests/sec. The end result is this: Zero optimization: 41.67 requests/second Stage 1 (persistent connections): 140.17 requests/second Stage 2 (bound parameters): 139.20 requests/second Stage 3 (prepare_cached): 152.72 requests/second Stage 3 (global references): 251.13 requests/second You can see that prepare_cached isn't much of an improvement over prepare. I've profiled this stuff in the past, and the prepare method really is quite expensive. A nice project for someone with sufficient spare time would be to optimize that code path. -jwb
Re: More on web application performance with DBI
On Thu, 14 Oct 1999, Perrin Harkins wrote: Date: Thu, 14 Oct 1999 17:53:15 -0700 (PDT) From: Perrin Harkins [EMAIL PROTECTED] To: Jeffrey Baker [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: More on web application performance with DBI On Thu, 14 Oct 1999, Jeffrey Baker wrote: Zero optimization: 41.67 requests/second Stage 1 (persistent connections): 140.17 requests/second Stage 2 (bound parameters): 139.20 requests/second Stage 3 (persistent statement handles): 251.13 requests/second I know you said you don't like it because it has extra overhead, but would you mind trying stage 3 with prepare_cached rather than your custom solution with globals? For some applications with lots of SQL statements, the prepare_cached appraoch is just much more manageable. Some databases doesn't support caches of prepared plans. PostgreSQL for example. So I think Jeffrey's approach is more generic. But I agree with you if database supports this feature it's worth to use it. It is interesting that the Stage 2 optimization didn't gain anything over Stage 1. I think Oracle 8 is doing some magic by parsing your SQL and matching it up to previous statements, whether you use bind variables or not. It may matter more on other databases. - Perrin _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
