Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2015

    Unanswered: How to optimize the query more?

    Table Dept has two columns (did(int) , dname(varchar)).
    Table emp has five columns (eid(int), mid(int), nme(varchar), did(int), city(varchar)).

    Now I need to get the names of all departments having more than 1000 employees.

    Below is my query to achieve this:

    ; with cte_nandu as (
    select d.dname, count(e.eid) [emp_count] from tb_emp e
    inner join tb_dept d
    on e.did=d.did
    group by d.dname
    select * from cte_nandu where emp_count > 1000

    However, in the above CTE all the count is captured first and only records with count more than 1000 is selected.

    Could you optimize the query so that we calculate only the departments with more than 1000 employees?

  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    we could simplify your query with a HAVING clause...

    select d.dname, count(e.eid) [emp_count] from tb_emp e
    inner join tb_dept d
    on e.did=d.did
    group by d.dname
    HAVING count(e.eid) > 1000
    As for optimizing it, you will have to give use the detail of your data definition language and details of the indices on the table. Then you will have to provide an execution plan and perhaps the output from SET STATISTICS IO and SET STATISTICS TIME.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2013
    Provided Answers: 1
    Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You got nothing right! In fact, you tibbled! You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

    And you need to read and download the PDF for:
    And learn about tibbles

    Here is a guess, made without any DDL and with corrections to the data element names:

    SELECT D.dept_name, COUNT(P.emp_id) AS emp_count
    FROM Personnel AS P,
    Departments AS D
    WHERE P.dept_id = D.dept_id
    GROUP BY d.dept_name
    HAVING COUNT(P.emp_id) > 1000;

    If the tables are simple, this could be fine. Is there DRI between Personnel and Departments? Sure wish we had DDL ..

Posting Permissions

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