SQL Server Performance

Record count is never the same

Discussion in 'SQL Server 2008 General DBA Questions' started by kreecha_pu, Aug 7, 2009.

  1. kreecha_pu New Member

    Hi there,
    I have a problem that i am unable to figure it out and need help please. I recorded a network traffic and direct the data to a SQL Server database using Stored Procedure below. However, the data get into the table with different number of records - never the same. Some time 200, 250, 270 and etc. I tried WITH (NOLOCK), WITH(UPDLOCK) or without anything. But result of records is never the same. Can anyone guide me please?
    Regards,
    KaiCREATE
    PROCEDURE [dbo].[usp_SummarizeACDQueue]
    @TempASAIDataSwitch as char(1) = 'A',
    @SiteId int = 1AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @tmpRawQueue TABLE(
    Idx int,
    ACDGroupId int,
    CallId int,
    InQDate smalldatetime
    );
    if @TempASAIDataSwitch = 'A'
    begin
    --Insert new call to the queue
    INSERT INTO @tmpRawQueue
    SELECT max(TempCalldataId) as Idx
    , DomainExtension
    , CallId
    , DateReceived
    FROM TT_ASAIDataA WITH (NOLOCK)
    WHERE DomainExtension > 0 and [Count] > 0
    and CallId not IN (SELECT CallId FROM TT_ASAIDataA WHERE OperationValue = 'Call Ended')
    GROUP BY DomainExtension , callid, DateReceived
    ORDER BY idx
    --Update Call Ended status: Out Of Queue for previous event
    UPDATE [TS_ACDCallInQueue]
    SET [InQueueFlag] = 0
    ,[DateModified] = GETDATE()
    FROM (SELECT CallId FROM TT_ASAIDataA WHERE OperationValue = 'Call Ended') tmp
    WHERE [TS_ACDCallInQueue].CallId = tmp.CallId
    end
    if @TempASAIDataSwitch = 'B'
    begin
    --Insert new call to the queue
    INSERT INTO @tmpRawQueue
    SELECT max(TempCalldataId) as Idx
    , DomainExtension
    , CallId
    , DateReceived
    FROM TT_ASAIDataB WITH (NOLOCK)
    WHERE DomainExtension > 0 and [Count] > 0
    and CallId not IN (SELECT CallId FROM TT_ASAIDataB WHERE OperationValue = 'Call Ended')
    GROUP BY DomainExtension , callid, DateReceived
    ORDER BY idx
    --Update Call Ended status: Out Of Queue for previous event
    UPDATE [TS_ACDCallInQueue]
    SET [InQueueFlag] = 0
    ,[DateModified] = GETDATE()
    FROM (SELECT CallId FROM TT_ASAIDataB WHERE OperationValue = 'Call Ended') tmp
    WHERE [TS_ACDCallInQueue].CallId = tmp.CallId
    end
    /**************************************/
    --Insert new CallId to the Queue
    INSERT INTO [TS_ACDCallInQueue]
    ([LSCallId]
    ,[ACDGroupId]
    ,[CallId]
    ,[InQueueTime]
    ,[InQueueFlag]
    ,[SiteId]
    ,[Deleted])
    SELECT Idx
    , ACDGroupId
    , CallId
    , InQDate
    , 1 -- InQFlag
    , 1 --@SiteId
    , 0 --deltete
    FROM @tmpRawQueueEND
  2. preethi Member

    First of all, It is your application and you know it better.
    There are some parts not bery clear here. How do you get the network data?
    From my understanding, you have a 3rd party tool which records the network traffic into TT_ASAIDataB and you worte the procedure to transfer into otehr tables? Am I right?
    From What I see from your query, you are not checking anything on time. Is your system alive when you run this query? I wonder whether you talk about the network traffic generated beteen executions.
    Please add more information to the post. It may help me/others to understand the problem better.
  3. kreecha_pu New Member

    you have a 3rd party tool which records the network traffic into TT_ASAIDataB and you worte the procedure to transfer into otehr tables? Am I right? -
    > Yes you are right.
    Is your system alive when you run this query?
    -> Yes it is alive
    I wonder whether you talk about the network traffic generated beteen executions.
    -> No, i am talking about that, because the other parts (stored procs) some where else work fine. But only this stored proc.
    From what now i wonder the cause might be this statement, but i am not sure:UPDATE
    [TS_ACDCallInQueue]
    SET [InQueueFlag] = 0 ,[DateModified] = GETDATE()
    FROM (SELECT CallId FROM TT_ASAIDataB WHERE OperationValue = 'Call Ended') tmp
    WHERE [TS_ACDCallInQueue].CallId = tmp.CallId
    Do you have anything to suggest? I have tried with (no lock), with (updlock) and WITH (UPDLOCK, HOLDLOCK) but no success on all.
    Thank you,
    Kai
  4. kreecha_pu New Member

    the problem i had was solved by removing the primary key. then the record count just the same everytime. can you think of the reason? I am confused.
  5. FrankKalis Moderator

    [quote user="kreecha_pu"]the problem i had was solved by removing the primary key. then the record count just the same everytime. can you think of the reason? I am confused.[/quote]
    Removing the PRIMARY KEY doesn't sound like a good, long-term solution to me. Though I have no idea what might cause this behaviour you've observed, I would continue to investigate and add the OK back as soon as possible.
  6. preethi Member

    [quote user="kreecha_pu"]
    the problem i had was solved by removing the primary key. then the record count just the same everytime. can you think of the reason? I am confused.
    [/quote]
    Looking back at the query you have posted I am just wondering whether your query could be re-written this way: Please check it too.
    UPDATE [TS_ACDCallInQueue]
    SET [InQueueFlag] = 0 ,[DateModified] = GETDATE()
    WHERE CallId IN (SELECT CallId FROM TT_ASAIDataB WHERE OperationValue = 'Call Ended')
    I too agree with Frank on PK issue. Please re-check the matter to see the cause of the problem.

Share This Page