Re: [Sqlrelay-discussion] the efficiency of sqlrelay

[David M. <dav...@fi...>]

Hmm,

I suspect that the issue is that the first time the query is parsed,
Oracle has to do work to parse it.  But then afterward, the
already-parsed query is cached (as is the result set, or at least part
of it) and oracle just has to look it up.  I bet that if you restart the
db, and run the query using php-oci first, then using php-sqlrelay,
you'll see that in that case, php-oci is slower than php-sqlrelay.

Dave
dav...@fi...

On Wed, 2007-09-05 at 17:32 +0800, 孙庚 wrote:
> hi,all:
>  
>  
> why sqlrelay query a intricate sql will so slow ?
> the same sql query :
>    php-sqlrelay:0.16688680648804s
>    php-oci func:0.046548843383789s
>  
>  
>  
>  
> my PC:
> intel c4 2.4 with 512ram
> my OS:
> rh linux 9
> oracle 9i
> apache 2
> php4.4
>  
>  
> when i try to user php with sqlrelay link this:
>  
> select i.P_PRODUCTNAME as P_PRODUCTNAME , i.p_companyid , i.p_id as
> p_id , i.p_type , to_char( I.P_CREATETIME, 'yy-mm-dd hh24:mi:ss') as
> createtime , i.p_user as user_name , to_char( I.P_CHECKTIME ,
> 'yy-mm-dd hh24:mi:ss') as checktime , to_char( I.p_lastupdatetime,
> 'yy-mm-dd hh24:mi:ss') as updatetime , i.p_admin , i.P_STATUS as sta ,
> l.g_languagename , l.g_id , i.p_productimage from c_product i ,
> g_language l where l.g_id=i.p_languageid and i.p_languageid=1 and
> I.P_CREATETIME>=to_date('2006-08-13 00:00:00','YYYY-MM-DD HH24:MI:SS')
> and I.P_CREATETIME<=to_date('2007-08-13 23:59:59','YYYY-MM-DD
> HH24:MI:SS') and i.p_productimage>0 order by I.P_CREATETIME desc
>  
> the timer tell me:
> conn time: 0.00015997886657715
> prase time: 0.16688680648804
> disconn time: 0.00068283081054688
> all time: 0.16797995567322
>  
> but,when i try php self oci functions to query the same sql:
> conn time: 0.046813011169434
> prase time: 0.046548843383789
> disconn time: 0.00010800361633301
> all time: 0.093719959259033
>  
> and i try php with sqlrelay agein:
> select a_admin from g_admin
>  
> the timer tell me:
> conn time: 0.00015783309936523
> prase time: 0.014791011810303
> disconn time: 0.00018692016601562
> all time: 0.015383958816528
>  
> and i try php self oci function:
> select a_admin from g_admin
>  
> the timer tell me:
> conn time: 0.048692941665649
> prase time: 0.0018470287322998
> disconn time: 9.1075897216797E-05
> all time: 0.050878047943115
>  
>  
> my sqlrelay.conf:
> <?xml version="1.0"?>
> <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
> <instances>
>         <instance id="oracletest" port="9000"
> socket="/tmp/oracletest.socket" dbase="oracle8" connections="10"
> maxconnections="100" maxqueuelength="5" growby="2" ttl="60"
> endofsession="commit" sessiontimeout="600" runasuser="nobody"
> runasgroup="nobody" cursors="5" authtier="listener" handoff="pass"
> debug="none">
>                 <users>
>                         <user user="oracletest"
> password="oracletest"/>
>                 </users>
>                 <connections>
>                         <connection connectionid="oracletest"
> string="user=bigfish_test;password=bigfish_test;oracle_sid=bigfish;oracle_home=/opt/oracle/product/9i2" metric="1"/>
>                 </connections>
>         </instance>
> </instances>
> 
>  
>  
>  
>  
>  
> 
> 
> 
> ______________________________________________________________________
> 独有“帐号保险柜”，保护网银网游密码，瑞星2008版免费
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems?  Stop.
> Now Search log events and configuration files using AJAX and a browser.
> Download your FREE copy of Splunk now >>  http://get.splunk.com/
> _______________________________________________ Sqlrelay-discussion mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion