Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to know if returning a count is much more expensive than returning a dataset in regard to processing performance? I assume there is a bandwidth increase in delivery to the application logic.

I am basically trying to determine if it is more worth my time to create separate procedures to get counts or just utilize the procedures returning datasets and count how many rows are in the dataset in the application logic.

share|improve this question
1  
Do you want COUNTs with a normal SELECT? Or separately? –  gbn Nov 2 '11 at 19:14
    
Fundamentally the question is along the lines of "Is returning a Count significantly less expensive than returning the dataset of the thing being counted? (since I can then simply get the count in my application logic)" i.e. seperately. –  Joshua Enfield Nov 3 '11 at 16:27
add comment

3 Answers

Not 100% sure I understand your question but - if you are already selecting the data, will also selecting a count be that much more expensive? No, I don't think you'd have an issue doing that. Selecting just a count should incur the same cost as far as the scans/seeks and joins/filtering/aggregating you had to do in your query but also show some time saved in processing/formatting and sending the actual rows as well. If you your select takes 10ms, I'd expect the count to also take 10 or less ms.

Alternatively you can get the rowcount returned by operations as well and return that without even incurring the additional 10 ms.

This SO question deals with the same basic question I think you are driving at and gives a few different answers on technique.

share|improve this answer
add comment

If there are parts of your application that need just a count, then create the appropriate procedures to return only the count. Benefits will of course vary depending on the size of the datasets involved. Obviously no point in calling the count procedure anywhere that you've also retrieved the dataset.

Depending on what it is your counting, you will likely be able to create a query to cover just the columns necessary for the count. The wider dataset may not be viable to cover and would then require lookups or a scan of the heap or clustered index to satisfy.

share|improve this answer
add comment

Using an index column for the count is recommendable (if you didn't know), then it will also scan the index instead of the table

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.