![]() i don't know, but at some point query is putted in the cache for no obvious reason. Why it puts query in cache? I believe there is some limit, maybe the number of variables inside the query, number of hints for recompile, temp table. If there are only couple of query combinations, i never use dynamic SQL(but those queries are less frequent). Even less complicated queries usually perform much better with dynamic sql (since only one case condition in where, order or join clause can be Since there could be thousands of different query combinations, i believe that dynamic SQL is the only way to go. And grid can be sorted by all columns(users request). I have grid on page, which has around 20 different filters(almost every filter could produce different join). I don't have any literals, everything is in parameters. To create the tablecreate table TestSetCache( ID int ,myName varchar(10))insert into TestSetCache values(1,'Tom'),(2,'Jerry') - To drop cached plans and check whether they have been cleaned.dbcc freeproccache goselect text,* from sys.dm_exec_cached_planscross apply sys.dm_exec_sql_text(plan_handle)where text like '%TestSetCache%' - To run our commandsDECLARE NVARCHAR(4000)SET INTSELECT TOP 1 FROM TestSetCache WHERE myName=''Tom'' OPTION (RECOMPILE) 'EXEC sp_executesql check the execution plans lect text,* from sys.dm_exec_cached_planscross apply sys.dm_exec_sql_text(plan_handle)where text like '%TestSetCache%' As far as I know, the “Option(Recompile)” option usually specified with “Select” commands rather than others, if you don’t want cache the plan, I suggest using “Select” command to get the value of I tested the following codes and found the execution ![]()
0 Comments
Leave a Reply. |