SMILE-4484 - [Change Healthcare - 860750428**] - Getting timeout issue for _contained searches

[JasonRoberts-smile]

Original Jira Description Hi Team, We have few _contained query which are still giving the timeout error. Below are the impacted URIs:

[https://api-qa.apip.awsnonprod.healthcareit.net/anon/CMSPatientAccess/provider/v2/PractitionerRole?_contained=true&practitioner.given=Adam&chc-payer-id=testCustomerId|https://api-qa.apip.awsnonprod.healthcareit.net/anon/CMSPatientAccess/provider/v2/PractitionerRole?_contained=true&practitioner.given=Adam&chc-payer-id=testCustomerId]

[https://api-qa.apip.awsnonprod.healthcareit.net/anon/CMSPatientAccess/provider/v2/PractitionerRole?_contained=&practitioner.active=true&chc-payer-id=testCustomerId|https://api-qa.apip.awsnonprod.healthcareit.net/anon/CMSPatientAccess/provider/v2/PractitionerRole?_contained=&practitioner.active=true&chc-payer-id=testCustomerId]

[https://api-qa.apip.awsnonprod.healthcareit.net/anon/CMSPatientAccess/provider/v2/PractitionerRole?_contained=true&practitioner.gender=male&chc-payer-id=testCustomerId|https://api-qa.apip.awsnonprod.healthcareit.net/anon/CMSPatientAccess/provider/v2/PractitionerRole?_contained=true&practitioner.gender=male&chc-payer-id=testCustomerId]

[https://api-qa.apip.awsnonprod.healthcareit.net/anon/CMSPatientAccess/provider/v2/PractitionerRole?_contained=true&chc-payer-id=testCustomerId&location.organization.address=1 Main st|https://api-qa.apip.awsnonprod.healthcareit.net/anon/CMSPatientAccess/provider/v2/PractitionerRole?_contained=true&chc-payer-id=testCustomerId&location.organization.address=1%20Main%20st]

Please let us know if we can have a discussion on this today.

Sample DEV URLs: [https://smilecdr.dev.ihdp.awsnonprod.healthcareit.net:8000/PractitionerRole?metatagcustomerinternalid=testCustomerId&_contained=true&practitioner.active=true&_requestTrace=true|https://smilecdr.dev.ihdp.awsnonprod.healthcareit.net:8000/PractitionerRole?metatagcustomerinternalid=testCustomerId&_contained=true&practitioner.active=true&_requestTrace=true]

Steps to Reproduce Run the Generated SQL Queries against the DB and notice that when you change UNION to UNION ALL, you get much better performance. This implies that we should change our code to use UNION ALL where we currently use UNION. Details available in this Internal Note: https://simpaticois.atlassian.net/browse/SMILE-4484?focusedCommentId=77722

Netsuite Project Number 93

Replicated by Kevin Dougan

Development Work Requested None

Reporter [email protected]