Remove duplicate sub-query

Question

I have a complex SQL query that can be simplified to the below:

Select ColA,ColB,ColC,ColD
From MyTable
Where (ColA In (Select ItemID From Items Where ItemName like '%xxx%') 
    or ColB In (Select ItemID From Items Where ItemName like '%xxx%'))

As you can see, the sub-query appears twice. Is the compiler intelligent enough to detect this and gets the result of the sub-query only once? Or does the sub-query run twice?

FYI, table Items has about 20,000 rows and MyTable has about 200,000 rows.

Is there another way to re-write this SQL statement so that the sub-query appears/runs only once?

Update: The Where clause in the main query is dynamic and added only when needed (i.e. only when a user searches for 'xxx'). Hence changes to the main select statement or re-structuring of the query are not possible.

Deflagrate

Perhaps creating a (materialized) view is an option? – Kilo

As you are given a OR statement, the first sub-query runs and only if ColA is not there the second query runs. Anyway compiler is not intelligent enof to identify the logic of subquery – Pine

@WillemVanOnsem, in the original query, MyTable is actually a complex view. Making changes to that view is out of scope. – Deflagrate

If you do not want / are not allowed to change the query structurally: What kind of help do you expect? – Confess

Is there a better way to write the where clause? – Deflagrate

Answer 1

UPDATE Your request not to change the query, just the WHERE

You can pack the CTE directly in the place where it is called (untested):

Select ColA,ColB,ColC,ColD
From MyTable
Where EXISTS (SELECT 1 FROM (Select i.ItemID 
                             From Items AS i 
                             Where iItemName like '%xxx%') AS itm 
              WHERE itm.ItemID=MyTable.ColA OR itm.ItemID=MyTable.ColB) 

previous

I think this should be the same...

WITH MyCTE AS
(
    Select ItemID From Items Where ItemName like '%xxx%'
)
Select ColA,ColB,ColC,ColD
From MyTable
Where EXISTS (SELECT 1 FROM MyCTE WHERE ItemID=ColA OR ItemID=ColB) 

A substring LIKE search is - for sure - not performant.

If you can reduce your "Items" to just a few rows with your LIKE filter, you must test which is fastest.

Confess

Thanks! But the sub-query is part of dynamic SQL that is added only if required. The original query must stay intact. Only the part after the where clause can be changed. – Deflagrate

@navigator, This is a piece of information you really should give in your question... – Confess

Looks very promising... Am checking the solution in the original query. Reverting soon... – Deflagrate

FANTASTIC!!! The change reduced my query time from 45 seconds to 5 seconds. I wish I could give more points to this!!! Accepted Answer! – Deflagrate

@navigator, I'm glad to help you, happy coding! – Confess

Wouldn't the sub query in the EXISTS part even be simpler like this: ... Where iItemName like '%xxx%' AND ( ItemID = MyTable.ColA OR ItemID=MyTable.ColB ) ? – Larine

Answer 2

You can also write the query like this:

SELECT ColA, ColB, ColC, ColD
FROM MyTable
WHERE EXISTS(
  (SELECT ItemID FROM Items WHERE ItemName LIKE '%xxx%') 
  INTERSECT
  SELECT t.v FROM (VALUES (ColA), (ColB)) AS t(v) )

Corespondent

Answer 3

There is no guarantee that it will follow the actual execution order. It depends on how you write a query. Identical subqueries are normally only performed once. There is a WITH clause in standard SQL.

WITH mySubQuery AS
(
  [the subquery code]
)

SELECT * FROM
    mySubQuery AS sq
WHERE xyz IN (mySubQuery)

Corregidor

Answer 4

SQL programmers can use CTE (Common Table Expression) in such cases

You can define a CTE using the sub-query once and use it in the SQL statement by referencing more than once.

Please refer to SQL CTE Common Table Expression tutorial for samples CTE's are very powerful tools for SQL developers especially when used as recursive-queries

Kluge