CODEWITHSUNDEEP
sqlsql-server
throwaway123
throwaway123

Reputation: 33

Many left joins on subqueries, need some way to increase performance

Below is an example of my query as it stands. I have at most, approximately 10 of these joins/subqueries all of basically the same format, but with different joins and where clauses.

SELECT DISTINCT mytable.label, tableA.counter, tableB.counter
FROM mytable
LEFT JOIN 
    (SELECT COUNT(id) as counter, label
        FROM mytable
        ...joins...
        ...where...
        GROUP BY label) tableA 
    ON tableA.label=mytable.label
LEFT JOIN
    (SELECT COUNT(id) as counter, label
        FROM mytable
        ...joins...
        ...where...
        GROUP BY label) tableB 
    ON tableB.label=mytable.label
...

It's taking about 2-4 seconds and this is a high-traffic page, so that kind of speed isn't good enough. Can anyone recommend a way to improve performance here?

Upvotes: 1

Views: 47

Answers (2)

Maciej Los
Maciej Los

Reputation: 8591

In addition to Jon Tirjan's solution, i'd share another one via using UNION and PIVOT table.

SELECT [A], [B]
FROM (
    SELECT 'A' AS TableName, COUNT(id) as counter
    FROM tableA 
    UNION ALL
    SELECT 'B' AS TableName, COUNT(id) as counter
    FROM tableB
) AS DT
PIVOT(SUM(counter) FOR TableName IN([A], [B])) AS PVT

Upvotes: 0

Jon Tirjan
Jon Tirjan

Reputation: 3694

No need to GROUP here, as you're only returning 1 value. Try a subquery approach like this:

SELECT DISTINCT T.label,
  (SELECT COUNT(id) as counter FROM tableA A WHERE A.blah = T.blah) as AValue,
  (SELECT COUNT(id) as counter FROM tableB B WHERE B.blah = T.blah) as BValue
FROM mytable T

Upvotes: 2

Related Questions