CODEWITHSUNDEEP
sqlsql-server
banana
banana

Reputation: 613

Efficient query for counting multiple relationships of multiple entities

Using MS SQL Server, I'd like to count the number of entities to which each of several entities is related. Let's say my schema looks something like this:

foo (
  id UNIQUEIDENTIFIER NOT NULL,
)

thing1 (
  id UNIQUEIDENTIFIER NOT NULL
)

thing2 (
  id UNIQUEIDENTIFIER NOT NULL
)

thingN (
  id UNIQUEIDENTIFIER NOT NULL
)

with join tables

foo_thing1 (
  foo_id UNIQUEIDENTIFIER NOT NULL,
  thing1_id UNIQUEIDENTIFIER NOT NULL
)

etc.

there might be 2 thingNs, or 10, or some other number- I want something that works well even when there are quite a few. If there was just one Foo, a possible solution looks like this:

SELECT COUNT(DISTINCT foo_thing1.thing1_id),
       COUNT(DISTINCT foo_thing2.thing2_id),
       COUNT(DISTINCT foo_thing3.thing3_id),
       COUNT(DISTINCT foo_thing4.thing4_id,
FROM foo
LEFT JOIN foo_thing1 ON foo_thing1.thing1_id = thing1.id
LEFT JOIN foo_thing2 ON foo_thing2.thing2_id = thing2.id
LEFT JOIN foo_thing3 ON foo_thing3.thing3_id = thing3.id
LEFT JOIN foo_thing4 ON foo_thing4.thing4_id = thing4.id
GROUP BY foo.id
HAVING foo.id = '12341234-1234-12341234-1234'

However, I'm not sure this is efficient. What if the Foo is related to a lot of thing2s and a lot of thing3s - am I getting far too many rows, and then hiding it with DISTINCT?

Further complicating things, I actually want to count the relationships of more than one foo at once. Ideally I'd like to do it by arbitrary where clause, but if necessary I can just supply a list of ids such that WHERE/HAVING foo.id IN (x, y, z). From there, though, how should I determine all the cardinalities? :(

UNION ALL is one option, since my goal in combining these queries is to save database roundtrip latency. But I suspect that the parse time of multiple SELECTs means it can't be the most efficient way...

Upvotes: 0

Views: 162

Answers (1)

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10703

This should work the way you expect:

SELECT foo.id,
    (SELECT count(*) FROM foo_thing1 WHERE foo_thing1.foo_id = foo.id),
    (SELECT count(*) FROM foo_thing2 WHERE foo_thing2.foo_id = foo.id),
    (SELECT count(*) FROM foo_thing3 WHERE foo_thing3.foo_id = foo.id),
    ...
FROM foo
WHERE foo.id IN (1, 2, 3, 7, ...);

Upvotes: 1

Related Questions