Below is my query
inner join pr ON pr.prid = jcp.prid
jcp.custid = 123 union select
inner join pr ON pr.prid = jcl.prid
jcl.custid = 123) as t
is there any way to make it more efficient? this query is inside some function and executing 1000s of time. so making it slow.
First of all, your query appears to be combining two very different types of data in your 'union' - the first part being the count of an ID, and the second being the literal ID - so I would question whether this is really doing what you intend it to do as written. However, just taking it at face value, you could eliminate the subquery in the first part as follows:
SELECT COUNT(pr.prid) FROM jcp INNER JOIN pr ON pr.prid = jcp.prid WHERE jcp.custid = 123
I can't say how much that would help your performance without knowing the context of your data, but it certainly wouldn't hurt.
Given the difference in the two data sets, it doesn't appear possible to avoid the union if you want to force these two different bits of data into the same column. If you were to put them into different columns, you could probably avoid the union.