Vamsi k

Vamsi k

  • NA
  • 296
  • 42.6k

Gridview custom paging

Feb 13 2019 12:41 PM
Hi All,
 
Can any one please give an example of custom  pagination in gridview.
 
Actually we have 35000 records are there when we execute union all query. We are facing time out exception. So temporary we kept CommandTimeout=1000 we kept. But we need permemnant solution. 
 
Here below is the query. Please optimise the query.Or please give example of Gridview pagination.
 
Below every table has clustered index 
 
CREATE PROCEDURE [rcmis].[CustomReport5]
(@DueDateStart datetime = NULL,
@DueDateEnd datetime = NULL,
@CompleteDateStart datetime = NULL,
@CompleteDateEnd datetime = NULL)
AS
BEGIN
SELECT mc.MstrCitnId AS MasterID,
LTRIM(cp.CmplncPlanId ) AS CPId,
SeqId,
LTRIM(RTRIM(SeqPrfxCd)) + '_' + CAST(SeqId as varchar) AS RecordId,
LTRIM(mc.MstrCitnNm) AS Name,
LTRIM(cp.CmplncPlanTitle ) AS Title,
(SELECT LTRIM(LAST_NAME) + ', ' + RTRIM(FIRST_NAME) From mnet_rcmis WHERE mnet_id = mc.CmplncOwnerId) AS AsOwner,
CONVERT(CHAR(10), cps.CmplncPlanSchdlOccurDt, 101) AS CPDueDate,
CONVERT(CHAR(10), cps.CmplncSchdlCmpltDt, 101) AS CPCompleteDate,
CONVERT(CHAR(10), cps.RowChangedTmstmp, 101) AS RecordedDt,
LTRIM(ct.CitnTypeDsc) AS Type,
LTRIM(jl.JrsdctnLocDsc) AS Geo,
REPLACE(REPLACE(REPLACE(STUFF((SELECT+'<br><a target=\"_blank\" href='+Replace(CmplncPlanDoc.CmplncPlanDocURL,' ','%20')+'?&documentID='+cast(CmplncPlanDocId as varchar)+'&documentTable=CmplncPlanDoc\" target=viewDocument onClick=\"window.open('',''viewDocument'',''width=775,height=500,resizable,scrollbars'')\">'+CmplncPlanDoc.CmplncPlanDocURL+'</a><br/>' FROM CmplncPlanDoc WHERE CmplncPlanDoc.CmplncPlanId = cp.CmplncPlanId
FOR XML PATH ('')),1,2,''),'&lt;','<'),'&gt;','>'),'t;','<') AS Documents,
(SELECT LTRIM(LAST_NAME) + ', ' + RTRIM(FIRST_NAME) From mnet_rcmis WHERE mnet_id = cp.BusUnitOwnerId) AS BUOwner,
mc.MstrCitnExecCertInd AS ExecCert,
(SELECT CONVERT(CHAR(10),cp.RowCreatedDt, 101)) AS RowCreatedDt,
LTRIM(cs.CitnStatDsc) AS CitnStatDsc,
(SELECT LTRIM(LAST_NAME) + ', ' + RTRIM(FIRST_NAME) From mnet_rcmis WHERE mnet_id = cp.CmplncOwnerId) AS RCOwner,
(SELECT CONVERT(CHAR(10),cp.RowCreatedDt, 101)) AS RowUpdatedDt,
LTRIM(wf.WrkflwStatDsc) AS WrkflwStatDsc,
LTRIM(jo.JrsdctnOrgNm) AS JrsdctnOrgNm,
(SELECT TOP 1 BusEntity.BusEntityNm FROM BusEntityMstrCitnXref
INNER JOIN BusEntity ON BusEntity.BusEntityId = BusEntityMstrCitnXref.BusEntityId AND BusEntityMstrCitnXref.MstrCitnId = mc.MstrCitnId) AS Entity,
(SELECT TOP 1 CmplncPlanNote.CmplncPlanNoteTxt FROM CmplncPlanNote WHERE CmplncPlanNote.CmplncPlanId = cp.CmplncPlanId )AS CPDueNote,
LTRIM(mc.DocketNo) AS DocketNo,
(SELECT LTRIM(LAST_NAME) + ', ' + RTRIM(FIRST_NAME) From mnet_rcmis WHERE mnet_id = mc.SMEOwnerId) AS LegalSME,
mc.MstrCitnSynopsisDsc AS Synopsis,
(SELECT (CASE WHEN (SELECT COUNT(*) FROM CmplncPlanEmailAlert
WHERE CmplncPlanEmailAlert.CmplncPlanId = cp.CmplncPlanId) > 0 THEN 'Y' ELSE 'N' END)) AS EmailSchdl
FROM MstrCitn mc, CmplncPlan cp, CmplncPlanSchdl cps, MstrCitnCmplncPlanXref xr, JrsdctnOrg jo, JrsdctnLoc jl, CitnType ct, CitnStat cs, WrkflwStat wf
WHERE xr.MstrCitnId = mc.MstrCitnId
AND cp.CmplncPlanId = xr.CmplncPlanId
AND cp.CmplncPlanId = cps.CmplncPlanId
AND jo.JrsdctnOrgId = mc.JrsdctnOrgId
AND jl.JrsdctnLocId = mc.JrsdctnLocId
AND mc.CitnTypeId = ct.CitnTypeId
AND mc.CitnStatId = cs.CitnStatId
AND mc.WrkflwStatId = wf.WrkflwStatId
AND cps.CmplncPlanSchdlOccurDt >= @DueDateStart
AND cps.CmplncPlanSchdlOccurDt <= @DueDateEnd
AND (cps.CmplncSchdlCmpltDt >= @CompleteDateStart OR cps.CmplncSchdlCmpltDt IS NULL)
AND (cps.CmplncSchdlCmpltDt <= @CompleteDateEnd OR cps.CmplncSchdlCmpltDt IS NULL)
UNION ALL
SELECT mc.MstrCitnId AS MasterID,
LTRIM(cp.CmplncPlanId ) AS CPId,
SeqId,
LTRIM(RTRIM(SeqPrfxCd)) + '_' + CAST(SeqId as varchar) AS RecordId,
LTRIM(mc.MstrCitnNm) AS Name,
LTRIM(cp.CmplncPlanTitle ) AS Title,
(SELECT LTRIM(LAST_NAME) + ', ' + RTRIM(FIRST_NAME) From mnet_rcmis WHERE mnet_id = mc.CmplncOwnerId) AS AsOwner,
CONVERT(CHAR(10), cps.CmplncPlanSchdlOccurDt, 101) AS CPDueDate,
CONVERT(CHAR(10), cps.CmplncSchdlCmpltDt, 101) AS CPCompleteDate,
CONVERT(CHAR(10), cps.RowChangedTmstmp, 101) AS RecordedDt,
LTRIM(ct.CitnTypeDsc) AS Type,
LTRIM(jl.JrsdctnLocDsc) AS Geo,
REPLACE(REPLACE(REPLACE(STUFF((SELECT+'<br><a target=\"_blank\" href='+Replace(CmplncPlanDoc.CmplncPlanDocURL,' ','%20')+'?&documentID='+cast(CmplncPlanDocId as varchar)+'&documentTable=CmplncPlanDoc\" target=viewDocument onClick=\"window.open('',''viewDocument'',''width=775,height=500,resizable,scrollbars'')\">'+CmplncPlanDoc.CmplncPlanDocURL+'</a><br/>' FROM CmplncPlanDoc WHERE CmplncPlanDoc.CmplncPlanId = cp.CmplncPlanId
FOR XML PATH ('')),1,2,''),'&lt;','<'),'&gt;','>'),'t;','<') AS Documents,
(SELECT LTRIM(LAST_NAME) + ', ' + RTRIM(FIRST_NAME) From mnet_rcmis WHERE mnet_id = cp.BusUnitOwnerId) AS BUOwner,
mc.MstrCitnExecCertInd AS ExecCert,
(SELECT CONVERT(CHAR(10),cp.RowCreatedDt, 101)) AS RowCreatedDt,
LTRIM(cs.CitnStatDsc) AS CitnStatDsc,
(SELECT LTRIM(LAST_NAME) + ', ' + RTRIM(FIRST_NAME) From mnet_rcmis WHERE mnet_id = cp.CmplncOwnerId) AS RCOwner,
(SELECT CONVERT(CHAR(10),cp.RowCreatedDt, 101)) AS RowUpdatedDt,
LTRIM(wf.WrkflwStatDsc) AS WrkflwStatDsc,
LTRIM(jo.JrsdctnOrgNm) AS JrsdctnOrgNm,
(SELECT TOP 1 BusEntity.BusEntityNm FROM BusEntityMstrCitnXref
INNER JOIN BusEntity ON BusEntity.BusEntityId = BusEntityMstrCitnXref.BusEntityId AND BusEntityMstrCitnXref.MstrCitnId = mc.MstrCitnId) AS Entity,
(SELECT TOP 1 CmplncPlanNote.CmplncPlanNoteTxt FROM CmplncPlanNote WHERE CmplncPlanNote.CmplncPlanId = cp.CmplncPlanId )AS CPDueNote,
LTRIM(mc.DocketNo) AS DocketNo,
(SELECT LTRIM(LAST_NAME) + ', ' + RTRIM(FIRST_NAME) From mnet_rcmis WHERE mnet_id = mc.SMEOwnerId) AS LegalSME,
mc.MstrCitnSynopsisDsc AS Synopsis,
(SELECT (CASE WHEN (SELECT COUNT(*) FROM CmplncPlanEmailAlert
WHERE CmplncPlanEmailAlert.CmplncPlanId = cp.CmplncPlanId) > 0 THEN 'Y' ELSE 'N' END)) AS EmailSchdl
FROM MstrCitn mc, Prvsn pv, CmplncPlanPrvsnXref px, CmplncPlan cp, CmplncPlanSchdl cps, JrsdctnOrg jo, JrsdctnLoc jl, CitnType ct, CitnStat cs, WrkflwStat wf
WHERE pv.MstrCitnId = mc.MstrCitnId
AND px.PrvsnId = pv.PrvsnId
AND cp.CmplncPlanId = px.CmplncPlanId
AND cps.CmplncPlanId = cp.CmplncPlanId
AND jo.JrsdctnOrgId = mc.JrsdctnOrgId
AND jl.JrsdctnLocId = mc.JrsdctnLocId
AND mc.CitnTypeId = ct.CitnTypeId
AND mc.CitnStatId = cs.CitnStatId
AND mc.WrkflwStatId = wf.WrkflwStatId
AND cps.CmplncPlanSchdlOccurDt >= @DueDateStart
AND cps.CmplncPlanSchdlOccurDt <= @DueDateEnd
AND (cps.CmplncSchdlCmpltDt >= @CompleteDateStart OR cps.CmplncSchdlCmpltDt IS NULL)
AND (cps.CmplncSchdlCmpltDt <= @CompleteDateEnd OR cps.CmplncSchdlCmpltDt IS NULL)
ORDER BY NAME
END
 

Answers (1)