CODEWITHSUNDEEP
sqlsql-serversubquery
rage8885
rage8885

Reputation: 417

Optimizing a query with many subqueries

I need to query data from two tables, one is a parent (batches) and the other a child (details), where I filter on the parent and want to return all items of the parent based on a where clause, but selectively filter different aspects of the child table into summary columns for the parent result set.

With that said, here is the query I have now that works: (sorry for the length, I stripped out some stuff that does not matter but wanted to keep the subqueries)

SELECT
  workid,
  siteid,
  (
    SELECT
      top 1 description
    FROM
      worksrcdesc a
    WHERE
      a.workid = b.workid
    AND
      a.site = b.site
    ORDER BY
      date DESC
  ) AS descript,
  itemid,
  mode,
  systemdate,
  (
    SELECT
      count(sequence)
    FROM
      details a
    WHERE
      a.reject = 0
    AND
      a.itemid = b.itemid
    AND
      a.siteid = b.siteid
    AND
      documenttype IN (1,2)
  ) AS documentcount,
  (
    SELECT
      count(sequence)
    FROM
      details a
    WHERE
      a.reject = 0
    AND
      a.itemid = b.itemid
    AND
      a.siteid = b.siteid
    AND
      documenttype = 15
  ) AS othercount,
  (
    SELECT
      count(distinct tranno)
    FROM
      details a
    WHERE
      a.reject = 0
    AND
      a.itemid = b.itemid
    AND
      a.siteid = b.siteid
  ) AS transactioncount,
  (
    SELECT
      top 1 groupnum
    FROM
      groups d
    WHERE
      b.siteid = d.siteid
    AND
      b.workid = d.workid
  ) AS sitegrouping
FROM
  batches b with (nolock)
WHERE
  workid IN (12345,67980)
ORDER BY
  workid ASC,
  itemid ASC

The batches table has 54,000 rows, which filtering by workid limits down to just 300 entries. The details table has 3.3 million rows, which gets filtered down to around 76,000 rows based on the same criteria used on batches.

Right now this takes over 4-5 seconds to run on average; is there any way to simplify this to get it running faster? I thought about doing joins instead of subqueries, but what throws me off (and I cannot get it working) is the different where clauses on the subqueries.

Upvotes: 0

Views: 174

Answers (1)

James Z
James Z

Reputation: 12318

At least you could combine your subqueries into one outer apply:

outer apply (
    SELECT
      sum(case when documenttype IN (1,2) then 1 else 0 end) as documentcount,
      sum(case when documenttype = 15 then 1 else 0 end) as othercount,
      count(distinct tranno) as transactioncount
    FROM
      details a
    WHERE
      a.reject = 0 AND
      a.itemid = b.itemid AND
      a.siteid = b.siteid
   )

If sequence can be null, the you might need to check it in the sum clause too.

Also indexing is important, especially if the number of rows that match is high, so that time isn't spent on key lookups. Most likely you want to have index with itemid, siteid and reject + documenttype and tranno as included columns. This of course needs consideration if the table is updated heavily.

Upvotes: 1

Related Questions