35eda513   
   From: esquel@sommarskog.se   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)