Vamsi k

NA
296
46.3k

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)

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,''),'<','<'),'>','>'),'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