Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    46

    Unanswered: Which query is faster.

    Can somebody please suggest which query will be faster.
    Notes:
    1. emp_table is quite big. task_table is bigger than emp_table
    2. Scenario# a) Need to get all manager b) Need to get all non manager
    3. Manager is just 10%
    4. Since I can not paste the exact queries, I have pasted the similar dummy queries.

    QUERY#1
    =======
    select T.emp_id, T.task_count from emp_table U,

    ( select emp_id, count(*) as task_count
    from
    (
    (select S.emp_id, S.task_id from task_table S where S.task_id in (select task_id from imp_task)
    union
    (select S.emp_id, S.task_id from some_table S where ....)
    )

    group by emp_id
    ) T
    WHERE T.emp_id = U.emp_id
    AND U.manager_flag <> 1
    ORDER BY T.task_count ASC;
    ----------------------------------------------------------------------
    QUERY#1
    =======
    select T.emp_id, T.task_count from

    ( select emp_id, count(*) as task_count
    from
    (
    (select S.emp_id, S.task_id from task_table S, emp_table U where WHERE T.emp_id = U.emp_id AND U.manager_flag <> 1 AND S.task_id in (select task_id from imp_task)
    union
    (select S.emp_id, S.task_id from some_table S, emp_table U where WHERE T.emp_id = U.emp_id AND U.manager_flag <> 1 AND ....)
    )

    group by emp_id
    ) T
    ORDER BY T.task_count ASC;

    Thanks
    Prashant

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    start from scratch. that is my opinion.
    you have way too many subqueries and there has got to be a better option
    than UNION.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can somebody please suggest which query will be faster.
    SQL> SET TIME ON
    run them both & measure the results YOURSELF!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •