none
Why Would Count() return differing values on an unchanging table. RRS feed

  • Question

  • In inheriting a website/database combo I have seen a weirdness with the results returned by this (and other like it) statement:

    var numberOfEmployees = (from u in db.users select u).Count();

    When the database isn't being stressed, it returns the appropriate number of records as 1800. But when its stressed, the database, that number goes between 0-1800.

    Its a SQL Server 2005 db and frankly the traffic on the website between 0-30 users per minute and shouldn't be stressing the database like this. The database has one table and is not an involved table at that with ten columns. But it seems to get locked up every so often.

    What would cause Linq to fail? I would expect either it fails and throws an exception or it returns the proper value. The page in question is a status page listing other statuses which the others will fail as well, but others work.(?)

    Thoughts
    William Wegerson (www.OmegaCoder.Com)
    Wednesday, August 5, 2009 2:53 PM
    Moderator

Answers

  • 1) Can you reproduce it in SSMS?
    2) Did you run dbcc checkdb? If not, do...
    3) Any try/catch surrounding the query above that could result in skewed results should an exception occur?
    4) Any funky stuff with datacontext instances being kept alive longer than necessary (recycled)?
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Wednesday, August 5, 2009 4:11 PM
    Answerer
  • As an aside I removed this a deconstructor on a centralized class for operations which was using datacontext and the site seems to run better

      ~userDAO()
        {
                db.Dispose();
        }
    
    // I removed that deconstructor and added a dispose instead:
    
      #region IDisposable Members
    
        public void Dispose()
        {
    
            if ( db != null )
            {
                db.Dispose();
                db = null;
            }
    
        }
    
        #endregion

    Would that matter I wonder....

    William Wegerson (www.OmegaCoder.Com)
    Wednesday, August 5, 2009 6:47 PM
    Moderator

All replies

  • 1) Can you reproduce it in SSMS?
    2) Did you run dbcc checkdb? If not, do...
    3) Any try/catch surrounding the query above that could result in skewed results should an exception occur?
    4) Any funky stuff with datacontext instances being kept alive longer than necessary (recycled)?
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Wednesday, August 5, 2009 4:11 PM
    Answerer
  • 1) No SSMS works unless the database gets locked.
    2) Asking for one now.
    3) It is within a try catch block which returns zero upon failure.
    4) Possible. I am fighting a mid-level developers first attempt at Asp.Net/Linq. Also fighting an analyst who is using VS2008 (non SP1) to attach to the database keeping records open and seems to cause the locking mentioned in 1. But I can't confirm that.



    William Wegerson (www.OmegaCoder.Com)
    Wednesday, August 5, 2009 6:41 PM
    Moderator
  • As an aside I removed this a deconstructor on a centralized class for operations which was using datacontext and the site seems to run better

      ~userDAO()
        {
                db.Dispose();
        }
    
    // I removed that deconstructor and added a dispose instead:
    
      #region IDisposable Members
    
        public void Dispose()
        {
    
            if ( db != null )
            {
                db.Dispose();
                db = null;
            }
    
        }
    
        #endregion

    Would that matter I wonder....

    William Wegerson (www.OmegaCoder.Com)
    Wednesday, August 5, 2009 6:47 PM
    Moderator
  • Since doing the Dispose...I have not seen any more problems. Thanks Kristofer for your suggestions.
    William Wegerson (www.OmegaCoder.Com)
    Friday, August 7, 2009 2:41 PM
    Moderator