cursor vs join

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
  

Poston Mon, 25 Aug 2014 6:00 pm

Good afternoon everyone!

Someone tell me if there's a peformance gain between create a cursor that then a tuple will query into another table or create a query with a join between two tables would be more performative?
For example:
Code: Select all
for cs in (select id, nome from pessoas)
loop
select cs.nome, c.curso, c.status_final from cursos c where c.id = cs.id
and
Code: Select all
select cs.nome, c.curso, c.status_final from cursos c,pessoas cs where c.id = cs.id
heraldoaraujo

Poston Mon, 25 Aug 2014 6:48 pm

As a general rule, the more performance would JOIN himself.
But you gotta see how are created the tables, indexes, amount of data in the tables and etc.
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.

Poston Mon, 01 Sep 2014 10:53 am

heraldoaraujo, avoid PL/SQL (cursor) If you can solve the problem with pure SQL. Pure SQL (without interaction with PL/SQL) is always faster.

[] s
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net

Poston Wed, 03 Sep 2014 6:02 pm

fbprado, I performed a test and finally the PL/SQL code with cursor was 20 x faster than a join SQL in Oracle.
But before that in this same query I created an index in which the course fell from 19000 (solid) to 500 (five hours), I think I created a good index, but still after changes the SQL code for a PL/SQL cursor time fell to 9mins.
Can there be any explanation for this? For the end result I wanted to inform you that the result was 189,923 tuples
heraldoaraujo

Poston Wed, 03 Sep 2014 10:32 pm

@heraldoaraujo, very strange that happens! I can only explain if u post here for us 2 code examples and the execution plan generated by SQL.

[] s
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net



Return to DBA Tuning

Who is online

Users browsing this forum: No registered users and 2 guests