2

I've got two tables with a many to one relationship which I'll call Parent_Table and Child_Table (i.e. a parent has zero or more children, but children have exactly one parent). I need to count the number of parents who have at least one child that fulfills some condition. Which query is optimal?

Option 1 (pretty sure it's not this one)

SELECT COUNT(DISTINCT(pt.ID)) 
FROM PARENT_TABLE pt
JOIN CHILD_TABLE ct
ON pt.ID =  ct.PARENT_ID
WHERE <parent meets some condition>
AND <child meets some condition>

Option 2

SELECT COUNT(pt.ID)
FROM PARENT_TABLE pt
WHERE pt.ID in
(
SELECT ct.PARENT_ID
FROM CHILD_TABLE ct
WHERE <child meets condition>
)
AND <parent meets some condition>

Option 3 (my guess as the fastest)

SELECT COUNT(pt.ID)
FROM PARENT_TABLE pt
WHERE EXISTS
(
SELECT 1
FROM CHILD_TABLE ct
WHERE ct.PARENT_ID = pt.ID
AND <child meets condition>
)
AND <parent meets some condition>

Or is it something else entirely? Does it depend on the sizes of each table, or the complexity of the two conditions, or whether the data is sorted?

EDIT: Database is Oracle.

Yozarian22
  • 279
  • 4
  • 9
  • 1
    Why not do some performance tests on some appropriately sized sample data? – a'r Sep 23 '11 at 14:18
  • In SQL Server 2005 or later options 2 and 3 would be processed the same. – JNK Sep 23 '11 at 14:20
  • To get an accurate answer, please share your RDBMS. – JNK Sep 23 '11 at 14:21
  • In Oracle depends on statistics, but 2 and 3 could be the same. – ssedano Sep 23 '11 at 14:23
  • 3
    Guesses are worth nothing, you should look at the actual query execution plan on big enough sample data. Pay attention to indexes usage and mismatches between actual and estimated rows number fetched (big mismatches says that you need to create/update statistics on tables). – pkmiec Sep 23 '11 at 15:00
  • +1 to query execution plan... –  Sep 23 '11 at 18:57

3 Answers3

3

The first query is slow, the others should run fast on most DB's.

Without knowing the DB it's hard to say more:

But: count(*) is often faster than count(names_field) and never slower
count(distinct (afield)) is slow

Or is it something else entirely?

That depends on the DB and the exact version of the DB.

Does it depend on the sizes of each table

Yes, that plays a big part

or the complexity of the two conditions

Possible

or whether the data is sorted?

If you want a fast select, all fields used to join must be indexed.
And all fields used in a where clause must either be indexed or low-cardinality.

Johan
  • 74,508
  • 24
  • 191
  • 319
-1

For me the first one seems the best since it's the easiest to read, but that obviously doesn't answer your question.

What you really have to do is generate execution plans for each of the queries and analyze them (I think most of the popular DBMS have a tool to do that). It will give you a cost value for each query.

If you can't do that I guess you could run the queries a bunch of times and compare the execution time.

Or is it something else entirely? Does it depend on the sizes of each table, or the complexity of the two conditions, or whether the data is sorted?

All of that and more.

pablochan
  • 5,625
  • 27
  • 42
-1

Like the commenters say, the best way to answer this question is to run the queries and measure.

However, in general, database engines optimize joins very, very efficiently - I'm pretty sure you will find almost no difference between the 3 queries, and it's entirely possible the query optimizers will turn them all into the same basic query (2 and 3 are equivalent as it is).

By far the biggest impact on the query will be the "child meet some condition" and "parent meets some condition" clauses. I'd concentrate on optimizing this bit.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • Not true. The `IN` vs `DISTINCT` will have a **VERY** big impact on processing time. – JNK Sep 23 '11 at 14:38
  • ah yes - DISTINCT is generally evil, esp. from a performance point of view. Hadn't spotted that little nugget...I do, however, think that the where clauses will have a big impact – Neville Kuyt Sep 23 '11 at 14:50