Questions about queries...

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

If you have an index on product_id and orderstatus then the query does not even have to read data pages.

Check fragmentation on index and table using dbcc showcontig before you venture out to rebuild indexes.

How does the query plan look for this?

Does the stored procedure fail with the same consistancy when run from Query analyzer as compared to the VB app?

If the sp fails the same way from Query Analyzer then the issue is database related, not application related. If it works consistantly then the issue is outside of the db server. Either network, application server, or both.

Sometimes we have issues with the same symptoms that occur on sp's that haven't been changed in weeks/months. Frequently it turns out that there's some issue with our application server(like an upgrade was applied to the os or mdac without our knowledge) or some network issue.

Hope I've helped. Good luck.

Sidney Ives
Database Administrator
Sentara Healthcare

Quote:

---

Originally posted by skhanal
How does the query plan look for this?

---

Do you mean Execution Plan? If not, could you explain how to get the query plan?

Execution plan = query plan

Hi,

If you are using ADO for accessing the sp then check for the commandtimeout property of the command. Setting it to 0 should prevent the timeout from occuring.

Also, as far as I know one of the versions of ADO had a problem with the timeout where setting it to 0 wouldnt make a difference. So dont forget to get the latest version of ADO

Let me know if this works for u!
Ketan