StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I agree with the comments stating that you can only find out by trying. In your other post you say that you have no test data available. So my guess is you don't know how to generate test data. I'll show you.</p> <p>I assume the following tables exist:</p> <pre><code>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) ) </code></pre> <p>Now populate Bar with test data:</p> <pre><code>insert into Bar (Bar_ID) values (0) insert into Bar (Bar_ID) select Bar_ID + 1 from Bar insert into Bar (Bar_ID) select Bar_ID + 2 from Bar insert into Bar (Bar_ID) select Bar_ID + 4 from Bar insert into Bar (Bar_ID) select Bar_ID + 8 from Bar insert into Bar (Bar_ID) select Bar_ID + 16 from Bar insert into Bar (Bar_ID) select Bar_ID + 32 from Bar insert into Bar (Bar_ID) select Bar_ID + 64 from Bar -- etc. update Bar set LotNumber = 'LN_' + convert(varchar(10), Bar_ID), ItemNumber = 'IN_' + convert(varchar(10), Bar_ID) </code></pre> <p>Populate Baz:</p> <pre><code>insert into Baz (Baz_ID) values (0) insert into Baz (Baz_ID) select Baz_ID + 1 from Baz insert into Baz (Baz_ID) select Baz_ID + 2 from Baz insert into Baz (Baz_ID) select Baz_ID + 4 from Baz insert into Baz (Baz_ID) select Baz_ID + 8 from Baz insert into Baz (Baz_ID) select Baz_ID + 16 from Baz insert into Baz (Baz_ID) select Baz_ID + 32 from Baz -- etc update Baz set Color = 'C_' + convert(varchar(10), Baz_ID), Type = 'T_' + convert(varchar(10), Baz_ID) </code></pre> <p>and put some data in Foo</p> <pre><code>insert into Foo (Foo_ID) values (0) insert into Foo (Foo_ID) select Foo_ID + 1 from Foo insert into Foo (Foo_ID) select Foo_ID + 2 from Foo insert into Foo (Foo_ID) select Foo_ID + 4 from Foo insert into Foo (Foo_ID) select Foo_ID + 8 from Foo insert into Foo (Foo_ID) select Foo_ID + 16 from Foo insert into Foo (Foo_ID) select Foo_ID + 32 from Foo insert into Foo (Foo_ID) select Foo_ID + 64 from Foo insert into Foo (Foo_ID) select Foo_ID + 128 from Foo insert into Foo (Foo_ID) select Foo_ID + 256 from 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 from Bar where Foo_ID % 128 = Bar.Bar_ID and Foo_ID % 3 = 0; update Foo set Baz_ID = Baz.Baz_ID from Baz where Foo_ID % 64 = Baz.Baz_ID and Foo_ID % 3 <> 0 </code></pre> <p>Before you run the queries and test their execution speed, make sure you have some indexes created:</p> <pre><code>create index Foo_Baz on Foo(Baz_ID) create index Foo_Bar on Foo(Bar_ID) </code></pre> <p>Now you can test your queries. I suggest you try this one too:</p> <pre><code>select F.Foo_id, F.*, isNull(R.barDescription, Z.bazDescription) as 'Ba?Description', isnull(R.fooCount, Z.fooCount) - 1 as fooCount from Foo F left join ( select F.Bar_ID, ISNULL(Bar.LotNumber + '-', '') + Bar.ItemNumber as 'BarDescription', count(F.Foo_id) as FooCount from Foo F, Bar where F.Bar_id = Bar.Bar_id group by 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 as 'BazDescription', count(F.Foo_id) as FooCount from Foo F, Baz where F.Baz_id = Baz.Baz_id group by F.Baz_id, Baz.Color, Baz.Type ) Z on F.Baz_ID = Z.Baz_ID </code></pre> <p>In my old version of SQL Query Analyzer there is an option to 'Display the generated execution plan'. Your version will have that option too probably. It shows that the above query will run faster than the 3 queries you suggested. But that is theory! So fill your tables with as much data as you think it will have in the production system and try.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload