Thanks Jeffrey Williams that solution worked out great.

The companies are allowed up to 3 coupons, So 3 titles.

Im going to try the method posted above and let you know the results.

Thanks. I did not notice that.

That column is not even being used in the source code or even being returned by the main query to begin with ,so its been...

Thank you very much!

I could not get what you gave me to work exactly as it is, but the execution time is now 2.437 seconds on average vs 5.966 seconds.

This...

The function runs very fast.

It returns all categories that are under the parent category.

In the case of category 42 - it returns 965 subcategories , sub-sub categories and so...

Auto Update for stats are turned on.

I updated the stats on all tables using this script

http://qa.sqlservercentral.com/scripts/Index+Management/31823/

but the estimated rows are still the same.

changing that into a inner join produces the same execution plan

I'm not sure how to get that information. I create all my indexes in SQL Management Studios table designer.

I think your on to something. I removed it completely from the...

The clustered index seek in the execution plan refers to these 2 lines in the sql below

"AND (dbo.CSK_Store_Product.statusID <> 99)

AND (dbo.CSK_Store_Product.isDeleted = 0)"

SELECT

ProductList.productID,

ProductList.sku,

ProductList.productName,

ProductList.shortDescription,

ProductList.manufacturerID,

ProductList.statusID,

ProductList.productTypeID,...