sql server - SQL - Avoid an additional GROUP BY (and improving query performance) -

sql server - SQL - Avoid an additional GROUP BY (and improving query performance) -

i stuck on solving issue , should nice hearing new fresh ideas :)

i have table billions of records this

tab_ix (int) (pk) tab_id (int) (pk) pr_id (int) (pk) sp_id (int) (pk)(ix) ....

previously retrieving info this

select tab_id, count (sp_id) hits table t inner bring together table_sp s on t.sp_id = s.id tab_ix = @tab_inx , pr_id in (select pr_id @pr_id) , s.name in (select distinct name @sp_names) grouping tab_id

table_sp little table 10k of records (id (int) (pk), name (varchar) (ix))

@pr_id , @sp_names table variables 1 column

the query fast (about 2-3 sec); don't want distinguish records different pr_id , same tab_ix, tab_id, sp_id

so illustration records like

tab_ix - tab_id - pr_id - sp_id 1 - 700 - 1 - 100 1 - 700 - 2 - 100

should considered one.

the way seems doing additional grouping by

like this

select tab_id, count(sp_id) hits ( select tab_id, sp_id, count (pr_id) table tab_ix = @tab_inx , pr_id in (select pr_id @pr_id) , s.name in (select distinct name @sp_names) grouping tab_id, sp_id) dummy grouping tab_id

the problem performance, because adding additional grouping operation looks painful.

do have thought improving query?

thanks in advance :)

i suppose specifying in original query want count distinct sp_id trick

select tab_id, count (distinct sp_id) hits table t inner bring together table_sp s on t.sp_id = s.id tab_ix = @tab_inx , pr_id in (select pr_id @pr_id) , s.name in (select distinct name @sp_names) grouping tab_id

sql sql-server group-by sql-tuning