Clever Geek Handbook

Optimize subquery usage to get MIN and MAX

Look for a better way to write this query, because at the moment I feel like the performance is not great. I am limited in what I can do on the database side, I cannot, for example, create better indexes on the tables I use - I am just a consumer of the data. The server that stores the data is MS SQL Server 2008R2.

So, in terms of context, what about the problem?

Link to SQL Fiddle!

This is my current request:

SELECT
  a.ID
  ,a.[GROUP]
  ,b.VALUE1
  ,b.VALUE2
  ,c.VALUE1
  ,c.VALUE2
FROM
  PRIMARY_TABLE a
  LEFT JOIN VALUE_TABLE b ON
    a.ID = b.ID
    AND a.[GROUP] = b.[GROUP]
    AND b.DEPTH = (
                 SELECT TOP 1 DEPTH 
                 FROM VALUE_TABLE
                 WHERE ID = a.ID AND [GROUP] = a.[GROUP]
                 ORDER BY DEPTH ASC
               )
  LEFT JOIN VALUE_TABLE c ON
    a.ID = c.ID
    AND a.[GROUP] = c.[GROUP]
    AND c.DEPTH = (
                 SELECT TOP 1 DEPTH 
                 FROM VALUE_TABLE
                 WHERE ID = a.ID AND [GROUP] = a.[GROUP]
                 ORDER BY DEPTH DESC
               )

To explain this a bit, I'm obviously trying to get the associated values ​​in VALUE_TABLE for MIN and MAX DEPTHS. VALUE_TABLE is of moderate size,> 1 million records, so I use TOP subqueries to prevent unnecessary table scans ... I wonder if I'm doing more harm than good though!

Seems gimmicky to do two left unions, I just can't think of a cleaner or faster way to do this at this time ... I'd love to hear your ideas!

Thanks in advance.

Some examples of PRIMARY and VALUE_TABLE data:

Declare @PRIMARY_TABLE Table([GROUP] varchar(25), [ID] integer, [TYPE] varchar(25));
Insert @PRIMARY_TABLE([GROUP], [ID], [TYPE]) Values
('TEST',123456,'WIDGET')

Declare @VALUE_TABLE Table([GROUP] varchar(25), [ID] integer, [DEPTH] integer, [VALUE1] float, VALUE2 float);
Insert @VALUE_TABLE([GROUP], [ID], [DEPTH], [VALUE1], [VALUE2]) Values
('TEST',123456,2,-89.7,314),
('TEST',123456,3,-89.6,26),
('TEST',123456,4,-89.7,155),
('TEST',123456,5,-89.6,357),
('TEST',123456,6,-89.4,349),
('TEST',123456,7,-89.5,351),
('TEST',123456,8,-89.1,356),
('TEST',123456,9,-89.3,2),
('TEST',123456,10,-89.3,2),
('TEST',123456,11,-89.3,1),
('TEST',123456,12,-89.2,359),
('TEST',123456,13,-89.1,352),
('TEST',123456,14,-89.2,330),
('TEST',123456,15,-89,339),
('TEST',123456,16,-88.5,332),
('TEST',123456,17,-88.8,329),
('TEST',123456,18,-88.4,320),
('TEST',123456,19,-88.5,318),
('TEST',123456,20,-88.3,317),
('TEST',123456,21,-87.8,310),
('TEST',123456,22,-88,311),
('TEST',123456,23,-87.7,304),
('TEST',123456,24,-87.4,305),
('TEST',123456,25,-87.4,301),
('TEST',123456,26,-87.4,303),
('TEST',123456,27,-87.4,306),
('TEST',123456,28,-87.2,299),
('TEST',123456,29,-87.4,301),
('TEST',123456,30,-87.3,302),
('TEST',123456,31,-87.4,299),
('TEST',123456,32,-87.3,298),
('TEST',123456,33,-87,293),
('TEST',123456,34,-87.3,300),
('TEST',123456,35,-87.3,302),
('TEST',123456,36,-87.3,301),
('TEST',123456,37,-87.2,298),
('TEST',123456,38,-87.3,297),
('TEST',123456,39,-87.5,294),
('TEST',123456,40,-87.3,296),
('TEST',123456,41,-87.2,299),
('TEST',123456,42,-87.1,296),
('TEST',123456,43,-87.1,300),
('TEST',123456,44,-87.2,296),
('TEST',123456,45,-87.2,297),
('TEST',123456,46,-87.1,293),
('TEST',123456,47,-87,293),
('TEST',123456,48,-87,297),
('TEST',123456,49,-87.1,298),
('TEST',123456,50,-87.2,291),
('TEST',123456,51,-87.3,297),
('TEST',123456,52,-86.9,293),
('TEST',123456,53,-87.1,298),
('TEST',123456,54,-87,292),
('TEST',123456,55,-87,297),
('TEST',123456,56,-86.8,297),
('TEST',123456,57,-86.9,296),
('TEST',123456,58,-86.8,295),
('TEST',123456,59,-86.9,295),
('TEST',123456,60,-86.8,296),
('TEST',123456,61,-86.6,299),
('TEST',123456,62,-86.5,291),
('TEST',123456,63,-86.6,294),
('TEST',123456,64,-86.8,293),
('TEST',123456,65,-86.8,296),
('TEST',123456,66,-86.7,291),
('TEST',123456,67,-86.7,299),
('TEST',123456,68,-86.7,293),
('TEST',123456,69,-86.7,295),
('TEST',123456,70,-86.7,294),
('TEST',123456,71,-86.8,296),
('TEST',123456,72,-86.5,297),
('TEST',123456,73,-86.6,297),
('TEST',123456,74,-86.5,294),
('TEST',123456,75,-86.4,298),
('TEST',123456,76,-86.5,299),
('TEST',123456,77,-86.4,297),
('TEST',123456,78,-86.3,300),
('TEST',123456,79,-86.4,300),
('TEST',123456,80,-86.7,295),
('TEST',123456,81,-86.6,299),
('TEST',123456,82,-86.6,300),
('TEST',123456,83,-86.6,299),
('TEST',123456,84,-86.5,300),
('TEST',123456,85,-86.4,299),
('TEST',123456,86,-86.3,296),
('TEST',123456,87,-86.2,300),
('TEST',123456,88,-86.2,300),
('TEST',123456,89,-86.2,297),
('TEST',123456,90,-86.1,301),
('TEST',123456,91,-86.3,301),
('TEST',123456,92,-86.2,300),
('TEST',123456,93,-86,301),
('TEST',123456,94,-86.2,302),
('TEST',123456,95,-86.2,301),
('TEST',123456,96,-86.2,304),
('TEST',123456,97,-86.2,303),
('TEST',123456,98,-86,305),
('TEST',123456,99,-86.1,301),
('TEST',123456,100,-86.1,305),
('TEST',123456,101,-86.2,302),
('TEST',123456,102,-86,304),
('TEST',123456,103,-86,303),
('TEST',123456,104,-86.1,307),
('TEST',123456,105,-86.1,306),
('TEST',123456,106,-86.1,305),
('TEST',123456,107,-86.2,302),
('TEST',123456,108,-86.2,307),
('TEST',123456,109,-86.3,309),
('TEST',123456,110,-86.3,303),
('TEST',123456,111,-86.4,304),
('TEST',123456,112,-86.4,308),
('TEST',123456,113,-86.3,310),
('TEST',123456,114,-86.4,304),
('TEST',123456,115,-86.5,307),
('TEST',123456,116,-86.3,308),
('TEST',123456,117,-86.3,309),
('TEST',123456,118,-86.3,306),
('TEST',123456,119,-86.2,311),
('TEST',123456,120,-86,308),
('TEST',123456,121,-85.9,308),
('TEST',123456,122,-86.1,307),
('TEST',123456,123,-86.1,308),
('TEST',123456,124,-86,310),
('TEST',123456,125,-86.2,305),
('TEST',123456,126,-86.1,312),
('TEST',123456,127,-86.2,308),
('TEST',123456,128,-86.1,309),
('TEST',123456,129,-86.1,309),
('TEST',123456,130,-86.2,310),
('TEST',123456,131,-86.5,306),
('TEST',123456,132,-86.5,311),
('TEST',123456,133,-86.6,306),
('TEST',123456,134,-86.8,314),
('TEST',123456,135,-86.7,314),
('TEST',123456,136,-86.8,312),
('TEST',123456,137,-86.7,315),
('TEST',123456,138,-86.9,311),
('TEST',123456,139,-86.8,316),
('TEST',123456,140,-86.9,312),
('TEST',123456,141,-86.9,312),
('TEST',123456,142,-86.7,314),
('TEST',123456,143,-86.7,317),
('TEST',123456,144,-86.7,316),
('TEST',123456,145,-86.7,316),
('TEST',123456,146,-86.8,318),
('TEST',123456,147,-86.8,316),
('TEST',123456,148,-86.8,317),
('TEST',123456,149,-87,317),
('TEST',123456,150,-86.9,315),
('TEST',123456,151,-86.8,321),
('TEST',123456,152,-86.9,319),
('TEST',123456,153,-86.9,320),
('TEST',123456,154,-86.7,318),
('TEST',123456,155,-86.6,316),
('TEST',123456,156,-86.7,317),
('TEST',123456,157,-86.8,319),
('TEST',123456,158,-86.8,317),
('TEST',123456,159,-86.7,318),
('TEST',123456,160,-86.8,316),
('TEST',123456,161,-86.8,317),
('TEST',123456,162,-86.7,320),
('TEST',123456,163,-86.5,318),
('TEST',123456,164,-86.6,315),
('TEST',123456,165,-86.9,316),
('TEST',123456,166,-86.8,317),
('TEST',123456,167,-86.9,316),
('TEST',123456,168,-86.9,317),
('TEST',123456,169,-87,321),
('TEST',123456,170,-87,316),
('TEST',123456,171,-87.1,318),
('TEST',123456,172,-86.9,316),
('TEST',123456,173,-86.9,310),
('TEST',123456,174,-87,315),
('TEST',123456,175,-87,312),
('TEST',123456,176,-87.2,309),
('TEST',123456,177,-87.5,315),
('TEST',123456,178,-87.2,313),
('TEST',123456,179,-87.5,312),
('TEST',123456,180,-87.3,308),
('TEST',123456,181,-87.3,308),
('TEST',123456,182,-87.3,313)

And the expected results:

123456, TEST, 'WIDGET', -89.7, 314, -87.3, 313
+3


source to share


2 answers


Try something like this ...

;WITH C1 AS
 (
  SELECT VT.*
        ,ROW_NUMBER() OVER (ORDER BY VT.DEPTH ASC)  rn1
        ,ROW_NUMBER() OVER (ORDER BY VT.DEPTH DESC) rn2
  FROM VALUE_TABLE VT INNER JOIN PRIMARY_TABLE a
  ON VT.ID = a.ID AND VT.[GROUP] = a.[GROUP] 
 )
SELECT a.ID
      ,a.[GROUP]
      ,b.VALUE1
      ,b.VALUE2
      ,c.VALUE1
      ,c.VALUE2
FROM PRIMARY_TABLE a 
LEFT JOIN C1 b ON b.ID      = a.ID 
              AND b.[GROUP] = a.[GROUP]
              AND b.rn1     = 1
LEFT JOIN C1 c ON c.ID      = a.ID      
              AND c.[GROUP] = a.[GROUP]
              AND c.rn2     = 1

Result



╔════════╦═══════╦════════╦════════╦════════╦════════╗
║   ID   ║ GROUP ║ VALUE1 ║ VALUE2 ║ VALUE1 ║ VALUE2 ║
╠════════╬═══════╬════════╬════════╬════════╬════════╣
║ 123456 ║ TEST  ║ -89.7  ║    314 ║ -87.3  ║    313 ║
╚════════╩═══════╩════════╩════════╩════════╩════════╝

SQL FIDDLE

+2


source


Try the following:

;WITH min_max AS(
    SELECT
        [GROUP],
        [ID],
        MIN_DEPTH = MIN(DEPTH),
        MAX_DEPTH = MAX(DEPTH)
    FROM @VALUE_TABLE
    GROUP BY [GROUP], [ID]
)
SELECT
    p.ID,
    p.[GROUP],
    p.TYPE,
    VALUE1 = MAX(CASE WHEN v.DEPTH = m.MIN_DEPTH THEN v.VALUE1 END),
    VALUE2 = MAX(CASE WHEN v.DEPTH = m.MIN_DEPTH THEN v.VALUE2 END),
    VALUE1 = MAX(CASE WHEN v.DEPTH = m.MAX_DEPTH THEN v.VALUE1 END),
    VALUE2 = MAX(CASE WHEN v.DEPTH = m.MAX_DEPTH THEN v.VALUE2 END)
FROM @PRIMARY_TABLE p
LEFT JOIN min_max m
    ON m.ID = p.ID
    AND m.[GROUP] = p.[GROUP]
LEFT JOIN @VALUE_TABLE v
    ON v.ID = m.ID
    AND v.[GROUP] = m.[GROUP]
GROUP BY p.[GROUP], p.ID, p.[TYPE]


EDIT: Moved cross tab to CTE

;WITH min_max AS(
    SELECT
        m.[GROUP],
        m.ID,
        MIN_VALUE1 = MAX(CASE WHEN v.DEPTH = m.MIN_DEPTH THEN v.VALUE1 END),
        MIN_VALUE2 = MAX(CASE WHEN v.DEPTH = m.MIN_DEPTH THEN v.VALUE2 END),
        MAX_VALUE1 = MAX(CASE WHEN v.DEPTH = m.MAX_DEPTH THEN v.VALUE1 END),
        MAX_VALUE2 = MAX(CASE WHEN v.DEPTH = m.MAX_DEPTH THEN v.VALUE2 END)
    FROM(
        SELECT
            [GROUP],
            [ID],
            MIN_DEPTH = MIN(DEPTH),
            MAX_DEPTH = MAX(DEPTH)
        FROM @VALUE_TABLE
        GROUP BY [GROUP], [ID]
    )m
    INNER JOIN @VALUE_TABLE v
        ON v.ID = m.ID
        AND v.[GROUP] = m.[GROUP]
    GROUP BY m.[GROUP], m.ID
)
SELECT
    p.ID,
    p.[GROUP],
    p.TYPE,
    MIN_VALUE1,
    MIN_VALUE2,
    MAX_VALUE1,
    MAX_VALUE2
FROM @PRIMARY_TABLE p
LEFT JOIN min_max m
    ON m.ID = p.ID
    AND m.[GROUP] = p.[GROUP]
+1


source






More articles:


All Articles