Ok this has been bothering me for about a week now so I guess I'll throw the question to you guys...

I have a SP that does nothing but gives all product_ID's and gives me a count of each:

select product_id, count(product_id) from [Order's table] inner join [shipping's table] on ... where [order has not been shipped] group by product_id


With the where clause, I'm looking at a total of around 4000 orders that will be group by and counted out of a total of around 300000 entries in the table. Almost 30% of the time this SP will Timeout on the application calling it. I've looked at the application, did a lot of reading and made as many changes as I can to optimize the code on both the application and SP to make sure its as efficient as it can(I'm on version 10 now of this code and SP...). Is there any issues with doing group by and counts that I'm not aware of? Could this be an index issue...should I rebuild my indexs? Any suggestions on what to do next would be great.

Thanks ahead of time