How could i tune this script without using cursors.DECLARE @AuditItemID int,@AuditItemName varchar(100),@IsGroupHeader bit,@GroupHeaderHasItems bit,@ZoneID int,@Score float,@InspectionItemId int,@Comment varchar(200),@ScoreTypeId int,@PreviousScore float SELECT@AuditItemID as AuditItemID,@AuditItemName as AuditItemName,@IsGroupHeader as IsGroupHeader,@GroupHeaderHasItems as GroupHeaderHasItems,@ZoneID as ZoneID,@Score as Score,@InspectionItemId as InspectionItemId,@Comment as Comment,@ScoreTypeId AS ScoreTypeId,@PreviousScore AS PreviousScoreINTO#tmpDELETE FROM #tmp--Get the audit date for this auditDECLARE @AuditDate datetime,@FloorId int,@ProgramInstanceId int SELECT @AuditDate = AuditDate, @FloorId = FloorId, @ProgramInstanceId = ProgramInstanceId FROM dbo.Audits WHERE AuditId = @AuditId--Get the id of the last audit to be done for this floorDECLARE @PreviousAuditId int SELECT TOP 1 @PreviousAuditId = AuditId FROM dbo.Audits WHERE (FloorId = @FloorId) AND (ProgramInstanceId = @ProgramInstanceId) AND (AuditDate <= @AuditDate) AND (AuditId <> @AuditId) ORDER BY AuditDate DESC PRINT @PreviousAuditId--Now we are going to get the previous audit information to add to the resultsSELECTdbo .Audits_InspectionItems.InspectionItemId,dbo .Audits_InspectionItems.SubInspectionItemId,dbo .Audits_ItemsScore.[Value] AS ScoreINTO#tmpPreviousAuditScoresFROMdbo .Audits_InspectionItems INNER JOINdbo .Audits_ItemsScore ON dbo.Audits_InspectionItems.ItemId = dbo.Audits_ItemsScore.AuditInspectionItemIdWHERE (dbo.Audits_InspectionItems.AuditId = @PreviousAuditId)ORDER BYdbo .Audits_InspectionItems.InspectionItemId,dbo.Audits_InspectionItems.SubInspectionItemId --We need to get the current profile and add in any that are missingDECLARE cur_InspectionGroup CURSOR FAST_FORWARD FOR SELECT DISTINCTdbo .InspectionItems.ItemId,dbo .InspectionItems.InspectionItemName,dbo .InspectionItems.ZoneInstanceId,dbo .InspectionItems.ScoreTypeIdFROMdbo .Audits INNER JOINdbo .InspectionItems ON dbo.Audits.FloorId = dbo.InspectionItems.FloorIdWHERE (dbo.Audits.AuditId = @AuditId) AND (dbo.InspectionItems.Active = 1)UNION SELECTdbo .InspectionItems.ItemId,dbo .InspectionItems.InspectionItemName,dbo .InspectionItems.ZoneInstanceId,dbo .InspectionItems.ScoreTypeIdFROMdbo .Audits_InspectionItems INNER JOINdbo .InspectionItems ON dbo.Audits_InspectionItems.InspectionItemId = dbo.InspectionItems.ItemIdWHERE --(ISNULL(dbo.Audits_InspectionItems.SubInspectionItemId,0) = 0) AND (dbo.Audits_InspectionItems.AuditId = @AuditId)ORDER BYdbo .InspectionItems.InspectionItemNameOPEN cur_InspectionGroupDECLARE @GroupId int,@GroupName varchar(100),@ZoneInstanceId int,@ScoreType int FETCH cur_InspectionGroup INTO @GroupId, @GroupName, @ZoneInstanceId, @ScoreTypeWHILE @@FETCH_STATUS = 0BEGIN DECLARE @GroupScore float,@InspItemId int,@InspectedZoneId int,@ItemComment varchar(200),@PrevScore float SET @GroupScore = nullSET @InspItemId = 0 SET @InspectedZoneId = 0 SET @ItemComment = ''SET @PrevScore = null SELECT TOP 1 @InspItemId = dbo.Audits_InspectionItems.ItemId,@GroupScore = dbo.Audits_ItemsScore.[Value],@InspectedZoneId = dbo.Audits_ItemsScore.ZoneInstanceId FROMdbo .Audits_InspectionItems INNER JOINdbo.Audits_ItemsScore ON dbo.Audits_InspectionItems.ItemId = dbo.Audits_ItemsScore.AuditInspectionItemId WHERE (dbo.Audits_InspectionItems.AuditId = @AuditId) AND (dbo.Audits_InspectionItems.InspectionItemId = @GroupId) AND (dbo.Audits_InspectionItems.SubInspectionItemId = 0)SELECT @ItemComment = Comment FROM Audits_Comments WHERE AuditInspectionItemId = @InspItemId IF(@InspectedZoneId = 0) BEGINSET @InspectedZoneId = @ZoneInstanceId END --Get any previous scores for this record SELECT @PrevScore = Score FROM #tmpPreviousAuditScores WHERE (InspectionItemId = @GroupId) AND (SubInspectionItemId = 0) --Inject the header row INSERT INTO #tmp (AuditItemID,AuditItemName,IsGroupHeader,GroupHeaderHasItems, ZoneID, Score, InspectionItemId, Comment, ScoreTypeId, PreviousScore) VALUES(@GroupId, @GroupName,1,0,@InspectedZoneId, @GroupScore, @InspItemId, @ItemComment, @ScoreType, @PrevScore) DECLARE cur_GroupItems CURSOR FOR SELECTdbo .IX_SubInspectionTypes.ItemId,dbo.SubInspectionTypes.SubInspectionTypeName FROMdbo .IX_SubInspectionTypes INNER JOINdbo.SubInspectionTypes ON dbo.IX_SubInspectionTypes.SubInspectionTypeId = dbo.SubInspectionTypes.SubInspectionTypeId WHERE (dbo.IX_SubInspectionTypes.InspectionItemId = @GroupId) AND (dbo.Audits_InspectionItems.SubInspectionItemId > 0) UNION SELECTdbo .IX_SubInspectionTypes.ItemId,dbo.SubInspectionTypes.SubInspectionTypeName FROMdbo .Audits_InspectionItems INNER JOINdbo .IX_SubInspectionTypes ON dbo.Audits_InspectionItems.SubInspectionItemId = dbo.IX_SubInspectionTypes.ItemId INNER JOINdbo.SubInspectionTypes ON dbo.IX_SubInspectionTypes.SubInspectionTypeId = dbo.SubInspectionTypes.SubInspectionTypeId WHERE (dbo.Audits_InspectionItems.SubInspectionItemId > 0) AND (dbo.Audits_InspectionItems.InspectionItemId = @InspItemId) AND (dbo.Audits_InspectionItems.AuditId = @AuditId) ORDER BYdbo.SubInspectionTypes.SubInspectionTypeName OPEN cur_GroupItems DECLARE @ItemId int,@ItemName varchar(100),@UpdateHeader bitSET @UpdateHeader = 0 FETCH cur_GroupItems INTO @ItemId, @ItemNameWHILE @@FETCH_STATUS = 0 BEGIN IF(@UpdateHeader = 0) BEGIN UPDATE #tmp SET GroupHeaderHasItems = 1 WHERE (AuditItemID = @GroupId) AND (IsGroupHeader = 1)SET @UpdateHeader = 1 END DECLARE @ItemInspectionZoneId int SET @GroupScore = nullSET @InspItemId = 0 SET @ItemInspectionZoneId = 0 SET @ItemComment = ''SELECT TOP 1 @InspItemId = dbo.Audits_InspectionItems.ItemId,@GroupScore = dbo.Audits_ItemsScore.[Value],@ItemInspectionZoneId = dbo.Audits_ItemsScore.ZoneInstanceId FROMdbo .Audits_InspectionItems INNER JOINdbo.Audits_ItemsScore ON dbo.Audits_InspectionItems.ItemId = dbo.Audits_ItemsScore.AuditInspectionItemId WHERE (dbo.Audits_InspectionItems.AuditId = @AuditId) AND (dbo.Audits_InspectionItems.InspectionItemId = @GroupId) AND (dbo.Audits_InspectionItems.SubInspectionItemId = @ItemId)SELECT @ItemComment = Comment FROM Audits_Comments WHERE AuditInspectionItemId = @InspItemId IF(@ItemInspectionZoneId < 1) SET @ItemInspectionZoneId = ISNULL(@ZoneInstanceId,0) --Get any previous scores for this record SELECT @PrevScore = null SELECT @PrevScore = Score FROM #tmpPreviousAuditScores WHERE (InspectionItemId = @GroupId) AND (SubInspectionItemId = @ItemId) --Inject the item row INSERT INTO #tmp (AuditItemID,AuditItemName,IsGroupHeader,GroupHeaderHasItems,ZoneID, Score, InspectionItemId, Comment, ScoreTypeId, PreviousScore) VALUES(@ItemId, @ItemName,0,0,@ItemInspectionZoneId, @GroupScore, @InspItemId, @ItemComment, @ScoreType,@PrevScore)FETCH cur_GroupItems INTO @ItemId, @ItemName ENDCLOSE cur_GroupItemsDEALLOCATE cur_GroupItems FETCH cur_InspectionGroup INTO @GroupId, @GroupName, @ZoneInstanceId, @ScoreTypeEND CLOSE cur_InspectionGroupDEALLOCATE cur_InspectionGroupSELECT * FROM #tmpDROP TABLE #tmpDROP TABLE #tmpPreviousAuditScoresSET NOCOUNT OFF
I would need to see some sample data. before and after how the data looks going in and out to get a better idea of what we should write.
Cursors can be replaced with while loop.. for your case, why dont you think about the while loop with cte's.. Regards, Venkatesan Prabu .J
