sorry for the lengthy post, kind of hard to describe the issue without pictures. Plainly put, SQL is using different query plans for the same query (depending on whether I use parameters or not). I understand this is an issue with parameter sniffing, but as I'm using Entity Framework, I don't have a way to use hints for my queries.
Here is a picture of the table setup (I only use a few fields in this example)
SELECT TOP 10 * FROM MarketItems mk JOIN ItemAttributes attr ON mk.TypeId == attr.Id WHERE attr.Name LIKE '%item_name%' AND Expired = 0 ORDER BY mk.LastSeen DESC
Market Items Table description:
Both TypeID and LastSeen are indexed. Expired is a bit field. On average, there are roughly ~40,000 items that are NOT expired, and ~4mil + expired (which shouldn't even matter).
Item Attributes Table description:
For the item attributes table, only the primary key (Id) is indexed, and there are approximately ~20,000 records.
This occurs when EF uses a parameter in the query and it takes over 6 seconds to query empty data, where it should normally be nearly instant. Even if I search for a non-exitant name in the attributes table, it takes roughly 6 seconds for the query to complete. It seems like it's going through ALL the Market Items, and doing a text lookup in the Attributes table every time.
The Bad Execution Plan
Here is the query plan with parameters (which takes 6-7s to run for no data)
Full res link: http://puu.sh/8fNKy/2a98367722.png
The Good Execution Plan
Here is exact same query without parameters (which of course, runs instantly for any string or item):
Full res link: http://puu.sh/8fNSH/6c6f7039fe.png
So, the question is either why does SQL generate such a bad plan, or how can I force EF (6.1) to use a different plan or accept query hints. I'm open to any other suggestions as to how I can resolve this issue.