Oracle DB access - having problems with speed?

[Markku Uttula]

I think I'm doing something wrong. I'm able to connect to Oracle just
fine, execute queries and all, but I'm having serious problems with
the speed :(

For example, the following PHP-script on my machine executes about 6
seconds:

<?
$db_conn = ocilogon("my_username", "my_password", "my_database");
$loop_count = 1000;
$query = "insert into oratest (id, text) values (:id, :data)";
$parsed = ociparse($db_conn, $query);
ocibindbyname($parsed, ":id", $x, 4);
ocibindbyname($parsed, ":data", $now, 14);
for ($x = 1; $x <= $loop_count; $x++) {
$now = date("YmdHis");
ociexecute($parsed, OCI_DEFAULT);
ocicommit($db_conn);
}
?>

However, if I have the following PL/SQL -stored procedure, it executes
in less than a second:

procedure testing is
l_loop_count number(10);
begin
l_loop_count := 1000;
for x in 1..l_loop_count loop
execute immediate 'insert into oratest (id, text) values ('||x||',
'''||toolkit.get_timestamp||''')';
commit;
end loop;
end;

Please note, that the only reason I used "execute immediate" instead
of just using a normal insert, was that execute immediate is a tiny
bit slower :)

The underlying table is defined as "CREATE TABLE ORATEST (id
NUMBER(10) NOT NULL, text VARCHAR2(14) NOT NULL);", I'm using PHP
4.3.1 (Yeah, somewhat outdated at the moment) on Apache 1.3.22 (the
one that came with my Oracle) running on Windows XP (SP2), OCI8 is
revision 1.183 ... in case any of this has any meaning.

Has anyone got experience on this field? Is there something I'm
overlooking? Any hints are appreciated. The reason I'm attempting to
do this with PHP is that I wish to have Object-capabilities in the
next version of my software, that has previously been built using just
PL/SQL.

--
Markku Uttula

[Brent Palmer]

"Markku Uttula" <ma***********@disconova.com> wrote in message
news:Gg****************@reader1.news.jippii.net...

I think I'm doing something wrong. I'm able to connect to Oracle just fine,
execute queries and all, but I'm having serious problems with the speed :(

For example, the following PHP-script on my machine executes about 6
seconds:

<?
$db_conn = ocilogon("my_username", "my_password", "my_database");
$loop_count = 1000;
$query = "insert into oratest (id, text) values (:id, :data)";
$parsed = ociparse($db_conn, $query);
ocibindbyname($parsed, ":id", $x, 4);
ocibindbyname($parsed, ":data", $now, 14);
for ($x = 1; $x <= $loop_count; $x++) {
$now = date("YmdHis");
ociexecute($parsed, OCI_DEFAULT);
ocicommit($db_conn);
}
?>

I do not know oracle with php but, do you need the ocicommit($db_conn);
every single time in your loop.
To me it lloks like your connecting with the database 1000 times. Can't you
just connect once.
I may be way off base here, but it's worth saying.

Brent Palmer.


However, if I have the following PL/SQL -stored procedure, it executes in
less than a second:

procedure testing is
l_loop_count number(10);
begin
l_loop_count := 1000;
for x in 1..l_loop_count loop
execute immediate 'insert into oratest (id, text) values ('||x||',
'''||toolkit.get_timestamp||''')';
commit;
end loop;
end;

Please note, that the only reason I used "execute immediate" instead of
just using a normal insert, was that execute immediate is a tiny bit
slower :)

The underlying table is defined as "CREATE TABLE ORATEST (id NUMBER(10)
NOT NULL, text VARCHAR2(14) NOT NULL);", I'm using PHP 4.3.1 (Yeah,
somewhat outdated at the moment) on Apache 1.3.22 (the one that came with
my Oracle) running on Windows XP (SP2), OCI8 is revision 1.183 ... in case
any of this has any meaning.

Has anyone got experience on this field? Is there something I'm
overlooking? Any hints are appreciated. The reason I'm attempting to do
this with PHP is that I wish to have Object-capabilities in the next
version of my software, that has previously been built using just PL/SQL.

--
Markku Uttula

[Markku Uttula]

Brent Palmer wrote:

<?
$db_conn = ocilogon("my_username", "my_password", "my_database");
$loop_count = 1000;
$query = "insert into oratest (id, text) values (:id, :data)";
$parsed = ociparse($db_conn, $query);
ocibindbyname($parsed, ":id", $x, 4);
ocibindbyname($parsed, ":data", $now, 14);
for ($x = 1; $x <= $loop_count; $x++) {
$now = date("YmdHis");
ociexecute($parsed, OCI_DEFAULT);
ocicommit($db_conn);
}
I do not know oracle with php but, do you need the
ocicommit($db_conn); every single time in your loop.

No. It's just there to make the DB-server actually "do something"
while in the loop (because I don't want to test the speed of the
memorypool). I've done it similarly in the PL/SQL-code. It's true that
I'd neve do something like this in production environment - the commit
would be only after all the inserts are made - but as said, that
wouldn't be testing the speed of accessing the database itself.

It is true, that when I move the ocicommit outside the loop, the speed
goes up by (nearly) a factor of five. Interestingly, when I move the
commit outside of the loop in PL/SQL-code, the speed slows down by
nearly the same amount. Very interesting results :-p
To me it lloks like your connecting with the database 1000 times.
Can't you just connect once.

Nope, the connection is established on the first line of script.
Committing changes does not (or at least, it should not) break the
connection.

--
Markku Uttula

[Colin McKinnon]

Brent Palmer wrote:

"Markku Uttula" <ma***********@disconova.com> wrote in message
news:Gg****************@reader1.news.jippii.net...

I think I'm doing something wrong. I'm able to connect to Oracle just
fine, execute queries and all, but I'm having serious problems with the
speed :(

Yup, sounds like Oracle ;)
For example, the following PHP-script on my machine executes about 6
seconds:

<?
$db_conn = ocilogon("my_username", "my_password", "my_database");
$loop_count = 1000;
$query = "insert into oratest (id, text) values (:id, :data)";
$parsed = ociparse($db_conn, $query);
ocibindbyname($parsed, ":id", $x, 4);
ocibindbyname($parsed, ":data", $now, 14);
for ($x = 1; $x <= $loop_count; $x++) {
$now = date("YmdHis");
ociexecute($parsed, OCI_DEFAULT);
ocicommit($db_conn);
}
?>

To me it lloks like your connecting with the database 1000 times.

No, but he's sending 2000 requests through the same connection. To run the
PL/SQL requires only 1 request to the DBMS, the iteration is carried out
within the DBMS. This ultimately is the issue - it doesn't matter what DBMS
is used. Unless you're really doing something dumb though, it's hardly a
representative test - a real application still needs the data sent from the
front-end to the back-end, so the PL/SQL vs PHP performance issue is void.

Even if the 'real' application is to generate a table containing a 1000 rows
of int, time there are much better algorithmic solutions. e.g. IIRC, Oracle
allows an INSERT statement to add multiple rows.

C.

[James Pittman]

Markku Uttula wrote:

I think I'm doing something wrong. I'm able to connect to Oracle just
fine, execute queries and all, but I'm having serious problems with the
speed :(

For example, the following PHP-script on my machine executes about 6
seconds:

<?
$db_conn = ocilogon("my_username", "my_password", "my_database");
$loop_count = 1000;
$query = "insert into oratest (id, text) values (:id, :data)";
$parsed = ociparse($db_conn, $query);
ocibindbyname($parsed, ":id", $x, 4);
ocibindbyname($parsed, ":data", $now, 14);
for ($x = 1; $x <= $loop_count; $x++) {
$now = date("YmdHis");
ociexecute($parsed, OCI_DEFAULT);
ocicommit($db_conn);
}
?>

However, if I have the following PL/SQL -stored procedure, it executes
in less than a second:

procedure testing is
l_loop_count number(10);
begin
l_loop_count := 1000;
for x in 1..l_loop_count loop
execute immediate 'insert into oratest (id, text) values ('||x||',
'''||toolkit.get_timestamp||''')';
commit;
end loop;
end;

Please note, that the only reason I used "execute immediate" instead of
just using a normal insert, was that execute immediate is a tiny bit
slower :)

The underlying table is defined as "CREATE TABLE ORATEST (id NUMBER(10)
NOT NULL, text VARCHAR2(14) NOT NULL);", I'm using PHP 4.3.1 (Yeah,
somewhat outdated at the moment) on Apache 1.3.22 (the one that came
with my Oracle) running on Windows XP (SP2), OCI8 is revision 1.183 ...
in case any of this has any meaning.

Has anyone got experience on this field? Is there something I'm
overlooking? Any hints are appreciated. The reason I'm attempting to do
this with PHP is that I wish to have Object-capabilities in the next
version of my software, that has previously been built using just PL/SQL.

Upgrade to Apache 2 and PHP 5. In PHP 5, you use oci_* functions rather
than oci* functions. These will be faster.

Jamie

[Markku Uttula]

James Pittman wrote:

Upgrade to Apache 2 and PHP 5. In PHP 5, you use oci_* functions
rather than oci* functions. These will be faster.

I just had to really test this, and nope - it doesn't seem to be the
case. The functions have no notable difference in speed (and I wonder
how - in theory - they even could have).

--
Markku Uttula

[Markku Uttula]

Colin McKinnon wrote:

I think I'm doing something wrong. I'm able to connect to Oracle
just fine, execute queries and all, but I'm having serious
problems
with the speed :(
Yup, sounds like Oracle ;)
Do you mean Oracle in general, or Oracle while used through PHP?
To me it lloks like your connecting with the database 1000 times.

No, but he's sending 2000 requests through the same connection. To
run the PL/SQL requires only 1 request to the DBMS, the iteration is
carried out within the DBMS. This ultimately is the issue - it
doesn't matter what DBMS is used.

<HOMER>D'oh</HOMER> ... how the heck didn't I think of that... I was
in the mindset that PL/SQL stored procedures were being executed in
Apache's mod_plsql, but now that I really think of it, why would it...
Makes one wonder how stupid things one can do even with 5 years of
experience developing software using Oracle :-p
Unless you're really doing
something dumb though, it's hardly a representative test - a real
application still needs the data sent from the front-end to the
back-end, so the PL/SQL vs PHP performance issue is void.

Yep. I was actually doing this with such vast amounts of data to get
something I could really measure the time with. On a real environment,
the amounts of data stored are nowhere close to this per transaction,
so you're 100% correct that this example gives no real pointers to
what the actual performance would be.

--
Markku Uttula

[Andy Hassall]

On Thu, 17 Mar 2005 08:06:02 -0500, James Pittman <jp*******@yahoo.com> wrote:

Markku Uttula wrote:

Upgrade to Apache 2 and PHP 5. In PHP 5, you use oci_* functions rather
than oci* functions. These will be faster.

No, they won't. It's the same extension. The functions have just been renamed.
And the oci* functions are still there are aliases.

You're probably comparing with the ora_ functions, which are based on the
long-dead OCI7.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[James Pittman]

Andy Hassall wrote:

On Thu, 17 Mar 2005 08:06:02 -0500, James Pittman <jp*******@yahoo.com> wrote:

Markku Uttula wrote:

Upgrade to Apache 2 and PHP 5. In PHP 5, you use oci_* functions rather
than oci* functions. These will be faster.

No, they won't. It's the same extension. The functions have just been renamed.
And the oci* functions are still there are aliases.

You're probably comparing with the ora_ functions, which are based on the
long-dead OCI7.

Yeah I guess I am. I do remember seeing a speed increase from ora_* to
oci*.

Jamie

[NSpam]

Markku Uttula wrote:

I think I'm doing something wrong. I'm able to connect to Oracle just
fine, execute queries and all, but I'm having serious problems with the
speed :(

For example, the following PHP-script on my machine executes about 6
seconds:

<?
$db_conn = ocilogon("my_username", "my_password", "my_database");
$loop_count = 1000;
$query = "insert into oratest (id, text) values (:id, :data)";
$parsed = ociparse($db_conn, $query);
ocibindbyname($parsed, ":id", $x, 4);
ocibindbyname($parsed, ":data", $now, 14);
for ($x = 1; $x <= $loop_count; $x++) {
$now = date("YmdHis");
ociexecute($parsed, OCI_DEFAULT);
ocicommit($db_conn);
}
?>

However, if I have the following PL/SQL -stored procedure, it executes
in less than a second:

procedure testing is
l_loop_count number(10);
begin
l_loop_count := 1000;
for x in 1..l_loop_count loop
execute immediate 'insert into oratest (id, text) values ('||x||',
'''||toolkit.get_timestamp||''')';
commit;
end loop;
end;

Please note, that the only reason I used "execute immediate" instead of
just using a normal insert, was that execute immediate is a tiny bit
slower :)

The underlying table is defined as "CREATE TABLE ORATEST (id NUMBER(10)
NOT NULL, text VARCHAR2(14) NOT NULL);", I'm using PHP 4.3.1 (Yeah,
somewhat outdated at the moment) on Apache 1.3.22 (the one that came
with my Oracle) running on Windows XP (SP2), OCI8 is revision 1.183 ...
in case any of this has any meaning.

Has anyone got experience on this field? Is there something I'm
overlooking? Any hints are appreciated. The reason I'm attempting to do
this with PHP is that I wish to have Object-capabilities in the next
version of my software, that has previously been built using just PL/SQL.

Somewhere buried in the thread is a thought regarding PHP 4 etc.

If you have access to PHP 5 then go for it, Ignoring Oracle issues for
the moment, PHP 5 gives you try catch exception handling, makes life so
much easier <g>

[NSpam]

NSpam wrote:

Markku Uttula wrote:

I think I'm doing something wrong. I'm able to connect to Oracle just
fine, execute queries and all, but I'm having serious problems with
the speed :(

For example, the following PHP-script on my machine executes about 6
seconds:

<?
$db_conn = ocilogon("my_username", "my_password", "my_database");
$loop_count = 1000;
$query = "insert into oratest (id, text) values (:id, :data)";
$parsed = ociparse($db_conn, $query);
ocibindbyname($parsed, ":id", $x, 4);
ocibindbyname($parsed, ":data", $now, 14);
for ($x = 1; $x <= $loop_count; $x++) {
$now = date("YmdHis");
ociexecute($parsed, OCI_DEFAULT);
ocicommit($db_conn);
}
?>

However, if I have the following PL/SQL -stored procedure, it executes
in less than a second:

procedure testing is
l_loop_count number(10);
begin
l_loop_count := 1000;
for x in 1..l_loop_count loop
execute immediate 'insert into oratest (id, text) values ('||x||',
'''||toolkit.get_timestamp||''')';
commit;
end loop;
end;

Please note, that the only reason I used "execute immediate" instead
of just using a normal insert, was that execute immediate is a tiny
bit slower :)

The underlying table is defined as "CREATE TABLE ORATEST (id
NUMBER(10) NOT NULL, text VARCHAR2(14) NOT NULL);", I'm using PHP
4.3.1 (Yeah, somewhat outdated at the moment) on Apache 1.3.22 (the
one that came with my Oracle) running on Windows XP (SP2), OCI8 is
revision 1.183 ... in case any of this has any meaning.

Has anyone got experience on this field? Is there something I'm
overlooking? Any hints are appreciated. The reason I'm attempting to
do this with PHP is that I wish to have Object-capabilities in the
next version of my software, that has previously been built using just
PL/SQL.

Somewhere buried in the thread is a thought regarding PHP 4 etc.

If you have access to PHP 5 then go for it, Ignoring Oracle issues for
the moment, PHP 5 gives you try catch exception handling, makes life so
much easier <g>

Whoops also forgot PHP 5 object behaviour is far better

[Andy Hassall]

On Thu, 17 Mar 2005 11:40:01 +0200, "Markku Uttula"
<ma***********@disconova.com> wrote:

I think I'm doing something wrong. I'm able to connect to Oracle just
fine, execute queries and all, but I'm having serious problems with
the speed :(

For example, the following PHP-script on my machine executes about 6
seconds:

$loop_count = 1000;
$query = "insert into oratest (id, text) values (:id, :data)";
$parsed = ociparse($db_conn, $query);
ocibindbyname($parsed, ":id", $x, 4);
ocibindbyname($parsed, ":data", $now, 14);
for ($x = 1; $x <= $loop_count; $x++) {
$now = date("YmdHis");
ociexecute($parsed, OCI_DEFAULT);
ocicommit($db_conn);
}

However, if I have the following PL/SQL -stored procedure, it executes
in less than a second:

procedure testing is
l_loop_count number(10);
begin
l_loop_count := 1000;
for x in 1..l_loop_count loop
execute immediate 'insert into oratest (id, text) values ('||x||',
'''||toolkit.get_timestamp||''')';
commit;
end loop;
end;

Please note, that the only reason I used "execute immediate" instead
of just using a normal insert, was that execute immediate is a tiny
bit slower :)

As others have said, the main reason here is likely to be database roundtrips;
the first one does at least 2001 round trips (1x prepare, 1000x bind+execute,
1000x commit), the second does 2 (1x prepare, 1x bind+execute).

I also seem to recall that PL/SQL has an optimisation with regards to commits
in loops; it may not actually do the commit until the end.

Consider that a commit partly means "do not return control to the client until
it is guaranteed that the changes I just made have been written to the redo
logs". If you're executing a PL/SQL block, control doesn't return to the client
until the whole block is finished. So PL/SQL can defer the physical effects of
the commit until the block completes.

I think this is the page that I'm remembering that explains some of this:
http://www.oracledba.co.uk/tips/lgwr_dilemma.htm

Commits are relatively expensive, so this may also contribute to the time
differences.

Note also that the PL/SQL version not only uses execute immediate,
guaranteeing soft parses for each execution, but you've also embedded literals
in the SQL you're executing, resulting in a potential hard parse for every
iteration, unless (a) you've done this loop before and they're still in the
cache, or (b) you've got cursor_sharing set to 'similar' or 'force' so it
rewrites SQL with embedded literals using bind variables.

Unless you're deliberately trying to make performance worse, if you do have to
resort to using execute immediate then you can still use bind variables, e.g.:

execute immediate 'insert into oratest (id, text) values (:x, :y)'
using x, toolkit.get_timestamp;

No doubt this is old news to you but I just didn't like seeing embedded
literals in SQL go unchallenged in case someone else saw it and thought it was
a good idea.
Drifting further off-thread, after a while I got fed up with the OCI8
extension in PHP in its raw form; it's a little too close to the OCI interface,
which whilst this is a great thing from performance point of view, it's a bit
verbose. After checking out various database layers I settled on ADOdb and I'm
very happy with that - it abstracts out the parse/bind/execute stuff just
enough for usability, but it's a thin enough layer that it doesn't cost much in
the way of speed.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool