I have been using sp_executesql to run my query to take advantage of its query plan caching and better security that prevent injection attacks on web application. but after some time working on project i found that when row counts in a table exceeds million records and your query becomes so complex that even might contain 15-30 joins in such queries i found problems with sp_executelsql.
sp_executelsql is meant to re-use compiled query plans, so it doesn't re-sniff parameters when the same query again hits it so it ends up in using a plan which might be very slow(will tell you why a slower query plan) with the current parameter values.it appears to be that sp_executesql uses a different method for choosing indexes and apparently it is a broken method compared to a plain text query running in SSMS.
For Example:
What differs is the select query on a table contains many join under a sp_executesql is fed via a chain of nested loops while the text query is fed via a chain of hash matches. The construction of the views appears identical between the two version (which I would expect). Unfortunately, the remainder is very complex and they appear to be doing radically different things in the middle; even pulling an "actual" execution plan doesn't give actual execution times for the various sub components of the query. Really, I can't see any reason for sp_executesql to be choosing anything differently, but it reliably constructs a significantly slower plan.
Solution i found so far:
Parameter sniffing is a solution to this, so you could rename the parameters names or you might even swap the column names in where clause that cause sp_executesql to recreate a query plan instead of using an old slow plan, of course this is not the solution but it won't cache a slower plan for so long.
sp_executelsql is meant to re-use compiled query plans, so it doesn't re-sniff parameters when the same query again hits it so it ends up in using a plan which might be very slow(will tell you why a slower query plan) with the current parameter values.it appears to be that sp_executesql uses a different method for choosing indexes and apparently it is a broken method compared to a plain text query running in SSMS.
For Example:
What differs is the select query on a table contains many join under a sp_executesql is fed via a chain of nested loops while the text query is fed via a chain of hash matches. The construction of the views appears identical between the two version (which I would expect). Unfortunately, the remainder is very complex and they appear to be doing radically different things in the middle; even pulling an "actual" execution plan doesn't give actual execution times for the various sub components of the query. Really, I can't see any reason for sp_executesql to be choosing anything differently, but it reliably constructs a significantly slower plan.
Solution i found so far:
Parameter sniffing is a solution to this, so you could rename the parameters names or you might even swap the column names in where clause that cause sp_executesql to recreate a query plan instead of using an old slow plan, of course this is not the solution but it won't cache a slower plan for so long.
No comments:
Post a Comment