SQL Query re-write help

Question

I need to rewrite the query below so that it is as efficient as possible. Any suggestions on how to increase the performance of this query? I plan on setting columns t2_1 and t2_2 to not nullable and assigning a default value, I'm mainly curious as to if/how I can modify the join so that I can move the "WHERE ... IN ... " sections there. Would it be possible to use an INNER JOIN instead?

SELECT t1_1 FROM t1
LEFT JOIN #t2 
ON t1.t1_3 = t2.t2_3
WHERE ISNULL (t2.t2_1, 54) in (SELECT ParsedValue FROM tf_ToTable(@IDList1))
AND ISNULL (t2.t2_2, 97) IN (SELECT ParsedValue FROM tf_ToTable(@IDList2))

Answer 1

If you make t1.t1_3 not nullable you can replace left join with inner join.

Answer 2

SELECT  t1_1
FROM    #t2 
JOIN    t1
ON      t1.t1_3 = t2.t2_3
WHERE   t2.t2_1 = @IDList1
        AND t2.t2_2 = @IDList2
UNION ALL
SELECT  t1_1
FROM    t1
WHERE   @IDList1 = 54
        AND @IDList2 = 97
        AND t1_3 NOT IN
        (
        SELECT  t2_3
        FROM    #t2
        )

Answer 3

To start out, if your @IDList1 and @IDList2 are comma separated strings, this query will not work at all. If they are table variables then you need to have a SELECT from the table variables for the IN to work.

Also, since you're posting this as a question on a public forum I'm going to hope that the table names and column names were changed to protect the innocent. Column names of t1_1, t1_2, etc. are pretty horrible as far as a naming convention goes.

I think that I misunderstood the question at first. Here are two possible solutions:

SELECT
     T1.t1_1
FROM
     T1
LEFT JOIN #t2 T2 ON
     T2.t2_3 = T1.t1_3
WHERE
     EXISTS
     (
          SELECT
               ParsedValue
          FROM
               tf_ToTable(@IDList1)
          WHERE
               ParsedValue = ISNULL(T2.t2_1, 54)
     ) AND
     EXISTS
     (
          SELECT
               ParsedValue
          FROM
               tf_ToTable(@IDList2)
          WHERE
               ParsedValue = ISNULL(T2.t2_2, 97)
     )

Or, if it's in the specific case of looking for the value in a delimited string:

SELECT
     T1.t1_1
FROM
     T1
LEFT JOIN #t2 T2 ON
     T2.t2_3 = T1.t1_3
WHERE
     ',' + @IDList1 + ',' LIKE '%,' + CAST(ISNULL(T2.t2_1, 54) AS VARCHAR) + ',%' AND
     ',' + @IDList2 + ',' LIKE '%,' + CAST(ISNULL(T2.t2_2, 97) AS VARCHAR) + ',%'

This last query assumes that you are using a comma as your delimiter and that there is no white space in the string. You can use the REPLACE function to get rid of spaces in the string if you need to do that.

Answer 4

You must be using Reporting Services to be able to provide a list like that, which means it's using dynamic SQL to work it out for you.

If you're making t2_1 and t2_2 disallow NULLs, then the only time they would be NULL would be because of the LEFT JOIN.

Also, consider whether t2_3 is unique, and whether t1_3 is a foreign key into that table, and whether t1_3 can allow NULLs. But I'm guessing this won't be the case, as #t2 is a temporary table.

If you can get rid of the ISNULL statements, then that will help.

And of course, don't forget indexing, and think about how big your tables are. There are so many factors involved here.

Answer 5

I'm thinking the query below and changing t2_1 and t2_2 to not nullable with default values will give me the best performance, what do you guys think? Wouldn't this allow for the best utilization of indexes?

SELECT t1_1 FROM t1
LEFT JOIN #t2 ON t1.t1_3 = t2.t2_3
INNER JOIN tf_ToTable(@IDList1) x_1 ON t2.t2_1 = x_1.ParsedValue 
INNER JOIN tf_ToTable(@IDList2) x_2 ON t2.t2_2 = x_2.ParsedValue