<div>
Hi,<br />
<br />
normally you would use a FROM T1 INNER JOIN T2 ON T1.PK1 = T2.K1 instead of using a CROSS JOIN and a WHERE. But modern query machines should find that out by themselves and create an identical execution plan in both cases.<br />
<br />
Instead of creating the WHERE at the end you could reduce the number of rows before joining like this:<br />

<pre><code class="code-1 nolinks" id="code-20-42718919-1"><span>SELECT COUNT(distinct Q1.PK1)</span>
<span>FROM ( SELECT T1.PK1 FROM T1 WHERE T1.C1 = 3) AS Q1</span>
<span>INNER JOIN (SELECT T2.K1 FROM T2 WHERE T2.C1 &gt; 0) AS Q2</span>
<span>ON Q1.PK1 = Q2.K1</span>
</code></pre>
<br />
But I think also this is something the query engine will find out by itself and optimize on it's own. Just create an execution plan for both and look at the result to see if there's any difference. You can also activate the execution time statistics (both in case of SQL Server, don't know which database you use).<br />
<br />
Cheers,<br />
<br />
Christian
</div>


<div>
Thanks <br />
Will SQL stop looking in T2 as soon as it finds one T2.C1 &gt;&nbsp;0 for each PK1 candidate ?
</div>


<div>
Depending on the cardinalities involved, you need the follwoing two indices: T1(C1) and T2(C1) to support the predicates for T1.C1=3 and T2.C1&gt;0
</div>


<div>
No problem with the predicates<br />
But my question remains : will SQL stop looking in T2 as soon as it sees a T2.C1 &gt;&nbsp;0 for each PK involved ?
</div>


<div>
For info, the table T1 is very small and the number of rows in T2 for each T1.PK is large (2.000 per PK)<br />
The primary key in T2 is (K1,C1)
</div>


<div>
Sure.<br />
<br />
After rereading your post again, the index on T2 should be T2(K1, C1). Cause it should give you an index seek instead of a table scan.<br />
<br />
When T2(K1) is indexed, a common practice, then you should get a index seek on this index with an key lookup for C1, which is slower, cause it requires the additional lookup.
</div>


LeTay

<div>
<div class="content wysiwyg-content">
I have two SQL tables, says T1 and T2<br />
T1 has PK1 as primary key and a column C1<br />
T2 has K1 as foreign key PK1 in T1 and a column C1 <br />
I need to count the number of T1.PK1 for which &nbsp;T1.C1 has a given value (3) and for which there is at least on T2.C1 &gt;&nbsp;0<br />
My current select is<br />
<br />
select &nbsp; count(distinct T1.PK1) <br />
from &nbsp; &nbsp;T1, T2<br />
where &nbsp;T1.PK1 = T2.K1<br />
and &nbsp; &nbsp; &nbsp;T1.C1 = 3<br />
and &nbsp; &nbsp; &nbsp;T2.C1 &gt;&nbsp;0<br />
<br />
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) ...
</div>
</div>


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) ...

Optimise a SQL query

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.