View takes a long time to populate!....!

[Esakkiappan Barathan]

Hai All,

Recently I have developed a user Stored Procedure code for my
Project. The structures of the tables used in this case are

1. Candidate Master Table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CandidateMaster](
[CandidateID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[FathersName] [nvarchar](50) NOT NULL,
[ApplicationReceiptNumber] [nvarchar](50) NOT NULL,
[ApplicationReceiptDate] [datetime] NOT NULL,
[DOB] [datetime] NOT NULL,
[Gender] [nvarchar](1) NOT NULL,
[Category] [nvarchar](3) NOT NULL,
[PhysicallyHandicapped] [bit] NOT NULL,
[SanctionedRejectedFlag] [nvarchar](1) NULL,
CONSTRAINT [PK_CandidateMaster] PRIMARY KEY CLUSTERED
(
[CandidateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



2 SanctionDetails Table

CREATE TABLE [dbo].[SanctionDetails](
[CandidateID] [int] NOT NULL,
[SanctionLetterNo] [nvarchar](25) NOT NULL,
[SanctionLetterDate] [datetime] NOT NULL,
[Amount] [money] NOT NULL,
[SanctionedFrom] [datetime] NOT NULL,
[SanctionedUpto] [datetime] NOT NULL,
[CreatedOn] [datetime] NOT NULL CONSTRAINT
[DF_SanctionDetails_CreatedOn] DEFAULT (getdate()),
[CreatedBy] [nvarchar](25) NOT NULL,
[ModifiedOn] [datetime] NULL,
[ModifiedBy] [nvarchar](25) NULL,
[AuditedOn] [datetime] NULL,
[AuditedBy] [nvarchar](25) NULL,
[StartActiveDate] [datetime] NOT NULL CONSTRAINT
[DF_SanctionDetails_StartActiveDate] DEFAULT (getdate()),
[EndActiveDate] [datetime] NULL,
CONSTRAINT [PK_SanctionDetails] PRIMARY KEY CLUSTERED
(
[CandidateID] ASC,
[SanctionLetterNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

3.CREATE TABLE [dbo].[CandidateStatusAsOnADate](
[CandidateID] [int] NOT NULL,
[StatusAsOnDate] [char](1) NOT NULL,
[StatusDate] [datetime] NOT NULL DEFAULT (getdate()),
[CreatedBy] [nvarchar](25) NOT NULL,
[CreatedOn] [datetime] NOT NULL DEFAULT (getdate()),
[ModifiedBy] [nvarchar](25) NULL,
[ModifiedOn] [datetime] NULL,
[AuditedBy] [nvarchar](25) NULL,
[AuditedOn] [datetime] NULL,
[StartActiveDate] [datetime] NOT NULL DEFAULT (getdate()),
[EndActiveDate] [datetime] NULL
) ON [PRIMARY]

GO

4. Rejection Details

CREATE TABLE [dbo].[RejectionDetails](
[CandidateId] [int] NOT NULL,
[RejectionLetterNo] [nvarchar](25) NOT NULL,
[RejectionLetterDate] [datetime] NOT NULL,
[RejectedBy] [int] NULL,
[CreatedOn] [datetime] NOT NULL CONSTRAINT
[DF_RejectionDetails_CreatedOn] DEFAULT (getdate()),
[CreatedBy] [nvarchar](25) NOT NULL,
[ModifiedOn] [datetime] NULL,
[ModifiedBy] [nvarchar](25) NULL,
[AuditedOn] [datetime] NULL,
[AuditedBy] [nvarchar](25) NULL,
[StartActiveDate] [datetime] NOT NULL CONSTRAINT
[DF_RejectionDetails_StartActiveDate] DEFAULT (getdate()),
[EndActiveDate] [datetime] NULL,
CONSTRAINT [PK_RejectionDetails] PRIMARY KEY CLUSTERED
(
[CandidateId] ASC,
[RejectionLetterNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

First I explain the process scenario.

For every application received from candidate, the data will be
entered to Candidatemaster like name, Sex, Category (GEN,OBC,SC,ST),
and PhysicallyHandicapped or not. If a candidate is eligble for
sanction, then his details with startdate and enddate will be stored
in that Sanction table. If a candidate is marked for rejection then
the details will be stored in Rejectiondetails table. If Sanctioned
candidate may be marked for PaymentStop. Every status of the process
will be stored in CandidateStatusAsOnADate table.

Sample Records for CandidateStatusAsOnADate table.

CandidateID StatusAsOnDate StatusDate
4 S 2007-08-01 00:00:00.000
5 S 2007-08-01 00:00:00.000
6 S 2007-08-01 00:00:00.000
7 S 2007-08-01 00:00:00.000
8 S 2007-08-01 00:00:00.000
9 S 2007-08-01 00:00:00.000
10 S 2007-08-01 00:00:00.000
11 S 2007-08-01 00:00:00.000
10 X 2008-01-01 00:00:00.000
942 R 2008-01-01 00:00:00.000

Sample Records From SanctionDetails

CandidateId SanctionLetterDate Amount

4 2007-08-01 00:00:00.000 400.00
5 2007-08-01 00:00:00.000 400.00
6 2007-08-01 00:00:00.000 400.00
7 2007-08-01 00:00:00.000 400.00
8 2007-08-01 00:00:00.000 400.00
9 2007-08-01 00:00:00.000 400.00
10 2007-08-01 00:00:00.000 400.00
11 2007-08-01 00:00:00.000 400.00
12 2007-08-01 00:00:00.000 500.00
13 2007-08-01 00:00:00.000 400.00

Sample Records From RejectionDetails

CandidateId RejectionLetterDate
----------- -----------------------
940 2008-01-01 00:00:00.000
941 2008-01-01 00:00:00.000
942 2008-01-01 00:00:00.000
943 2008-01-01 00:00:00.000
944 2008-01-01 00:00:00.000
945 2008-01-01 00:00:00.000
946 2008-01-01 00:00:00.000
947 2008-01-01 00:00:00.000
948 2008-01-01 00:00:00.000
949 2008-01-01 00:00:00.000

Using these tables i have to get a result table that contains month
wise breakup of Candidates
How many non-ph males candidates sanctioned , and how many non-ph
female candidates and how many PH candidates within this
classfication how many Male-SC,Male-ST,MALE-OBC,MALE-GEN and Female-
SC,Female-ST,Female-OBC, and Female-GEN , PH-SC,PH-ST,PH-obc, PH-GEN
and all these classification should be return for Rejected candidates
and stop pay list


For this i Created a view for month wise breakup.



The view is as follows

Create View [dbo].[vew_Summary_for_report]
as
With CTE_SAN_REJ_PEN_SP ([Date],[Sanctioned],[Rejected],[Pending],
[PayStopped])
AS
(
select * from
( Select CandidateID, dbo.ufn_GetFirstDayOfMonth (StatusDate) as
[Date], StatusAsOnDate
From dbo.CandidateStatusAsOnADate
)S
Pivot
( Count(CandidateId) for StatusAsOnDate in ([S],[R],[P],[X])) P
),

CTE_Application_CategoryWise ( [Date], [GEN_App],[OBC_App],[SC_App],
[ST_App])
as (
Select * From
(
Select [dbo].[ufn_GetFirstDayOfMonth] (ApplicationReceiptDate) as
[Date],CandidateId,Category
From CandidateMaster)
sc
Pivot
(
count(CandidateID) For Category In([GEN],[OBC],[SC],[ST]))Pc
),
CTE_MaleApplication_CategoryWise ( [Date],[Male_GEN],[Male_OBC],
[Male_SC],[Male_ST])
As
(
Select * From
(
Select [dbo].[ufn_GetFirstDayOfMonth] (ApplicationReceiptDate) as
[Date],CandidateId,category
From CandidateMaster
Where Gender='M' And PhysicallyHandicapped='False')
sc
Pivot
(
count(CandidateID) For Category In([GEN],[OBC],[SC],[ST]))Pc
),
CTE_FemaleApp_CategoryWise ([Date],[Fem_GEN],[Fem_OBC],[Fem_SC],
[Fem_ST])
as (
Select * From
(
Select [dbo].[ufn_GetFirstDayOfMonth] (ApplicationReceiptDate) as
[Date],CandidateId,Category
From CandidateMaster
Where Gender ='F' and PhysicallyHandicapped= 'False')
sc
Pivot
(
count(CandidateID) For Category In([GEN],[OBC],[SC],[ST]))Pc
),
CTE_Ph_App_CategroyWise([Date],[Ph_GEN],[Ph_OBC],[Ph_SC],[Ph_ST])
as
(
Select * From
(
Select [dbo].[ufn_GetFirstDayOfMonth] (ApplicationReceiptDate) as
[Date],CandidateId,Category
From CandidateMaster
Where PhysicallyHandicapped='True')
sc
Pivot
(
count(CandidateID) For Category In([GEN],[OBC],[SC],[ST]))Pc
),
CTE_Male_Female_Sanctioned ([Date],[San_Male],[San_Female])
as
(
Select * From (
Select [dbo].[ufn_GetFirstDayOfMonth] (SanctionLetterDate) as
[Date],SD.CandidateID,Gender
From SanctionDetails SD
Join CandidateMaster CM
on SD.CandidateId = CM.CandidateID
Where PhysicallyHandicapped = 'False'
) S1

Pivot
(Count(CandidateId) For Gender In([M],[F])) P1
),
CTE_PH_Male_Femal_Sanctioned ([Date],[San_Ph_Male],[San_Ph_Female])
as
(
Select * From (
Select [dbo].[ufn_GetFirstDayOfMonth] (SanctionLetterDate) as
[Date],SD.CandidateID,Gender
From SanctionDetails SD
Join CandidateMaster CM
on SD.CandidateId = CM.CandidateID
Where PhysicallyHandicapped = 'True'
) S2

Pivot
(Count(CandidateId) For Gender In([M],[F])) P2
),

CTE_Sanctioned_Male_CategoryWise ([Date], [San_Male_GEN],
[San_Male_OBC],[San_Male_SC],[San_Male_ST])
as
(
Select * From (
Select [dbo].[ufn_GetFirstDayOfMonth] (SanctionLetterDate) as
[Date],SD.CandidateID,Category
From SanctionDetails SD
Join CandidateMaster CM
on SD.CandidateId = CM.CandidateID
Where PhysicallyHandicapped = 'False'
and Gender ='M'
) S3

Pivot
(Count(CandidateId) For cateGory In([GEN],[OBC],[SC],[ST])) P3
),
CTE_Sanctioned_Female_CategoryWise ([Date], [San_FeMale_GEN],
[San_FeMale_OBC],[San_FeMale_SC],[San_FeMale_ST])
as
(
Select * From (
Select [dbo].[ufn_GetFirstDayOfMonth] (SanctionLetterDate) as
[Date],SD.CandidateID,Category
From SanctionDetails SD
Join CandidateMaster CM
on SD.CandidateId = CM.CandidateID
Where PhysicallyHandicapped = 'False'
and Gender ='F'
) S3

Pivot
(Count(CandidateId) For cateGory In([GEN],[OBC],[SC],[ST])) P3
),
CTE_Sanctioned_PH_CategoryWise ([Date], [San_PH_GEN],[San_PH_OBC],
[San_PH_SC],[San_PH_ST])
as
(
Select * From (
Select [dbo].[ufn_GetFirstDayOfMonth] (SanctionLetterDate) as
[Date],SD.CandidateID,Category
From SanctionDetails SD
Join CandidateMaster CM
on SD.CandidateId = CM.CandidateID
Where PhysicallyHandicapped = 'True'
) S3

Pivot
(Count(CandidateId) For cateGory In([GEN],[OBC],[SC],[ST])) P3
),
CTE_Male_Female_SP([Date],[SP_Male],[SP_Female])
as
(
Select * From (
Select [dbo].[ufn_GetFirstDayOfMonth] (StatusDate) as
[Date],SP.CandidateID,Gender
From dbo.CandidateStatusAsOnADate SP
Join CandidateMaster CM
on SP.CandidateId = CM.CandidateID
Where PhysicallyHandicapped = 'False' and StatusAsOnDate='X' ) S1

Pivot
(Count(CandidateId) For Gender In([M],[F])) P1
),
CTE_PH_Male_Female_SP([Date],[SP_Ph_Male],[SP_Ph_Female])
as
(
Select * From (
Select [dbo].[ufn_GetFirstDayOfMonth] (StatusDate) as
[Date],SP.CandidateID,Gender
From dbo.CandidateStatusAsOnADate SP
Join CandidateMaster CM
on SP.CandidateId = CM.CandidateID
Where PhysicallyHandicapped = 'True' and StatusAsOnDate='X'
) S2

Pivot
(Count(CandidateId) For Gender In([M],[F])) P2
),
CTE_Male_Category_SP([Date],[SP_Male_SC],[SP_Male_ST],[SP_Male_OBC],
[SP_Male_GEN])
as
(
Select * From (
Select [dbo].[ufn_GetFirstDayOfMonth] (StatusDate) as
[Date],SP.CandidateID,Category
From dbo.CandidateStatusAsOnADate SP
Join CandidateMaster CM
on SP.CandidateId = CM.CandidateID
Where PhysicallyHandicapped = 'False' and StatusAsOnDate='X' and
Gender ='M' ) S1

Pivot
(Count(CandidateId) For Category In([SC],[ST],[OBC],[GEN])) P1
),
CTE_PHMale_Category_SP([Date],[SP_PHMale_SC],[SP_PHMale_ST],
[SP_PHMale_OBC],[SP_PHMale_GEN])
as
(
Select * From (
Select [dbo].[ufn_GetFirstDayOfMonth] (StatusDate) as
[Date],SP.CandidateID,Category
From dbo.CandidateStatusAsOnADate SP
Join CandidateMaster CM
on SP.CandidateId = CM.CandidateID
Where PhysicallyHandicapped = 'True' and StatusAsOnDate='X' and
Gender ='M' ) S1

Pivot
(Count(CandidateId) For Category In([SC],[ST],[OBC],[GEN])) P1
),

CTE_FeMale_Category_SP([Date],[SP_feMale_SC],[SP_feMale_ST],
[SP_feMale_OBC],[SP_feMale_GEN])
as
(
Select * From (
Select [dbo].[ufn_GetFirstDayOfMonth] (StatusDate) as
[Date],SP.CandidateID,Category
From dbo.CandidateStatusAsOnADate SP
Join CandidateMaster CM
on SP.CandidateId = CM.CandidateID
Where PhysicallyHandicapped = 'False' and StatusAsOnDate='X' and
Gender ='F' ) S1

Pivot
(Count(CandidateId) For Category In([SC],[ST],[OBC],[GEN])) P1
),
CTE_PHFeMale_Category_SP([Date],[SP_PHFeMale_SC],[SP_PHFeMale_ST],
[SP_PHFeMale_OBC],[SP_PHFeMale_GEN])
as
(
Select * From (
Select [dbo].[ufn_GetFirstDayOfMonth] (StatusDate) as
[Date],SP.CandidateID,Category
From dbo.CandidateStatusAsOnADate SP
Join CandidateMaster CM
on SP.CandidateId = CM.CandidateID
Where PhysicallyHandicapped = 'True' and StatusAsOnDate='X' and
Gender ='F' ) S1

Pivot
(Count(CandidateId) For Category In([SC],[ST],[OBC],[GEN])) P1
)



Select
CTESRPX.[Date],
CTEAC.[GEN_App]+CTEAC.[OBC_App]+CTEAC.[SC_App]+CTEAC.[ST_App]
As[TotalApplications],
CTESRPX.[Sanctioned],CTESRPX.[Rejected],CTESRPX.[Pending],CTESRPX.
[PayStopped],
CTEAC.[GEN_App],CTEAC.[OBC_App],CTEAC.[SC_App],CTEAC.[ST_App],
CTEMA.[Male_GEN]+CTEMA.[Male_OBC]+CTEMA.[Male_SC]+CTEMA.[Male_ST] as
[TotalMale],
CTEMA.[Male_GEN],CTEMA.[Male_OBC],CTEMA.[Male_SC],CTEMA.[Male_ST],
CTEFAC.[Fem_GEN]+CTEFAC.[Fem_OBC]+CTEFAC.[Fem_SC]+CTEFAC.[Fem_ST] as
[TotlaFemale],
CTEFAC.[Fem_GEN],CTEFAC.[Fem_OBC],CTEFAC.[Fem_SC],CTEFAC.[Fem_ST],
CTEPAC.[Ph_GEN]+CTEPAC.[Ph_OBC]+CTEPAC.[Ph_SC]+CTEPAC.[Ph_ST] as
[TotalPH],
CTEPAC.[Ph_GEN],CTEPAC.[Ph_OBC],CTEPAC.[Ph_SC],CTEPAC.[Ph_ST],
CTEMFS.[San_Male],CTEMFS.[San_Female], CTEPHMFS.[San_Ph_Male] +
CTEPHMFS.[San_Ph_Female] as [San_PH_Total],
CTEPHMFS.[San_Ph_Male],CTEPHMFS.[San_Ph_Female],
CTESMCW.[San_Male_GEN],CTESMCW.[San_Male_OBC],CTESMCW.
[San_Male_SC],CTESMCW.[San_Male_ST],
CTESFCW.[San_FeMale_GEN],CTESFCW.[San_FeMale_OBC],CTESFCW.
[San_FeMale_SC],CTESFCW.[San_FeMale_ST],
CTESPHCW.[San_PH_GEN],CTESPHCW.[San_PH_OBC],CTESPHCW.
[San_PH_SC],CTESPHCW.[San_PH_ST],
[SP_Male] as [SP_Male],[SP_Female] as [SP_Female],[SP_Ph_Male] as
[SP_Ph_Male] , [SP_Ph_Female] as [SP_Ph_Female],
CTE_Male_Category_SP.[SP_Male_SC],CTE_Male_Category_SP.
[SP_Male_ST],CTE_Male_Category_SP.[SP_Male_OBC],[SP_Male_GEN],
CTE_PHMale_Category_SP.[SP_PHMale_SC],CTE_PHMale_Category_SP.
[SP_PHMale_ST],CTE_PHMale_Category_SP.
[SP_PHMale_OBC],CTE_PHMale_Category_SP.[SP_PHMale_GEN],
CTE_FeMale_Category_SP.[SP_feMale_SC],CTE_FeMale_Category_SP.
[SP_feMale_ST],CTE_FeMale_Category_SP.
[SP_feMale_OBC],CTE_FeMale_Category_SP.[SP_feMale_GEN],
CTE_PHFeMale_Category_SP.[SP_PHFeMale_SC],CTE_PHFeMale_Category_SP.
[SP_PHFeMale_ST],CTE_PHFeMale_Category_SP.
[SP_PHFeMale_OBC],CTE_PHFeMale_Category_SP.[SP_PHFeMale_GEN]

From CTE_MaleApplication_CategoryWise CTEMA
Left Outer Join CTE_Application_CategoryWise CTEAC
on CTEAC.[Date] = CTEMA.[Date]
Left outer Join CTE_PH_Male_Female_SP
on CTE_PH_Male_Female_SP.[Date] = CTEAC.[Date]
Left outer Join CTE_Male_Female_SP
on CTE_Male_Female_SP.[DATE] = CTEAC.[Date]
Left Outer Join CTE_SAN_REJ_PEN_SP CTESRPX
on CTEAC.[Date] = CTESRPX.[Date]
Left Outer Join CTE_FemaleApp_CategoryWise CTEFAC
on CTEAC.[Date] = CTEFAC.[Date]
Left Outer Join CTE_Ph_App_CategroyWise CTEPAC
on CTEAC.[Date] = CTEPAC.[Date]
Left Outer Join CTE_Male_Female_Sanctioned CTEMFS
on CTEMA.[Date] = CTEMFS.[Date]
Left Outer Join CTE_PH_Male_Femal_Sanctioned CTEPHMFS
on CTEMA.[Date] = CTEPHMFS.[Date]
Left Outer Join CTE_Sanctioned_Male_CategoryWise CTESMCW
on CTEMA.[Date] = CTESMCW.[Date]
Left Outer Join CTE_Sanctioned_Female_CategoryWise CTESFCW
on CTEMA.[Date] = CTESFCW.[Date]
Left Outer Join CTE_Sanctioned_PH_CategoryWise CTESPHCW
on CTEMA.[Date] = CTESPHCW.[Date]
Left outer join CTE_Male_Category_SP
on CTE_Male_Category_SP.[Date] = CTESPHCW.[Date]
Left outer join CTE_PHMale_Category_SP
on CTE_PHMale_Category_SP.[Date] = CTE_Male_Category_SP.[Date]
Left Outer Join CTE_FeMale_Category_SP
on CTE_FeMale_Category_SP.[Date] = CTE_Male_Category_SP.[Date]
Left Outer Join CTE_PHFeMale_Category_SP
on CTE_PHFeMale_Category_SP.[Date] = CTE_Male_Category_SP.[Date]



it is working fine but the execution time to populate this view is
getting more than 1 minutes even though my table contains very small
amount of records may be 1500.

so I added this discussion so that any one can gimme a quick solution!

[Esakkiappan Barathan]

I have used PIVOT to get monthwise break up for SC,ST,OBC and GEN and
MALE,FEMALE. As I have to conbine a list Pivoted tables so I use CTEs.

In Some months there may not be PH candidates so some of the CTEs may
be null for a particular month, so I used LEFT OUTER Join to join
all CTEs.