{"id":7092,"date":"2016-02-10T18:39:53","date_gmt":"2016-02-10T17:39:53","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/"},"modified":"2023-07-17T14:32:04","modified_gmt":"2023-07-17T12:32:04","slug":"dynamic-filters-and-kitchen-sink-queries-dilemma","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/","title":{"rendered":"Dynamic filters and “kitchen sink” queries dilemma"},"content":{"rendered":"

Dealing with dynamic filters is a common scenario with management software or ERP. Indeed, users should like to have the flexibility to filter and sort their business data as they want in order to be efficient. Target and operate quickly on the right data is in line with the required performance every day. This is at least what I could notice from different customer places.<\/p>\n

So, through my mandates at customer places, I could see various T-SQL implementations of dynamic filters, and some of them do not comply with performance best practices. The blog post doesn’t aim to provide one-size-fits-all solutions for such issues but only expose a compilation of different implementations I encountered so far. Please feel free to comment or add your thoughs!<\/p>\n

Firstly, the most common implementation I\u2019ve seen so far the use of the well-known \u201ckitchen Sink\u201d anti-pattern. You may see a good example with the following stored procedure (My examples are based on the AdventureWorks database to stay on simple explanations. Of course in customer scenarios, this kind of stored procedure may be a little bit complex)<\/p>\n

create procedure PR_get_customer_sales_info\n(\n\t@SalesOrderID int = null,\n\t@LastName nvarchar(50) = null,\n\t@StoreName nvarchar(50) = null,\n\t@AccountNumber varchar(10) = null\n)\nas\n\n\nselect \n\tp.FirstName + '.' + p.LastName as customer_name,\n\tc.AccountNumber,\n\th.OrderDate,\n\td.SalesOrderID as order_id,\n\td.SalesOrderDetailID as order_detail_id,\n\td.UnitPrice * d.OrderQty as total_sale,\n\ts.Name as store_name,\n\ts.Demographics\nfrom Sales.SalesOrderHeader as h\njoin Sales.SalesOrderDetail as d on h.SalesOrderID = d.SalesOrderID\njoin Sales.Customer  as c on c.CustomerID = h.CustomerID\njoin Person.Person as p on p.BusinessEntityID = c.PersonID\njoin Sales.Store as s on s.BusinessEntityID = c.StoreID\nwhere 1=1\n\tand (h.SalesOrderID = @SalesOrderID or @SalesOrderID is null)\n\tand (p.LastName = @LastName or @LastName is null)\n\tand (s.Name = @StoreName or @StoreName is null)\n\tand (c.AccountNumber = @AccountNumber or @AccountNumber is null)<\/pre>\n
The main idea here is to satisfy a variety of search conditions from the application users. If you take a look at the predicate, you will recognize a common pattern used by developers (I used myself the same pattern in the past when I was a T-SQL developer). Writing this kind of predicate is very simple (and maybe intuitive). If the application user doesn\u2019t want to filter data, the clause 1=1 will come into play. Otherwise, if the same user wants to apply more restrictive filters by using either one or a set of parameters, the procedure still remains flexible to meet the user requirement.<\/p>\n
But simplicity doesn\u2019t rely always on performance in all cases and unfortunately we are unlucky here. Indeed, we may face performance issues with this kind of T-SQL statement because of the parameter sniffing phenomena. To understand what I mean, let\u2019s say that the first compilation of this stored procedure is based on the predicate filter 1=1 only. The application user doesn\u2019t want to apply filter to his data. So we just have to execute our stored procedure without specifying any parameters as follows:<\/p>\n
exec dbo.PR_get_customer_sales_info<\/pre>\n
Chances are that SQL Server will choose to use index scan operators and this is what we\u2019ll notice by retrieving the correspond execution plan in the cache.<\/p>\n
select \n\tt.text,\n\tp.query_plan,\n\tcp.refcounts,\n\tcp.usecounts,\n\tcp.cacheobjtype,\n\tcp.size_in_bytes\nfrom sys.dm_exec_cached_plans as cp\ncross apply sys.dm_exec_sql_text(cp.plan_handle) as t\ncross apply sys.dm_exec_query_plan(cp.plan_handle) as p\nwhere t.text like '%from Sales.SalesOrderHeader as h%'\n\tand t.text not like '%sys.dm_exec_cache_plans%'<\/pre>\n
\"blog<\/p>\n
…<\/p>\n
\"blog<\/p>\n
The execution plan is now in cache such as the next executions will use it. This time, let\u2019s say another application user wants to use a completely different predicate that is more restrictive compared to the previous query as follow. So the new execution of the stored procedure will be as follows:<\/p>\n
exec dbo.PR_get_customer_sales_info\n\t@SalesOrderID = 47454<\/pre>\n
The query returns 37 rows against 60919 from the previous query. At this point, we may guess that this query will potentially use index seek operators rather than index scan operators but as you may expect, the former will not be chosen by SQL Server because the second query execution will be based on the existing plan in cache. So let\u2019s imagine a stored procedure with much more parameters and the number of combinations of search predicates and values we may get. There is a good probability that the execution plan becomes quickly obsolete.<\/p>\n
We may avoid this issue by using the recompile option either at the statement or the stored procedure level but at the price of a resource wasting at each execution.<\/p>\n
Let\u2019s continue with the second alternative I faced recently and that consists in using dynamic SQL. In addition, in this specific case, the number of parameters may be large and was completely dynamic such as we didn\u2019t know in advance which parameters the application users will use to filter their data. The solution found by my customer consisted in transferring the parameters and their respective values as a string chain to the stored procedure from the URL application as follows (I don\u2019t remember the exact structure of the stored procedure).<\/p>\n
create procedure PR_get_customer_sales_info_dynamic\n(\n\t@parameters varchar(max) = null\n)\nas\n\ndeclare @where varchar(max);\n\nset @where = case \n\t\t\t\twhen @parameters is not null then 'AND ' + REPLACE(@parameters, ';', ' AND ')\n\t\t\t\telse ''\n\t\t\t end\n\ndeclare @sql varchar(max) = N'\nselect \n\tp.FirstName + ''.'' + p.LastName as customer_name,\n\tc.AccountNumber,\n\th.OrderDate,\n\td.SalesOrderID as order_id,\n\td.SalesOrderDetailID as order_detail_id,\n\td.UnitPrice * d.OrderQty as total_sale,\n\ts.Name as store_name,\n\ts.Demographics\nfrom Sales.SalesOrderHeader as h\njoin Sales.SalesOrderDetail as d on h.SalesOrderID = d.SalesOrderID\njoin Sales.Customer  as c on c.CustomerID = h.CustomerID\njoin Person.Person as p on p.BusinessEntityID = c.PersonID\njoin Sales.Store as s on s.BusinessEntityID = c.StoreID\nwhere 1=1' + char(13)\n\nset @sql = @sql + @where;\n\nexec(@sql);\ngo<\/pre>\n
At the first glance, it could be a better idea than the previous stored procedure even if writing this kind of T-SQL code may be more difficult. Note that this time the query is dynamically constructed and includes only the minimal predicate clause that will probably lead to a better execution plan.<\/p>\n
Let\u2019s execute this second stored procedure with two different parameters as the previous test:<\/p>\n
declare @parameters varchar(max) = null;\n\nexec dbo.PR_get_customer_sales_info_dynamic\n\t@parameters = @parameters;\ngo\n\ndeclare @parameters varchar(max) = 'h.SalesOrderID=47454';\n\nexec dbo.PR_get_customer_sales_info_dynamic\n\t@parameters = @parameters;\ngo<\/pre>\n
And let\u2019s have a look at the cached execution plan cache:<\/p>\n
\"blog<\/p>\n
Well, we may notice that new stored procedure is reused but let\u2019s focus on the two others lines below: we have two other lines related to each execution of the dynamic T-SQL statement inside the procedure. Take a look at the objtype column value and you will understand why we will get as many lines as the number of stored procedure executions. You may test by yourself by changing only the parameter values \u2026 For example in my case, after changing the value of the SalesOrderID parameter I got an additional record in the execution plan cache:<\/p>\n
declare @parameters varchar(max) = 'h.SalesOrderID=47453'\n\nexec dbo.PR_get_customer_sales_info_dynamic\n\t@parameters = @parameters;<\/pre>\n
\"blog<\/p>\n
So keep in mind that executing dynamic T-SQL code with EXEC command is always considered as adhoc queries by SQL Server. It means that the related execution plan will be generated and only once for a specific set of parameters and values as well. However since the 2005, there are some optimizations on this topic because SQL Server is able to recompile at the statement level instead of the entire procedure level leading to reduce considerably the CPU consumption compared to SQL Server 2000.<\/p>\n
By taking a look at the query plans, we may notice that each is more suitable for each statement execution context.<\/p>\n
declare @parameters varchar(max) = null;\n\nexec dbo.PR_get_customer_sales_info_dynamic\n\t@parameters = @parameters;\ngo<\/pre>\n
\"blog<\/p>\n
declare @parameters varchar(max) = 'h.SalesOrderID=47454';\n\nexec dbo.PR_get_customer_sales_info_dynamic\n\t@parameters = @parameters;\ngo<\/pre>\n
\"blog<\/p>\n
Using this solution has some advantages but also some important drawbacks:<\/p>\n
    \n
  • The execution plan cache may be bloated by a lot of batch compilations plans. You may mitigate this issue by using the server level option \u201cOptimize for adhoc workload\u201d if your workload is suitable.<\/li>\n
  • Even if SQL Server is able to compile only the concerned batch, the compilation stuff is there and may be part of general compilation issue regarding the workload. It was the case with one my customers.<\/li>\n<\/ul>\n
    Finally, the previous method may be enhanced by changing the way to execute the dynamic SQL statement. We must use system stored procedure sp_executesql instead of the EXEC command. In the context of dynamic parameters, if we use the same way than the previous procedure, that will be more challenging because we will also have to provide the type of each parameter.<\/p>\n
    If it is technically possible, I personally prefer to use stored procedures parameters instead of passing the parameters inside a string chain from a web URL to avoid exposing applications to malicious attacks. From my point of view, this is not a good idea but I would like to hear you about this point\u2026<\/p>\n
    Of course, if we face a large amount of parameters, we will probably write a very big stored procedure to finally use maybe 5% of the parameters. Yes, this a trade-off to find here but trust me, in the most cases, it will finally benefit the performance.<\/p>\n
    Let\u2019s rewrite the previous stored procedure by preparing correctly the dynamic T-SQL.<\/p>\n
    create procedure PR_get_customer_sales_info_dynamic_enhanced\n(\n\t@SalesOrderID int = null,\n\t@LastName nvarchar(50) = null,\n\t@StoreName nvarchar(50) = null,\n\t@AccountNumber varchar(10) = null\n)\nas\n\ndeclare @where nvarchar(max) = N'';\ndeclare @parameters nvarchar(max) = N'';\n\nif @SalesOrderID is not null\n\tset @where = N'and h.SalesOrderID = @SalesOrderID'  + CHAR(13);\n\n\nif @LastName is not null\n\tset @where = @where + N'and c.LastName = N''@LastName''' + CHAR(13);\n\t\nif @StoreName is not null\n\tset @where = @where + N'and s.Name = N''@StoreName''' + CHAR(13);\n\t\nif @AccountNumber is not null\n\tset @where = @where + N'and c.AccountNumber = N''@AccountNumber''' + CHAR(13);\n\ndeclare @sql nvarchar(max) = N'\nselect \n\tp.FirstName + ''.'' + p.LastName as customer_name,\n\tc.AccountNumber,\n\th.OrderDate,\n\td.SalesOrderID as order_id,\n\td.SalesOrderDetailID as order_detail_id,\n\td.UnitPrice * d.OrderQty as total_sale,\n\ts.Name as store_name,\n\ts.Demographics\nfrom Sales.SalesOrderHeader as h\njoin Sales.SalesOrderDetail as d on h.SalesOrderID = d.SalesOrderID\njoin Sales.Customer  as c on c.CustomerID = h.CustomerID\njoin Person.Person as p on p.BusinessEntityID = c.PersonID\njoin Sales.Store as s on s.BusinessEntityID = c.StoreID\nwhere 1=1' + CHAR(13);\n\nset @sql = @sql + @where;\n\n\nset @parameters = N'@SalesOrderID INT, @LastName nvarchar(50), @StoreName nvarchar(50), @AccountNumber varchar(10)';\n\nexec sp_executesql @sql, @parameters, @SalesOrderID, @LastName, @StoreName, @AccountNumber;\ngo<\/pre>\n
    Yes I agree we are far from the first procedure but this last version will probably help you in the most cases in terms of performance. Let\u2019s have a look at the execution plan cache:<\/p>\n
    \"blog<\/p>\n
    This time, each batch is considered as prepared by SQL Server and can be reused as a normal compiled object. So, we will probably have as many rows as we have searched filter variations but that\u2019s all. What about parameters sniffing? It is a legitimate question here because as said above, prepared queries are considered as compiled objects and may suffer from parameters sniffing. Depending on your data distribution, you still have the possibility to add hints like OPTIMIZE FOR.<\/p>\n
    In this blog post, we\u2019ve seen that dealing with \u201ckitchen sink\u201d procedures is not easy. Unfortunately, we don\u2019t \u2018have any perfect solution but what is certain is that some solutions are generally better than others.<\/p>\n
    Happy development!<\/p>\n
    By David Barbarin<\/p>\n","protected":false},"excerpt":{"rendered":"
    Dealing with dynamic filters is a common scenario with management software or ERP. Indeed, users should like to have the flexibility to filter and sort their business data as they want in order to be efficient. Target and operate quickly on the right data is in line with the required performance every day. This is […]<\/p>\n","protected":false},"author":26,"featured_media":7100,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[368],"tags":[739,740,348,741,742,51],"type_dbi":[],"class_list":["post-7092","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-development-performance","tag-compilation","tag-dynamic-filters","tag-execution-plan","tag-parameter-sniffing","tag-plan-cache","tag-sql-server"],"acf":[],"yoast_head":"\nDynamic filters and "kitchen sink" queries dilemma<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dynamic filters and "kitchen sink" queries dilemma\" \/>\n<meta property=\"og:description\" content=\"Dealing with dynamic filters is a common scenario with management software or ERP. Indeed, users should like to have the flexibility to filter and sort their business data as they want in order to be efficient. Target and operate quickly on the right data is in line with the required performance every day. This is […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-02-10T17:39:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-07-17T12:32:04+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-77-1-sp-kitchen-sink-and-parameter-sniffing-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"866\" \/>\n\t<meta property=\"og:image:height\" content=\"41\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"Dynamic filters and “kitchen sink” queries dilemma\",\"datePublished\":\"2016-02-10T17:39:53+00:00\",\"dateModified\":\"2023-07-17T12:32:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/\"},\"wordCount\":1373,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-77-1-sp-kitchen-sink-and-parameter-sniffing-1.jpg\",\"keywords\":[\"compilation\",\"dynamic filters\",\"Execution plan\",\"parameter sniffing\",\"plan cache\",\"SQL Server\"],\"articleSection\":[\"Development & Performance\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/\",\"name\":\"Dynamic filters and \\\"kitchen sink\\\" queries dilemma\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-77-1-sp-kitchen-sink-and-parameter-sniffing-1.jpg\",\"datePublished\":\"2016-02-10T17:39:53+00:00\",\"dateModified\":\"2023-07-17T12:32:04+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-77-1-sp-kitchen-sink-and-parameter-sniffing-1.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-77-1-sp-kitchen-sink-and-parameter-sniffing-1.jpg\",\"width\":866,\"height\":41},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dynamic filters and “kitchen sink” queries dilemma\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Dynamic filters and \"kitchen sink\" queries dilemma","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/","og_locale":"en_US","og_type":"article","og_title":"Dynamic filters and \"kitchen sink\" queries dilemma","og_description":"Dealing with dynamic filters is a common scenario with management software or ERP. Indeed, users should like to have the flexibility to filter and sort their business data as they want in order to be efficient. Target and operate quickly on the right data is in line with the required performance every day. This is […]","og_url":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/","og_site_name":"dbi Blog","article_published_time":"2016-02-10T17:39:53+00:00","article_modified_time":"2023-07-17T12:32:04+00:00","og_image":[{"width":866,"height":41,"url":"http:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-77-1-sp-kitchen-sink-and-parameter-sniffing-1.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"Dynamic filters and “kitchen sink” queries dilemma","datePublished":"2016-02-10T17:39:53+00:00","dateModified":"2023-07-17T12:32:04+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/"},"wordCount":1373,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-77-1-sp-kitchen-sink-and-parameter-sniffing-1.jpg","keywords":["compilation","dynamic filters","Execution plan","parameter sniffing","plan cache","SQL Server"],"articleSection":["Development & Performance"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/","url":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/","name":"Dynamic filters and \"kitchen sink\" queries dilemma","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-77-1-sp-kitchen-sink-and-parameter-sniffing-1.jpg","datePublished":"2016-02-10T17:39:53+00:00","dateModified":"2023-07-17T12:32:04+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-77-1-sp-kitchen-sink-and-parameter-sniffing-1.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-77-1-sp-kitchen-sink-and-parameter-sniffing-1.jpg","width":866,"height":41},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/dynamic-filters-and-kitchen-sink-queries-dilemma\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Dynamic filters and “kitchen sink” queries dilemma"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7092","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=7092"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7092\/revisions"}],"predecessor-version":[{"id":26726,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7092\/revisions\/26726"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/7100"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7092"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7092"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7092"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7092"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}