CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Sumit Gupta
Sumit Gupta

Reputation: 2192

Optimizing a GROUP BY query

Just trying to optimize a SQL Query.

SELECT   Code, COUNT(*) 
FROM     tblData 
WHERE    ListID = 380 
GROUP BY Code

The table contains 10 million records. So the query usually take 4-5 secs to find the record on SQL SERVER 2008 R2. I found another way of expressing it:

SELECT a.Code, COUNT(*) 
FROM   (  SELECT * 
          FROM   tblData 
          WHERE  ListID = 380 
       ) a 
GROUP BY a.Code

Though in my tests it produce the results in the exact same time. But I wonder if this is faster the previous one or visa versa. Any idea?

Upvotes: 0

Views: 4345

Answers (3)

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79828

As @cornuz and @Wim both said, these two queries should be equivalent.

However, you may find that having a two-column index (on ListId then Code) would improve the performance quite a bit, because

  1. the database would just visit the index, not the table; and
  2. the index wouldn't have to do an extra sort, to bring together the rows that have to be counted together.

Upvotes: 3

Wim
Wim

Reputation: 1096

In Management Studio, select both queries and click on "Display Estimated Execution Plan". As soon as one Query Cost differs from 50%, you have winner (and a looser).

Your second query looks as the result of the first query after it has passed the Analyser. I think both will perform exactly the same.

Upvotes: 0

swingbit
swingbit

Reputation: 2745

With any decent query optimizer, the two queries should boil down to the exact same operations. So I'm not surprised you find they run in the same time.

I don't think you can modify your query to make it run any faster. What you can do, of course, is to have an index on ListID - but I expect you have that already.

Upvotes: 4

Related Questions