CODEWITHSUNDEEP
sqloracle-databaseperformance
Teja
Teja

Reputation: 13534

Tuning my SQL query

Hi I am trying to improvise my query to get better performance.Is any other way of writing my SQL.The question is as below.

SELECT DISTINCT A.name as name,
                A.gender as gender
FROM 
(
SELECT *  
 FROM Students S,
      Enrollment E,
      Group1 G,
      Ingroup I   
 WHERE S.sid = E.sid 
   AND S.sid = I.sid
   AND I.gid = G.gid 

)A,
(SELECT *
 FROM Students S,
      Enrollment E,
      Group1 G,
      Ingroup I   
 WHERE S.sid = E.sid 
   AND S.sid = I.sid
   AND I.gid = G.gid 
   AND S.name="Andrew Peers"
) B
WHERE A.dept = B.dept
  AND A.cid  = B.cid
  AND A.gid  = B.gid;

Upvotes: 1

Views: 177

Answers (3)

jakub.petr
jakub.petr

Reputation: 3031

Just idea...

WITH (SELECT *
 FROM Students S,
      Enrollment E,
      Group1 G,
      Ingroup I   
 WHERE S.sid = E.sid 
   AND S.sid = I.sid
   AND I.gid = G.gid 
   AND S.name="Andrew Peers"
) Andrew
SELECT A.name as name,
                A.gender as gender

 FROM Students S,
      Enrollment E,
      Group1 G,
      Ingroup I   
 WHERE S.sid = E.sid 
   AND S.sid = I.sid
   AND I.gid = G.gid 

   AND g.gid = Andrew.gid
   AND e.cid = Andrew.cid
   AND s.dept = Andrew.dept;

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

The two subqueries returns too many rows. I don't know the structure and relationship between your tables, so, all I can do is to reduce the number of rows of second query. Also use ANSI join syntax:

SELECT S.name as name,
       S.gender as gender
FROM Students S
      JOIN Enrollment E ON S.sid = E.sid 
      JOIN Ingroup I on  S.sid = I.sid
      JOIN Group1 G on I.gid = G.gid
      JOIN 
         (SELECT dept, cid, gid
          FROM Students S
            JOIN Enrollment E ON S.sid = E.sid 
            JOIN Ingroup I on  S.sid = I.sid
            JOIN Group1 G on I.gid = G.gid
          WHERE S.name="Andrew Peers"
         GROUP BY dept, cid, gid
        ) B 
        ON S.dept = B.dept AND G.cid  = B.cid AND G.gid  = B.gid;

The subquery will return the department, class_id and the group of Andrew and then the query will get all students with the same specs.

Upvotes: 1

Deceiver
Deceiver

Reputation: 324

First building sub-queries in the from is bad because you lose the indexes, decreasing the performance. I'm not sure but i don't see the need to use all 4 tables, check to see if this query that i leave serves your purposes:

    Select distinct S.name AS name, 
           S.gender AS gender
      from Students S,
           Group1 G,
           Ingroup I
     where 1=1
       AND S.sid   = I.sid
       AND I.gid   = G.gid
       and exists (select 'X'
                     from Students S1
                        , Ingroup I1
                    where 1=1
                      and S1.sid   = I1.sid
                      and I1.gid = I.gid
                      and S1.sid = S.sid)

Upvotes: 0

Related Questions