sql-server - 高速化 - データベース 遅い 原因




SQL Serverの空間検索がPostGISより遅いのはなぜですか? (6)

私はPostGISを使ったPostgresからSQL Serverへの空間検索機能の移動に取り組んでいます、そしてインデックスを使ったとしてもかなりひどいパフォーマンスを見ています。

私のデータはおよそ100万点です、そして、私はそれらの点のどれが与えられた形の中にあるかについて調べたいと思います、それで質問はこのように見えます:

DECLARE @Shape GEOMETRY = ...
SELECT * FROM PointsTable WHERE Point.STWithin(@Shape) = 1

かなり小さい図形を選択した場合、1秒未満の時間になることがありますが、私の図形がかなり大きい場合(それが時には大きい場合)、5分以上の時間がかかることがあります。 私がPostgresで同じ検索を実行した場合、それらは常に1秒未満です(実際、ほとんどすべてが200ミリ秒未満です)。

私は私のインデックスにいくつかの異なるグリッドサイズ(すべて高、すべて中、すべて低)、オブジェクトごとに異なるセル(16、64、256)を試してみました。 もっと組み合わせを試してみたいのですが、どの方向に進むべきかさえわかりません。 オブジェクトあたりのセル数が多い? もっと少なく? グリッドサイズの奇妙な組み合わせは?

私は自分のクエリプランを見て、彼らは常にインデックスを使用しています、それはまったく役に立ちません。 私はインデックスなしでも試しました、そしてそれはそれほど悪くありません。

誰かがこれについて何かアドバイスがありますか? 私が見つけることができるすべては "私達はあなたにインデックスについてのアドバイスを与えることはできません、ただすべてを試してみて、おそらくうまくいくでしょう"を示唆していますが、インデックスを作成するのに10分かかっています。

編集:私もこれをマイクロソフトフォーラムに投稿しまし 。 これが彼らがそこで要求したいくつかの情報です:

私が手に入れられる最高のワーキングインデックスはこれです:

CREATE SPATIAL INDEX MapTesting_Location_Medium_Medium_Medium_Medium_16_NDX
    ON MapTesting (Location)
 USING GEOMETRY_GRID
  WITH (
    BOUNDING_BOX = ( -- The extent of our data, data is clustered in cities, but this is about as small as the index can be without missing thousands of points
        XMIN = -12135832,
        YMIN = 4433884,
        XMAX = -11296439,
        YMAX = 5443645),
    GRIDS = (
        LEVEL_1 = MEDIUM,
        LEVEL_2 = MEDIUM,
        LEVEL_3 = MEDIUM,
        LEVEL_4 = MEDIUM),
     CELLS_PER_OBJECT = 256 -- This was set to 16 but it was much slower
  )

インデックスを使用する際に問題がいくつかありましたが、これは異なります。

これらのテストでは、私の各インデックスに対してWITH(INDEX(...))句を指定してテスト検索(私の最初の投稿にリストされているもの)を実行しました。どんなヒントでも。 各インデックスと同じ検索形状を使用してsp_help_spatial_geometry_indexも実行しました。 上記のインデックスは最速で実行され、sp_help_spatial_geometry_indexで最も効率的であるとリストされていました。

検索を実行すると、これらの統計が表示されます。

(1 row(s) affected)
Table 'MapTesting'. Scan count 0, logical reads 361142, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'extended_index_592590491_384009'. Scan count 1827, logical reads 8041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 6735 ms,  elapsed time = 13499 ms.

ランダムな点をデータとして使用することも試みましたが(実際のデータを提供することはできないため)、この検索はランダムなデータを使用すると非常に高速になります。 これは、私たちの問題はグリッドシステムが私たちのデータとどのように連携しているかであると私たちに信じさせます。

私たちのデータは州全体の住所であるため、非常に高密度の領域がいくつかありますが、そのほとんどは疎なデータです。 問題は、グリッドサイズの設定が両方に適していないことです。 グリッドがHIGHに設定されていると、インデックスは低密度領域では多すぎるセルを返し、グリッドがLOWに設定されていると、グリッドは高密度領域では役に立たなくなります(中程度、それほど悪くはありませんがそれでも良くありません)。

インデックスを使用することはできますが、役に立ちません。 すべてのテストは「実際の実行計画の表示」をオンにして実行され、常にインデックスが表示されます。


2つのパスに分割してみてください。

  1. .Filter()一時テーブルに候補を選択します。
  2. .STWithin()候補をクエリします。

例えば:

SELECT * INTO #this FROM PointsTable WHERE Point.Filter(@Shape) = 1
SELECT * FROM #this WHERE Point.STWithin(@Shape) = 1

SELECT *を実際の列のみに置き換えて、入出力を減らす必要があります)

この種のマイクロ最適化は必要ではないはずですが、私は前にまともなパフォーマンスの改善を見たことがあります。 また、(1)と(2)の比率によって、指数がどれだけ選択的であるかを判断することができます。


SQL Serverの空間的拡張と、インデックスが効率的に使用されるようにする方法について、いくつか説明します。

明らかに、解析者が解析時に実際のジオメトリを知らない場合、プランナは良い計画を立てるのが困難です。 autorはexec sp_executesqlを挿入することを勧めexec sp_executesql

交換します。

-- does not use the spatial index without a hint
declare @latlonPoint geometry = geometry::Parse('POINT (45.518066 -122.767464)')
select a.id, a.shape.STAsText() 
from zipcodes a 
where a.shape.STIntersects(@latlonPoint)=1
go

と:

-- this does use the spatial index without using a hint
declare @latlonPoint geometry = geometry::Parse('POINT (45.518066 -122.767464)')
exec sp_executesql 
N'select a.id, a.shape.STAsText() 
from zipcodes a 
where a.shape.STIntersects(@latlonPoint)=1', N'@latlonPoint geometry', @latlonPoint
go

私の直感的な反応は、「Microsoftはそれを早くすることを煩わさなかったので、それはエンタープライズ機能ではないので」です。 多分私は皮肉なことです。

私がなぜPostgresから移行しているのか、よくわかりません。


私はSTIntersectsがSTWithinよりもインデックスを使用するためによりよく最適化されていると思います、特により大きな形状のために。


私は空間的な問い合わせには慣れていませんが、パラメータ化された問い合わせの問題かもしれません。

(パラメータを使用せずに)固定値(パラメータ化されたクエリで動作が遅い値を使用)でクエリを作成して実行します。 パラメータ化されたバージョンと時間を比較してください。 はるかに速い場合は、問題はパラメータ化されたクエリです。

上記の方がはるかに速い場合は、文字列に埋め込まれたパラメータ値を使用してSQL文字列を動的に構築します。これにより、問題の原因となっているパラメータを削除できます。


空間インデックスを正しく設定しましたか? あなたのバウンディングボックスは正しいですか? すべての点は中ですか? あなたの場合は、おそらくHHMM for GRIDSが最善の方法で動くでしょう(やはり配給箱によります)。

何が悪いのか調べるために、sp_help_spatial_geometry_indexを使用してみることができますか? http://msdn.microsoft.com/en-us/library/cc627426.aspx

代わりにフィルタ操作を使ってみて、どのようなperf番号を取得したのか教えてください。 (二次フィルタを通過せずに一次フィルタ(インデックス使用)のみを実行します(真の空間操作))

設定に問題があります。 Spatialは確かに新機能ですが、それほど悪くありません。





spatial-index