title: How can I speed up this linq to sql function?
body: <p>I have a function (called "powersearch", the irony!) that searches for a set of strings across a bunch(~ 5) of fields.
  The words come in as one string and are separated by spaces.<br>
  Some fields can have exact matches, others should have "contains".  </p>
  
  <p>(Snipped for brevety)</p>
  
  <pre><code>//Start with all colors
  IQueryable&lt;Color&gt; q = db.Colors;
  //Filter by powersearch
  if (!string.IsNullOrEmpty(searchBag.PowerSearchKeys)){
      foreach (string key in searchBag.SplitSearchKeys(searchBag.PowerSearchKeys)
                                      .Where(k=&gt; !string.IsNullOrEmpty(k))){
          //Make a local copy of the var, otherwise it gets overwritten
          string myKey = key;
          int year;
          if (int.TryParse(myKey, out year) &amp;&amp; year &gt; 999){
              q = q.Where(c =&gt; c.Company.Name.Contains(myKey)
                  || c.StockCode.Contains(myKey)                                
                  || c.PaintCodes.Any(p =&gt; p.Code.Equals(myKey))
                  || c.Names.Any(n =&gt; n.Label.Contains(myKey))
                  || c.Company.CompanyModels.Any(m =&gt; m.Model.Name.Contains(myKey))
                  || c.UseYears.Any(y =&gt; y.Year.Equals(year))
              );
          }
          else{
              q = q.Where(c =&gt; c.Company.Name.Contains(myKey)
                  || c.StockCode.Contains(myKey)
                  || c.PaintCodes.Any(p =&gt; p.Code.Contains(myKey))
                  || c.Names.Any(n =&gt; n.Label.Contains(myKey))                                
                  || c.Company.CompanyModels.Any(m =&gt; m.Model.Name.Equals(myKey))
              );
          }
      }
  }
  </code></pre>
  
  <p>Because the useYear count is rather large, I tried to check for it as little as possible by outruling all numbers that can never be a number that makes sence in this case. Similar checks are not possible on the other fields since they can pretty much contain any thinkable string.</p>
  
  <p>Currently this query takes about 15 secs for a single, non-year string. That's too much.
  Anything I can do to improve this?</p>
  
  <p><strong>--Edit--</strong><br>
  Profiler shows me the following info for the part where the string is not a year:  </p>
  
  <p>exec sp_reset_connection
  Audit login</p>
  
  <pre><code>exec sp_executesql N'
  SELECT COUNT(*) AS [value]
  FROM [dbo].[CLR] AS [t0]
  INNER JOIN [dbo].[CO] AS [t1] ON [t1].[CO_ID] = [t0].[CO_ID]
  WHERE 
      ([t1].[LONG_NM] LIKE @p0)
      OR ([t0].[EUR_STK_CD] LIKE @p1)
      OR (EXISTS(
          SELECT NULL AS [EMPTY]
          FROM [dbo].[PAINT_CD] AS [t2]
          WHERE ([t2].[PAINT_CD] LIKE @p2)
              AND ([t2].[CLR_ID] = [t0].[CLR_ID])
              AND ([t2].[CUSTOM_ID] = [t0].[CUSTOM_ID])
          )
      )OR (EXISTS(
          SELECT NULL AS [EMPTY]
          FROM [dbo].[CLR_NM] AS [t3]
          WHERE ([t3].[CLR_NM] LIKE @p3)
              AND ([t3].[CLR_ID] = [t0].[CLR_ID])
              AND ([t3].[CUSTOM_ID] = [t0].[CUSTOM_ID])
          )
      ) OR (EXISTS(
          SELECT NULL AS [EMPTY]
          FROM [dbo].[CO_MODL] AS [t4]
          INNER JOIN [dbo].[MODL] AS [t5] ON [t5].[MODL_ID] = [t4].[MODL_ID]
          WHERE ([t5].[MODL_NM] = @p4)
              AND ([t4].[CO_ID] = [t1].[CO_ID])
          )
      )
  ',N'@p0 varchar(10),@p1 varchar(10),@p2 varchar(10),@p3 varchar(10),@p4 varchar(8)',@p0='%mercedes%',@p1='%mercedes%',@p2='%mercedes%',@p3='%mercedes%',@p4='mercedes'
  </code></pre>
  
  <p>(took 3626 msecs)
  Audit Logout (3673 msecs)
  exec sp_reset_connection (0msecs)
  Audit login</p>
  
  <pre><code>exec sp_executesql N'
  SELECT TOP (30) 
  [t0].[CLR_ID] AS [Id],
  [t0].[CUSTOM_ID] AS [CustomId],
  [t0].[CO_ID] AS [CompanyId], 
  [t0].[EUR_STK_CD] AS [StockCode], 
  [t0].[SPCL_USE_CD] AS [UseCode], 
  [t0].[EFF_IND] AS [EffectIndicator]
  FROM [dbo].[CLR] AS [t0]
  INNER JOIN [dbo].[CO] AS [t1] ON [t1].[CO_ID] = [t0].[CO_ID]
  WHERE 
      ([t1].[LONG_NM] LIKE @p0)
      OR ([t0].[EUR_STK_CD] LIKE @p1)
      OR (EXISTS(
          SELECT NULL AS [EMPTY]
          FROM [dbo].[PAINT_CD] AS [t2]
          WHERE ([t2].[PAINT_CD] LIKE @p2)
              AND ([t2].[CLR_ID] = [t0].[CLR_ID])
              AND ([t2].[CUSTOM_ID] = [t0].[CUSTOM_ID])
          )
      )
      OR (EXISTS(
          SELECT NULL AS [EMPTY]
          FROM [dbo].[CLR_NM] AS [t3]
          WHERE ([t3].[CLR_NM] LIKE @p3)
              AND ([t3].[CLR_ID] = [t0].[CLR_ID])
              AND ([t3].[CUSTOM_ID] = [t0].[CUSTOM_ID])
          )
      )
      OR (EXISTS(
          SELECT NULL AS [EMPTY]
          FROM [dbo].[CO_MODL] AS [t4]
          INNER JOIN [dbo].[MODL] AS [t5] ON [t5].[MODL_ID] = [t4].[MODL_ID]
          WHERE ([t5].[MODL_NM] = @p4)
              AND ([t4].[CO_ID] = [t1].[CO_ID])
          )
      )'
  ,N'@p0 varchar(10),@p1 varchar(10),@p2 varchar(10),@p3 varchar(10),@p4 varchar(8)',@p0='%mercedes%',@p1='%mercedes%',@p2='%mercedes%',@p3='%mercedes%',@p4='mercedes'
  </code></pre>
  
  <p>(took 3368 msecs)</p>
  
  <p>The database structure, sadly, is not under my control. It comes from the US and has to stay in the exact same format for compatibility reasons. Although most of the important fields are indeed indexed, they are indexed in (unnecessary) clustered primary keys. There's verry little I can do about that.</p>