(RADIATOR) SQL "bind variables" ?

[Olivier Macchioni]

Hello All,

DBI's "Prepared Statements" could help a lot - we're using MySQL and 
PostgreSQL here, and in both cases those prepared statements would 
actually work (and help).... I'm not sure that Stored Procedures would 
help that much, and they're more difficult to maintain than regular Perl 
code (I'm thinking of Version Control for instance).

I don't have any figure regarding the actual benefit of Prepared 
Statements though, but I know that they can be 3x to 10x faster than 
regular SQL queries for bulk inserts / updates.

 - I'm actually much more interested in performance gain for the 
Accounting tables - we're using Interim Accounting and we have really a 
lot of UPDATEs there -

Olivier



Hugh Irvine wrote:

>
> Hello Ricardo -
>
> In general with Oracle you should use stored procedures if you are  
> concerned about performance.
>
> You can then configure the AuthBy SQL clause to call the stored  
> procedure.
>
> Unfortunately there is no standard way to implement bind variables  
> across different databases - and some databases don't support them at  
> all.
>
> regards
>
> Hugh
>
>
>
> On 15 Aug 2006, at 08:42, Ricardo Martinez wrote:
>
>> Hello.
>>         I have a question regarding to the "AuthBy SQL".  We have  
>> several AuthBy for this type working fine.  Each AuthBy make a  
>> connection to a Oracle database.  The problem/question is ragarding  
>> to the performance in my DB.  As far as i know Radiator is making  
>> "inserts" like :
>>
>> insert into ACCOUNTING  
>> (ACCTDELAYTIME,ACCTSESSIONID,ACCTSTATUSTYPE,CALLEDSTATIONID,CALLINGSTA 
>> TIONID,NASIPADDRESS,NASPORT,SERVICETYPE,SIPCSEQ,SIPFROMTAG,SIPMETHOD,S 
>> IPRESPONSECODE,SIPTOTAG,SIPTRANSLATEDREQID,USERNAME) values  
>> ('0','9831a498-91b9ca at 130.130.28.41','Stop','sip: 
>> 584 at sip.mydomain.com,'sip: 
>> 5591232 at sip.mydomain.com,'200.100.100.35','5060','Sip- 
>> Session','103','1fec2b5a740','8','200','5fdc88b54i0','sip: 
>> 5591097 at 200.27.40.86:5060','55911232 at sip.mydomain.com')
>>
>> and for each insert use the common instruction from above.  With  
>> this format the DB has to parse each "instruction" every time a new  
>> insert arrives, making the DB performance degrade.
>>
>> Is possible for Radiator use "bind variables" to work with  Oracle?.  
>> With "bind variables" you only need to send the  instruction one time :
>>
>> INSERT into temporal values (:value1,:value2)
>>
>> and then only send the respective values, making the insertions  much 
>> more efficient.  Is maybe this format implemented in Radiator?  or is 
>> in the Roadmap?
>>
>> Thanks !
>>
>> Ricardo.-
>>
>>
>
>
>
> NB:
>
> Have you read the reference manual ("doc/ref.html")?
> Have you searched the mailing list archive (www.open.com.au/archives/ 
> radiator)?
> Have you had a quick look on Google (www.google.com)?
> Have you included a copy of your configuration file (no secrets),
> together with a trace 4 debug showing what is happening?
>

--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.