Question

4
Replies
47
Views
Close popover
Pradeep Chowdary Pydi (PradeepPydi)
Anthem
Senior PEGA DEVOPS and Cloud Administrator
Anthem
IN
PradeepPydi Member since 2017 208 posts
Anthem
Posted: September 2, 2020
Last activity: September 24, 2020

CPMHC_WORK SQL INDEXES | Alternatives.

Hello,

For the following SQL Query, I was able to find the below mentioned missing Index [PRPC731 and MSSQL].

SELECT "pyResolvedUserID" AS "pyResolvedUserID" , "pxObjClass" AS "pxObjClass" , "pyResolvedTimestamp" AS "pyResolvedTimestamp" , "pyLabel" AS "pyLabel" , "pyID" AS "pyID" , "pyDescription" AS "pyDescription" , "pyStatusWork" AS "pyStatusWork" , "WorkbasketName" AS "WorkbasketName" , "MemberFullName" AS "MemberFullName" , "MemberLOB" AS "MemberLOB" , "ProviderName" AS "ProviderName" , "ProviderNumber" AS "ProviderNumber" , "MemberID" AS "MemberID" , "pxCreateOperator" AS "pxCreateOperator" , "pxCreateDateTime" AS "pxCreateDateTime" , "FaxWOID" AS "FaxWOID" , "DocumentID" AS "DocumentID" , "pxUpdateDateTime" AS "pxUpdateDateTime" , "pzInsKey" AS "pzInsKey" from dbo.CPMHC_WORK WHERE ( "pyResolvedUserID" = 'AD69288' ) AND ( "pxObjClass" NOT LIKE '%-Research%' ) AND "pyResolvedTimestamp" >= '2020-09-01 00:00:00.0' AND "pyResolvedTimestamp" <= '2020-09-02 00:00:00.0' AND "pxObjClass" IS NOT NULL AND ( ( "pyLabel" = 'Provider Search Criteria' ) OR ( "pyDescription" = 'Provider Search Criteria' ) ) AND ( "pyStatusWork" LIKE '%Resolved%' ) ORDER BY "pyResolvedTimestamp" DESC

 

USE [DB Name] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[CPMHC_WORK] ([pyResolvedUserID],[pxObjClass],[pyResolvedTimestamp]) INCLUDE ([pxCreateDateTime],[pxCreateOperator],[pxUpdateDateTime],[pyDescription],[pyID],[pyLabel],[pyStatusWork],[pzInsKey],[ProviderNumber],[MemberID],[WorkbasketName],[MemberLOB],[MemberFullName],[ProviderName],[FaxWOID],[DocumentID]) GO

 

Since this is a Work table and it does have N number of sql statements, we cannot keep creating Indexes for all.

Please let me know if you disagree with the above statement and suggest any other alternatives.

Thank you.

Pega Platform 7.3.1 Case Management Healthcare and Life Sciences