We are facing performance issues when trying to count the number of records in the foreign key column that don’t have a corresponding value in the parent table’s primary key. While we already have the total row count, the query still takes too long to run, especially with large datasets.

What We’ve Tried:

• NOT EXISTS Query: We tried using a NOT EXISTS query, but it still took too long to execute.
• LEFT JOIN Query: We also tried using a LEFT JOIN with a NULL check on the parent table, but the performance was still suboptimal.

What We Need:

• We need a more efficient approach to count the records where the foreign key exists in the child table but doesn’t have a corresponding primary key in the parent table. The current methods are still taking longer than expected with larger datasets(Indexes already implemented).

We would appreciate suggestions on how to optimize this query further. Whether it’s through other query techniques, we are open to solutions that can improve speed