Performance Tuning – SP | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Performance Tuning – SP

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
Any one help me here…
is any want to help here?
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |