Home » RDBMS Server » Performance Tuning » Can this query be improved for performance?
Can this query be improved for performance? [message #445495] Tue, 02 March 2010 03:55 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
while bad_records_cursor_rec%found
  loop
    update reviews
    set invalid_review='Y'
    where isbn = bad_record_cursor.isbn
    and book_id = bad_recods_cursor.id
    fetch bad_records_cursor into bad_records_cursor_rec;
end loop; 
   


The above query results in a read on a table that has about 10,000 rows and for each row it searches another table that has about 3 million rows. This means it will search the 3 million row table for each of the rows in the 10,000 row table. Is there any way i can improve the query.

Is there any difference in performance if i do something like this

update reviews
set invalid_review='Y'
where exists <bad rows table>

thanks
Re: Can this query be improved for performance? [message #445498 is a reply to message #445495] Tue, 02 March 2010 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Doing this is one SQL statement is far far faster than your PL/SQL block.

Regards
Michel
Re: Can this query be improved for performance? [message #445501 is a reply to message #445498] Tue, 02 March 2010 04:33 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Do you mean using the "where exists" clause?

Re: Can this query be improved for performance? [message #445507 is a reply to message #445501] Tue, 02 March 2010 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I mean 1 SQL statement is faster that your PL/SQL block.
The best SQL statement depends on your data and so on, but anyway is faster than PL/SQL.

Regards
Michel
Re: Can this query be improved for performance? [message #445512 is a reply to message #445507] Tue, 02 March 2010 05:07 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Basicly i have two tables with the following columns

Reviews
-------
id
isbn
name
title
author
invalid_review

Bad_records
-----------
isbn
book_id
reason_rejected

The bad_records table contains rows that should be marked as invalid in the reviews tables. What i am trying to do is set the invalid_review columns to 'Y' for each row that is in the "Bad_records" table in the "reviews" table.

The while loop shown above just loops through each row in the bad_records table and update the reviews table if the isbn and book_id match. This is done by selecting all records in bad_records into a cursor.

while bad_records_cursor_rec%found
  loop
    update reviews
    set invalid_review='Y'
    where isbn = bad_record_cursor.isbn
    and book_id = bad_recods_cursor.id
    fetch bad_records_cursor into bad_records_cursor_rec;
end loop;


This means that for each row in the bad_Records table i will scan 2 million rows in the reviews table to find the record to mark as invalid. This seems ineficient. How can i do it in one query? Could you post an example.

THanks
Re: Can this query be improved for performance? [message #445515 is a reply to message #445495] Tue, 02 March 2010 05:21 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to give us the query for bad_records_cursor_rec
Re: Can this query be improved for performance? [message #445516 is a reply to message #445512] Tue, 02 March 2010 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read your own post:
update reviews
set invalid_review='Y'
where exists <bad rows table>


Regards
Michel

[Updated on: Tue, 02 March 2010 05:22]

Report message to a moderator

Re: Can this query be improved for performance? [message #445543 is a reply to message #445516] Tue, 02 March 2010 07:23 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
The bad_Records_cur looks like this

CURSOR  bad_records_cursor IS
		select *
		from Bad_records;
Re: Can this query be improved for performance? [message #445557 is a reply to message #445495] Tue, 02 March 2010 08:11 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then use exists
Re: Can this query be improved for performance? [message #445565 is a reply to message #445543] Tue, 02 March 2010 08:32 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just take what your PL/SQL loop does:

update reviews
set invalid_review='Y'
where (isbn,book_id) in 
      (select isbn,id from Bad_records)

It is as simple as that.

Regards
Michel
Previous Topic: Should an index be rebuilt or recreated during data load.
Next Topic: Autotrace (merged by CM)
Goto Forum:
  


Current Time: Fri Dec 09 13:51:08 CST 2016

Total time taken to generate the page: 0.19712 seconds