How to optimize the below query?

  • 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?

  • I do not know if I understand correctly, but try adding the 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

    Hope this helps.

  • toraghubiz (1/28/2015)


    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?

    Something like this?

    with d1000 as

    (select did, Total = count(did) from emp group by did having count(did) > 1000)

    select d.*

    from d1000

    join dept d on d.did = d1000.did

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • also is there an index on tb_dept(dname) ? sincethat's the group by, an index would help that specific query.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • A quick note: starting a CTE with a semicolon is like starting a sentence with a '.'

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • FWIW, imex's query will generate the same plan as yours but it's simpler to write.

    Phil's might be better or not, you'll need to test it before you implement it.

    For questions about performance, you should read the following article to ensure that you're giving all the information needed. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • toraghubiz (1/28/2015)


    ...

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

    That's illogical, captain.

    What you could do is precalculate, using insert/delete triggers on the employees table.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply