CODEWITHSUNDEEP
sqlsql-serverperformance
Darkstorm
Darkstorm

Reputation: 23

SQL Server - improve performance of searching a values in table

I'm facing with problem in one query. The easiest will be to explain step by step:

At first I'm searching a specific values in colum1 in table1 by using query like this:

Query #1:

select column1 
from table1 
where column1 in('xxx','yyy','zzz')
group by column1
having count(*) >3

So now I have a list on values from column1, which occurs more than 3 times.

Then I need to use that list in where condition in another query:

select column1, column2, column3
from table1
where column1 in (query 1)

Unfortunately when I'm using query 1 as subquery, execution is really slow and I need to find a different way to this. Any suggest how can I increase a performance ?

Best regards and thank you in advance

Upvotes: 1

Views: 86

Answers (2)

Galma88
Galma88

Reputation: 2546

1)First of all take a look if the query is correctly indexed.

Maybe you have to add an index on column1.

2) try with it:

select column1, column2, column3
from table1 as T1 inner join (
                              select column1, column2, column3
                              from table1 
                              where column1 in (query 1)) as T2
                  on t1.column1 = t2.column1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If they are the same table, then use window functions:

select t.*
from (select t.*, count(*) over (partition by column1) as cnt
      from table1 t
      where column1 in ('xxx', 'yyy', 'zzz')
     ) t
where cnt > 3;

Both this an your original query will benefit from h having an index on table1(column1).

Upvotes: 1

Related Questions