Post reply

Record Counts differ in QA vs EM???

  • February 15, 2005 at 11:09 am

    #86725

    MSSQL 2000/sp3

    I'm trying to compare record counts between 2 databases. Any ideal why some table record counts differ when using Query Analyzer <select count(*) from tablename> vs using Enterprise Manager's: right click on tablename, properties, rows ... does it have something to do with sysindexes?

    Many thanks. Jeff

  • February 15, 2005 at 11:35 am

    #542360

    Everything to do with sysindexes!  The EM counts are based on sysindexes which are updated when the statistics are updated.  Should be a reasonable estimate, but never guaranteed.

    Steve

  • February 16, 2005 at 12:08 am

    #542456

    in addition to Steve's responce:

    If you're running sql2k sp3 standard edition on a multi-proc-box, you might encounter this bug (fix available) : FIX: A Parallel Query with a COUNT Aggregate Function May Return Unexpected Results (Q814509)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 16, 2005 at 3:40 am

    #542481

    Do an update stats on both databases and then compare the results. I suspect that sysindexes is out.

    Also use select (1) instead of select(*) - it is marginally faster (no idea why) and use a nolock hint.

  • February 16, 2005 at 3:58 am

    #542488

    select (1) instead of select(*) - it is marginally faster (no idea why) and use a nolock hint

    Can you give a prove for this?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 16, 2005 at 4:59 am

    #542499

    1 enumerates quicker than *

  • February 16, 2005 at 4:59 am

    #542498

    1 enumerates quicker than *

  • February 16, 2005 at 5:06 am

    #542500

    I know that. But that's not really a prove, since you don't know what SQL Server exactly internally will do when you leave the choice up to it. 

    Actually I have never observed any consistent reproducable differences. That's why I was asking.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 16, 2005 at 6:34 am

    #542515

    I already ran a test with this (don't have it anymore). Was checking to see if doing

    IF EXISTS (Select 1 from long query)

    would be faster than

    IF EXISTS (Select * from long query)

    and I couldn't proove anyone faster than the other on a consistant basis.

  • February 16, 2005 at 7:10 am

    #542520

    Hi,

    We ran sp_updatestats ... but it did not change the differeneces. What we've determined is that EM is using the value from sysindexes "rowcnt" column which is a different number than count(*) ... could this be due to row pointers vs actual data rows re: clustered vs non-clustered? (There are no primary or Unique keys on the table ... 13 columns have been indexed).

    Many thanks. Jeff

  • February 16, 2005 at 9:40 am

    #542579

    the EXISTS Clause NEVER looks at the field List

     


    * Noel

  • February 16, 2005 at 9:45 am

    #542582

    Do a dbcc updateusage and that will update the count in enterprise manager.

  • February 16, 2005 at 3:17 pm

    #542653

    I cannot prove that count(1) is quicker than count(*) - I was told this by PSS. But my reasoning is that if 1 enumerates quicker than * by a millisecond - it is still quicker, you won't notice any difference and teh query analyzer will still create the same plan as the enumeration still needs to take place. 

  • February 17, 2005 at 1:52 am

    #542725

    Okay, PSS...

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 17, 2005 at 1:56 am

    #542727

    ...oh, and by the way. EXISTS() performs a a boolean operation. I think that's one of the few occasions where using a SELECT * is valid, to leave it up to SQL Server to find the most effective way to perform the test. Methinks that a bit different than performing a COUNT().

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply