SQL Server Performance

Tuning

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Reddy, Jul 30, 2008.

  1. Reddy New Member

    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
  2. MichaelB Member

    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.
  3. venkatesanj@hcl.in New Member

    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

Share This Page