Question

4
Replies
47
Views
PradeepPydi Member since 2017 206 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
Share this page LinkedIn