在SQL Server中选择几何列数据需要更多时间。 [英] Selecting geometry column data taking more time in SQL server.

问题描述

Hi,

 declare @points nvarchar(500)='POINT(-76.5921662 39.2674632)'

SELECT *
  FROM [NeighborhoodsTable] 
  WHERE [NbhoodPolygon].MakeValid().STIntersects(geometry::STGeomFromText(@points, 4326)) =1

此查询需要20秒。

NbhoodPolygon列数据类型是几何。

在我的数据库中有2万卢比的记录。

帮帮我Plz ...



我尝试过：



我需要优化查询，我想快速得到结果。

推荐答案

首先不要使用SELECT *。指定要在查询中返回的列，或SQL需要执行另一个查询以获取列名。



其次，使用数据库引擎优化顾问确定查询需要索引 - 它位于SQL>>下的Windows开始菜单上性能工具

通常可以通过选择查询然后右键单击来完成，也可以使用SQL事件探查器来捕获查询的跟踪。

您还可以使用SQL Management Studio中的选项包括实际执行计划。这通常会建议缺少索引，但它不如调整向导有效。



这两种方法都不是万无一失，也不考虑您的典型工作量。阅读索引以获取有关在盲目应用建议和测试实际工作负载之前需要考虑的内容的其他信息 - 当表数据发生变化时，索引需要更新，这可能会对性能产生负面影响。



亲切的问候

Firstly do not use SELECT *. Specify the columns you want to return in your query or SQL needs to perform another query to get the column names.

Secondly, use the Database Engine Tuning Adviser to determine what indexes are required for the query - it is on the Windows Start Menu under SQL >> Performance Tools
This can be also normally be done by selecting your query & then right-clicking or can also be done by using SQL Profiler to capture a trace of the query.
You can also use the option in SQL Management Studio to "Include actual execution plan". This will often advise of missing indexes but it is not as effective as the Tuning Wizard.

Neither method is foolproof nor do they take into account your typical workload. Read up on Indexing for additional information on what you need to take into account before blindly applying recommendations & test against real workloads - indexes need to be updated when the table data is changed & this may negatively affect performance.

Kind Regards

这篇关于在SQL Server中选择几何列数据需要更多时间。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！