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

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

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

Show all headers | View raw

bill (billmaclean1@gmail.com) writes:
> 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.
 
As Hugo said, there first query gives the optimizer more information.

That lookup table, it does have a primary key defined, hasn't it?

Else, I can only give generic tips:

o  Run UPDATE STATISTICS WITH FULLSCAN on all involved tables.
o  Review the query in the view, and check that there are useful 
   indexes.
o  Try hints to persuade the optimizer in the right direction.
o  Review whether you really need SELECT * -  by reducing the number
   of columns, you make make an index covering.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

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