I'm working on a huge project, much of which is based around stored procedures.A particular aspect is the reporting process, and we have a set of procedures that return data for specific groups. The change I have made is to add another field to the query, for each of the stored procedures, but as soon as I do this, the time it takes goes from milliseconds to 10 seconds plus. By removing everything I added, and slowly adding it all back, I have come to the conclustion that it's to do with the columns selected that are then used in the UNPIVOT command. I have included the final block of code below, only the bold lines are those I have added, but the odd thing is that I can remove any of the fields from the intial select, and it returns back to runnign quickly, so it does not appear to be my new code.I have not included all the code, and I appreciate some of the code may not be brilliant, but changing one will mean changing 15 others, and also lots of re-testing so not an option at this time.Any ideas on what/why there is this sudden run-time lag?Thanks in advanceMartinSELECT 'Single figures' as SetOfMemberGroup, MemberID, BenefitCategoryID, BenCat, BenefitDefinitionID, ISNULL(Tranche, -100) as Tranche, GenusCode, FundingMethodCode, Gender, MemberGroupID, ReportingGroupID, AssumptionGroupID, GroupDefinitionGroupID, SetOfMember, ValueOfMemberFROM ( SELECT -- Grouping NULL as MemberID, CASE WHEN @GroupBenefitCategoryID = 1 THEN LRBC.ID ELSE NULL END as BenefitCategoryID, CASE WHEN @GroupBenefitCategoryID = 1 THEN LRBC.Name ELSE NULL END as BenCat, CASE WHEN @GroupBenefitDefinitionID = 1 THEN MER.BenefitDefinitionID ELSE NULL END as BenefitDefinitionID, CASE WHEN @GroupTrancheIndex = 1 THEN MERT.TrancheIndex ELSE NULL END as Tranche, NULL as GenusCode, CASE WHEN @GroupFundingMethodCode = 1 THEN SD.FundingMethodCode ELSE NULL END as FundingMethodCode, CASE WHEN @GroupGender = 1 THEN LRM.Gender ELSE NULL END as Gender, CASE WHEN @GroupMemberGroupID = 1 THEN LRM.MemberGroupID ELSE NULL END as MemberGroupID, CASE WHEN @GroupReportingGroupID = 1 THEN LRM.ReportingGroupID ELSE NULL END as ReportingGroupID, CASE WHEN @GroupAssumptionGroupID = 1 THEN LRMG.AssumptionGroupID ELSE NULL END as AssumptionGroupID, CASE WHEN @GroupDefinition = 1 THEN DG.GroupDefinitionID ELSE NULL END as GroupDefinitionGroupID, CAST(COUNT(DISTINCT LRM.ID) as Float) as [Number of members], --SUM(SDA.PastService) as [Total past service (years)], SUM(CASE WHEN MER.GenusCode = @GenusCode OR (@GenusCode = 'AFSR' AND MER.GenusCode = 'RAFS') THEN SDA.PastService ELSE 0.0 END) as [Total past service (years)], SUM(SD.MemberAmountPre88GMP) as [Pre 88 GMP], SUM(SD.MemberAmountPost88GMP) as [Post 88 GMP], SUM(SD.MemberAmount) as [Total member amount], SUM(SD.SpouseAmount) as [Total spouse amount], ISNULL(SUM(SRA.MemberAgePension + SRA.MemberAgeSupplement),0.0) + ISNULL(SUM(SRAL.MemberAgeLumpSum),0.0) as [MAge retirement], ISNULL(SUM(SRA.MemberIllHealthPension + SRA.MemberIllHealthSupplement),0.0) + ISNULL(SUM(SRAL.MemberIllHealthLumpSum),0.0) as [MIll health retirement], ISNULL(SUM(SRA.MemberWithdrawalPension + SRA.MemberWithdrawalSupplement),0.0) + ISNULL(SUM(SRAL.MemberWithdrawalLumpSum),0.0) as [MWithdrawal], ISNULL(SUM(SR.TotalMember),0.0) as [Total member liability], ISNULL(SUM(SRA.SpouseAgePension),0.0) as [SAge retirement], ISNULL(SUM(SRA.SpouseIllHealthPension),0.0) as [SIll health retirement], ISNULL(SUM(SRA.SpouseWithdrawalPension),0.0) as [SWithdrawal], ISNULL(SUM(SRA.TotalDeathInService),0.0) as [SDeath in service], ISNULL(SUM(SR.TotalSpouse),0.0) as [Total spouse liability], ISNULL(SUM(SR.Total),0.0) as [Total combined liability], ISNULL(SUM(SR.ValueMemberContribution),0.0) as [Value of member contributions], ISNULL(SUM(SR.ValueOnePercentGrossSalary),0.0) as [1% Gross salary], ISNULL(SUM(SR.ValueOnePercentNetSalary),0.0) as [1% Net salary], ISNULL(SUM(SRA.MemberProspectiveIllHealthPension + SRA.SpouseProspectiveIllHealthPension + SRA.MemberProspectiveIllHealthSupplement),0.0) as [FIll-health], ISNULL(SUM(SRA.SpouseProspectiveDeathInServicePension),0.0) as [FDeath in service], --Note: all Future fields for next 6. ISNULL(SUM(SRA.SpouseProspectiveDeathInServiceLumpSum),0.0) as [FLump sum death in service], ISNULL(SUM(ISNULL(SRA.TotalProspective,0.0) + ISNULL(SRC.TotalProspective,0.0) + ISNULL(SRAL.TotalProspective,0.0)),0.0) as [FTotalAddition], ISNULL(SUM(SR.Total),0.0) as [FTotal combined liability], ISNULL(SUM(SRA.TotalGMP),0.0) as [Total GMP Liability] FROM LiabilityRunBenefitCategory LRBC JOIN LiabilityRunMemberGroup LRMG ON LRMG.BenefitCategoryID = LRBC.ID JOIN LiabilityRunMember LRM ON LRM.MemberGroupID = LRMG.ID JOIN LiabilityRunMemberEngineRun MER ON MER.MemberID = LRM.ID AND (MER.GenusCode = @GenusCode OR (@GenusCode = 'AFSR' AND MER.GenusCode IN ('RAFS','RGBU','RREP','RRSL','RRSP'))) LEFT JOIN LiabilityRunMemberDatasetGroup MDG ON MDG.MemberID = LRM.ID AND @GroupDefinition = 1 LEFT JOIN LiabilityRunDatasetGroup DG ON DG.ID = MDG.DatasetGroupID AND @GroupDefinition = 1 AND DG.GroupDefinitionID = @GroupDefinitionID LEFT JOIN LiabilityRunMemberEngineRunTranche MERT ON @GroupTrancheIndex = 1 AND MER.ID = MERT.MemberEngineRunID JOIN LiabilityRunSummaryDataCommon SD ON SD.SummaryID = CASE WHEN @GroupTrancheIndex = 1 THEN MERT.SummaryID ELSE MER.SummaryID END LEFT JOIN LiabilityRunSummaryDataActive SDA ON SDA.SummaryID = SD.SummaryID AND SDA.FundingMethodCode = SD.FundingMethodCode JOIN LiabilityRunSummaryResultsCommon SR ON SR.SummaryID = SD.SummaryID AND SR.FundingMethodCode = SD.FundingMethodCode LEFT JOIN LiabilityRunSummaryResultsAfs SRA ON SRA.SummaryID = SD.SummaryID AND SRA.FundingMethodCode = SD.FundingMethodCode LEFT JOIN LiabilityRunSummaryResultsAls SRAL ON SRAL.SummaryID = SD.SummaryID AND SRAL.FundingMethodCode = SD.FundingMethodCode LEFT JOIN LiabilityRunSummaryResultsCa SRC ON SRC.SummaryID = SD.SummaryID AND SRC.FundingMethodCode = SD.FundingMethodCode WHERE LRM.LiabilityRunID = @LiabilityRunID AND LRMG.LiabilityRunID = @LiabilityRunID -- performance AND (@FundingMethodCode = '' OR SD.FundingMethodCode = @FundingMethodCode) GROUP BY CASE WHEN @GroupBenefitCategoryID = 1 THEN LRBC.ID ELSE NULL END, CASE WHEN @GroupBenefitCategoryID = 1 THEN LRBC.Name ELSE NULL END, CASE WHEN @GroupBenefitDefinitionID = 1 THEN MER.BenefitDefinitionID ELSE NULL END, CASE WHEN @GroupTrancheIndex = 1 THEN MERT.TrancheIndex ELSE NULL END, CASE WHEN @GroupFundingMethodCode = 1 THEN SD.FundingMethodCode ELSE NULL END, CASE WHEN @GroupGender = 1 THEN LRM.Gender ELSE NULL END, CASE WHEN @GroupMemberGroupID = 1 THEN LRM.MemberGroupID ELSE NULL END, CASE WHEN @GroupReportingGroupID = 1 THEN LRM.ReportingGroupID ELSE NULL END, CASE WHEN @GroupAssumptionGroupID = 1 THEN LRMG.AssumptionGroupID ELSE NULL END, CASE WHEN @GroupDefinition = 1 THEN DG.GroupDefinitionID ELSE NULL END ) as R UNPIVOT (ValueOfMember FOR SetOfMember IN ( [1% Gross salary],[1% Net salary],[FDeath in service],[FIll-health],[FLump sum death in service], [FTotal combined liability],[FTotalAddition],[MAge retirement],[MIll health retirement], [MWithdrawal],[Number of members],[Post 88 GMP],[Pre 88 GMP],[SAge retirement],[SDeath in service], [SIll health retirement],[SWithdrawal],[Total combined liability],[Total GMP Liability], [Total member amount],[Total member liability],[Total past service (years)],[Total spouse amount], [Total spouse liability],[Value of member contributions] )) as V
Issue with selection for UNPIVOT
Posted - 2010-09-27 : 07:00:03