问题描述:

Below is my query

`Select`

count(t.prid)

from

(select

pr.prid

from

jcp

inner join pr ON pr.prid = jcp.prid

where

jcp.custid = 123 union select

pr.prid

from

jcl

inner join pr ON pr.prid = jcl.prid

where

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.