50 ms·
How does it work when parameter value changes plan? Simple example: `where name like ?`. If parameter is 'abc%', then you can use index. If parameter is '%abc',
by vbezhenar 5y ago
How does it work when parameter value changes plan? Simple example: `where name like ?`. If parameter is 'abc%', then you can use index. If parameter is '%abc', you can't use index.
- briHass 5y agoYou can use a statment 'OPTION RECOMPILE' to force the query plan to be rebuilt with every execution. A similar thing can be done with stored procedures, which are also aggressively plan cached.
- SigmundA 5y agoIt's uses the index either way, specifically it still index seeks but ends up doing the same thing as an index/table scan although slower. It normally makes a plan based on first execution and uses parameter sniffing, so it might optimize for no index seek if the first request has wildcard in front. You can pass it a hint OPTION (RECOMPILE) that will make it act like PG and make new plan every time, if its a query with wildly varying parameters that doesn't execute as often this can make sense, most of the time it doesn't due to planning overhead. There is also the OPTIMIZE FOR hint that can have it make a plan optimized for the more common of the parameter values if you don't want it planning every execution regardless of value passed on first execute.