Question
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.
Hi Moderators,
Please route this request to a right point of contact.
Thank you.