SQL Server Performance

Performance Tuning - SP

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by sqlderby, Jul 16, 2012.

  1. sqlderby Member

    Hi All

    I need help on following SP code to tune up mainly where "CREATE TABLE #TblRawQueue" creating and DROPING in side While loop.

    DECLARE @rFlag int
    DECLARE @qFlag int
    DECLARE @qcount int
    DECLARE @RawID int
    DECLARE @QueID int
    DECLARE @rdCount int
    DECLARE @instanceID int
    DECLARE @RawIDs int
    DECLARE @AgentIds int
    Declare @SupervisorIDs int
    Declare @instanceConfigId int
      RowID int IDENTITY (1, 1) NOT NULL,
      QueueIDs int,
      InstanceIDS int,
      instanceConfigId int
    DECLARE @TblRawdata TABLE(RowID int IDENTITY (1, 1) NOT NULL,RawDataIDs int,InstanceIDs int ,AgentId int,SupervisorID int,instanceConfigId int)
    EXEC dbo.usp_qm_UpdateHRISG2W
    -- get queueid and Instanceids from Tbl_Queues where IsSubQueue =0 and hasMonitoringData = 0 and CalibrationConfigID = 0
    INSERT @TblQue(QueueIDs,InstanceIDS,instanceConfigId)
    SELECT QueueID,InstanceID,InstanceConfigID FROM Tbl_Queues WHERE CalibrationConfigID = 0 and IsSubQueue =0 and hasMonitoringData = 0
    SELECT * FROM @TblQue
    --SELECT @qcount = count(*) FROM @TblQue
    SELECT @qcount = count(RowID) FROM @TblQue
    -- get row count
    IF( @qcount!=0)
    -- get RawDataID,InstanceID FROM  Tbl_FilteredRawData_Voice where InstanceID in @TblQue and  ImportStatus = 0
    INSERT INTO @TblRawdata(RawDataIDs,InstanceIDs,AgentID,SupervisorID,instanceConfigId)
    SELECT RawDataID,InstanceID,AgentID,SupervisorID,instanceConfigId FROM  Tbl_FilteredRawData_G2W
    WHERE InstanceID in (SELECT  InstanceIDS FROM @TblQue) and  ImportStatus = 0
      --select * from @TblRawdata
    SET @qFlag = 1
    WHILE (@qFlag <= @qcount)
        -- get instanceid from @TblQue one by one
      SELECT @instanceID = InstanceIDS, @instanceConfigId = InstanceConfigID FROM @TblQue WHERE RowID = @qFlag
        -- get queueid on basis of above instanceid from @TblQue one by one
      SELECT @QueID = QueueIDs FROM @TblQue WHERE InstanceIDS = @instanceID and RowID = @qFlag
    -- create temp table #TblRawQueue
        CREATE TABLE #TblRawQueue(RowID int IDENTITY (1, 1) NOT NULL,RawIDs int,AgentId int,SupervisorID int)
        INSERT INTO #TblRawQueue (RawIDs,AgentId,SupervisorID)
    -- get rawdataids on basis of above instanceids
        Select RawDataIDs,AgentId,SupervisorID from @TblRawdata WHERE InstanceIDs = @instanceID and InstanceConfigID = @instanceConfigId
    -- get row count
    -- select * from #TblRawQueue
        SELECT @rdCount = count(RowID) FROM #TblRawQueue
        SET @rFlag = 1
    --SELECT * FROM  #TblRawQueue
    WHILE (@rFlag <= @rdCount)
    -- get rawdataids  from #TblRawQueue one by one insert into Tbl_MonitoringData with queueid
        SELECT @RawIDs = RawIDs,@AgentIDs= AgentId,@SupervisorIDs=SupervisorID FROM #TblRawQueue WHERE RowID = @rFlag
        INSERT INTO Tbl_MonitoringData(QueueID,RawDataID,AgentId,ToBeMoniteredBy )
    -- update  Tbl_Queues  set hasMonitoringData = 1 on basis of queueid
    -- (this can written oustside this loop)
        UPDATE Tbl_Queues SET hasMonitoringData = 1 where QueueID = @QueID
    -- update  Tbl_FilteredRawData_G2W set ImportStatus = 1 on basis of rawdataid
        UPDATE Tbl_FilteredRawData_G2W SET ImportStatus = 1 WHERE RawDataID = @RawIDs
        SET @rFlag = @rFlag + 1
    -- drop the temp table #TblRawQueue
    DROP TABLE #TblRawQueue
    SET @qFlag = @qFlag + 1
    Thanks in Advance
  2. sqlderby Member

    Any one help me here...
  3. sqlderby Member

    is any want to help here?

Share This Page