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 11 3274
"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
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
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.
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
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
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
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
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
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 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Terence |
last post by:
These comparrison articles are always fun :)
http://otn.oracle.com/pub/articles/hull_asp.html
|
by: Mike |
last post by:
No flame wars, please!
We're planning a move from a non-relational system to
a relational system. Our choices have been narrowed to
Oracle and DB2. Since we're moving from non-relational
to...
|
by: BigDaDDY |
last post by:
Um yeah....In case you haven't figured it out, Microsoft sucks. I'm going
to be kicked back in my chair eating popcorn and watching football 10 years
from now, while all you clowns are scrambling...
|
by: mcbill20 |
last post by:
Hello all. I just installed Oracle 10g developer tools on a machine
running XP Pro and Office XP. Before this I had just the Oracle 9
client installed. I the previous configuration, I was able to...
|
by: s-dogg |
last post by:
If anyone has seen this before, please help. I work at a company that
has an Access application front end attached to tables in an Oracle 10g
backend. In order to run our application this way,...
|
by: AnandaSim |
last post by:
Hi All,
I've had Access 97, 2000 connections to the corporate Oracle database
for a few years now - but seldom use it. When I did use it years ago,
performance was not fast but the features were...
|
by: egoldthwait |
last post by:
I need to convert a 17mb access 2000 db to Oracle and house it in a
Citrix farm. The issue: we have never converted an Access Db to Oracle
but can probably use Oracle's Workbench to assist with...
|
by: jasmith |
last post by:
How will Access fair in a year? Two years? .... The new version of
Access seems to service non programmers as a wizard interface to
quickly create databases via a fancy wizard. Furthermore, why...
|
by: jonceramic |
last post by:
Hi All,
I started developing in Access, and people took notice and so we're
starting to migrate into our corporate's bigger Oracle system.
I'll still be using my developed Access front ends,...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |