SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast pedershk's Avatar
    Join Date
    Mar 2001
    Location
    Oslo, Norway
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Preparing / preparsing Oracle SQL statements with PHP

    Hi guys

    I'm having this problem at work. I'm working on a registration/billing/account/whathaveyounot system for my employer.

    It's based on PHP on the client end, with an Oracle 8i Enterprise Database in "the backoffice".

    Now, with for example Perl DBI, you can preparse the statements, but them in "the back of your hand", and never explicitly PARSE them again until you restart the web server (in my case, Apache 1.3.20 ModSSL & PHP/4.0.5).

    The parsing is what is killing our database, since we have something in the area of atleast 200-250 sessions simultaneously accessing the various registration scripts, all very database intensive tasks.

    I have a rather large buffer cache on the DB, so it almost doesn't touch the disk. The parsing/CPU usage is the problem.

    Is there any way to store variables as *permanent* pointers to the statement for the entire webserver? I.e. I do not have to define the variable at all, just assume that it is there by some inclusion before the script even runs?

    The problem is that I cannot and will not parse the same sql statement 250 times in a row because I have 250 users. I just need to use OCIBindByName and bind variables to the binds I have defined in the statement, and then OCIExecute ($stmt) them. That means I have to have, somewhere $stmt = OCIParse ($connection, "select whatnot from whathaveyou where thisandthat = :this".

    Help?!?!?!

    Man, I'm making a lot of noise here...

    Hope somebody can help!
    Last edited by pedershk; Jun 13, 2001 at 10:14.
    **Henning K. Pedersen**
    Forbrukerkraft Norway A/S

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Short of putting things in Stored Procedures I know of no PHP-based method to 'pre-parse' as you suggest (isn't that a function of the DB and *not* the host language??).

    Looking over the Oracle 8 functions I can see there is no equivalent of *_query or whatnot which is bizarre. I guess it is the whole "You will always get a cursor" logic of Oracle..

    Hm, nothing in the PHP documentation indicates anything about caching so I doubt it exists.

    Are you using persistent DB connections? If not, see if that helps.

    Also, if you have many statements you are executing which are interrelated a stored procedure would help cut down on the parsing costs, no? (e.g. you send a single statement to be parsed vs. 5 or 6).

    And hey! I'm part of the large beta test for AO; it took me three days to find an open FTP to grab it... and 45 minutes to install on my PIII850 w/256MB RAM and an Ultra-ATA 100 HD.. That doesn't seem right!

  3. #3
    SitePoint Enthusiast pedershk's Avatar
    Join Date
    Mar 2001
    Location
    Oslo, Norway
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hiya MattR

    Yep, it sure seems like stored procedures are the way to go here. Unfortunately, we're so close to launch I'm about to pee my pants if I don't get this done ;-)

    Atleast it is *working* in its current state (after numerous silly bugfixes), but the performance could be better.

    As a curiosity, our forums at http://aoforums.funcom.com had a record high of 1018 simulatenous connected users a couple of days ago. Now that's what I call Disco! On Mysql... I'm really looking into porting vBulletin to Oracle as well, and that would definitely be stored procedures...

    I guess I'll have to go look how PHP accesses return cursors etc. from Oracle. Sigh....

    Oh - and yeah, the AO beta Cool The file has been downloaded like 45,000 times from Fileplanet alone... We got /.'ed and they said it was a demo.. Not a closed beta.. Sigh...
    Last edited by pedershk; Jun 13, 2001 at 13:29.
    **Henning K. Pedersen**
    Forbrukerkraft Norway A/S

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I keep getting the boot from the servers; I guess we're really stress-testing them, eh?

    The latest one is:
    There is a problem with the server (not found / lost / timed out)


    I can't bring up the forums either but 1000 is quite a bit!

  5. #5
    SitePoint Enthusiast pedershk's Avatar
    Join Date
    Mar 2001
    Location
    Oslo, Norway
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Both of those are because the frikken Oracle server that runs the frikken accounting system just decided to take a hike

    Man! I guess this is why we have a betatest
    **Henning K. Pedersen**
    Forbrukerkraft Norway A/S

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Seems to be all better now! What kind of hardware are you running this thing on?

  7. #7
    SitePoint Enthusiast pedershk's Avatar
    Join Date
    Mar 2001
    Location
    Oslo, Norway
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not allowed to tell

    But it's a lot (you can email me if you wish to know the real numbers, and I'll just slap a lawsuit on you if you reveal them to anyone ;-)
    **Henning K. Pedersen**
    Forbrukerkraft Norway A/S

  8. #8
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's gotta be what; midnight there? 1AM? GET BACK TO WORK ON THE LOGIN SERVER!!

    I'm tempted to go to South Carolina for the 'launch' party thing that they are having just to see if I can get a peek at the servers!

  9. #9
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you got the Zend Optimizer installed? It caches the compiled scripts to the RAM so it doesn't have to recompile them everytime. Might help.

    www.zend.com , there's a free version which seems to work fine plus a few other pay versions.

  10. #10
    SitePoint Enthusiast pedershk's Avatar
    Join Date
    Mar 2001
    Location
    Oslo, Norway
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Robo, thanks for your answer. I'll look into the Optimizer - to be honest, I haven't even thought of using it. I'm not quite sure how it will help here, though - but it might be worth a try anyhow
    **Henning K. Pedersen**
    Forbrukerkraft Norway A/S

  11. #11
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually it is the Zend *Cache* which caches compiled scripts into memory.

    The Zend Optimizer takes your PHP code and optimizes it on the fly.

    The Zend Optimizer is free whereas the Cache is not.

    There are several free caches out there which do the job (albeit a bit slower than the Cache and they don't work with the Optimizer like the Cache does):
    AB Cache
    APC Cache

    I can't remember the URLs since we use the Zend Cache but google should know.

  12. #12
    SitePoint Enthusiast pedershk's Avatar
    Join Date
    Mar 2001
    Location
    Oslo, Norway
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm going to take a look at the Zend Cache, since I came to the same conclusion as you just noted above, MattR.

    It's got a fairly hefty price tag, but everything pales in comparison after we purchased our Oracle licenses
    **Henning K. Pedersen**
    Forbrukerkraft Norway A/S

  13. #13
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    9i is out for Linux BTW. 2GB download!!

    Also, you can get substantial discounts from Zend if you buy 2 or more licenses (we got a good deal for a 4CPU license + Case Study).


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •