{"id":676,"date":"2008-09-06T11:23:00","date_gmt":"2008-09-06T11:23:00","guid":{"rendered":"\/blogs\/bobb\/post\/On-hinting-spatial-indexes-and-query-complexity.aspx"},"modified":"2013-01-03T23:59:48","modified_gmt":"2013-01-04T07:59:48","slug":"on-hinting-spatial-indexes-and-query-complexity","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/","title":{"rendered":"On hinting spatial indexes and query complexity"},"content":{"rendered":"

\nAfter reading Isaac's recent blog posting about spatial queries and index hinting<\/a>, I thought I might add some information based on a query I was working with this week. Sometimes if you have a query that's too syntactically complex, hinting won't work. In these cases you can cause the index to be used (or at least hintable) by breaking up the query.<\/font><\/span> <\/font><\/span>Warning. Protracted code example follows.<\/font><\/span> <\/font><\/span>\n<\/p>\n

\n<\/span>If the spatial query is somewhat complex, it's useful to write it out step by step, so we start with the query written out in steps. We're using three tables, a county table, highway table, and POI (points of interest) table. Without pondering the necessity of clipping to the county boundary twice, here's the original logic.<\/span><\/span><\/font> <\/font><\/span>\n<\/p>\n

\n–Find all banks within 2km of I66 as it runs through Fairfax County
\nDECLARE @g geography
\nDECLARE @h geography
\nDECLARE @i geography
\nDECLARE @j geography
\nDECLARE @k geography
\nSELECT @g = geog from va where fips = 51059 — Fairfax County
\nSELECT @h = geog from us_hwys where route_num = 'I66' — I66 Hwy
\nSELECT @i = @h.STIntersection(@g)– I66 within county
\nSELECT @j = @i.STBuffer(2000)– buffer around I66
\nSELECT @k = @j.STIntersection(@g)– clip buffer to county boundary
\nSELECT geog from POI
\n  WHERE geog.STIntersects(@k)=1 and Description = 'bank' <\/span>\n<\/p>\n

\nWe thought that it might be better for the overall query performance to write this as a single SQL query, so, doing simple substitution, one subquery at a time, we come up with these two equivalent query sets at the end of the process: <\/span> <\/span>\n<\/p>\n

\n— A. Almost there… 2-query process
\nDECLARE @k geography
\nselect @k =
\n((SELECT geog from us_hwys where route_num = 'I66').STIntersection(
\n  (SELECT geog from va where fips = 51059)
\n)).STBuffer(2000).STIntersection(
\n  (SELECT geog from va where fips = 51059)
\n)
\n— uses the spatial index without a hint, total elapsed time for both steps 2 seconds
\nSELECT geog from POI — with (index(spatial_idx_2))
\nwhere geog.STIntersects(@k)=1 and Description = 'bank'
\ngo <\/span>\n<\/p>\n

\n— B. Done… 1 query
\n— But, doesn't use the spatial index
\n— Total elapsed time, 48 minutes 53 seconds!
\nSELECT geog from POI  — with (index(spatial_idx_2))  hint doesn't work
\nwhere geog.STIntersects(
\n  ((SELECT geog from us_hwys where route_num = 'I66').STIntersection(
\n    (SELECT geog from va where fips = 51059)
\n  )).STBuffer(2000).STIntersection(
\n    (SELECT geog from va where fips = 51059)
\n  )
\n)=1 and Description = 'bank' <\/span>\n<\/p>\n

\nThe resulting query is too complex for the query processor to even "think of" a plan that uses the spatial index. Even hinting doesn't work. The simpler STIntersects query using the 2-query process can be hinted, but we don't need the hint. The query processor is smart enough to use the spatial index with the simplified version. Oh… <\/span>\n<\/p>\n

\nSo the moral of the story is not only to be on the lookout for index-hinting opportunities, but that sometimes, if the query is too complex the query processor won't take the hint. The error in this case was: <\/span>\n<\/p>\n

\nMsg 8622, Level 16, State 1, Line 1
\nQuery processor could not produce a query plan because of the hints defined in this query.
\nResubmit the query without specifying any hints and without using SET FORCEPLAN. <\/span>\n<\/p>\n

\nIn cases like this, it may be necessary to break the query into pieces in order to apply the hint. In this case, it happens with an STIntersects() that used four subqueries, the exact "complexity point" may vary with the query itself. <\/span>\n<\/p>\n

\nOf course, in this case, because of the repeating "clipping" steps (clip to a particular county), we can rewrite the query to use common table expressions. This not only makes it easier to read but, by removing the redundant subquery, the query processor decides to use the index with the hint. <\/span>\n<\/p>\n

\nWITH GetCounty as (SELECT geog from va where fips = N'51059'),
\n     GetHighway as(SELECT geog from us_hwys where route_num = 'I66')
\nSELECT POI.geog from POI with (index(spatial_idx_2)),  GetCounty C,  GetHighway H
\nWHERE POI.geog.STIntersects(
\n  H.geog.STIntersection(C.geog).STBuffer(2000).STIntersection(C.geog)
\n)=1 and Description = 'bank' <\/span>\n<\/p>\n

\nSo watch out for introduction of excess complexity. Hope this was helpful.<\/font><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

After reading Isaac's recent blog posting about spatial queries and index hinting, I thought I might add some information based on a query I was working with this week. Sometimes if you have a query that's too syntactically complex, hinting won't work. In these cases you can cause the index to be used (or at least […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,36],"tags":[],"class_list":["post-676","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sql-server-spatial"],"yoast_head":"\nOn hinting spatial indexes and query complexity - Bob Beauchemin<\/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.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"On hinting spatial indexes and query complexity - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"After reading Isaac's recent blog posting about spatial queries and index hinting, I thought I might add some information based on a query I was working with this week. Sometimes if you have a query that's too syntactically complex, hinting won't work. In these cases you can cause the index to be used (or at least […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2008-09-06T11:23:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:48+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/\",\"name\":\"On hinting spatial indexes and query complexity - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2008-09-06T11:23:00+00:00\",\"dateModified\":\"2013-01-04T07:59:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2008\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2008\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"On hinting spatial indexes and query complexity\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"On hinting spatial indexes and query complexity - Bob Beauchemin","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.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/","og_locale":"en_US","og_type":"article","og_title":"On hinting spatial indexes and query complexity - Bob Beauchemin","og_description":"After reading Isaac's recent blog posting about spatial queries and index hinting, I thought I might add some information based on a query I was working with this week. Sometimes if you have a query that's too syntactically complex, hinting won't work. In these cases you can cause the index to be used (or at least […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/","og_site_name":"Bob Beauchemin","article_published_time":"2008-09-06T11:23:00+00:00","article_modified_time":"2013-01-04T07:59:48+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/","name":"On hinting spatial indexes and query complexity - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2008-09-06T11:23:00+00:00","dateModified":"2013-01-04T07:59:48+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/on-hinting-spatial-indexes-and-query-complexity\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2008","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2008\/"},{"@type":"ListItem","position":3,"name":"On hinting spatial indexes and query complexity"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/676","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=676"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/676\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=676"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=676"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=676"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}