sql server - SQL optimization question -

let's start scenario defined in my previous question.

now want create query generates list of foos f1 , count of foos f2 distinct f1 nevertheless associated same bar or baz f1 associated to:

select    f1.*,           case             when f1.bar_id not null               isnull(bar.lotnumber + '-', '') + bar.itemnumber             when f2.baz_id not null               isnull(baz.color + ' ', '') + baz.type           end 'ba?description',           (select count(*)              foo f2             f2.bar_id = f1.bar_id            or     f2.baz_id = f1.baz_id) - 1 foocount      foo f1 left join bar on bar.bar_id = f1.bar_id left join baz on baz.baz_id = f1.baz_id 

what worries me efficiency. must admit know nothing regarding how sql server generates execution plans sql sentences, common sense tells me subquery executed once each row in main query, i.e., once each value of f1.foo_id. not efficient.

an alternative not run problem is...

select    f1.*,           case             when f1.bar_id not null               isnull(bar.lotnumber + '-', '') + bar.itemnumber             when f2.baz_id not null               isnull(baz.color + ' ', '') + baz.type           end 'ba?description',           count(*) - 1 foocount      foo f1 left join bar    on bar.bar_id = f1.bar_id left join baz    on baz.baz_id = f1.baz_id left join foo f2 on f2 .bar_id = f1.bar_id                  or f2 .baz_id = f1.baz_id group  f1.foo_id, f1.somefoofield, f1.someotherfield, ...,           case             when f1.bar_id not null               isnull(bar.lotnumber + '-', '') + bar.itemnumber             when f2.baz_id not null               isnull(baz.color + ' ', '') + baz.type           end 

but worse, since runs bigger problem related fact sql databases not true relational databases. if sql databases relational, sql engines able infer value of every field not affected aggregate function uniquely determined f1.foo_id. thus, group f1.foo_id should sufficient produce desired result. sql still forces me explicitly group by every field not affected aggregate function. result? inefficiency.

a third alternative not run of 2 previous problems is...

select    foo.*,           case             when foo.bar_id not null               isnull(bar.lotnumber + '-', '') + bar.itemnumber             when foo.baz_id not null               isnull(baz.color + ' ', '') + baz.type           end 'ba?description',           isnull(temp.foocount, 0) foocount      foo left join bar on bar.bar_id = foo.bar_id left join baz on baz.baz_id = foo.baz_id left join (select   f1.foo_id, count(*) - 1 foocount                foo f1            join     foo f2 on f2.bar_id = f1.bar_id                            or f2.baz_id = f1.baz_id            group f1.foo_id) temp on temp.foo_id = foo.foo_id 

but has disadvantage of requiring instantiation of three copies of foo in memory, not two.

how should structure query produce desired result in efficient way possible?

i agree comments stating can find out trying. in other post have no test data available. guess don't know how generate test data. i'll show you.

i assume following tables exist:

create table bar (     bar_id int not null primary key,     lotnumber varchar(10),     itemnumber varchar(10) )  create table baz (     baz_id int not null primary key,     color varchar(10),     type varchar(10) )  create table foo (     foo_id int not null primary key,     bar_id int null references bar,     baz_id int null references baz,     somefoofield varchar(10),     someotherfoofield varchar(10) ) 

now populate bar test data:

insert bar (bar_id) values (0) insert bar (bar_id) select bar_id + 1 bar insert bar (bar_id) select bar_id + 2 bar insert bar (bar_id) select bar_id + 4 bar insert bar (bar_id) select bar_id + 8 bar insert bar (bar_id) select bar_id + 16 bar insert bar (bar_id) select bar_id + 32 bar insert bar (bar_id) select bar_id + 64 bar -- etc.   update bar set      lotnumber = 'ln_' + convert(varchar(10), bar_id),      itemnumber = 'in_' + convert(varchar(10), bar_id) 

populate baz:

insert baz (baz_id) values (0) insert baz (baz_id) select baz_id + 1 baz insert baz (baz_id) select baz_id + 2 baz insert baz (baz_id) select baz_id + 4 baz insert baz (baz_id) select baz_id + 8 baz insert baz (baz_id) select baz_id + 16 baz insert baz (baz_id) select baz_id + 32 baz -- etc  update baz set      color = 'c_' + convert(varchar(10), baz_id),      type = 't_' + convert(varchar(10), baz_id) 

and put data in foo

insert foo (foo_id) values (0) insert foo (foo_id) select foo_id + 1 foo insert foo (foo_id) select foo_id + 2 foo insert foo (foo_id) select foo_id + 4 foo insert foo (foo_id) select foo_id + 8 foo insert foo (foo_id) select foo_id + 16 foo insert foo (foo_id) select foo_id + 32 foo insert foo (foo_id) select foo_id + 64 foo insert foo (foo_id) select foo_id + 128 foo insert foo (foo_id) select foo_id + 256 foo -- etc...  update foo set      somefoofield = 'sff_' + convert(varchar(10), foo_id),      someotherfoofield = 'soff_' + convert(varchar(10), foo_id)  update foo set bar_id = bar.bar_id     bar      foo_id % 128 = bar.bar_id         , foo_id % 3 = 0;  update foo set baz_id = baz.baz_id     baz      foo_id % 64 = baz.baz_id         , foo_id % 3 <> 0 

before run queries , test execution speed, make sure have indexes created:

create index foo_baz on foo(baz_id) create index foo_bar on foo(bar_id) 

now can test queries. suggest try 1 too:

select f.foo_id, f.*,     isnull(r.bardescription, z.bazdescription) 'ba?description',      isnull(r.foocount, z.foocount) - 1 foocount foo f left join (     select f.bar_id,          isnull(bar.lotnumber + '-', '') + bar.itemnumber 'bardescription',         count(f.foo_id) foocount      foo f, bar     f.bar_id = bar.bar_id     group f.bar_id, bar.lotnumber, bar.itemnumber ) r on f.bar_id = r.bar_id left join (     select f.baz_id,         isnull(baz.color + '-', '') + baz.type 'bazdescription',         count(f.foo_id) foocount     foo f, baz     f.baz_id = baz.baz_id     group f.baz_id, baz.color, baz.type ) z on f.baz_id = z.baz_id 

in old version of sql query analyzer there option 'display generated execution plan'. version have option probably. shows above query run faster 3 queries suggested. theory! fill tables data think have in production system , try.