Please help me optimize a Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Please help me optimize a Stored Procedure

Author

Topic

LaurieCox

158 Posts

Posted - 2014-05-08 : 14:22:53

I have a stored procedure that pulls data for a report. It is running very slow and I am hoping that I can get some suggestions for speeding it up (either in modifying the queries or adding indexes).

The complete stored procedure is at the end of this message.

I am not sure what information would be helpful in giving me advice. Hopefully the comments in the stored procedure are explicit enough to explain what I am trying to do. I will clarify anything if asked. I assume the table definitions would help so I have included them below. The procedure pulls data from three main tables (one is accessed thru a view) and uses temp tables as well.

Thanks,

Laurie

Here are the table definitions for the main tables:

ServiceFunding (1236622 rows)


CREATE TABLE [dbo].[ServiceFunding](
	[ServiceFundingId] [int] IDENTITY(1,1) NOT NULL,
	[SourcePKId] [int] NOT NULL,
	[MasterClientId] [int] NULL,
	[ClientId] [int] NULL,
	[ClientName] [varchar](150) NULL,
	[ServiceCodeId] [int] NULL,
	[ServiceCode] [varchar](50) NULL,
	[ServiceCodeName] [varchar](150) NULL,
	[DateOfService] [datetime] NULL,
	[SourceId] [int] NULL,
	[DatabaseName] [varchar](50) NULL,
	[InsurerID] [int] NULL,
	[InsurerName] [varchar](100) NULL,
	[PopulationId] [int] NULL,
	[PopulationName] [varchar](250) NULL,
	[ProviderProgramId] [int] NULL,
	[ProgramName] [varchar](250) NULL,
	[ProviderName] [varchar](250) NULL,
	[ProviderProgramName] [varchar](250) NULL,
	[DOB] [datetime] NULL,
	[TimeOfServiceAgeGroupId] [int] NULL,
	[TimeOfServiceAgeGroupName] [varchar](20) NULL,
	[LineCost] [money] NULL,
 CONSTRAINT [PK_ServiceFunding] PRIMARY KEY CLUSTERED 
(
	[ServiceFundingId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

EligibilityChanges(61010 rows)


CREATE TABLE [dbo].[EligibilityChanges](
	[MemberId] [int] NOT NULL,
	[CoverageId] [int] NULL,
	[ClientId] [int] NOT NULL,
	[CapitatedFundingSourceId] [tinyint] NOT NULL,
	[FundAbbreviation] [nvarchar](50) NULL,
	[FundName] [nvarchar](50) NOT NULL,
	[MemberMedicaidId] [char](12) NULL,
	[NegitiveActionDate] [date] NULL,
	[FirstDayOfMonth] [date] NULL,
	[EligibilityLast] [varchar](20) NULL,
	[EligibilityCurrent] [varchar](20) NULL,
	[EligibilityChange] [varchar](20) NULL,
	[MemberSSN] [varchar](9) NULL,
	[MemberName] [varchar](113) NULL,
	[MemberLastName] [varchar](60) NULL,
	[MemberFirstName] [varchar](25) NULL,
	[MemberMiddleName] [varchar](25) NULL,
	[MemberDOB] [date] NULL,
	[MemberDOD] [date] NULL,
	[MemberGender] [char](1) NULL
) ON [PRIMARY]

SC_F_DEMOGRAPHICS_v4_2 (via a view see below) (533394 rows)


CREATE TABLE [dbo].[SC_F_DEMOGRAPHICS_v4_2](
	[DemographicsId] [int] IDENTITY(1,1) NOT NULL,
	[DemographicAddedDate] [datetime] NOT NULL,
	[DemographicModifiedDate] [datetime] NOT NULL,
	[CheckSumValue] [int] NULL,
	[ClientId] [int] NOT NULL,
	[DemographicsDate] [date] NOT NULL,
	[Active] [char](1) NOT NULL,
	
-- 	196 more columns
(
	[DemographicsId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

View used to pull most recent demographic data (60383 rows)


CREATE view [dbo].[v_SC_F_DEMOGRAPHICS_Current_v4_2] as
select * 
  from 
   (select d.*
         , ROW_NUMBER () 
             over (partition by ClientId order by DemographicsDate desc) as Row_Num
          from dbo.SC_F_DEMOGRAPHICS_v4_2 d
         ) CurDemo
  where Row_Num = 1
    AND CurDemo.ClientRecordDeleted = 'N'

And here is the stored procedure


USE [dwh_v4test]
GO
create PROCEDURE [dbo].[usp_report_service_funding_Most_expensive_clients_v_c02]
	@ServiceDateStart date,
	@ServiceDateEnd date,
	@SourceId int = 0,
	@InsurerId int = 0,
	@ProviderProgramId int = 0,	
	@ServiceCodeId int = 0,
	@PopulationId int = 0,		
	@TimeOfServiceAgeGroupId int = 0,
	@EligibilityFilter int = 0,
	@EligibilityMonth date,
	@FundingSource varchar(20) = 'Medicaid',
	@TopCountType int = 0, --1 = Count, 2 = Percent
	@TopValue int = 0,
	@Debug int = 0
AS
-- =============================================================================
-- 
-- =============================================================================
DECLARE @TopRecords int
DECLARE @ClientCount int

-- =============================================================================
-- Filter Records based on Paramenters
-- =============================================================================
 select sf.*
      , CAST(null as DATE) as NegativeActionDate
      , CAST(null as varchar(20)) as EligibilityLast
      , CAST(null as varchar(20)) as EligibilityCurrent
      , CAST(null as varchar(20)) as EligibilityChange
   into #ServiceFundingFiltered1
   from ServiceFunding sf
  WHERE 1=1
	AND (@ServiceDateStart is null OR @ServiceDateStart <= DateOfService)
	AND (@ServiceDateEnd is null OR @ServiceDateEnd >= DateOfService)
	AND (@SourceId = 0 OR @SourceId is null OR @SourceId = SourceId)
	AND (@InsurerId = 0 OR @InsurerId is null OR @InsurerId = InsurerId)
	AND (@ProviderProgramId = 0 
	 OR  @ProviderProgramId is null 
	 OR  @ProviderProgramId = ProviderProgramId)
	AND (@ServiceCodeId = 0 
	 OR  @ServiceCodeId is null 
	 OR  @ServiceCodeId = ServiceCodeId)
	AND (@PopulationId = 0 
	 OR  @PopulationId is null 
	 OR  @PopulationId = PopulationId)
	AND (@TimeOfServiceAgeGroupId = 0 
	 OR  @TimeOfServiceAgeGroupId is null 
	 OR  @TimeOfServiceAgeGroupId = TimeOfServiceAgeGroupId)	
    and sf.ClientId is not null --fix this later

-- =============================================================================
-- Add Eligibility Data
-- =============================================================================

update sf 
  set NegativeActionDate = ec.NegitiveActionDate
    , EligibilityChange  = ec.EligibilityChange
    , EligibilityCurrent = ec.EligibilityCurrent
    , EligibilityLast    = ec.EligibilityLast
 from #ServiceFundingFiltered1 sf
         join EligibilityChanges ec on ec.ClientId = sf.ClientId
                                   and ec.FirstDayOfMonth = @EligibilityMonth
                                   and ec.FundAbbreviation = @FundingSource

-- =============================================================================
-- Filter out records based on @EligibilityFilter parameter
-- =============================================================================

Select *
  into #ServiceFundingFiltered
  from #ServiceFundingFiltered1
 where (@EligibilityFilter = 1 and NegativeActionDate is not null)
    or (@EligibilityFilter = 2 and EligibilityChange = 'Lost Eligibility')
    or (@EligibilityFilter = 3 and (NegativeActionDate is not null 
                                or  EligibilityChange = 'Lost Eligibility'))
    or (@EligibilityFilter = 4)

-- =============================================================================
-- Compute Sum of LineCost
-- =============================================================================

SELECT 
	 s.ClientId AS CandidateClientId
	,SumOfCost = SUM(s.LineCost)
INTO #ClientSumOfCost
FROM #ServiceFundingFiltered AS s
GROUP BY s.ClientId

-- =============================================================================
-- Compute @TopRecords based on @TopCountType and @TopValue
-- =============================================================================
 
IF @TopCountType = 2
	BEGIN
	    select @ClientCount = (select COUNT(*)  from #ClientSumOfCost sff)
	END

SET @TopRecords = CASE
	WHEN @TopCountType = 1 THEN @TopValue
	WHEN @TopCountType = 2 THEN @TopValue * @ClientCount / 100
END

-- =============================================================================
-- Get the ClientsToReport based on @TopRecords and add in Demographic Data
-- =============================================================================

select x.*
     , d.PrimaryClinicianName
     , d.PrimaryProgramName 
  INTO #ClientsToReport
  from (SELECT CandidateClientId AS ClientId
	         , SumOfCost
	         , ROW_NUMBER() over (order by SumOfCost desc) RowNumber
          FROM #ClientSumOfCost s) x
  join dwh_v2.dbo.v_SC_F_DEMOGRAPHICS_Current_v4_2 d on x.ClientId = d.ClientId
 where x.RowNumber <= @TopRecords

-- =============================================================================
-- Return result set
-- =============================================================================

SELECT 
	 s.*
	,t.SumOfCost
	,t.PrimaryClinicianName
	,t.PrimaryProgramName
	,t.RowNumber
FROM #ClientsToReport AS t
JOIN #ServiceFundingFiltered AS s ON s.ClientId = t.ClientId

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-08 : 14:47:11

Try putting indices on your temp table columns that are in the JOIN ON clauses or the WHERE/HAVING predicates. Also, make sure that the non-temp tables they are joining with have indices on those columns.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-08 : 14:48:20

Not enough information to really help you here.

I noticed that "Filter Records based on Paramenters" query looks like a catch-all-query. So, that might be a candidate to convert to Dynamic SQL. The "Filter out records based on @EligibilityFilter parameter" uses OR comparisons which may be hampering performance. Depending on the weight of that query, you might try separate queries to avoid scanning the table, but it may not help here since your temp table doesn't have any indexes anyway.

- Which query is the biggest/slowest?
- What indexes exist on the tables?
- Perhaps indexing the temp tables will help?

LaurieCox

158 Posts

Posted - 2014-05-08 : 15:56:33

Thanks for the replies. I will add some indexes to the ServiceFunding and EligibilityChanges tables (they currently have none) and the temp tables. I will also see if turning the first query into a dynamic query will help.

But one place that I really think I have a big bottle neck is when I pull data from my demographics table. This is a huge denormalized history table. It has 203 columns with a new record created for each clientId every month.

So the table looks like this


CREATE TABLE [dbo].[SC_F_DEMOGRAPHICS_v4_2](
	[DemographicsId] [int] IDENTITY(1,1) NOT NULL,
	[DemographicAddedDate] [datetime] NOT NULL,
	[DemographicModifiedDate] [datetime] NOT NULL,
	[CheckSumValue] [int] NULL,
	[ClientId] [int] NOT NULL,
	[DemographicsDate] [date] NOT NULL,

… a bunch more columns (see end of message for complete definition)

With DemographicsId being the primary key. It also has a natural key made from the ClientId and DemographicsDate columns (I have a unique index on these two columns). DemographicsDate is always the last day of a given month.

So to pull the most current demographics I wrote this view:


CREATE view [dbo].[v_SC_F_DEMOGRAPHICS_Current_v4_2] as
select * 
  from 
   (select d.*
         , ROW_NUMBER () 
             over (partition by ClientId order by DemographicsDate desc) as Row_Num
      from dbo.SC_F_DEMOGRAPHICS_v4_2 d
    )CurDemo
  where Row_Num = 1
    AND CurDemo.ClientRecordDeleted = 'N'

Doing a select * from this view can take somewhere around 40 seconds. I added an index to the ClientId on the SC_F_DEMOGRAPHICS_v4_2 table but that did not seem to improve things. Is there anything else I can do?

Thanks again,

Laurie

Full definition of Demographics table:


CREATE TABLE [dbo].[SC_F_DEMOGRAPHICS_v4_2](
	[DemographicsId] [int] IDENTITY(1,1) NOT NULL,
	[DemographicAddedDate] [datetime] NOT NULL,
	[DemographicModifiedDate] [datetime] NOT NULL,
	[CheckSumValue] [int] NULL,
	[ClientId] [int] NOT NULL,
	[DemographicsDate] [date] NOT NULL,
	[Active] [char](1) NOT NULL,
	[ClientRecordDeleted] [char](1) NOT NULL,
	[ClientDeletedBy] [varchar](30) NULL,
	[ClientDeletedDate] [datetime] NULL,
	[LastUpdatedClient] [datetime] NOT NULL,
	[LastUpdatedAddress] [datetime] NULL,
	[LastUpdatedTimeliness] [datetime] NULL,
	[LastUpdatedRace] [datetime] NULL,
	[LastUpdatedCustomFieldsData] [datetime] NULL,
	[LastUpdatedCustomStateReporting] [datetime] NULL,
	[LastUpdatedClientEpisodes] [datetime] NULL,
	[CurrentEpisodeNumber] [int] NULL,
	[EpisodeRegistrationDate] [datetime] NULL,
	[EpisodeDischargeDate] [datetime] NULL,
	[InformationComplete] [char](1) NULL,
	[MasterClientId] [int] NULL,
	[LastName] [varchar](50) NULL,
	[FirstName] [varchar](30) NULL,
	[MiddleName] [varchar](30) NULL,
	[ClientName] [varchar](150) NULL,
	[SSN] [varchar](25) NULL,
	[Sex] [char](1) NULL,
	[City] [varchar](50) NULL,
	[State] [char](2) NULL,
	[Address] [varchar](150) NULL,
	[Zip] [varchar](25) NULL,
	[DisplayAddress] [varchar](210) NULL,
	[CountyOfResidence] [varchar](50) NULL,
	[CountyOfLiability] [varchar](50) NULL,
	[DOB] [datetime] NULL,
	[Age] [int] NULL,
	[AdultOrChild] [varchar](5) NOT NULL,
	[PrimaryClinicianId] [int] NULL,
	[PrimaryClinicianName] [varchar](73) NULL,
	[PrimaryClinicianProgram] [varchar](250) NULL,
	[PopulationId] [int] NULL,
	[PopulationName] [varchar](250) NULL,
	[DchDD] [char](1) NULL,
	[ServicePopulationDD] [varchar](1) NULL,
	[ServicePopulationDDManualDetermination] [varchar](1) NULL,
	[ServicePopulationDDManualOverride] [char](1) NULL,
	[DchMI] [char](1) NULL,
	[ServicePopulationMI] [varchar](1) NULL,
	[ServicePopulationMIManualDetermination] [varchar](1) NULL,
	[ServicePopulationMIManualOverride] [char](1) NULL,
	[DchSA] [int] NULL,
	[ServicePopulationSUDId] [int] NULL,
	[ServicePopulationSUDName] [varchar](250) NULL,
	[ServicePopulationSUDCode] [varchar](25) NULL,
	[ServicePopulationSUDManualDeterminationId] [int] NULL,
	[ServicePopulationSUDManualDeterminationName] [varchar](250) NULL,
	[ServicePopulationSUDManualDeterminationCode] [varchar](25) NULL,
	[ServicePopulationSUDManualOverride] [char](1) NULL,
	[PrimaryLanguageId] [int] NULL,
	[PrimaryLanguageName] [varchar](250) NULL,
	[PrimaryLanguageCode] [varchar](25) NULL,
	[RaceId1] [int] NULL,
	[RaceName1] [varchar](250) NULL,
	[RaceCode1] [varchar](25) NULL,
	[RaceId2] [int] NULL,
	[RaceName2] [varchar](250) NULL,
	[RaceCode2] [varchar](25) NULL,
	[RaceId3] [int] NULL,
	[RaceName3] [varchar](250) NULL,
	[RaceCode3] [varchar](25) NULL,
	[MaritalStatusId] [int] NULL,
	[MaritalStatusName] [varchar](250) NULL,
	[MaritalStatusCode] [varchar](25) NULL,
	[MilitaryStatusId] [int] NULL,
	[MilitaryStatusName] [varchar](250) NULL,
	[MilitaryStatusCode] [varchar](25) NULL,
	[AnnualHouseholdIncome] [money] NULL,
	[IncomeRangeId] [int] NULL,
	[IncomeRangeName] [varchar](50) NULL,
	[DoesNotSpeakEnglish] [char](1) NOT NULL,
	[HispanicOriginId] [int] NULL,
	[HispanicOriginName] [varchar](250) NULL,
	[HispanicOriginCode] [varchar](25) NULL,
	[NumberOfDependents] [int] NULL,
	[NumberInHousehold] [int] NULL,
	[EducationalStatusId] [int] NULL,
	[EducationalStatusName] [varchar](250) NULL,
	[EducationalStatusCode] [varchar](25) NULL,
	[EmploymentStatusId] [int] NULL,
	[EmploymentStatusName] [varchar](250) NULL,
	[EmploymentStatusCode] [varchar](25) NULL,
	[MinimumWageId] [int] NULL,
	[MinimumWageName] [char](3) NULL,
	[LivingArrangementId] [int] NULL,
	[LivingArrangementName] [varchar](250) NULL,
	[LivingArrangementCode] [varchar](25) NULL,
	[CorrectionStatusId] [int] NULL,
	[CorrectionStatusName] [varchar](250) NULL,
	[CorrectionStatusDch] [varchar](25) NULL,
	[ATPStartDate] [datetime] NULL,
	[ATPEndDate] [datetime] NULL,
	[ATPAmount] [money] NULL,
	[FosterCareLicense] [varchar](250) NULL,
	[FosterCareLicenseRequired] [varchar](1) NOT NULL,
	[PrimaryProgramName] [varchar](250) NULL,
	[PrimaryProgramId] [int] NULL,
	[ChildFIAAbuse] [char](1) NULL,
	[ChildFIAOther] [char](1) NULL,
	[EarlyOnProgram] [char](1) NULL,
	[WrapAround] [char](1) NULL,
	[EPSDT] [char](1) NULL,
	[ParentofYoungChild] [char](1) NULL,
	[ProgramOrPlanNotListed] [char](1) NULL,
	[AdoptionStudy] [char](1) NULL,
	[SSI] [char](1) NULL,
	[IndividualNotEnrolledOrEligibleForPlan] [char](1) NULL,
	[HealthInformationDate] [datetime] NULL,
	[AbilityToHearId] [int] NULL,
	[AbilityToHearName] [varchar](250) NULL,
	[AbilityToHearDch] [varchar](25) NULL,
	[HearingAidId] [int] NULL,
	[HearingAidName] [varchar](250) NULL,
	[HearingAidDch] [varchar](25) NULL,
	[AbilityToSeeId] [int] NULL,
	[AbilityToSeeName] [varchar](250) NULL,
	[AbilityToSeeDch] [varchar](25) NULL,
	[VisualApplianceId] [int] NULL,
	[VisualApplianceName] [varchar](250) NULL,
	[VisualApplianceDch] [varchar](25) NULL,
	[PneumoniaId] [int] NULL,
	[PneumoniaName] [varchar](250) NULL,
	[PneumoniaDch] [varchar](25) NULL,
	[AsthmaId] [int] NULL,
	[AsthmaName] [varchar](250) NULL,
	[AsthmaDch] [varchar](25) NULL,
	[UpperRespiratoryId] [int] NULL,
	[UpperRespiratoryName] [varchar](250) NULL,
	[UpperRespiratoryDch] [varchar](25) NULL,
	[GastroesophagealId] [int] NULL,
	[GastroesophagealName] [varchar](250) NULL,
	[GastroesophagealDch] [varchar](25) NULL,
	[ChronicBowelId] [int] NULL,
	[ChronicBowelName] [varchar](250) NULL,
	[ChronicBowelDch] [varchar](25) NULL,
	[SeizureDisorderId] [int] NULL,
	[SeizureDisorderName] [varchar](250) NULL,
	[SeizureDisorderDch] [varchar](25) NULL,
	[NeurologicalDiseaseId] [int] NULL,
	[NeurologicalDiseaseName] [varchar](250) NULL,
	[NeurologicalDiseaseDch] [varchar](25) NULL,
	[DiabetesId] [int] NULL,
	[DiabetesName] [varchar](250) NULL,
	[DiabetesDch] [varchar](25) NULL,
	[HypertensionId] [int] NULL,
	[HypertensionName] [varchar](250) NULL,
	[HypertensionDch] [varchar](25) NULL,
	[ObesityId] [int] NULL,
	[ObesityName] [varchar](250) NULL,
	[ObesityDch] [varchar](25) NULL,
	[DDInformationDate] [datetime] NULL,
	[DdCommunicationStyleId] [int] NULL,
	[DdCommunicationStyleName] [varchar](250) NULL,
	[DdCommunicationStyleDch] [varchar](25) NULL,
	[DdMakeSelfUnderstoodId] [int] NULL,
	[DdMakeSelfUnderstoodName] [varchar](250) NULL,
	[DdMakeSelfUnderstoodDch] [varchar](25) NULL,
	[DdSupportWithMobilityId] [int] NULL,
	[DdSupportWithMobilityName] [varchar](250) NULL,
	[DdSupportWithMobilityDch] [varchar](25) NULL,
	[DdNutritionalIntakeId] [int] NULL,
	[DdNutritionalIntakeName] [varchar](250) NULL,
	[DdNutritionalIntakeDch] [varchar](25) NULL,
	[DdSupportPersonalCareId] [int] NULL,
	[DdSupportPersonalCareName] [varchar](250) NULL,
	[DdSupportPersonalCareDch] [varchar](25) NULL,
	[DdRelationshipsId] [int] NULL,
	[DdRelationshipsName] [varchar](250) NULL,
	[DdRelationshipsDch] [varchar](25) NULL,
	[DdFamilyFriendSupportSystemId] [int] NULL,
	[DdFamilyFriendSupportSystemName] [varchar](250) NULL,
	[DdFamilyFriendSupportSystemDch] [varchar](25) NULL,
	[DdSupportForChallengingBehaviorsId] [int] NULL,
	[DdSupportForChallengingBehaviorsName] [varchar](250) NULL,
	[DdSupportForChallengingBehaviorsDch] [varchar](25) NULL,
	[DdBehaviorPlanPresentId] [int] NULL,
	[DdBehaviorPlanPresentName] [varchar](250) NULL,
	[DdBehaviorPlanPresentDch] [varchar](25) NULL,
	[DdMajorMentalIllnessId] [int] NULL,
	[DdMajorMentalIllnessName] [varchar](250) NULL,
	[DdMajorMentalIllnessDch] [varchar](25) NULL,
	[DdNumberOfAntiPsychoticMedications] [int] NULL,
	[DdNumberOfOtherPsychotropicMedications] [int] NULL,
	[ScDupeMaybe] [int] NOT NULL,
	[ScDupeMaybeNote] [varchar](100) NULL,
	[ScDupeDontUse] [int] NOT NULL,
	[ScDupeDontUseNote] [varchar](100) NULL,
	[ScDupeDoUse] [int] NOT NULL,
	[ScDupeDoUseNote] [varchar](100) NULL,
	[MedicaidId] [varchar](100) NULL,
	[MedicaidPlanEndDate] [date] NULL,
	[MedicaidIdIsValid] [int] NULL,
	[MedicaidIdIsMultiple] [int] NULL,
 CONSTRAINT [pk_SC_F_DEMOGRAPHICS_v4_2] PRIMARY KEY CLUSTERED 
(
	[DemographicsId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

LaurieCox

158 Posts

Posted - 2014-05-08 : 16:27:39

Ok so this view seems to be faster:


CREATE view [dbo].[v_SC_F_DEMOGRAPHICS_Current_v4_2Max] as
select * 
  from SC_F_DEMOGRAPHICS_v4_2
  where ClientRecordDeleted = 'N'
    and DemographicsDate = (select MAX(DemographicsDate) from SC_F_DEMOGRAPHICS_v4_2)

It just doesn't have the cool Row_Number partition stuff. But changing it didn't make a noticeable change to the report response time.

I also added these indexes:


create index ix_ServiceFunding_DateOfService on ServiceFunding (DateOfService)
create index ix_ServiceFunding_SourceId on ServiceFunding (SourceId)
create index ix_ServiceFunding_InsurerId on ServiceFunding (InsurerId)
create index ix_ServiceFunding_ProviderProgramId on ServiceFunding (ProviderProgramId)
create index ix_ServiceFunding_ServiceCodeId on ServiceFunding (ServiceCodeId)
create index ix_ServiceFunding_PopulationId on ServiceFunding (PopulationId)
create index ix_ServiceFunding_TimeOfServiceAgeGroupId on ServiceFunding (TimeOfServiceAgeGroupId)


create index ix_EligibilityChanges_ClientId on EligibilityChanges (ClientId) 
create index ix_EligibilityChanges_FirstDayOfMonth  on EligibilityChanges (FirstDayOfMonth)
create index ix_EligibilityChanges_FundAbbreviation on EligibilityChanges (FundAbbreviation)

But they didn't seem to help either. Still need to try indexes on the temp tables and turning the initial query into a dynamic query. Won't be able to try that until tomorrow.

Is there anything else I can do? Is there any more information I could supply that would be helpful in solving the problem?

Thanks in advance,

Laurie

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-08 : 16:45:24

When tuning, you should first get the best clustered indexes on the tables.
( [RANT on clustering key] That means not using identity by default, because:
There is no such thing as a default clustering key; instead, the clus key should always be carefully chosen based on that table's specific requirements. [/RANT on clustering key] )

For the Demographics table, the clus key should be either:
( ClientId, DemographicsDate DESCENDING )
/* "DESCENDING" only because it eliminates the sort in your view */
--OR--
( DemographicsDate, ClientID ).

I don't really know enough about the table to be sure which is best, but for now I'd say use the first:
( ClientId, DemographicsDate DESCENDING ).

For EligibilityChanges, maybe try:
( FirstDayOfMonth, ClientId, MemberId )

Can't tell at all about ServiceFunding.

Note that you may need to reorg the tables somewhat more frequently; then again, you may not. Fundamentally, new rows are still being inserted only at specific locations.

Again, these recommendations are based on very limited data, some refining may be required, but overall performance should be much better than what you have now.

For further details, run the two queries below for each of the tables and post the results:



--!! chg to your db name
USE [dwh_v4test]

SET DEADLOCK_PRIORITY LOW --probably irrelevent, but just in case

DECLARE @list_missing_indexes bit 
DECLARE @table_name_pattern sysname

SET @list_missing_indexes = 1 --1=list missing index(es); 0=don't.
--!! put your table name/name pattern here
SET @table_name_pattern = 'ServiceFunding'

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

--SELECT create_date FROM sys.databases WITH (NOLOCK) WHERE name = N'tempdb'

IF @list_missing_indexes = 1
BEGIN
    SELECT
        GETDATE() AS capture_date,
        DB_NAME(mid.database_id) AS Db_Name,
        OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,        
        mid.equality_columns, mid.inequality_columns, mid.included_columns,
        ca1.sql_up_days AS days_in_use,
        migs.*,
        mid.statement, mid.object_id, mid.index_handle
    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
    CROSS APPLY (
        SELECT DATEDIFF(DAY, create_date, GETDATE()) AS sql_up_days FROM sys.databases WHERE name = 'tempdb'
    ) AS ca1
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
        migs.group_handle = mig.index_group_handle
    --order by
        --DB_NAME, Table_Name, equality_columns
    WHERE
        1 = 1 
        AND mid.database_id = DB_ID()
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name, Table_Name, equality_columns, inequality_columns
END --IF

-- list index usage stats (seeks, scans, etc.)
SELECT 
    ius2.row_num, DB_NAME() AS db_name,
    i.name AS index_name,
    OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name,
    i.index_id, --ius.user_seeks + ius.user_scans AS total_reads,
    dps.row_count,
    SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,
    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,
    ca1.sql_up_days AS days_in_use,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON
    o.object_id = i.object_id
CROSS APPLY (
    SELECT DATEDIFF(DAY, create_date, GETDATE()) AS sql_up_days FROM sys.databases WHERE name = 'tempdb'
) AS ca1
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(object_id, ic.column_id)
    FOR XML PATH('')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id
WHERE
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND 
     o.name NOT LIKE 'sys%'
     )
    --AND OBJECT_NAME(i.object_id, DB_ID()) IN ('tbl1', 'tbl2', 'tbl3')
ORDER BY
    --row_count DESC,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans+user_seeks
    -- list clustered index first, if any, then other index(es)
    db_name, table_name, CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, index_name

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

LaurieCox

158 Posts

Posted - 2014-05-09 : 09:42:50

Hi Scott,

Thank you for your reply.

I have run the script you posted in both my production and test databases. I had added some indexes in the test database. I have posted the results below.

I will work on your suggestions about clustering key plus turning the first query into a dynamic query.

FYI: The ServiceFunding and EligibilityChanges tables are rebuilt every night. This means if there are any changes that can be made to the actual structure that could help, I can do so.

I may not get back to this until next week but I really do appreciate everybody's help.

Laurie

Note: I posted the results as comma delimited and did not put them in code blocks as I don't want to stretch out this post.

Results from production:

Started @ 2014-05-09 09:20:01
capture_date, Db_Name, Table_Name, equality_columns, inequality_columns, included_columns, days_in_use, group_handle, unique_compiles, user_seeks, user_scans, last_user_seek, last_user_scan, avg_total_user_cost, avg_user_impact, system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact, statement, object_id, index_handle
2014-05-09 09:20:01.337, dwh_v4, ServiceFunding, [PopulationId], NULL, [ServiceFundingId], 42, 33748, 2, 2, 0, 2014-05-09 05:40:14.300, NULL, 30.4865577298744, 98.94, 0, 0, NULL, NULL, 0, 0, [dwh_v4].[dbo].[ServiceFunding], 756913768, 33747
2014-05-09 09:20:01.337, dwh_v4, ServiceFunding, [ServiceCodeId], NULL, [ServiceFundingId], 42, 33745, 2, 2, 0, 2014-05-09 05:40:13.913, NULL, 30.5718328752737, 99.41, 0, 0, NULL, NULL, 0, 0, [dwh_v4].[dbo].[ServiceFunding], 756913768, 33744

(2 row(s) affected)

row_num, db_name, index_name, table_name, index_id, row_count, key_cols, nonkey_cols, user_seeks, user_scans, user_lookups, user_updates, last_user_seek, last_user_scan, last_user_lookup, last_user_update, fk_ref_count, filegroup_name, days_in_use, system_seeks, system_scans, system_lookups, system_updates, last_system_seek, last_system_scan, last_system_lookup, last_system_update
32, dwh_v4, PK_ServiceFunding, ServiceFunding, 1, 1236708, ServiceFundingId, NULL, 0, 2, 0, 3, NULL, 2014-05-09 05:40:14.300, NULL, 2014-05-09 05:40:14.300, NULL, PRIMARY, 42, 0, 2, 0, 0, NULL, 2014-05-09 05:40:14.150, NULL, NULL

(1 row(s) affected)

Ended @ 2014-05-09 09:20:01

-----------------------------------------------------------------------------
Results from test:

Started @ 2014-05-09 09:24:50
capture_date, Db_Name, Table_Name, equality_columns, inequality_columns, included_columns, days_in_use, group_handle, unique_compiles, user_seeks, user_scans, last_user_seek, last_user_scan, avg_total_user_cost, avg_user_impact, system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact, statement, object_id, index_handle

(0 row(s) affected)

row_num, db_name, index_name, table_name, index_id, row_count, key_cols, nonkey_cols, user_seeks, user_scans, user_lookups, user_updates, last_user_seek, last_user_scan, last_user_lookup, last_user_update, fk_ref_count, filegroup_name, days_in_use, system_seeks, system_scans, system_lookups, system_updates, last_system_seek, last_system_scan, last_system_lookup, last_system_update
4, dwh_v4test, NULL, ServiceFunding, 0, 1236622, NULL, NULL, 0, 24, 0, 0, NULL, 2014-05-08 16:17:59.463, NULL, NULL, NULL, PRIMARY, 42, 0, 16, 0, 0, NULL, 2014-05-08 16:02:14.327, NULL, NULL
4, dwh_v4test, ix_ServiceFunding_DateOfService, ServiceFunding, 11, 1236622, DateOfService, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PRIMARY, 42, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
4, dwh_v4test, ix_ServiceFunding_InsurerId, ServiceFunding, 13, 1236622, InsurerID, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PRIMARY, 42, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
4, dwh_v4test, ix_ServiceFunding_PopulationId, ServiceFunding, 16, 1236622, PopulationId, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PRIMARY, 42, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
4, dwh_v4test, ix_ServiceFunding_ProviderProgramId, ServiceFunding, 14, 1236622, ProviderProgramId, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PRIMARY, 42, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
4, dwh_v4test, ix_ServiceFunding_ServiceCodeId, ServiceFunding, 15, 1236622, ServiceCodeId, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PRIMARY, 42, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
4, dwh_v4test, ix_ServiceFunding_SourceId, ServiceFunding, 12, 1236622, SourceId, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PRIMARY, 42, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
4, dwh_v4test, ix_ServiceFunding_TimeOfServiceAgeGroupId, ServiceFunding, 17, 1236622, TimeOfServiceAgeGroupId, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PRIMARY, 42, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL

(8 row(s) affected)

Ended @ 2014-05-09 09:24:50

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-09 : 11:29:13

I don't know if I'm stating the obvious, but those two queries probably won't return the same results:

CREATE view [dbo].[v_SC_F_DEMOGRAPHICS_Current_v4_2] as
select * 
  from 
   (select d.*
         , ROW_NUMBER () 
             over (partition by ClientId order by DemographicsDate desc) as Row_Num
          from dbo.SC_F_DEMOGRAPHICS_v4_2 d
         ) CurDemo
  where Row_Num = 1
    AND CurDemo.ClientRecordDeleted = 'N'

CREATE view [dbo].[v_SC_F_DEMOGRAPHICS_Current_v4_2Max] as
select * 
  from SC_F_DEMOGRAPHICS_v4_2
  where ClientRecordDeleted = 'N'
    and DemographicsDate = (select MAX(DemographicsDate) from SC_F_DEMOGRAPHICS_v4_2)

The second one might return the same data as the first one assuming that all the latest/current rows all have the same "max date."

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-09 : 11:32:28

Thanks. That confirms that ServiceFunding should be clustered by either PopulationId and/or ServiceCodeId, but definitely not by identity (as usual).

Please run the code for the other two tables as well.

SET @table_name_pattern = 'v_SC_F_DEMOGRAPHICS_Current_v4_2Max'
--and run the code

SET @table_name_pattern = 'EligibilityChanges'
--and run the code

LaurieCox

158 Posts

Posted - 2014-05-09 : 16:18:48

Lamprey,

You are right but before I made the change to the view I double checked my demographics merge procedure and it creates a new record for every (non deleted) client at beginning of each month even if none of the demographics data has changed:


MERGE dwh_v2.dbo.SC_F_DEMOGRAPHICS_v4_2 as T
 using dwh_v2.dbo.SC_F_DEMOGRAPHICS_Stage_v4_2 as S
   on T.ClientId = S.Clientid
  and T.DemographicsDate = S.DemographicsDate
 
 when MATCHED AND T.CheckSumValue <> S.CheckSumValue THEN 
      UPDATE 
      SET T.DemographicModifiedDate = S.DemographicStageDate
        , T.CheckSumValue = S.CheckSumValue
        , T.Active = S.Active

...
when NOT MATCHED BY TARGET THEN
      INSERT 
	 	   ( DemographicAddedDate
	 	   , DemographicModifiedDate
 	 	   , CheckSumValue
...

If I wanted to find the oldest demographics for all clients I would have to do the row_number/partition thing (or find the minimum date per client) but not for the most current data.

I also ran both views and they returned the same number of rows.

Scott,

Running your script on v_SC_F_DEMOGRAPHICS_Current_v4_2Max gave me zero rows.

So I ran it on the underlying table. Results below.

Again Thanks for the all the help,

Laurie

SC_F_DEMOGRAPHICS_v4_2

Started @ 2014-05-09 15:54:45
capture_date, Db_Name, Table_Name, equality_columns, inequality_columns, included_columns, days_in_use, group_handle, unique_compiles, user_seeks, user_scans, last_user_seek, last_user_scan, avg_total_user_cost, avg_user_impact, system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact, statement, object_id, index_handle

(0 row(s) affected)

row_num, db_name, index_name, table_name, index_id, row_count, key_cols, nonkey_cols, user_seeks, user_scans, user_lookups, user_updates, last_user_seek, last_user_scan, last_user_lookup, last_user_update, fk_ref_count, filegroup_name, days_in_use, system_seeks, system_scans, system_lookups, system_updates, last_system_seek, last_system_scan, last_system_lookup, last_system_update
13, dwh_v2, pk_SC_F_DEMOGRAPHICS_v4_2, SC_F_DEMOGRAPHICS_v4_2, 1, 533405, DemographicsId, NULL, 0, 138, 3, 16, NULL, 2014-05-09 05:40:50.590, 2014-05-02 13:18:34.787, 2014-05-09 05:35:14.710, NULL, PRIMARY, 42, 0, 199, 0, 0, NULL, 2014-05-02 12:58:54.973, NULL, NULL
13, dwh_v2, ix_DEMOGRAPHICS_ClientId_DemographicsDate, SC_F_DEMOGRAPHICS_v4_2, 2, 533405, ClientId, DemographicsDate, NULL, 3, 19, 0, 16, 2014-05-02 13:18:34.787, 2014-05-09 05:35:13.673, NULL, 2014-05-09 05:35:14.710, NULL, PRIMARY, 42, 0, 3, 0, 0, NULL, 2014-05-08 15:35:56.890, NULL, NULL
13, dwh_v2, ix_SC_F_DEMOGRAPHICS_v4_2_ClientId, SC_F_DEMOGRAPHICS_v4_2, 200, 533405, ClientId, NULL, 0, 0, 0, 1, NULL, NULL, NULL, 2014-05-09 05:35:14.710, NULL, PRIMARY, 42, 0, 0, 0, 0, NULL, NULL, NULL, NULL

(3 row(s) affected)

Ended @ 2014-05-09 15:54:46
---------------------------------------------------------------------------------------------------------------------------
EligibilityChanges

Started @ 2014-05-09 15:57:10
capture_date, Db_Name, Table_Name, equality_columns, inequality_columns, included_columns, days_in_use, group_handle, unique_compiles, user_seeks, user_scans, last_user_seek, last_user_scan, avg_total_user_cost, avg_user_impact, system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact, statement, object_id, index_handle

(0 row(s) affected)

row_num, db_name, index_name, table_name, index_id, row_count, key_cols, nonkey_cols, user_seeks, user_scans, user_lookups, user_updates, last_user_seek, last_user_scan, last_user_lookup, last_user_update, fk_ref_count, filegroup_name, days_in_use, system_seeks, system_scans, system_lookups, system_updates, last_system_seek, last_system_scan, last_system_lookup, last_system_update
5, dwh_v4test, NULL, EligibilityChanges, 0, 61010, NULL, NULL, 0, 20, 0, 0, NULL, 2014-05-08 16:18:33.560, NULL, NULL, NULL, PRIMARY, 42, 0, 6, 0, 0, NULL, 2014-05-08 16:02:23.507, NULL, NULL
5, dwh_v4test, ix_EligibilityChanges_ClientId, EligibilityChanges, 5, 61010, ClientId, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PRIMARY, 42, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
5, dwh_v4test, ix_EligibilityChanges_FirstDayOfMonth, EligibilityChanges, 6, 61010, FirstDayOfMonth, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PRIMARY, 42, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
5, dwh_v4test, ix_EligibilityChanges_FundAbbreviation, EligibilityChanges, 7, 61010, FundAbbreviation, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PRIMARY, 42, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL

(4 row(s) affected)

Ended @ 2014-05-09 15:57:10

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-12 : 09:43:38

Thanks Laurie.

Those stats confirm that the existing clustering indexes are not helping processing. Try changing the clustered indexes to column(s) you (almost) always use for lookup. If you have a naturally ascending key, such a datetime, that will reduce fragmentation. But insert fragmentation is not the primary consideration for now, rather trying to get clustered keys that speed up normal processing of the tables.

LaurieCox

158 Posts

Posted - 2014-05-12 : 10:47:44

Thank you Scott for helping me on this.

I have just run into a couple of major dead lines in other projects so I won't have time to play with this until the end of this week or the beginning of next week.

But again thank you very much for all of your time.

Laurie

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-12 : 11:09:33

No problem, good luck.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources