0

I have what I call a 'Hamlet' query in the form:

select * from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
where
 (b.ToBe = 1 and a.Being in (select Being from stuffThatIs where stuffThatIs.Name = b.Name)
 or
 (b.ToBe <> 1 and a.Being not in (select Being from stuffThatIs where stuffThatIs.Name = b.Name)

The most-nested queries being identical, so I thought the database would reuse the result instead of doing 2 queries.

But in the execution plan the query shows twice with the same cost of 19%.

Can this be optimized manually? Or a hint added? I'd prefer a portable solution instead of a maximum perf one.

There are indexes on all named columns, sometimes even unique. The example is simplified, the whole where clause is in a join condition itself etc. So please optimize just the pattern, not the whole query.

0

Maybe this query would be better? We would check stuffThatIs for every row, and then validate if

  1. ToBe=1 and there is a.Being in stuffThatIs OR
  2. ToBe<>1 and there isn't a.Being in stuffThatIs
SELECT *
FROM someStuff a
LEFT JOIN otherStuff b ON (a.x = b.x)
OUTER APPLY (
  SELECT 1 [is_inside]
  WHERE EXISTS(SELECT *
               FROM stuffThatIs x
               WHERE x.Name = b.Name  
                 AND x.Being = b.Being) -- instead of "...a.Being in (select Being..."
) x
WHERE ((a.ToBe = 1 AND x.is_inside IS NOT NULL)
    OR (a.ToBe <> 1 AND x.is_inside IS NULL))
| improve this answer | |
0

I think this might work... unless you expect multiple duplicates from stuffthatis (in which case, try joining to a subquery of SELECT DISTINCT).

select A.*, B.*
from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
LEFT JOIN stuffthatis c on b.name = c.name

where
 (b.ToBe = 1 and a.Being = C.being)
 or
 (b.ToBe <> 1 and C.name is null)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.