{"id":33925,"date":"2023-02-16T02:59:50","date_gmt":"2023-02-15T21:29:50","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/"},"modified":"2023-02-16T02:59:50","modified_gmt":"2023-02-15T21:29:50","slug":"solved-how-to-improve-performance-of-this-query-closed","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/","title":{"rendered":"[Solved] How to improve performance of this query [closed]"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-45863441\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"45863441\" data-parentid=\"45863241\" data-score=\"2\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>Few ideas below<\/p>\n<ol>\n<li>\n<p>Remove as many left joins as possible. Use inner joins where-ever possible. <\/p>\n<\/li>\n<li>\n<p>Remove sub-query to get ISerialNumber. Get this later in wrapper query.<\/p>\n<\/li>\n<li>\n<p>Create indexes on columns in the WHERE clause if not already existing. <\/p>\n<\/li>\n<li>\n<p>Do not use this way of date comparison. Imagine this conversion for every row in your result set.<\/p>\n<pre><code>CONVERT(VARCHAR(8), a.CreationDate, 112) BETWEEN (' + (@StartDate) +\n') AND (' + (@EndDate) + ')\n<\/code><\/pre>\n<\/li>\n<\/ol>\n<p>Instead, convert your <code>@startDate<\/code> and <code>@EndDate<\/code> variables to datetime and compare with <code>a.CreationDate<\/code><\/p>\n<ol start=\"5\">\n<li>\n<p>Calculate these before running this query into another table variables.<br \/>\nUse those variables in the query.<\/p>\n<pre><code>(SELECT ParamValue FROM LMG.dbo.MultiValue(''' + @JurisdictionId + ''','','',1))\n\nSELECT ParamValue FROM LMG.dbo.MultiValue(''' + @StateOfConvictionId + ''','','',1)\n<\/code><\/pre>\n<\/li>\n<li>\n<p>Check Actual execution query plan in SQL Server Management Studio. The step which is taking more cost (in percentage) will need to be taken care of. <\/p>\n<\/li>\n<\/ol>\n<\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">3<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved How to improve performance of this query [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Few ideas below Remove as many left joins as possible. Use inner joins where-ever possible. Remove sub-query to get ISerialNumber. Get this later in wrapper query. Create indexes on columns in the WHERE clause if not already existing. Do not use this way of date comparison. Imagine this conversion for every row in your &#8230; <a title=\"[Solved] How to improve performance of this query [closed]\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/\" aria-label=\"More on [Solved] How to improve performance of this query [closed]\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[341],"class_list":["post-33925","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] How to improve performance of this query [closed] - JassWeb<\/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:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] How to improve performance of this query [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Few ideas below Remove as many left joins as possible. Use inner joins where-ever possible. Remove sub-query to get ISerialNumber. Get this later in wrapper query. Create indexes on columns in the WHERE clause if not already existing. Do not use this way of date comparison. Imagine this conversion for every row in your ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2023-02-15T21:29:50+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] How to improve performance of this query [closed]\",\"datePublished\":\"2023-02-15T21:29:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/\"},\"wordCount\":128,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/\",\"name\":\"[Solved] How to improve performance of this query [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2023-02-15T21:29:50+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] How to improve performance of this query [closed]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] How to improve performance of this query [closed] - JassWeb","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:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] How to improve performance of this query [closed] - JassWeb","og_description":"[ad_1] Few ideas below Remove as many left joins as possible. Use inner joins where-ever possible. Remove sub-query to get ISerialNumber. Get this later in wrapper query. Create indexes on columns in the WHERE clause if not already existing. Do not use this way of date comparison. Imagine this conversion for every row in your ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/","og_site_name":"JassWeb","article_published_time":"2023-02-15T21:29:50+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] How to improve performance of this query [closed]","datePublished":"2023-02-15T21:29:50+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/"},"wordCount":128,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/","name":"[Solved] How to improve performance of this query [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2023-02-15T21:29:50+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-improve-performance-of-this-query-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] How to improve performance of this query [closed]"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/33925","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=33925"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/33925\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=33925"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=33925"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=33925"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}