SQL Server Performance

Record count errors

Discussion in 'General DBA Questions' started by pbrewer, Mar 24, 2004.

  1. pbrewer New Member

    I have a table, tbCDR, with 40 columns, one is an identity column called CDRID,another column is AdminRate, which is a decimal(16, 9) and is nullable.

    When I run the following scripts I get the results commented out by the "--".

    These numbers are bogus. If there are 19 million records in the table and 15.7 million have a value in the AdminRate column, how can 17.5 million have a null value in the AdminRate column. Help me please.

    SelectCount(CDRID)
    FromCDRArchive.dbo.tbCDR
    --19,321,170

    SelectCount(CDRID)
    FromCDRArchive.dbo.tbCDR
    WhereAdminRate Is Not Null
    --15,790,085

    SelectCount(CDRID)
    FromCDRArchive.dbo.tbCDR
    WhereAdminRate Is Null
    --17,518,988

  2. ChrisFretwell New Member

    I have no idea based on what I can see here.
    Can you try this

    select count(cdrid), count(*),
    sum(case when adminrate is null then 1 else 0 end) as totalnull,
    sum(case when adminrate is null then 0 else 1 end) as totalNotnull
    from CDRArchive.dbo.tbCDR

    the first 2 values should be the same and the last 2 should add up to the first.

    Then try

    select count(cdrid), count(*),
    sum(case when adminrate is null then 1 else 0 end) as totalnull,
    sum(case when adminrate is null then 0 else 1 end) as totalNotnull
    from CDRArchive.dbo.tbCDR
    where adminrate is null

    and the first 3 values should be the same, the last should be zero.

    I dont know if this will make a difference (it shouldnt, but I'm curious)
  3. airjrdn New Member

    I work with pbrewer...

    I ran a DBCC CheckDB on the database (excluding indexes) and it had no errors. I'm now running it again (including indexes) to see if there's an issue there.

    When that's complete, if it shows no errors, I'll run what you posted.

    In addition to his queries, I ran:

    Selectdistinct AdminRateCodeNew
    FromtbCDRArchive
    WhereAdminRateCodeNew Is Null

    And got values, not a null. Weird to say the least.
  4. ChrisFretwell New Member

    Very strange, very, very strange. Next question(probably already looked at), what are your ansi null settings on that database? In QA where you're running this?

  5. airjrdn New Member

    For:
    select count(cdrid), count(*),
    sum(case when adminrate is null then 1 else 0 end) as totalnull,
    sum(case when adminrate is null then 0 else 1 end) as totalNotnull
    from CDRArchive.dbo.tbCDR

    I got:
    totalnull totalNotnull
    ----------- ----------- ----------- ------------
    19321170 19321170 3531085 15790085


    For:
    select count(CDRRecordID), count(*),
    sum(case when AdminRateCodeNew is null then 1 else 0 end) as totalnull,
    sum(case when AdminRateCodeNew is null then 0 else 1 end) as totalNotnull
    from CDRArchive.dbo.tbCDRArchive
    where AdminRateCodeNew is null

    I got:
    totalnull totalNotnull
    ----------- ----------- ----------- ------------
    19054553 19054553 3531085 15523468
  6. airjrdn New Member

    Set ansi_defaults has a gray check
    Set ansi_null_dflt_on is checked
    Set ansi_padding is checked
    Set ansi_warnings is checked


    At the database level, we've tried ANSI Null Default both checked and unchecked.
  7. ChrisFretwell New Member

    wow, I can see your frustration,and even though this is not my data, feel it too!!!

    Okay, response from the first query, says that adminrate is being found correctly as null/not null since the 2 sums of possible values equals the total....

    The second one though...
    You selected only where the value is null, there should have been nothing in the second sum and all three other values the same. Can you run the second query, without the where clause and try the first with the where clause (still grasping straws, but at least one field is returning expected results)
  8. airjrdn New Member

    select count(CDRRecordID), count(*),
    sum(case when AdminRateCodeNew is null then 1 else 0 end) as totalnull,
    sum(case when AdminRateCodeNew is null then 0 else 1 end) as totalNotnull
    from CDRArchive.dbo.tbCDRArchive
    where AdminRateCodeNew is null

    produced:
    totalnull totalNotnull
    ----------- ----------- ----------- ------------
    19054553 19054553 3531085 15523468



    select count(CDRRecordID), count(*),
    sum(case when AdminRateCodeNew is null then 1 else 0 end) as totalnull,
    sum(case when AdminRateCodeNew is null then 0 else 1 end) as totalNotnull
    from CDRArchive.dbo.tbCDRArchive

    produced:
    totalnull totalNotnull
    ----------- ----------- ----------- ------------
    19321170 19321170 3531085 15790085
  9. airjrdn New Member

    BTW, the DBCC checkDB (including indexes) returned w/no errors.

    Just being curious, we rebooted the server also. The reboot was done prior to my first post.
  10. airjrdn New Member

    More Info:

    Selectdistinct AdminRateCodeNew -- Count(CDRRecordID)
    FromtbCDRArchive -- With(NoLock)
    WhereCoalesce(AdminRateCodeNew, -999) = -999

    Produced Null (correct)


    SelectCount(CDRRecordID)
    FromtbCDRArchive -- With(NoLock)
    WhereCoalesce(AdminRateCodeNew, -999) = -999

    Produced 3531085 (correct)



    I'm now running:

    Updatea
    Set AdminRateCodeNew = Null
    FromtbCDRArchive a
    WhereCoalesce(AdminRateCodeNew, -999) = -999

    I'll then run the initial queries again to see if that fixed the problem. I'll let you know as soon as it's done.

  11. airjrdn New Member

    It updated the correct # of records, but querying the data still gives the odd/inaccurate results.
  12. Adriaan New Member

    Guessing ... Perhaps you're trying to apply criteria on a calculated field, and maybe that won't work?
  13. airjrdn New Member

    Are you asking if AdminRateCodeNew is a calculated field? It's not, it's a normal nullable decimal field.
  14. Adriaan New Member

    I meant the AdminRate field you mentioned in the first post.<br /><br />Another idea would be to check if your identity field contains NULLs, because if you do COUNT(&lt;fieldname&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' />, only non-nulls get counted. That's different from COUNT(*), which will count all rows.<br /><br />Is the table involved in some sort of replication?
  15. airjrdn New Member

    The identity column "shouldn't" contain nulls, it's a SQL identity column. As for replication, I'll let pbrewer answer that.
  16. pbrewer New Member

    The table is not replicated, and CDRRecordID is an identity column. The seed is (1, 1) and should not have any nulls.
  17. Adriaan New Member

    I know it shouldn't contain nulls. But did you doublecheck? Sorry if this seems pointless, but unexpected results are often caused by incorrect assumptions - and I think we've already established that there has to be something here that is not what we assume it is.
  18. pbrewer New Member

    I executed “Select Count(*)
    FromCDRArchive.dbo.tbCDRArchive
    WhereCDRRecordID Is Null”, and had 0 records.

  19. ChrisFretwell New Member

    Also, in an above message in this post, at my pbrewer already compared the count(*) to count(field) values and they were the same. I asked, just to be sure.

    What I find so strange are the results from the where compared to the results from the count case...its like "is null" is behaving differently depending on where its used. What is the datatype of adminratecode and adminratecodenew?

    Next set of 'humor me' queries, since "is null" looks a bit off, lets see how "isnull" behaves....
    You need to pick a value for the field that cannot ever be there, if its an integer field, pick a negative number (-1), thats what I'll put here


    select count(CDRRecordID),
    sum(case when AdminRateCodeNew is null then 1 else 0 end) as totalnull,
    sum(case when AdminRateCodeNew is null then 0 else 1 end) as totalNotnull
    from CDRArchive.dbo.tbCDRArchive
    where isnull(AdminRateCodeNew,-1) = -1


    then for the heck of it --- what, if any records are returned from the below (should be nothing)

    select top 10 ccdrrecorid, AdminRateCodeNew
    from CDRArchive.dbo.tbCDRArchive
    where isnull(AdminRateCodeNew,-1) = -1
    and adminratecodenew is not null

    select top 10 ccdrrecorid, AdminRateCodeNew
    from CDRArchive.dbo.tbCDRArchive
    where isnull(AdminRateCodeNew,-1) <> -1
    and adminratecodenew is null

  20. Chappy New Member

    have you tried updating statistics on your tables? This shouldnt really help but I notice the system tables hold the record count and perhaps this is being used as an optimisation, but is out of date?<br /><br />hmmm well ok, its a long shot <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />
  21. airjrdn New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Chappy</i><br /><br />have you tried updating statistics on your tables? This shouldnt really help but I notice the system tables hold the record count and perhaps this is being used as an optimisation, but is out of date?<br /><br />hmmm well ok, its a long shot <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />That was pbrewer's first thought, we need to do an update stats just to see if that fixes it.
  22. pbrewer New Member

    I performed the following code and still the same problem in the new database and new table.

    Select *
    Into CDRArchiveNew.dbo.tbCDRArchiveNew
    From tbCDRArchive

    I also found that many of the columns in the table are lacking "COLLATE SQL_Latin1_General_CP1_CI_AS" when script a "Create" in QA.

  23. airjrdn New Member

    Get this

    SelectCount(CDRRecordID)
    FromCDRArchive.dbo.tbCDRArchive
    WhereAdminRateCodeNew Is Null
    andAdminRateCodeNew Is Not Null


    Result:

    -----------
    13152625

    (1 row(s) affected)


    Note that the result changes from run to run.
  24. ChrisFretwell New Member

    Can you post the create script for your table here please?

    Love that last set of results.....I know null is not a value, but sheesh....

    one last one too (since I'm here) for the heck of it

    Select Count(CDRRecordID), count(adminratecodenew)
    From CDRArchive.dbo.tbCDRArchive
    Where AdminRateCodeNew = Null
  25. airjrdn New Member

    Create script as generated via Query Analyzer (right click on table in object browser and script as create):


    Create script as generated via Query Analyzer (right click on table in object browser and script as create):

    CREATE TABLE [tbCDRArchive] (
    [CDRRecordID] [int] IDENTITY (1, 1) NOT NULL ,
    [MemberID] [smallint] NOT NULL ,
    [MemberIdentMethodID] [int] NULL ,
    [ServiceMonth] [tinyint] NULL CONSTRAINT [DF_tbCDRArchive_ServiceMonth] DEFAULT (datepart(month,getdate())),
    [ServiceYear] [smallint] NULL CONSTRAINT [DF_tbCDRArchive_ServiceYear] DEFAULT (datepart(year,getdate())),
    [CarrierID] [tinyint] NOT NULL ,
    [ProductID] [tinyint] NOT NULL ,
    [JurisdictionID] [tinyint] NOT NULL CONSTRAINT [DF_tbCDRArchive_JurisdictionID] DEFAULT (0),
    [EMIIndicator19] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [SettlementCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CategoryID] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [GroupID] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [RecordType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CDRReceiveDate] [smalldatetime] NOT NULL ,
    [CallDate] [smalldatetime] NOT NULL ,
    [CallTime] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CallDurationMinutes] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CallDurationSeconds] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CallDurationTenths] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CallDurationTotal] [decimal](18, 4) NOT NULL ,
    [BillableDurationTotal] [decimal](18, 4) NOT NULL CONSTRAINT [DF_tbCDRArchive_BillableDurationTotal] DEFAULT (0),
    [FromStateCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ToStateCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [FromNumNPA] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ToNumNPA] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [FromLataCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ToLataCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [PlatformNum] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AuthCode] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [BillPhoneNum] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CIC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [PIN] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AddedToEMIInd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [PayPhoneFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DirectAssistFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CDRStatsUpdateFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AttentionFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DedicatedFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [PeakFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CDRCostAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CostAmt1] DEFAULT (0),
    [CDRRateAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CDRRateAmt] DEFAULT (0),
    [CostMinusSurchargesAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CostMinusSurchargesAmt1] DEFAULT (0),
    [CarrierContractRateAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CarrierContractRateAmt1] DEFAULT (0),
    [CarrierContractCostAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CarrierContractCostAmt] DEFAULT (0),
    [CalculatedRateAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CalculatedRateAmt1] DEFAULT (0),
    [CalculatedCostAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CalculatedCostAmt1] DEFAULT (0),
    [PayphoneSurChargeAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_PayphoneSurChargeAmt1] DEFAULT (0),
    [AdminFeeAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_AdminFeeAmt] DEFAULT (0),
    [CarrierExceptionAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CarrierExceptionAmt] DEFAULT (0),
    [GVNWExceptionAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_GVNWExceptionAmt] DEFAULT (0),
    [ANPIExceptionAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_ANPIExceptionAmt] DEFAULT (0),
    [InvoiceRateAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_InvoiceRateAmt] DEFAULT (0),
    [InvoiceAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_InvoiceAmt1] DEFAULT (0),
    [EMIRecord] [char] (210) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CDRModules] [varchar] (236) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [SourceFileName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [EMIFileName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [StaffID] [smallint] NOT NULL ,
    [UpdateDate] [smalldatetime] NOT NULL CONSTRAINT [DF_tbCDRArchive_UpdateDate] DEFAULT (getdate()),
    [Identified] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SourceRecord] [varchar] (700) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ProductTypeCodeID] [int] NULL ,
    [DACodeID] [int] NULL ,
    [OrigNum] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TermNum] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CostMinusSurchargesRateAmt] [decimal](25, 9) NULL ,
    [CarrierContractRateCodeID] [int] NULL ,
    [ContractRateCodeID1] [int] NULL ,
    [ContractRateAmt1] [decimal](16, 9) NULL ,
    [ContractCostAmt1] [decimal](25, 9) NULL ,
    [ContractRateCodeID2] [int] NULL ,
    [ContractRateAmt2] [decimal](16, 9) NULL ,
    [ContractCostAmt2] [decimal](25, 9) NULL ,
    [ContractRateCodeID3] [int] NULL ,
    [ContractRateAmt3] [decimal](16, 9) NULL ,
    [ContractCostAmt3] [decimal](25, 9) NULL ,
    [ContractRateCodeID4] [int] NULL ,
    [ContractRateAmt4] [decimal](16, 9) NULL ,
    [ContractCostAmt4] [decimal](25, 9) NULL ,
    [ContractRateCodeID5] [int] NULL ,
    [ContractRateAmt5] [decimal](16, 9) NULL ,
    [ContractCostAmt5] [decimal](25, 9) NULL ,
    [UsedRateCodeID] [int] NULL ,
    [UsedRateAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_UsedRateAmt] DEFAULT (0),
    [UsedCostAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_UsedCostAmt] DEFAULT (0),
    [AdminRateCodeID] [int] NULL ,
    [AdminRateCodeNewID] [int] NULL ,
    [AdminRateCodeNew] [decimal](16, 9) NULL ,
    [OrigLRN] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TermLRN] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO


  26. airjrdn New Member

    Running:

    Select Count(CDRRecordID), count(adminratecodenew)
    From CDRArchive.dbo.tbCDRArchive
    Where AdminRateCodeNew = Null


    consistently produces:


    ----------- -----------
    0 0

    (1 row(s) affected)

  27. ChrisFretwell New Member

    Well at least = null is behaving correctly. I'll see if anything leaps out from the create table.
  28. airjrdn New Member

    Not exactly, = null isn't returning (and won't to my knowledge) records where that field is null.

    For instance, the following query produces 0 as a result:


    SelectCount(CDRRecordID)
    FromCDRArchive.dbo.tbCDRArchive
    WhereAdminRateCodeNew = Null


    But this query actually produces 100 rows of valid data (where AdminRateCodeNew is null):

    Selecttop 100 *
    FromtbCDRArchive -- With(NoLock)
    WhereCoalesce(AdminRateCodeNew, -999) = -999

  29. ChrisFretwell New Member

    I know, but I was jsut wondering if there had been something odd setup on your server/db that was making null behave as something other htan null. I expected no results since nothing can = null, but had to ask based on the other mess that is going on.

    oh, and then since you brought up coalesce, do you get the same results from each of these?
    select count(*) from tbcdrarchive where Coalesce(AdminRateCodeNew, -999) = -999
    select count(*) from tbcdrarchive where isnull(AdminRateCodeNew, -999) = -999
    select count(*) from tbcdrarchive where AdminRateCodeNew is null

  30. pbrewer New Member

    Here are the results from the queries. The table count has changed due to daily processing that we cannot defer to another table.

    select count(*) from tbcdrarchive where Coalesce(AdminRateCodeNew, -999) = -999
    9231932

    select count(*) from tbcdrarchive where isnull(AdminRateCodeNew, -999) = -999
    9231932

    select count(*) from tbcdrarchive where AdminRateCodeNew is null
    24064826 -- Should be somewhere around 10,000,000.

    select count(*) from tbcdrarchive
    25033915
  31. ChrisFretwell New Member

    Now is the time to beat your head against the wall. Wow. The first three should produce the exact same results, but only the first 2 did! I'm actually stuck now, even with wild suggestions.

    From what I knew, coalesce, isnull and is null should all be check for the same concept of null, but is null seems to be off doing its own thing.

    Unless someone can come up with a reason, use isnull and/or coalesce with an impossible value rather than is null to keep your application going.
    Then if you are bored and have a spare machine around, build a clean version of your server (service packs etc) then restore a database and see if behaves the same way. Sorry, wish I could help more.
  32. airjrdn New Member

    pbrewer actually created another database on the same server, and inserted the data into it. The new database acts as it should, but I'll tell you, when you start seeing results like that, you start to lose confidence in SQL Server.
  33. ChrisFretwell New Member

    If you still have your old database around, have you run full diagnostics on it? There must be some corruption somewhere? Wish I could get my hands on it to play, but oh well. At least you have something working!
  34. pbrewer New Member

    I think there must be something with the database because a new table in the same database produces the exact problems. When I created the new database and table, though on the same server, it is working well.

Share This Page