'joining' tables without actually grabbing the data?

Thanks lion, a treasure trove of info. It will take while to absorb it all.


"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:e967jd$5eo$1@forums.macromedia.com...
> In most cases, there is no answer as far as which of two equivalent
> queries will be faster. Generally, the SQL Server optimizer does a fine
> job of distilling even ugly queries to their simplest execution plan.
> That being said, there are some situations which tend to trick the
> optimizer into making bad decisions. Rather than try to figure it out
> yourself, the best thing you can do is write the query both ways, execute
> both in query analyzer with the 'show execution plan' option turned on,
> and see which one performs best.
>
> For SQL Server, generally speaking, a subquery is slower than a join, even
> a complicated join, and especially when it's a correlated subquery
> anywhere other than the FROM clause. So much so that SQL Server supports
> non-standard query constructs like UPDATE ... FROM that allow you to join
> a table to be updated with other tables and then use the result set to
> update the target table.
>
> The worst thing you can do from a performance standpoint is to use a
> "non-sargeable" expression in your WHERE clause, which usually prevents
> the optimizer from making use of your indexes. That's outside the scope
> of this forum. :)
>
> If you're interested, lots of good rules of thumb here:
> http://www.sql-server-performance.com/transact_sql.asp
> http://www.sql-server-performance.com/nb_query_process_tuning.asp
> http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp
>
> Those are the most helpful starter articles (query tuning, process tuning,
> and solving cursor-friendly problems without using T-SQL's notoriously
> slow cursor implementation), but the whole site is FULL of information
> about all kinds of ways to measure, tune, and tweak your SQL Server
> database. :)
>
>
>
> "crash" <crash@bcdcdigital.com> wrote in message
> news:e95rre$jre$1@forums.macromedia.com...
>>> Using IN with a subquery is another option. Test each to see which give
>>> you better performance for your actual query.
>>
>> Where can I find more information about this? The 'why's of why one
>> method is faster than another? I have some optimization issues i really
>> need to take care of.
>>
>
>

In most cases, there is no answer as far as which of two equivalent queries
will be faster. Generally, the SQL Server optimizer does a fine job of
distilling even ugly queries to their simplest execution plan. That being
said, there are some situations which tend to trick the optimizer into
making bad decisions. Rather than try to figure it out yourself, the best
thing you can do is write the query both ways, execute both in query
analyzer with the 'show execution plan' option turned on, and see which one
performs best.

For SQL Server, generally speaking, a subquery is slower than a join, even a
complicated join, and especially when it's a correlated subquery anywhere
other than the FROM clause. So much so that SQL Server supports
non-standard query constructs like UPDATE ... FROM that allow you to join a
table to be updated with other tables and then use the result set to update
the target table.

The worst thing you can do from a performance standpoint is to use a
"non-sargeable" expression in your WHERE clause, which usually prevents the
optimizer from making use of your indexes. That's outside the scope of this
forum. :)

If you're interested, lots of good rules of thumb here:
http://www.sql-server-performance.com/transact_sql.asp
http://www.sql-server-performance.com/nb_query_process_tuning.asp
http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp

Those are the most helpful starter articles (query tuning, process tuning,
and solving cursor-friendly problems without using T-SQL's notoriously slow
cursor implementation), but the whole site is FULL of information about all
kinds of ways to measure, tune, and tweak your SQL Server database. :)



"crash" <crash@bcdcdigital.com> wrote in message
news:e95rre$jre$1@forums.macromedia.com...
>> Using IN with a subquery is another option. Test each to see which give
>> you better performance for your actual query.
>
> Where can I find more information about this? The 'why's of why one
> method is faster than another? I have some optimization issues i really
> need to take care of.
>

> Using IN with a subquery is another option. Test each to see which give
> you better performance for your actual query.

Where can I find more information about this? The 'why's of why one method
is faster than another? I have some optimization issues i really need to
take care of.

> SELECT I.Item
> FROM dbo.FoodItem I
> INNER JOIN dbo.FoodType T ON I.TypeID=T.ID
> GROUP BY I.Item
> ORDER BY I.Item

Joe, Lionstone, Julian...THANKS! That gives me plenty to play with...

-Darrel

SELECT I.Item
FROM dbo.FoodItem I
INNER JOIN dbo.FoodType T ON I.TypeID=T.ID
GROUP BY I.Item
ORDER BY I.Item

You get a list of all items that have one or more types. You won't get all
pairings because of the GROUP BY - you get only the groups, in this case
consisting of only I.Item.

Using IN with a subquery is another option. Test each to see which give you
better performance for your actual query.


"darrel" <notreal@nowhere.com> wrote in message
news:e93l61$qdv$1@forums.macromedia.com...
> I'm not quite sure how to word this question, so I'll use an example:
>
> I have two tables:
>
> FOOD TYPE
> ---------------
> Type | ID
> ---------------
> Fruit | 1
> Meat | 2
> Grain | 3
>
>
> FOOD ITEM
> ---------------
> Item | TypeID
> ---------------
> Banana | 1
> Apple | 1
> pork | 2
> beef | 2
> ---------------
>
> What I want to do is grab all the items from table 1 that have one or more
> related items in table 2. So, in the above example, I want to return:
>
> Fruit
> Meat
>
> I can do a join, but then end up with something like this:
> Fruit, Banana
> Fruit, Apple
> Meat, pork
> Meat, beef
>
> Is there a way to accomplish this from within the SQL query itself?
> (Rather
> than having to loop through the duplicate records returned from the join).
>
> -Darrel
>
>
>

A solution is

select * from FoodTypes where TypeID in (select TypeID from FoodItems)

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

> Just ones where a record exists in FoodItem. Tested it in Access; it gets
> the results you want.

Right, but I think I want the opposite since there could be multiple records
in FoodItem that match the FoodType.

But, let me try it...I'll see if that works...

-Darrel

On Wed 12 Jul 2006 05:56:11p, darrel wrote in
macromedia.dreamweaver.appdev:

>> SELECT FoodType.Type
>> FROM FoodItem INNER JOIN FoodType ON FoodItem.TypeID=FoodType.ID
>> GROUP BY FoodType.Type;
>
> Won't that grab all the pairings?

Just ones where a record exists in FoodItem. Tested it in Access; it gets
the results you want.

> SELECT FoodType.Type
> FROM FoodItem INNER JOIN FoodType ON FoodItem.TypeID=FoodType.ID
> GROUP BY FoodType.Type;

Won't that grab all the pairings?

-Darrel

hehehe, if this works, it was actually what my suggestion was as well (joe
you might have suggested this to my query).

But doesn't it also grab too much info? I might have not been payign close
enough attention to your tables and what-not.


"Joe Makowiec" <makowiec@invalid.invalid> wrote in message
news:Xns97FEB0E148134makowiecatnycapdotrE@216.104.212.96...
> On Wed 12 Jul 2006 04:13:53p, darrel wrote in
> macromedia.dreamweaver.appdev:
>
>> Is there a way to accomplish this from within the SQL query itself?
>> (Rather than having to loop through the duplicate records returned
>> from the join).
>
> SELECT FoodType.Type
> FROM FoodItem INNER JOIN FoodType ON FoodItem.TypeID=FoodType.ID
> GROUP BY FoodType.Type;