title: SQL Server - Create Index on query over multiple tables?
body: <p>I'm having big problems with optimizing a specific SQL-Query. It contains multiple inner joins and adding indices to each table doesn't improve performance at all.</p>
  
  <p>My query:</p>
  
  <pre><code>declare @categoryid  int = 2;
  
  SELECT  [Scanresultebay].Id
          , [Scanresultebay].Productebayid
          , [Scanresultebay].Price
          , [Scanresultebay].Stockamount
          , [Scanresultebay].Timestamp
          , [Category].Categoryname
          , (
              SELECT TOP 1 [Scanresultebay].price
              FROM    [Scanresultebay]
                      INNER JOIN [Productebay] ON [Productebay].id = .[Scanresultebay].productebayid
                      INNER JOIN [EbaySeller] on [EbaySeller].id = [ProductEbay].ebaysellerid
              WHERE   [dbo].[EbaySeller].id = 28
                      and [ProductEbay].categoryid = @categoryid
              ORDER BY [Scanresultebay].Id DESC
          ) AS 'OurPrice'
  FROM    [Scanresultebay]
          INNER JOIN [Productebay] ON [Productebay].Id = [Scanresultebay].productebayid 
          INNER JOIN [Category] ON [Category].Id = [Productebay].categoryid 
  WHERE   [Scanresultebay].productebayid in ( 
              SELECT  [Scanresultebay].productebayid
              FROM    [Scanresultebay]
                      INNER JOIN [ProductEbay] ON [ProductEbay].id = [ScanResultEbay].ProductEbayId 
                      INNER JOIN [Category] ON [Category].Id = ProductEbay.CategoryID 
              WHERE   [ProductEbay].categoryid = @categoryid  and [ProductEbay].expired is null 
              GROUP BY [Scanresultebay].ProductEbayId 
          )
          and [Scanresultebay].Id in (
              SELECT  max(Id)
              FROM    [Scanresultebay]
              WHERE   productebayid = [Scanresultebay].ProductEbayId 
                      and [Scanresultebay].Price &lt;&gt; 0 
              GROUP BY [Scanresultebay].[ProductEbayId]
          );
  </code></pre>
  
  <p>My Index (as SQL Server suggested me):</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX [ind_GetPrice]
  ON [dbo].[ScanResultEbay] ([Id],[ProductEbayId]) include ([Stockamount], [Timestamp],  [Price])
  </code></pre>
  
  <p>I need this query for displaying infos on a dashboard on my website. So I have to loop through each category (100 in total) with this query. This lasts up to 30-40 seconds, which is too much of course.</p>
  
  <p>Creating a view is a problem, as I have to declare the parameter of categoryid for subqueries aswell and parameters can't be passed to a view.</p>
  
  <p>So my questions:</p>
  
  <ol>
  <li>Can I do a specific index just for this query, so it gets a lot faster (now it takes 0,5sec).</li>
  <li>Is my query too long (or ineffective) due to those subqueries?</li>
  <li>What can I do else, to improve my performance?</li>
  </ol>