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.

    Code:
    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
    DECLARE @TblQue TABLE
    (
      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)
    BEGIN
    -- 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)
    BEGIN
        -- 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)
      BEGIN
    -- 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 )
        VALUES(@QueID,@RawIDs,@AgentIds,@SupervisorIDs)
    -- 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
      END
    -- drop the temp table #TblRawQueue
    DROP TABLE #TblRawQueue
    SET @qFlag = @qFlag + 1
    END
    END    
    Thanks in Advance
  2. sqlderby Member

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

    is any want to help here?

Share This Page