Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Quick Tip: Using SQL to return the number of records in a Table

Quick Tip: Using SQL to return the number of records in a Table

Rate This
  • Comments 11

David Meego - Click for blog homepageNow 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:

SELECT COUNT(*) FROM GL30000

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:

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')

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/j

Hope you find this useful.

David

  • 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.

    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

    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'

Page 1 of 1 (11 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 8 and 3 and type the answer here:
  • Post