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_us ername", "my_passwor d", "my_databas e");
$loop_count = 1000;
$query = "insert into oratest (id, text) values (:id, :data)";
$parsed = ociparse($db_co nn, $query);
ocibindbyname($ parsed, ":id", $x, 4);
ocibindbyname($ parsed, ":data", $now, 14);
for ($x = 1; $x <= $loop_count; $x++) {
$now = date("YmdHis");
ociexecute($par sed, OCI_DEFAULT);
ocicommit($db_c onn);
}
?>
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.ge t_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
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_us ername", "my_passwor d", "my_databas e");
$loop_count = 1000;
$query = "insert into oratest (id, text) values (:id, :data)";
$parsed = ociparse($db_co nn, $query);
ocibindbyname($ parsed, ":id", $x, 4);
ocibindbyname($ parsed, ":data", $now, 14);
for ($x = 1; $x <= $loop_count; $x++) {
$now = date("YmdHis");
ociexecute($par sed, OCI_DEFAULT);
ocicommit($db_c onn);
}
?>
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.ge t_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
Comment