1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Darkstorm
  • 23
  • 4
  • Indexes can help you in regard. Here is more info http://stackoverflow.com/questions/2955459/what-is-an-index-in-sql – Coder221 Oct 16 '15 at 15:00

2 Answers2

1

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).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

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
Galma88
  • 2,398
  • 6
  • 29
  • 50