Need Help Analyzing 2 Tables

[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

 

[ClaireHCM]

Probably you can drop and recreate index on uniqueidentifier column on both parent and child table.
And execute "update statistics", and using "table hint" to force indexes.

Way one is better than way two.But also try to use inner join.

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

 

[jackmenus]

Does it seem reasonable that the query processing time can be reduced down to a few seconds (versus the current 2-3 minutes)?

I'm open to any ideas... including drastic ones!

 

[SQLSister]

Sure I once changed things and had something go from about 5 minutes to milliseconds. First update your statistics or drop and recreate the indexes. The try:

select table2.name, count (*) from table2 inner join table1 on table2.id = table1.id group by table2.name

also try
Select Name, Count (*) from (select table2.name as Name from table2 inner join table1 on table2.id = table1.id order by table2.name) a group by Name

 

[Mdavis123]

Jack

Don't use count(*). Use Count(Colx) in both sections of your query.

See if it helps.

MikeD