SQL Query :Change to improve execution time

Question

I have 2 Tables . CategoryMaster(CATEGORY_ID (primaryKey),Category_Name) and FILE_MASTER (FileId primaryKey,FILE_TITLE,FILE_DESCRIPTION,CATEGORY_ID (refer to Category Id of category master))

Now I want to have an SQL query which will return a resultset with 2 columns : Categories and the number of Files created under the category.

I am using the below query to get the data. It is returning the correct data.

select Category_Name,Count(FILE_TITLE) Item_Count 
from (
   select cm.Category_Name,fm.FILE_TITLE,FILE_DESCRIPTION 
   from CATEGORY_MASTER cm, FILE_MASTER fm 
   where fm.CATEGORY_ID=cm.CATEGORY_ID
) AllRecords 
group by Category_Name

How can i reframe the above query so that it will return the same result set but,executes in a very less time (Assume that there are more than 20000 records in ItemMaster table)

Answer 1

select CM.Category_Name, Count(FM.Category_ID) Item_Count
from CategoryMaster CM
left join FILE_MASTER FM on CM.Category_ID = FM.Category_ID
group by CM.Category_Name

Should be fine - if you're getting poor performance have you checked you execution plans to see how the query is actually running. Do you have correct indexes in place to help this query run - e.g. an index on Category_ID in the FILE_MASTER table would probably help?