Querying a join (Which method is better?)

9 Jul 2006 12:30 PM

Bruce One

Hey folks, I have a quiz in my mind...

When u have a query that u have 3 ou 4 tables involved (tables), I figure
there r 2 solutions:

1) You write a SELECT for capturing fields in just one table, and then,
based on values of FKs fields, u then go gathering information (always based
on single SELECTs) in other tables.

2) You fire all in once 1 SELECT containing all the joins needed, bringing
all the fields needed.

Is 1st method so slower than second one? Currently I follow method 1, since
my framework I created to abstract data access does not work with joins...

9 Jul 2006 9:09 PM

Cowboy (Gregory A. Beamer)

When you get data, you are best to have filtering up front rather than later
selecting from a very large set of data. Whether you compile to a temp
location, use a view, or select with multiple joins, the filter that
eliminates the most data should be run first.

In most cases, I see no reason to denormalize into a temp location and
pulling from there, esp. if you have not filtered data into the temp
location. The main time to use temps is when you have too many joins and it
would be quicker to whittle down one of the joins into the temp location to
get it out of the way.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************

Show quote

"Bruce One" <r***@virtualsoftware.com.br> wrote in message
news:F7D4A04D-C0A0-450A-9D37-C544E2935008@microsoft.com...
> Hey folks, I have a quiz in my mind...
>
> When u have a query that u have 3 ou 4 tables involved (tables), I figure
> there r 2 solutions:
>
> 1) You write a SELECT for capturing fields in just one table, and then,
> based on values of FKs fields, u then go gathering information (always
> based
> on single SELECTs) in other tables.
>
> 2) You fire all in once 1 SELECT containing all the joins needed, bringing
> all the fields needed.
>
> Is 1st method so slower than second one? Currently I follow method 1,
> since
> my framework I created to abstract data access does not work with joins...
>