CODEWITHSUNDEEP
sqlsql-servert-sql
BitLagoon
BitLagoon

Reputation: 33

Subquery takes 67 seconds to process, using temp table is instantaneous

Running the following a query of the following form takes over 60 seconds to process.

SELECT *
FROM View_A
WHERE ( Column_1 IN (SELECT Column_1 FROM View_B WHERE Column_2 = 'my_test_value' OR Column_3 = 'my_test_value'))

Using a Temp Table the batch returns the same result in less than 1 second.

SELECT Column_1
INTO #temp1
FROM View_B
WHERE Column_2 = 'my_test_value' OR Column_3 = 'my_test_value'

SELECT *
FROM View_A
WHERE  Column_1 IN (SELECT * FROM #temp1)

Running the original query with either of the tests removed (no OR) results in a sub-second result. Also running the sub-query (SELECT Column_1 FROM View_B WHERE Column_2 = 'my_test_value' OR Column_3 = 'my_test_value') without the surrounding query is also sub-second in response.

I tried using UNION ALL but in some cases both column 2 and column 3 have the same value due to the architecture of the legacy application. As a result union all gives me duplicate rows. And some of the columns in view A are text so a straight union complains that it can not compare the column values.

Columns 1,2 & 3 are all indexed and the relationships between tables referenced in Views A & B are based on foreign key constraints.

Any insight on where to look next would be greatly appreciated. I need to get a concise SQL Query that performs like the temp table solution.

Upvotes: 0

Views: 65

Answers (1)

Dale K
Dale K

Reputation: 27202

Try using a UNION - OR is notorious for performing badly in certain queries.

As you have indicated that a text column is preventing the union from working, instead of using * (which is never best practice anyway), list your columns and convert any text columns to varchar(max).

SELECT Col1, Col2, convert(varchar(max),TextCol1) -- And so on
FROM View_A
WHERE (Column_1 IN (SELECT Column_1 FROM View_B WHERE Column_2 = 'my_test_value'))
union
SELECT Col1, Col2, convert(varchar(max),TextCol1) -- And so on
FROM View_A
WHERE (Column_1 IN (SELECT Column_1 FROM View_B WHERE Column_3 = 'my_test_value'));

As you have indicated that a union does improve the performance (to 9s), but that its still not fast enough, try a window function i.e. where you assign each row a row number based on whether it is unique or not (as I don't know your schema I don't know what that is, so you need to replace the ColUnique with the actual unique condition.

So the steps are:

  1. Obtain the data as fast as possible, now using a UNION ALL
  2. Add an outer query with a row number based on whatever makes a row unique
  3. Filter out any duplicate rows using a second outer query
    select ColUnique, Col1, Col2, Col3
    from (
      select ColUnique, Col1, Col2, Col3
        , row_number() over (partition by ColUnique order by ColUnique) row#
      from (
        SELECT ColUnique, Col1, Col2, Col3 -- and so on
        FROM View_A
        WHERE (Column_1 IN (SELECT Column_1 FROM View_B WHERE Column_2 = 'my_test_value'))
        union all
        SELECT ColUnique, Col1, Col2, Col3
        FROM View_A
        WHERE (Column_1 IN (SELECT Column_1 FROM View_B WHERE Column_3 = 'my_test_value')) 
      ) X
    ) Y
    where Y.row# = 1;

Upvotes: 1

Related Questions