SQL Server Query Performance Issue: Need Replacement of NOT EXISTS

Question

Could someone optimitize the performance of below General SQL Query:

select fn.column1 
from dbo.function1(input1) as fn
where (not exists (select 1 from table1 t1 
                   where fn.column1 = t1.column1) 
       and not exists (select 1 from table2 t2 
                       where fn.column1 = t2.column1))

For the above query, consider the approximate row count given below.

1. select fn.column1 from dbo.function1(input1) as fn -- returns 64000 records in 2 seconds.

2. table 1 (Column1) record-- returns 3000 records -- 1 second

3. table 2 (Column1) record-- returns 2000 records -- 1 second

So, if I run each select statement, it pulls and displays record in 1 or 2 seconds. But, if I run the full query, it takes more than a minute to display 64000 - (3000 + 2000) = 59000 records.

I tried the using EXCEPT like this:

 select fn.column1 
 from dbo.function1(input1) 

 except 

 (select column1 from dbo.table1 union select column1 from dbo.table2)

Nothing improves my performance. Same it takes a minute to display 59000 records. This is with the same case for "NOT IN" Scenario.

Also I noticed that if we use UNION, instead of EXCEPT in the above query, it returns 59K records in 2 seconds.

UPDATED:

The function (a bit complex) contains the below pseudocode

select column1, column2,...column6 
from dbo.table1 
inner join dbo.table2 
inner join ....
inner join dbo.table6 
inner join dbo.innerfunction1 
where <Condition 1>

UNION ALL

select column1, column2,...column6 
from dbo.table1 
inner join dbo.table2 
inner join ...
inner join dbo.table4 
inner join dbo.innerfunction2 
where (condition 2)

Assume that two inner functions has single table select statement

My question is: if I select the column from the function, it displays 64K records in 1 sec. But, if the whole query executed, it takes more than a minute.

[Please Note: This query need to be used in function]

Could any one help me to improve this?

Kindly let me know if you need more details or clarifications.

Regards, Viswa V.

Answer 1

Its a bit hard to optimize without data to play with. A fiddle would be good. Nonetheless here is an approach that may work. Create a temp table, index it then do the EXCEPT as follows:

SELECT
  fn.column1
INTO
  #temp  
FROM
  dbo.function1(input1) AS fn

CREATE NONCLUSTERED INDEX [temp_index] ON #temp 
(
    column1 ASC
)

SELECT
  column1
FROM
  #temp AS t
EXCEPT
(
  SELECT
    column1
  FROM
    dbo.table1
  UNION
  SELECT
    column1
  FROM
    dbo.table2
)

I would be interested in the result.