Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From: "Bob Barrows" <reb01501@NOSPAMyahoo.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Explicit IN clause vs JOIN.  Plan tanks with Join, Why?
Date: Fri, 30 Sep 2011 07:45:13 -0400
Organization: A noiseless patient Spider
Lines: 43
Message-ID: <j64a54$vte$1@dont-email.me>
References: <36300203-0fcb-4e25-b476-210034ac5f07@db5g2000vbb.googlegroups.com>
Injection-Date: Fri, 30 Sep 2011 11:45:41 +0000 (UTC)
Injection-Info: mx04.eternal-september.org; posting-host="inlgCTpOMxujS+VHkVo6dA"; logging-data="32686"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19E1OfbEaZi6jIQ3/1JLJx6Q6WttT6Pmnk="
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109
X-RFC2646: Format=Flowed; Original
X-Antivirus-Status: Clean
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-Antivirus: avast! (VPS 110930-0, 09/30/2011), Outbound message
Cancel-Lock: sha1:Pb5wlo+XOBQkhyx1MVSdphpKja8=
X-Priority: 3
X-MSMail-Priority: Normal
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:689

bill wrote:
> I try to generall supply real DDL and sample data, but the problem I
> have only shows up in certain cases, with SELECTS against very large
> tables and I can't supply enough sample data.
>
> I have a view (some_view) that joins about eight tables.   A query
> like this:
>
> SELECT
> *
> FROM
> some_view
> WHERE some_column IN ('x', 'y', 'z')
>
> Is blazingly fast and yields a very good plan (all SEEKs, no SCANs).
>
> But I don't want to build an IN list, and instead would rather join to
> table that has the "some_column" values for which I want to filter the
> SELECT on the view.  The new query would thus look like this:
>
> SELECT
> *
> FROM
> some_view
> INNER JOIN
> some_table
> ON some_view.some_column = some_table.some_column
>
> some_table has a primary key on some_column.  With the same three
> values in "some_table" the query goes to hell.  The plan ends up
> SCANing 7 of the 8 tables and it takes forever to return data.
>
> Does anyone have some general pointers for me?  I know it's difficult
> without sample data, but if you have some general ideas, I will try
> them.
>

Have you run it through the tuning advisor? You failed to mention what 
version of SS you are using so I wasn't sure what to call it.
If that fails to give you any hints, I think you're going to have to post 
the execution plan.