SPUG: cgi help

William Julien moonbeam at catmanor.com
Fri Apr 6 15:21:09 CDT 2001


>
>
>>>>      I am writing a cgi script that is using oraperl to query the
>>>>  database and post the results.  My queries are somewhat on the hefty
>>>side
>>>>  and my browser keeps timing out.  Is there any way that I can keep
>>>this from
>>>>  happening?  thanks....
>
>Depending on your situation, there may be other acceptable solutions.
>
>1. Try to reduce the time that the queries take. Talk to a DBA. With
>some careful planning and indexing, we were able to reduce running
>time for some of our queries by an order of magnitude. Oracle can do
>amazing things if you tweak it just right.
>

I had a query that before the oracle admins "optimised" the database
was completing in sub second response time. After their changes it
took up to 4 minutes. The fix for me was to add the following to my query.

    $query = "
    select /*+ CHOOSE */
    ....
    ";

This brought the time back down to about 1.5 seconds. 

Also, I agree with the statement above that careful construction of
your query can really make a difference. If you can avoid joins though
the use of a corralative sub query, that will really speed it up.

For example. Here is a query where I have avoided a join with the use
of a corralative sub query.

$query = "
select /*+ CHOOSE */
        hd_helpd.device_id,
        hd_helpd.symptom_type,
        hd_helpd.symptom_qualifier
from    rptadmin.hd_helpd
where
        hd_helpd.request_type  = 'Resolve'
and     hd_helpd.sr_start_d_t >= 946713600
and (
exists
    ( select request_
        from rptadmin.hd_helpnet
        where hd_helpnet.request_ = hd_helpd.request_
        and  hd_helpnet.remote_device_name in ('BOST','TOST','DOST')
    )
)"; 

At last resort, you could use the nph multipart protocol and fork your
query into a background process. It is nasty and generally annoying.
Here is a nph "hello world".

-->cat nph-count.cgi
#!/usr/bin/perl -- # -*-Perl-*-

#
# this is a demonstration of server push
#

# enable autoflush
$| = 1;   

# send the http header to establish the boundry
print "HTTP/1.0 200\n";
print "Content-type: multipart/x-mixed-replace; boundary=ThisRandomString\n\n"
;

# start the first boundry
print "--ThisRandomString\n";

# loop 10 times
$i = 0;
while ($i < 10) {
  $i++;
  print "Content-type: text/html\n\n";
  print "<html><body><title>This is run $i</title>\n";
  print "<h1>This is run $i</h1></body></html>\n";
  print "--ThisRandomString\n";
  sleep 2;
}

# send the last page
print "Content-type: text/html\n\n";
print "<html><body><title>Fin</title>\n";
print "<h1>Fin</h1></body></html>\n";
print "--ThisRandomString--\n";

exit 0;                        


---
   William Julien           _,'|            _.-''``-...___..--';
moonbeam at catmanor.com      /, \'.      _..-' ,      ,--...--'''
 vi is my shepherd;       < \   .`--'''      `     /| 
 i shall not font.         `-,;'              ;   ; ;  
                     __...--''     __...--_..'  .;.'  
                    (,__....----'''      (,..--''     
perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'
perl -e '( $ ,, $ ")=("a".."z")[0,-1]; print "sh", $ ","m\n";;";;"'


 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     POST TO: spug-list at pm.org       PROBLEMS: owner-spug-list at pm.org
      Subscriptions; Email to majordomo at pm.org:  ACTION  LIST  EMAIL
  Replace ACTION by subscribe or unsubscribe, EMAIL by your Email-address
 For daily traffic, use spug-list for LIST ;  for weekly, spug-list-digest
  Seattle Perl Users Group (SPUG) Home Page: http://www.halcyon.com/spug/





More information about the spug-list mailing list