Difference between Count & CountApprox

chandrurec

Hi all,

I want to know the difference between Count keyword and CountApprox keyword which is assosiated with table.

Both are used for counting the number of records in the given table , if that is the case what is the difference between these two keywords and in what situation we go for Count and CountApprox.

If anybody knows the difference , kindly reply me back.

Thanks in advance.

Regards,
Chandru.

kine

Have you read the on-line help for these two commands?

chandrurec

Hi Kine,

I do read the online help.It is saying that Count is inappropriate because it takes into consideration of both the committed as well as non committed entries into account whereas CountApprox dont.

But still I am not very clear with what do u mean by comitted and non comitted entries.

So if you know the difference , kindly let me know.

Thanks in advance.

Regards,
Chandru.

kine

Than it is not about the function but it is about what does it mean "commited and uncommited"... ;-) It will be quicker for all of us to enter the correct question... ;-)

Commited - any record which was created/modified and the transaction is already commited (the record is there, is not locked, is persistent)
Uncommited - any record which was created/modified/deleted but the transaction is not commited yet - it means that the insert/modify/delete could be rolled back and you cannot be sure if the record will be there as it is or not ("Work in progress" on this record... another process is working with it)


I highly recommend to learn something about transactions and commit and rollback functionality in database systems...

stryk

The difference is this:

A COUNT command actually sends a SELECT COUNT(*) FROM ... statement to the SQL Server, thus the SQL Server is actually counting the number of rows within the filter. Hence, the result is 100% correct.
To perform such a query OK the SQL Server need apropriate indexes, statistics, etc..

A COUNTAPPROX tells the SQL Server to only create a Query Execution Plan for a query:

1. SET SHOWPLAN_ALL ON
2. SELECT * FROM ...
3. SET SHOWPLAN_ALL OFF

Thus SQL Server is not performing a count, it only compiles a QEP. This QEP contains a field names "Estimate Rows" which tells the estimated number of rows within the filtered result. This estimate is shown in NAV - as it says: it's an estimate and might be incorrect.
As the query is not really executed it's faster than a real COUNT.

kitik

I always use CountApprox when I need to count the total rows to show a progress bar. Since it is only a progress bar, it is not needed to be 100% accuratte.

Salut!

stryk

kitik wrote:

I always use CountApprox when I need to count the total rows to show a progress bar. Since it is only a progress bar, it is not needed to be 100% accuratte.
Salut!

I have to admit that I use a full COUNT even with Progress-Bars as I find it so annoying if the status is not telling the truth ](*,) ... means it shows 80% and is finished or it's 100% and still processing ...

kitik

When do you use CountApprox then?

Salut!
Laura Nicolàs

stryk

Well, I don't use it at all ... :roll:

Marije_Brummel

The only valid place for CountApprox might be in Progress Dialogs.

Tomas

stryk wrote:

Hence, the result is 100% correct.

Well. Result is usually %100 correct

What exactly does COUNT count?

stryk

stryk wrote:

The difference is this:

A COUNT command actually sends a SELECT COUNT(*) FROM ... statement to the SQL Server, thus the SQL Server is actually counting the number of rows within the filter. Hence, the result is 100% correct.