Any performance difference between using variables or constants in an Entity Framework query?
I noticed that if I specify a constant in the EF query that the value is nested, but if I specify the same value as the variable, then EF will create a subquery for it and pass it as a parameter. Are there performance differences between the two approaches?
I have several massive Linq queries and am wondering if using constants can help with performance in terms of both query execution (and plan caching) and translation from Linq to SQL.
source to share
I suggest you check out this TechNet article:
https://technet.microsoft.com/en-us/library/ms175580(v=sql.105).aspx
It states that if you are using literals, then the query optimizer should recognize it, but sometimes it may not.
The only difference between the execution plans for these queries is the value stored for comparison against the ProductSubcategoryID column. While the goal of SQL Server is always to recognize that statements generate essentially the same plan and reuse plans, SQL Server sometimes does not detect this in complex SQL statements.
Pay attention to the words "difficult" and "sometimes" - pretty specific explanation, isn't it? :)
The article also explains that if you use parameters it "helps" the engine to reuse plans (again, specific ones), some things about simple parameterization and forced parameterization.
So the documentation says it isn't accurate, but usually it shouldn't change. As well as my own experience: I've found that the engine is good at recognizing constants in queries generated by EF. I had the same question a while ago and did some testing on Azure SQL. I would not say that I learned the most complex generated SQL queries in the world, but these were not just select-where-let-join compilations.
But again, this was for my requests in a specific version of the engine. Of course, I would suggest that you check your questions as well and you can be sure.
source to share