If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > the more performatic sql

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 12-12-07, 08:06
dreampeppers99 dreampeppers99 is offline
Registered User
 
Join Date: Dec 2007
Posts: 2
Question the more performatic sql

The two sql:

Number 1:
SELECT * FROM conteudo_imagem WHERE idConteudo
IN (SELECT idConteudo FROM conteudo WHERE idGrupo = 9) ORDER BY idImagem


Number 2:
select conteudo_imagem.*
from conteudo_imagem ci
inner join conteudo c
on ci.idconteudo = c.idconteudo
where
c.idgrupo = 9
order by ci.idimagem


The questions:

The two sql resulting at the same result?
What of them is more performatic?
Reply With Quote
  #2 (permalink)  
Old 12-12-07, 08:21
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
i believe you will find that number 2 will give an error

instead of select conteudo_imagem.* you need to say select ci.*

otherwise, yes, both will give the same result

which of them performs better? that depends on your database management system

this is the ANSI SQL forum, which is more concerned with correctness of the SQL than performance
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 12-12-07, 08:38
dreampeppers99 dreampeppers99 is offline
Registered User
 
Join Date: Dec 2007
Posts: 2
Quote:
Originally Posted by r937
i believe you will find that number 2 will give an error
instead of select conteudo_imagem.* you need to say select ci.*otherwise, yes, both will give the same result
which of them performs better? that depends on your database management system this is the ANSI SQL forum, which is more concerned with correctness of the SQL than performance
Thanks !!!
Reply With Quote
  #4 (permalink)  
Old 12-16-07, 16:31
Peter.Vanroose Peter.Vanroose is offline
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
Quote:
Originally Posted by dreampeppers99
Number 1:
SELECT * FROM conteudo_imagem WHERE idConteudo
IN (SELECT idConteudo FROM conteudo WHERE idGrupo = 9) ORDER BY idImagem


Number 2:
select conteudo_imagem.*
from conteudo_imagem ci
inner join conteudo c
on ci.idconteudo = c.idconteudo
where
c.idgrupo = 9
order by ci.idimagem
There is actually a third equivalent formulation, sometimes more performant than the two others:
Code:
SELECT *
FROM conteudo_imagem ci
WHERE EXISTS
 (SELECT 1 FROM conteudo
  WHERE idGrupo = 9
  AND  ci.idConteudo = idConteudo)
ORDER BY idImagem
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
Reply With Quote
  #5 (permalink)  
Old 12-16-07, 16:35
Peter.Vanroose Peter.Vanroose is offline
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
Quote:
Originally Posted by r937
which of them performs better? that depends on your database management system
Not only on the RDBMS, but also on the current status of the two tables (i.e., number of rows, width of the rows) and surroundings (presence of indexes, physical ordering of the data, ...)
Even with the same tables on the same RDBMS, but at different time instances, a different one from the three formulations will be the most performant one.
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On