Help: Indexing / Speeding Up Query?

[jackmenus]

Hi Everyone,

I'm having a really tough time getting a query to run quickly. There are two tables invovled:

Table 1 is a temporary table that's created by the program, and will contain anywhere between 1,000 and 100,000 rows. The only field is a unique identifier code.

Table 2 has approximately 5 million rows. There are several fields that can be queried against, such as state, score (a number field), and gender. There is also a field called name, as well as the unique identifier that is the same as in table 1.

Non-clustered indexes have been created against the various fields in table 2.

The goal is to bring back the name field from table 2 and the number of times that it cross-references against the unique id in table 1... using a query similar to:

select table2.name, count (*) from table2, table1 where table2.id = table1.id group by table2.name order by count (*) desc

We've also tried:

select name, count (*) from table2 where id in (select id from table1) group by name order by count (*) desc

Both queries take 2-3 minutes to run... which is waaaaay too long. A friend tells me there should be a way to get the results we need in a few seconds, and we're totally frustrated right now.

Can anyone out there think of an indexing scheme, a different way to write the query, or any other solution to help with the problem? Thanks!

- Jack

 

[JamesLean]

To optmimize this particular query I would try creating a clustered index on the id column of table1 and a clustered index covering the id, name columns of table2.

I would certainly expect that query to run in a matter of seconds rather than minutes. If it still doesn't then you may have other issues affecting it - hardware, resources, other database activity etc.

--James