Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

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

Show all headers | 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