cursor vs join

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
Post Reply
User avatar
heraldoaraujo
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 41
Joined: Sun, 19 Feb 2012 12:10 pm

Good afternoon people!

Can anyone tell me if there are any peform gain between creating a cursor that then a tuple will consult in another table or create a query with a join between two tables would be more performative?
For example:

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

Select all

select cs.nome, c.curso, c.status_final from cursos c,pessoas cs where c.id = cs.id 
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

As a general rule, the most performative would be Join himself.
But you have to see how tables are created, indexes, quantity of data in tables and so on.
User avatar
fbifabio
Moderador
Moderador
Posts: 199
Joined: Tue, 22 Feb 2011 1:51 pm
Location: São Paulo - SP
Contact:
Fábio Prado
www.fabioprado.net

Heraldoaraujo,

Avoid pl / sql (cursor) if you can solve the problem with pure SQL. Pure SQL (without interaction with PL / SQL) is always faster.


User avatar
heraldoaraujo
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 41
Joined: Sun, 19 Feb 2012 12:10 pm

FBRED,

I finally performed a test and the PL / SQL code with cursor was 20x faster than a Join SQL in Oracle.
But before that in this same appointment I created an index where the course fell from 19000 (5hs) to 500 (5hs), I think I created a good index, but even then after changes the SQL code for a cursor In PL / SQL time dropped to 9mins.
Can there be any explanation for this? For the final result, I wanted to inform you that the result was 189,923 tuples
User avatar
fbifabio
Moderador
Moderador
Posts: 199
Joined: Tue, 22 Feb 2011 1:51 pm
Location: São Paulo - SP
Contact:
Fábio Prado
www.fabioprado.net

@heraldaraujo,

very strange this happens! I will only be able to explain if you post here for us the code of the 2 examples and the execution plan generated by SQL.


Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 4 guests