This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
Now I am sure there are people out there that will be thinking that this is a silly topic for a blog article. Surely everyone knows how to get the number of records from a SQL table using the COUNT(*) function?
Well, you would be correct that most people do know the COUNT(*) method as shown below:
[code]
SELECT COUNT(*) FROM GL30000
[/code]
But that is not what this article is about.
I was working on a upgrade support case where we needed to check the number of records in the GL30000 table. The upgrade process was taking a long time and I wanted to know how many records the conversion script for the GL30000 table needed to work on. So we issued the line above and 10 minutes later we got the result that there were just over 1 million records in the table.
While working on the new build of the Support Debugging Tool, my beta testers noticed that the Resource Explorer lookups for “Associated Tables” and “Tables Containing Field” were slow when the tables returned had a large number of records. This is because the COUNT(*) function used to get the number of records is slow, it just counts the number of records returned by the SELECT statement limited by the WHERE clause (if provided).
If I am just interested in the total number of records in the table (without any WHERE clause), there must be a better way to get this information?
Well, yes there is… I am glad you asked.
For every table, there is a primary key and stored in the internal system tables for that key is the number of records contained in the key. So using a slightly more complex query, it is possible to obtain the number of records in a table instantaneously without needed to actually count the records themselves.
This is the SQL query to obtain an estimate of the number of records from the index data for a table:
[code]
SELECT objs.name, indx.rowcnt FROM sys.objects objs
INNER JOIN sys.sysindexes indx ON (objs.object_id = indx.id) AND (indx.indid < 2)
WHERE (objs.type = ‘U’) AND (objs.name = ‘GL30000’)
[/code]
This is the code that the Support Debugging Tool now uses, which means it can display the number of records in tables without any performance delays.
Note 1: This method works best when the AUTO UPDATE STATISTICS option on the database is selected, otherwise the estimate of rows might not be as accurate.
Note 2: For SQL Server 2000, there is an issue where the rowcnt column of the sysindexes system table is not updated until a transaction is committed. For more information see http://support.microsoft.com/kb/308822 (Not available)
Hope you find this useful.
David
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.
Great piece of information! We can definitely make use of this logic in other procs that we may write too. Thanks David… 🙂
This is because the COUNT(*) function used to get the number of records is slow, it just counts the number of records returned by the SELECT statement limited by the WHERE clause (if provided).
It is slow, but not because of the reasons provided. It's slow because a COUNT(*) performs a table scan. In tables with large amount of records, this can be a performance killer.
"This method works best when the AUTO UPDATE STATISTICS option on the database is selected, otherwise the estimate of rows might not be as accurate".
It's an estimate and by definition, not necessarily accurate.
MG.-
Mariano Gomez, MVP
What a silly topic for a Blog article! Just kidding David – that's very cool. Thanks for posting this.
Hi Mariano
Yes, it counts the number of records using a table scan.
I did mention that the SQL Query provided an estimate, but if the statistics are kept up to date, the estimate can be accurate.
On my system I am yet to find a table where the "Estimate" gives a different result to the COUNT(*).
David
Hi Michael
Glad you liked my "silly" topic. This method made a significant performance difference to a number of windows in the Support Debugging Tool.
David
Posting from Jivtesh Singh at About Dynamics, Development and Life.
http://www.jivtesh.com/…/everything-dynamics-gp-46.html
Hi David,
That's an interesting one, and of course not silly at all 🙂 DBa's often come up with workaround like this when bumping into road-blocks that MS not necessarily thought they might become an RB one day :-).
Unfortunately the alternative query doesn't seem to work out-of-the-box on my SQL 2005 system… It returns nothing 🙁 and curiously if I leave out the table filer, the query only returns a dozen records, from which a few are GP related (BM10115 and IVR10015). I checked my DB settings and 'AutoUpdate Statistics' is enabled… so what could it be ?
Have a great time,
Beat
Posting from Mark Polino at DynamicAccounting.net
msdynamicsgp.blogspot.com/…/quick-tip-using-sql-to-return-number-of.html
Beat – Please ensure you run the query in the company database. The query does not return records if the table which is being queried is not present in the specific database you are running the query against.
Hope this helps… 🙂
Posting from Vaidy Mohan at Microsoft Dynamics GP – Learn & Discuss
http://www.vaidy-dyngp.com/…/using-sql-to-retrieve-number-of-records.html
It's even easier in SQL Server 2008 R2 and forward, with sp_spaceused. This will give you some other basic information about the object size as well. msdn.microsoft.com/…/ms188776.aspx
ex:
exec sp_spaceused 'GL30000'