Groups > comp.databases.ms-sqlserver > #689

Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why?

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> (permalink)
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

Show key headers only | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Explicit IN clause vs JOIN.  Plan tanks with Join, Why? bill <billmaclean1@gmail.com> - 2011-09-30 00:24 -0700
  Re: Explicit IN clause vs JOIN.  Plan tanks with Join, Why? "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-09-30 07:45 -0400
  Re: Explicit IN clause vs JOIN.  Plan tanks with Join, Why? Hugo Kornelis <hugo@perFact.REMOVETHIS.info.INVALID> - 2011-09-30 21:27 +0200
  Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? Erland Sommarskog <esquel@sommarskog.se> - 2011-09-30 23:49 +0200
    Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? bill <billmaclean1@gmail.com> - 2011-10-03 22:02 -0700
      Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? Erland Sommarskog <esquel@sommarskog.se> - 2011-10-04 07:22 +0000
        Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? bill <billmaclean1@gmail.com> - 2011-10-04 23:23 -0700

csiph-web