9 Replies Latest reply: Dec 28, 2012 6:42 AM by ranit B RSS

    Sql Queries performance

    RajeshKanna
      Hi,

      I am writing below two queries,to display the employee details having managers


      select *
      from emp e
      where exists ( select 'x'
      from emp e1
      where e.empno = e1.mgr)

      select ename
      from emp
      where empno in (select mgr
      from emp)

      Performance wise which query is faster.i am checking the explain plan both the queries showing same result.

      can any one explain which one is better with proper reason?

      Regards,
      Rajesh
        • 1. Re: Sql Queries performance
          BluShadow
          What do you get if you look at the explain plan for both?

          When I run it on my database, the explain plans are identical.
          • 2. Re: Sql Queries performance
            BluShadow
            969052 wrote:
            Performance wise which query is faster.i am checking the explain plan both the queries showing same result.
            Ah, just seen you've already looked.

            In that case, the optimiser is determining that they are essentially the same and they will both likely perform the same.
            • 3. Re: Sql Queries performance
              RajeshKanna
              most of the people said that correlated subqueries are more faster than normal subqueries.
              but here no differance in the explainplan
              • 4. Re: Sql Queries performance
                BluShadow
                969052 wrote:
                most of the people said that correlated subqueries are more faster than normal subqueries.
                but here no differance in the explainplan
                As with most things... it depends. Your example query is very basic, and easy for the optimiser to change for optimum performance. More complex queries it may not change as easily, and it also depends on the data on the tables (emp is a very small table and not suitable for performance testing) and the indexes and statistics available.
                • 5. Re: Sql Queries performance
                  Karthick_Arp
                  969052 wrote:
                  most of the people said that correlated subqueries are more faster than normal subqueries.
                  but here no differance in the explainplan
                  IN and EXIST both uses SEMI-JOIN. So both does not make any difference. So you might think why have two statements. The reason is NOT IN and NOT EXIST are totally different thing. Thats because the way they IN and EXISTS process NULL are quiet different.
                  • 6. Re: Sql Queries performance
                    981337
                    Hi,

                    For Hierarchial relations better to use start with and connect by prior clause which have better performance,

                    SELECT employee_id, last_name, manager_id
                    FROM employees
                    CONNECT BY PRIOR employee_id = manager_id
                    order by manager_id;

                    To get level
                    SELECT employee_id, last_name, manager_id, LEVEL
                    FROM employees
                    CONNECT BY PRIOR employee_id = manager_id;

                    Thanks!
                    • 7. Re: Sql Queries performance
                      BluShadow
                      978334 wrote:
                      Hi,

                      For Hierarchial relations better to use start with and connect by prior clause which have better performance,
                      That wasn't the OP's question though.
                      • 8. Re: Sql Queries performance
                        APC
                        969052 wrote:
                        Performance wise which query is faster.
                        As is often the case with tuning questions, the answer is "it depends". Specifically it depends on the data volumes - especially the number of employees who are also managers - and the presence of indexes.

                        If the number of distinct managers is small and the MGR column has an index it is likely that the IN sub-query will perform better. But if you have a highly vertical organisation where most people manage somebody it is very possible that the EXISTS will be faster.
                        i am checking the explain plan both the queries showing same result.
                        If you're using the canonical SCOTT.EMP table (or something similarly toy-sized) the small number of rows is unlikely to make a difference in execution times for different plans.

                        Cheers, APC
                        • 9. Re: Sql Queries performance
                          ranit B
                          IN and EXIST both uses SEMI-JOIN. So both does not make any difference.
                          So, do u mean IN and EXIST always perform equally ?
                          I didn't get this point exactly.
                          The reason is NOT IN and NOT EXIST are totally different thing. Thats because the way they IN and EXISTS process NULL are quiet different.
                          They both look for records not present in the table...right? So, then how it handles NULL differently.

                          Could you please explain this??

                          Thanks,
                          Ranit B.