Is there any optimizations I can do with this statement to get the count of used rows?

Question

Is there any optimizations I can do with this statement to get the count of used rows.

Note: The ProductOrderInfo table has over 40 million rows in it.

SELECT @TotalUsed = COUNT(*)
FROM ProductInfo WITH (NOLOCK)
WHERE ProductInfoId IN 
(
    SELECT ProductInfoId FROM ProductOrderInfo WITH (NOLOCK)
);

Answer 1

Try this

SELECT @TotalUsed = COUNT(*) FROM ProductInfo WITH (NOLOCK) as p 
WHERE EXISTS ( SELECT ProductInfoId FROM ProductOrderInfo WITH (NOLOCK) 
WHERE ProductInfoId =p.ProductInfoId );

Answer 2

Assuming there are no orphan ProductInfoIds on ProductOrderInfo, the following:

SELECT @TotalUsed = COUNT(DISTINCT ProductInfoId) 
FROM ProductOrderInfo WITH (NOLOCK);

may be faster, as it only accesses one table.

Whichever query is being run, it should run faster if there is an index on ProductInfoID on ProductOrderInfo.

Answer 3

Use JOIN for such queries:

SELECT @TotalUsed = COUNT(DISTINCT i.ProductInfoId) 
FROM ProductInfo i WITH (NOLOCK) 
JOIN ProductOrderInfo oi WITH (NOLOCK) 
    ON io.ProductInfoId = i.ProductInfoId

Tables should have Indexes by these columns for fast search.