speed issue with query

I have this query -

SELECT distinct FIPS, NAME FROM table1.dbo.view1 WHERE state_abbreviation = 'NY' and FIPS not in (SELECT FIPS FROM table1.dbo.view2 WHERE State_Abbreviation = 'NY' and order in (12345))

It takes over a minute. The second view is big - like 8 million rows

the first query -

SELECT distinct FIPS, NAME FROM Table1.dbo.view1 WHERE state_abbreviation = 'NY'

and the second query -

SELECT FIPS FROM table1.dbo.view2 WHERE State_Abbreviation = 'NY' and order in (12345)

take less than a second each - but put them both together and ass the "not in" and it take about a minute and a half.

Any help would be greatly appreciated

 

While perhaps not the most elegant solution, you could do a lazy-dba's "caching" of the results of this query if it doesn't need to be performed in real time (though you could update the results multiple times in an hour/day).

insert into cache_table (select .....)

You could cache the query that pulls out records where state = "NY" into your temp table, and reference the cache table in your Not In sub-query for a potentially workable "hybrid" solution.

 

Thank you for the reply benajnim but I actually figured out a much faster way of doing it -

SELECT distinct * FROM table1.dbo.view1
left join (SELECT FIPS as fips1 FROM table1.dbo.view2
WHERE State_Abbreviation = 'NY' and order in (12345))t3 on
table1.dbo.view1.fips = t3.fips1
WHERE state_abbreviation = 'NY' and t3.fips1 is null

I had never used the left join before - but this only takes 1 second to run

Thanks again