LEFT JOIN PERFORMACE WORKS IN PRODUCTION, SQA DATA PEFORMANCE ISSUE Need some urgent hint please

[gopal5879]

Hello folks,

I have tables as below

CLAIM:

LOAD MPI_CLAIM_ID ,

CLAIMNUMBER

RESIDENT TEMPCLAIM;

I AM ABLE TO LOAD CLAIM TABLE without any issue in SQA and production.

-- when the below part is called it takes enoromous amount of time in SQA. In production it  works fine.

Can some give some tip to tweak this to work better or point me where I could be  wrong.

The below part is causing the performance problem which I am facing hard to figure out what could be wrong in

SQA environment.

LEFT JOIN(CLAIM)
    LOAD Engine AS Engine,
    ClaimID  AS MPI_CLAIM_ID_KEY                                        
    FROM [$(vpath)\EDP_CLAIM_EXTENDED.qvd](qvd)
     WHERE  EXISTS ( NTWX_MPI_CLAIM_ID,ClaimID);

[marcus_sommer · Accepted solution]

There is no common key between both tables which will result in a cartesian product which is usually very expensive and assumingly not intended here. Are you sure that both environments (prod and sqa) are identically in script and data?

- Marcus

[gopal5879 · Accepted solution]

Thanks Marcus, Actually I had typo in column name,  but there was lot of repetitive data from left join part causing to take long time. I traced from your message assuming cartesian ,  then found out due to repetitive data causing delay with main table. Thanks a lot for your time.,