troubleshooting Question

Optimise a SQL query

Avatar of LeTay
LeTay asked on
SQL
7 Comments1 Solution80 ViewsLast Modified:
I have two SQL tables, says T1 and T2
T1 has PK1 as primary key and a column C1
T2 has K1 as foreign key PK1 in T1 and a column C1
I need to count the number of T1.PK1 for which  T1.C1 has a given value (3) and for which there is at least on T2.C1 > 0
My current select is

select   count(distinct T1.PK1)
from    T1, T2
where  T1.PK1 = T2.K1
and      T1.C1 = 3
and      T2.C1 > 0

I think that it is possible to optimise this as I presume that the entire T2 table is scanned for those rows where T2.C1 = T1.C1 (and T1.C1 = 3) ...
ASKER CERTIFIED SOLUTION
Pavel Celba
All in One

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros