title: sql runs fast in ssms slow in asp.net
body: <p>I have been having this problem for a couple of weeks now. The problem is that the query takes 4-5 minutes to run on the website and at most 2 or 3 seconds to run in ssms. Also I found that after I make a change to this query like adding the customerId variable it will start running quickly on the web page but by the next day it is slow again. The query in question is this one:</p>
  
  <pre><code>DECLARE @customerID INT
  SET @customerID = @CustID
  DECLARE @MyTable table(
  Iden int NOT NULL IDENTITY(1,1),
  ProductID int)
  
  INSERT INTO @MyTable(ProductID)
  SELECT P.ProductID FROM Product P WITH (NOLOCK)
  left join Compunix_ProductMMY cpmmy with (nolock) on p.ProductID = cpmmy.ProductID
  left join Compunix_CustomerMMY ccmmy with (nolock) on ccmmy.mmyid = cpmmy.mmyid
  WHERE P.Deleted=0 AND P.Published=1 and (ccmmy.customerid = @customerID OR cpmmy.productid IS NULL)
  
  SELECT c.Name, c.SeName, c.CategoryID
  FROM Category c WITH (NOLOCK) 
  JOIN ProductCategory PC With (NOLOCK) ON C.CategoryID = PC.CategoryID
  JOIN @MyTable MT ON PC.ProductID=MT.ProductID
  WHERE C.Published = 1
  GROUP BY c.Name, c.SeName, c.CategoryID
  ORDER BY c.Name
  </code></pre>
  
  <p>I have the same query running on 2 other site which work just fine. The only difference between the sites is that they are run on different databases and the slow site has a bit more than double the products (54000 products) on it compared to the other 2. All three sites and their databases are hosted on the same machine.</p>