Home » SQL & PL/SQL » SQL & PL/SQL » Please fine tune my query
Please fine tune my query [message #283461] Tue, 27 November 2007 04:09 Go to next message
kprasanna_79
Messages: 5
Registered: October 2007
Junior Member
Hi,
<br>Please findmy below query which takes 2-3 days to run. Please can somebody suggest some idea to fine tune it.

   my $a = 'ngps_iri_reports_temp';
   my $b = 'ngps_iri_reports';
   my $test_sql="select filename from $a";
   @results = $db->query_loop($test_sql,\&code);
for(@results) {
  my $sql = "update $b set report = (select $a.report from $a where substr($b.filename,instr($b.filename,'data
.ond',1,1),8) = 'data.ond' and substr($b.filename,instr($b.filename,$a.filename,1,1),length($b.filename))=$a.fi
lename) where substr($b.filename,instr($b.filename,'$_',1,1),length($b.filename)) in (select filename from $a);
$db->execute($sql);
}


I think the final select at the end of the query can be avoided.But need some advice.

or else....here is nutshell of the issue
i need to update one table based on the data present in the other table. What is the shortest way to do it.



-Prasanna.K
Re: Please fine tune my query [message #283462 is a reply to message #283461] Tue, 27 November 2007 04:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Is there a reason for this construct?

My first choice would be to do it in one update statement, if possible.

Second choice would be in a pl/sql procedure.

Doing it in a loop on the client side is the worst thing one can do performance-wise.

What's wrong with simply running :

update ngps_iri_reports b 
   set b.report = (select a.report 
                   from ngps_iri_reports_temp a 
             where substr(b.filename, instr(b.filename, 'data.ond',1,1),8) = 'data.ond' 
               and substr(b.filename,instr(b.filename,a.filename,1,1),length(b.filename))=a.filename)
Re: Please fine tune my query [message #283467 is a reply to message #283462] Tue, 27 November 2007 04:49 Go to previous messageGo to next message
kprasanna_79
Messages: 5
Registered: October 2007
Junior Member
Thanks Thomas,
If i need to update multiple columns say reports, category, geography etc, how should follow with your idea.
-Prasanna.K
Re: Please fine tune my query [message #283470 is a reply to message #283461] Tue, 27 November 2007 04:54 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Update Table_name
set    (field_1,field_2,field_3) = (Select val1,val2,val3
                                    From .....)
where .....


Or

Update Table_name
set    field_1 = (Select   VAL1
                 From .....),
       field_2 = (Select   VAL2
                 From .....),
       field_3 = (Select   VAL3
                 From .....)
where .....


Thumbs Up
Rajuvan.

[Updated on: Tue, 27 November 2007 04:55]

Report message to a moderator

Previous Topic: using multiple "single quotation"
Next Topic: type mismatch found at 'LOAD_CV' between FETCH cursor and INTO variables
Goto Forum:
  


Current Time: Sat Dec 03 03:58:09 CST 2016

Total time taken to generate the page: 0.14454 seconds