<\/HEAD>
SQL Spatial Index Tuningに関する意見やリソースは、Sequential GUIDの使用が良いか悪いかについての意見と同じくらいたくさんあります。The Black Art Of Spatial Index Tuning In SQL Server | boomphisto<\/A> 、ArcGIS Help 10.1、<\/A>Spatial Indexing Overview<\/A> 、<\/A> Spatial Indexing: From 4 Days to 4 Hours - CSS SQL Server Engineers - Site Home - MSDN Blogs<\/A>、sp_help_spatial_geography_histogram and Indexing geography data in SQL Server Denali | Alastair Aitchison<\/A> 、Basic Multi-Level Grids - Isaac @ MSDN - Site Home - MSDN Blogs<\/A> 、http:\/\social.technet.microsoft.com\wiki\contents\articles\9694.tuning-spatial-point-data-queries-in-sql-server-2012.aspa6<\/_A> 、そしてもちろん、もしパフォーマンスの悪い spatial index に苦労してインターネットに投稿したことがあれば、おそらくこの人から返信をもらったことでしょう。 <\/_SPAN><\/_P><\/_P>残念ながら、私たちの多くはデータベースプログラマーではなく、SQL構文で夢を見るわけでもありません。SQLは中国語辞書を読んでフランス語を学ぶようなものです。あなたはどうかわかりませんが、私はSQLコードの一行も理解できません。他人のSQLスニペットをコピーして動作するまで改造するのは喜んでやります。特にArcGIS Helpのこの文言が好きです：「ArcGIS for Desktopを使ってデータを作成すると、空間グリッドインデックスが自動的に計算されます。」これは「キーをイグニッションに差し込めば、車は店まで走り、牛乳を買い、途中でアライグマを轢かない」と言っているようなものです。マサチューセッツ州のドライバーでない限り、明らかにステアリングホイールを回し、アクセルペダルを無数の組み合わせで踏むなど調整が必要であり、それによって牛乳屋に行きアライグマを避けることができます。同様にSDE DatabaseのSQL Spatial Indexもそうです。ソフトウェアが箱から出してすぐに有効にしてくれるからといって、そのインデックスがあなたの特定のデータ環境に最適化されているとは限りません。 <\/_SPAN><\/_SPAN><\/_P><\/_P>ArcGISはデフォルトで16 Cells Per Object、すべてのレベルがMedium Grid Levelsに設定された空間インデックスを作成します。<\/_SPAN><\/_P><\/_P>
<\/_SPAN><\/_P>
<\/_P>ストレージタイプがGeometryであることに注目してください。これは皆さんが発見しつつある「新しい」ESRIのデフォルトストレージ形式です。 <\/_P><\/_P>「Out of the Box」の空間インデックスのパフォーマンスを見てみましょう。空間インデックスを「簡単かつ迅速」にテストする方法の一つはSpatial Index Stored Proceduresを使うことです。 <\/_P><\/_P>CREATE SPATIAL INDEX
--これはARCGISによって作成されたインデックスのデフォルト名です
[S1169_idx]
ON
--これはARCGISユーザーが作成時に指定したテーブル名です
[dbo].[TEST_GEOM]
(
[SHAPE]
)USING GEOMETRY_GRID
WITH (BOUNDING_BOX =(227166.13, 3925740.74, 314851.6915, 3968047.64),
GRIDS =(
LEVEL_1 = MEDIUM,
LEVEL_2 = MEDIUM,
LEVEL_3 = MEDIUM,
LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
--DROP_EXISTING = ON はIDX削除なしで何度も実行可能にします
DROP_EXISTING = ON,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
DECLARE @geom geometry
--ポリゴンはバウンディングボックスより小さい領域です
SET @geom = GEOMETRY::STGeomFromText('POLYGON ((247804.201 3943957.896, 29932.568 3943963.210, 247671.344 3942876.441, 247684.630 3943652.325,247804.201 3943957.896))', 26917)jjj
exec sp_help_spatial_geometry_index 'TEST_GEOM', 'S1169_idx', 1, @geom大量の出力があります。最初に目についたのは
| Primary_Filter_Efficiency | 19.634703196347 |
と
| Internal_Filter_Efficiency | 0 |
私はSQLや空間インデックスの専門家ではありませんが、その数値は良くないと思います。だから描画時間が遅く、空間クエリも鈍く、全体的にデータベースのパフォーマンスがひどいのでしょう。すべてのArcGIS作成テーブルは同じインデックスパラメータです！
ちょっと遊びで上記コードスニペット内のこれだけ変更してみましょう：
CELLS_PER_OBJECT = 4096
効率性の向上をご覧ください！
| Internal_Filter_Efficiency | 76.7441860465116 |
| Primary_Filter_Efficiency | 91.4893617021277 |
繰り返しますが、私はSQL Spatial Index Tuningの専門家ではありませんが、何か掴んだ気がします。私のテストデータは「非常に複雑なラインストリング」であり、GIS業界ではそれがほとんどすべてのデータだからです。偶然にも、このシナリオでCells Per Objectを8192に設定することは良い出発点となります。
しかし16から8092までにはたくさん（非常に多く）の値があり、それからLow、Medium、Highというすべての組み合わせがあります。それらをテストして、多くの場合で最高のパフォーマンスを提供する可能性が高い空間インデックス設定を決定できたらどうでしょう？
SDE Hacker登場...
私はこのgeospatial - Selecting a good SQL Server 2008 spatial index with large polygons - Stack Overflow
投稿を偶然見つけました。その中で誰かがCell SizesとGrid Levelsをループしてすべての組み合わせ（ユーザー指定）について空間クエリ結果を報告するSQLストアドプロシージャを書いていました。私は好奇心旺盛なので、多くの出力を追加しようとしてコードを壊しましたが、最終的には動作させました。こちらです：
--元々のSQLコードはこちらから
--http://stackoverflow.com/users/2250424/greengeo
--SPATIAL INDEX HELP出力を含むよう修正
--テーブル内
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--感嘆符部分をご覧ください このコード
--このストアドプロシージャが機能するためには、いくつかのユーザー提供変数を入力する必要があります
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
USE DATBASE
GO
CREATE PROCEDURE dbo.sp_tune_spatial_index
(
@tabnm VARCHAR(MAX), -- このパラメータは、インデックスを調整する空間テーブルの名前を格納します
@idxnm VARCHAR(MAX), -- このパラメータは、指定されたテーブルの空間インデックスの名前を格納します
@min_cells_per_obj INT, -- テストするオブジェクトあたりの最小セル数。2から開始することを推奨します。
@max_cells_per_obj INT, -- テストするオブジェクトあたりの最大セル数。
/* テストには、テストクエリ1および2で使用する2つのgeometryインスタンスが必要です。
最初のものはデフォルト範囲のエリアをカバーすべきです。2番目はズームインしてパンしたときに表示されるエリアのおおよそのサイズをカバーすべきです。
これらはプロシージャ内で実行されるため、GEOMETRY型の変数ではなく、geometryインスタンスを作成する文字列を格納する必要があります。
これらのインスタンスのSRIDは、テスト対象のテーブルと一致しなければなりません。 */
@testgeom1 VARCHAR(MAX), -- テストで使用される最初のgeometryインスタンス作成文字列を格納します
@testgeom2 VARCHAR(MAX) -- テストで使用される2番目のgeometryインスタンス作成文字列を格納します
)
AS
SET NOCOUNT ON;
/* このプロシージャを実行する前に、2つのテーブルが必要です。これらのテーブルはここで作成され、プロシージャ実行準備が整います。 */
PRINT '必要なテーブルを確認しています...'
IF EXISTS(SELECT 1 FROM sysobjects WHERE name IN ('cell_opt_perm', 'spat_idx_test_result'))
BEGIN
PRINT '... "cell_opt_perm" と "spat_idx_test_result" テーブルが存在します。'
END
ELSE
BEGIN
PRINT '... "cell_opt_perm" と "spat_idx_test_result" テーブルを作成しています。'
CREATE TABLE cell_opt_perm(
[perm_id] [smallint] NOT NULL,
[permutation] [nvarchar](4) NOT NULL,
[level1] [nvarchar](6) NOT NULL,
[level2] [nvarchar](6) NOT NULL,
[level3] [nvarchar](6) NOT NULL,
[level4] [nvarchar](6) NOT NULL
)
INSERT INTO cell_opt_perm ([perm_id], [permutation], [level1], [level2], [level3], [level4])
VALUES (1,'LLLL','LOW','LOW','LOW','LOW'),
(2,'LLLM','LOW','LOW','LOW','MEDIUM'),
(3,'LLLH','LOW','LOW','LOW','HIGH'),
(4,'LLML','LOW','LOW','MEDIUM','LOW'),
(5,'LLMM','LOW','LOW','MEDIUM','MEDIUM'),
(6,'LLMH','LOW','LOW','MEDIUM','HIGH'),
(7,'LLHL','LOW','LOW','HIGH','LOW'),
(8,'LLHM','LOW','LOW','HIGH','MEDIUM'),
(9,'LLHH','LOW','LOW','HIGH','HIGH'),
(10,'LMLL','LOW','MEDIUM','LOW','LOW'),
(11,'LMLM','LOW','MEDIUM','LOW','MEDIUM'),
(12,'LMLH','LOW','MEDIUM','LOW','HIGH'),
(13,'LMML','LOW','MEDIUM','MEDIUM','LOW'),
(14,'LMMM','LOW','MEDIUM','MEDIUM','MEDIUM'),
(15,'LMMH','LOW','MEDIUM','MEDIUM','HIGH'),
(16,'LMHL','LOW','MEDIUM','HIGH','LOW'),
(17,'LMHM','LOW','MEDIUM','HIGH','MEDIUM'),
(18,'LMHH','LOW','MEDIUM','HIGH','HIGH'),
(19,'LHLL','LOW','HIGH','LOW','LOW'),
(20,'LHLM','LOW','HIGH','LOW','MEDIUM'),
(21,'LHLH','LOW','HIGH','LOW','HIGH'),
(22,'LHML','LOW','HIGH','MEDIUM','LOW'),
(23,'LHMM','LOW','HIGH','MEDIUM','MEDIUM'),
(24,'LHMH','LOW','HIGH','MEDIUM','HIGH'),
(25,'LHHL','LOW','HIGH','HIGH','LOW'),
(26,'LHHM','LOW','HIGH','HIGH','MEDIUM'),
(27,'LHHH','LOW','HIGH』『高い』),
nbsp;(28, 'MLLL', '中', '低', '低', '低') ,
bsp;(29, 'MLLM', '中', '低', '低', '中') ,
bsp;(30, 'MLLH', '中', '低', '低', '高') ,
bsp;(31, 'MLML', '中', '低', '中', '低') ,
bsp;(32, 'MLMM', '中', '低', '中', '中') ,
bsp;(33, 'MLMH', '中', '低', '中', '高') ,
bsp;(34, 'MLHL', '中', '低', '高', '低') ,
bsp;(35, 'MLHM', '中', '低', '高', '中') ,
bsp;(36, 'MLHH', '中', '低', '高', '高') ,
bsp;(37, 'MMLL', '中', 『中』、『低』、『低』)、
bsp;(38,『MMLM』、『中』、『中』、『低』、『中』)、
bsp;(39,『MMLH』、『中』、『中』、『低』、『高』)、
bsp;(40,『MMML』、『中』、『中』、『中』、『低』)、
bsp;(41,『MMMM』、『中』、『中』、『中』、『中』)、
bsp;(42,『MMMH』、『中』、『中』、『中』、『高』)、
bsp;(43,『MMHL』、『中』、『中』、『高』、『低』)、
bsp;(44,『MMHM』、『中』、『中』、『高』、『中』)、
bsp;(45,『MMHH』、『中』、『中』、『高』、『高'}),
bsp;(46,『MHLL』『Medium』『High』『Low』『Low'）、
bsp;(47,『MHLM』『Medium』『High』『Low』『Medium'）、
bsp;(48,『MHLH』『Medium』『High』『Low』『High'）、
bsp;(49,『MHML』『Medium』『High』『Medium』『Low'）、
bsp;(50,『MHMM』『Medium』『High』『Medium』『Medium'）、
bsp;(51,『MHMH』『Medium』『High』『Medium』『High'）、
bsp;(52,『MHHL』『Medium』『High』『High』『Low'）、
bsp;(53,『MHHM』『Medium』『High』『High』『Medium'）、
bsp;(54,『MHHH』『Medium』『High』『High』『High'）、
bsp;(55,『HLLL』『High』『Low』『Low』『Low'）、
bsp;(56,『HLLM』『High』『Low』『Low』『Medium'）、
bsp;(57,『HLLH』『High』『Low』『Low』『High'）、
bsp;(58,『HLML』『High』『Low']['Medium']['Low']、
bsp;(59、'HLMM'、'High'、'Low'、'Medium'、'Medium') 、& nbsp ;(60、'HLMH'、'High'、'Low'、'Medium'、'High') 、& nbsp ;(61、'HLHL'、'High'、'Low'、'High'、'Low') 、& nbsp ;(62、'HLHM'、'High'、'Low'、'High'' 、& nbsp ;(63、‘HLHH’ 、‘High’ 、‘Low’ 、‘High’ 、‘High’) 、& nbsp ;(64 、‘HMLL’ 、‘High’ 、‘Medium’ 、‘Low’ 、‘Low’) 、& nbsp ;(65 、‘HMLM’ 、‘High’ 、‘Medium’ 、‘Low’ 、‘Medium’) 、& nbsp ;(66 、‘HMLH’ 、‘High’ 、‘Medium’ 、‘Low’ 、‘High’) 、& nbsp ;(67 、‘HMML’ 、‘High’ 、‘Medium’ 、‘Medium’ 、‘Low’) 、& nbsp ;(68 、‘HMMM’ 、‘High’ 、‘Medium’ 、‘Medium’ 、‘Medium’) 、& nbsp ;(69 、‘HMMH’ 、‘High’ 、‘Medium’ 、‘Medium’ 、‘High’) 、& nbsp ;(70 ‘HMHL’, ‘High’, ‘Medium’, ‘High’, ‘Low’) , & nbsp ;(71 ‘HMHM’, ‘ High’, ‘ Medium’, ‘ High’, ‘ Medium’) , & nbsp ;(72 ‘HMHH’, ‘ High’, ‘ Medium’, ‘ High’, ‘ High’) , & nbsp ;(73 ‘HHLL’, ‘ High’, ‘ High’, ‘ Low’, ‘ Low’) , & nbsp ;(74 ‘HHLM’, ‘ High’, ‘ High’, ‘ Low’, ‘ Medium’) , & nbsp ;(75 ‘HHLH’, ‘ High’, ‘ High’, ‘ Low’, ‘ High’) , & nbsp ;(76 ‘HHML’, ‘ High’, ‘ High’, ‘ Medium’, ‘ Low’) , & nbsp ;(77 ‘HHMM’, ‘ High’, ‘ High’, ’ Medium ’,’ Medium ’) , & nbsp ;(78 ’ HHMH ’,’ High ’,’ High ’,’ Medium ’,’ High ’) , & nbsp ;(79 ’ HHHL ’,’ High ’,’ High ’,’ High ’,’ Low ’) , & nbsp ;(80 ’ HHHM ’,’ High ’,’ High ’,’ High ’,’ Medium ’) , & nbsp ;(81 ’ HHHH ’,’ High ’,’ High ’,’ High ’,’ High ’)
CREATE TABLE spat_idx_test_result(
[perm_id] [int] NOT NULL,
[num_cells] [int] NOT NULL,
[permut] [nvarchar](4) NOT NULL,
[g1t1] [bigint] NULL,
[g1t2] [bigint] NULL,
[g1t3] [bigint] NULL,
[g1t4] [bigint] NULL,
[g2t1] [bigint] NULL,
[g2t2] [bigint] NULL,
[g2t3] [bigint] NULL,
[g2t4] [bigint] NULL,
[PF_EFF][float] NULL,
[IF_EFF][float] NULL,
[GRIDL1] [int] NULL,
[GRIDL2] [int] NULL,
[GRIDL3] [int] NULL,
[GRIDL4] [int] NULL,
[TPIR] [bigint] NULL,
[TPIP] [bigint] NULL,
[ANOIRPBR] [bigint] NULL,
[TNOOCILFQ] [bigint] NULL,
[TNOOCIL3FQ] [bigint] NULL,
[TNOOCIL4FQ] [bigint] NULL,
[TNOOCIL0II] [bigint] NULL,
[TNOOCIL4II] [bigint] NULL,
[TNOIOIL3FQ] [bigint] NULL,
[ TNOIOIL4FQ ] 〔 bigint 〕 null ，
〔 ITTCNTLGP 〕 〔 float 〕 null ，
〔 INTTTCNTLGP 〕 〔 float 〕 null ，
〔 BTTCNTLGP 〕 〔 float 〕 null ，
〔 ACPONTLGP 〕 〔 float 〕 null ，
〔 AOPG 〕 〔 float 〕 null ，
〔 NORSBPF 〕 〔 bigint 〕 null ，
〔 NORSBIF 〕 〔 bigint 〕 null ，
〔 NOTSFIC 〕 〔 bigint 〕 null ，
〔 NORO 〕 〔 bigint 〕 null ，
〔 PORNBPF 〕 float null ，
〔 POPFRSBIF 〕 float null
）
INSERT INTO dbo.spat_idx_test_result
VALUES (0,16,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
END
/* "spat_idx_test_result" テーブルからすべての行を削除します。これにより、新しい結果を格納する準備が整います。
!!!警告!!! テストが途中で中断された場合、このテーブルはクリアされてテストは最初から再開されます。
途中から開始するように変更することも可能ですが、自分は時間がなく、この方法で十分でした。 */
DELETE FROM spat_idx_test_result
WHERE perm_id != 0
/* カウンターを設定 */
DECLARE @a1 INT
DECLARE @a2 INT
DECLARE @a3 INT
DECLARE @a4 INT
/* 空間インデックス再構築および統計記録に使用するhigh/medium/low値と順列を保持する変数を設定 */
DECLARE @lev1 VARCHAR(6)
DECLARE @lev2 VARCHAR(6)
DECLARE @lev3 VARCHAR(6)
DECLARE @lev4 VARCHAR(6)
DECLARE @permut VARCHAR(6)
DECLARE @num_cell VARCHAR(4)
DECLARE @time_str VARCHAR(20)
DECLARE @perm_id VARCHAR(20)
DECLARE @x xml
DECLARE @pf_eff FLOAT
/* テストクエリ開始および終了時刻を保持する変数を作成 */
DECLARE @start_t DATETIME
DECLARE @end_t DATETIME
DECLARE @elapse_t INT
/* セルオプション順列のループ開始 */
SET @a1 = @min_cells_per_obj
WHILE @a1 <= @max_cells_per_obj
BEGIN
SET @a2 = 1
PRINT 'オブジェクトあたり '+CAST(@a1 AS VARCHAR(10))+' セルでテスト開始'
WHILE @a2 < 82
BEGIN
SELECT @lev1 = level1, @lev2 = level2, @lev3 = level3, @lev4 = level4 FROM cell_opt_perm WHERE perm_id = @a2
SET @permut = ''''+(SELECT permutation FROM cell_opt_perm WHERE perm_id = @a2)+'''''
EXEC
(''
CREATE SPATIAL INDEX '+@idxnm+' ON '+@tabnm+'
(
[SHAPE]
)
USING GEOMETRY_GRID
WITH
(
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--バウンディングボックスは空間テーブルのバウンディングボックスと完全に一致させてください。
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
BOUNDING_BOX =(227166.13, 3925740.74, 314851.6915, 3968047.64),
GRIDS =(LEVEL_1 = '+@lev1+' ,LEVEL_2 = '+@lev2+' ,LEVEL_3 = '+@lev3+' ,LEVEL_4 = '+@lev4+' ),
CELLS_PER_OBJECT = ' +@a1 +' ,
PAD_INDEX = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = ON,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 100
)
ON [PRIMARY]
'
)
PRINT 'インデックスを再構築しました: ' +@permut
SET @a3 = 1
SET @a4 = 1
WHILE @a3 < 5
BEGIN
SET @start_t = GETDATE()
EXEC
(
'CREATE TABLE #tmp_tab (shp GEOMETRY)
DECLARE @g1 GEOMETRY
SET @g1 = ' +@testgeom1 +'
INSERT #tmp_tab (shp)
SELECT
r.Shape AS shp
FROM
' +@tabnm +' r
WHERE
r.SHAPE.STIntersects(@g1) = 1
DROP TABLE #tmp_tab'
)
SET @end_t = GETDATE()
SET @elapse_t = (SELECT DATEDIFF(MS, @start_t, @end_t))
SET @num_cell = CAST(@a1 AS VARCHAR(6))
SET @time_str = CAST(@elapse_t AS VARCHAR(20))
IF @a3 = 1
BEGIN
IF (SELECT TOP 1 perm_id FROM spat_idx_test_result) IS NULL
BEGIN
SET @perm_id = 1
END
ELSE
BEGIN
SET @perm_id = CAST((SELECT MAX(perm_id+1) FROM spat_idx_test_result) AS VARCHAR(20))
END
EXEC
(
'
INSERT INTO spat_idx_test_result (perm_id, num_cells, permut, g1t' +@a3 +')
VALUES (' +@perm_id +', ' +@num_cell +', ' +@permut +', ' +@time_str +')'
)
END
ELSE
EXEC
(
'
UPDATE spat_idx_test_result
SET
num_cells = ' +@num_cell +',
permut = ' +@permut +',
g1t' +@a3 +' = ' +@time_str +'
WHERE perm_id = ' +@perm_id
)
SET @a3 = @a3 + 1
END
WHILE @a4 < 5
BEGIN
SET @start_t = GETDATE()
EXEC
nbsp;
nbsp;
nbsp;
'tCREATE TABLE #tmp_tab (shp GEOMETRY)
nbsp;
declare @g2 GEOMETRY
nbsp;
sET @g2 =' +@testgeom2+'
nbsp;
insert #tmp_tab (shp)
nbsp;
select
r.Shape as shp
from
' +@tabnm +' r
where
r.SHAPE.STIntersects(@g2) = 1
drop table #tmp_tab'
nbsp;
)
sET @end_t=GETDATE()
sET @elapse_t=(select datediff(ms,@start_t,@end_t))
sET @num_cell=cast(@a1 as varchar(6))
sET @time_str=cast(@elapse_t as varchar(20))
sET exec(
declare @geom geometry
declare @x xml
declare @PFVALUE float
declare @IFVALUE float
declare @GRIDL1VALUE int
declare @GRIDL2VALUE int
declare @GRIDL3VALUE int
declare @GRIDL4VALUE int
declare @TPIRVALUE bigint
declare @TPIPVALUE bigint
declare @ANOIRPBRVALUE bigint
declare @TNOOCILFQVALUE bigint
declare @TNOOCIL0IIVALUE bigint
declare @TNOOCIL4IIVALUE bigint
declare @TNOOCIL3FQVALUE bigint
declare @TNOOCIL4FQVALUE bigint
declare @TNOIOIL3FQVALUE bigint
declare @TNOIOIL4FQVALUE bigint
declare @ITTCNTLGPVALUE float
declare @INTTTCNTLGPVALUE float
declare @BTTCNTLGPVALUE float
declare @ACPONTLGPVALUE float
declare @AOPGVALUE float
declare @NORSBPFVALUE bigint
declare @NORSBIFVALUE bigint
declare @NOTSFICVALUE bigint
declare @NOROVALUE bigint
declare @PORNBPFVALUE float
declare @POPFRSBIFVALUE float
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--必ず以下のGEOMETRY変数を編集して、あなたのバウンディングボックス内のポリゴンを表すようにしてください
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
sET @geom=GEOMETRY::STGeomFromText('POLYGON ((247804.201 3943957.896, 29932.568 3943963.210, 247671.344 3942876.441, 247684.630 3943652.325,247804.201 3943957.896))',26917)
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--必ず空間テーブルの名前と空間インデックスの名前を指定してください
--sp_help_spatial_geometry_index_xmlの変数で
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
eXEC sp_help_spatial_geometry_index_xml TEST_GEOM , S1169_idx , 1, @geom, @x output
sET @PFVALUE= @x.value('(\/Primary_Filter_Efficiency\/text())[1]', 'float')
sET @IFVALUE= @x.value('(\/Internal_Filter_Efficiency\/text())[1]', 'float')
sET @GRIDL1VALUE= @x.value('(\/Grid_Size_Level_1\/text())[1]', 'int')
sET @GRIDL2VALUE= @x.value('(\/Grid_Size_Level_2\/text())[1]', 'int')
sET @GRIDL3VALUE= @x.value('(\/Grid_Size_Level_3\/text())[1]', 'int')
sET @GRIDL4VALUE= @x.value('(\/Grid_Size_Level_4\/text())[1]', 'int')
sET @TPIRVALUE= @x.value('(\/Total_Primary_Index_Rows\/text())[1]', 'bigint')
sET @TPIPVALUE= @x.value('(\/Total_Primary_Index_Pages\/text())[1]', 'bigint')
sET @ANOIRPBRVALUE= @x.value('(\/Average_Number_Of_Index_Rows_Per_Base_Row\/text())[1]', 'bigint')
sET @TNOOCILFQVALUE= @x.value('(\/Total_Number_Of_ObjectCells_In_Level0_For_QuerySample\/text())[1]', 'bigint')
sET @TNOOCIL0IIVALUE= @x.value('(\/Total_Number_Of_ObjectCells_In_Level0_In_Index\/text())[1]', 'bigint')
sET @TNOOCIL4IIVALUE= @x.value('(\/Total_Number_Of_ObjectCells_In_Level4_In_Index\/text())[1]', 'bigint')
sET @TNOOCIL3FQVALUE= @x.value('(\/Total_Number_Of_ObjectCells_In_Level3_For_QuerySample\/text())[1]', 'bigint')
sET @TNOOCIL4FQVALUE= @x.value('(\/Total_Number_Of_ObjectCells_In_Level4_For_QuerySample\/text())[1]', 'bigint')
sET @TNOIOIL3FQVALUE= @x.value('(\/Total_Number_Of_Interior_ObjectCells_In_Level3_For_QuerySample\/text())[1]', 'bigint')
sET @TNOIOIL4FQVALUE= @x.value('(\/Total_Number_Of_Interior_ObjectCells_In_Level4_For_QuerySample\/text())[1]', 'bigint')
sET @ITTCNTLGPVALUE= @x.value('(\/Interior_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage\/text())[1]', 'float')
sET @INTTTCNTLGPVALUE= @x.value('(\/Intersecting_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage\/text())[1]', 'float')
sET @BTTCNTLGPVALUE= @x.value('(\/Border_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage\/text())[1]', 'float')
sET @ACPONTLGPVALUE= @x.value('(\/Average_Cells_Per_Object_Normalized_To_Leaf_Grid\/text())[1]', 'float')
sET @AOPGVALUE= @x.value('(\/Average_Objects_PerLeaf_GridCell\/text())[1]', 'float')
sET @NORSBPFVALUE= @x.value('(\/Number_Of_Rows_Selected_By_Primary_Filter\/text())[1]', 'bigint')
sET @NORSBIFVALUE= ＠ｘ．ｖａｌｕｅ（＇（／Ｎｕｍｂｅｒ＿Ｏｆ＿Ｒｏｗｓ＿Ｓｅｌｅｃｔｅｄ＿Ｂｙ＿Ｉｎｔｅｒｎａｌ＿Ｆｉｌｔｅｒ／ｔｅｘｔ（））［１］＇，＇ｂｉｇｉｎｔ＇）） 。
nSET NOTSFIC VALUE=@ x .value('(/Number Of Times Secondary Filter Is Called/text())[1]','bigint') nSET NORO VALUE=@ x .value('(/Number Of Rows Output/text())[1]','bigint') nSET PORNBPF VALUE=@ x .value('(/Percentage Of Rows NotSelected By Primary Filter/text())[1]','float') nSET POPFRSBIF VALUE=@ x .value('(/Percentage Of Primary Filter Rows Selected By Internal Filter/text())[1]','float') nUPDATE spat_idx_test_result nSET nnum_cells=' +num_cell+', npermut=' +permut+', ng2t' +a4+'=' +time_str+', nPF_EFF=@PF VALUE,nIF_EFF=@IF VALUE,nGRIDL1=@GRIDL １ VALUE,nGRIDL2=@GRIDL２ VALUE,nGRIDL3=@GRIDL３ VALUE,nGRIDL4=@GRIDL４ VALUE,nTPIR=@TPIR VALUE,nTPIP=@TPIP VALUE,nANOIRPBR=@ANOIRPBR VALUE,nTNOOCILFQ=@TNOOCILFQ VALUE,nTNOOCIL0II=@TNOOCIL0IIVALUE,nTNOOCIL4II=@TNOOCIL4IIVALUE,nTNOOCIL3FQ= ；＠ＴＮＯＯＣＩＬ３ＦＱＶＡＬＵＥ， ｎＴＮＯＯＣＩＬ４ＦＱ＝＆nbsp；＠ＴＮＯＯＣＩＬ４ＦＱＶＡＬＵＥ， ｎＴＮＯＩＯＩＬ３ＦＱ＝＠ＴＮＯＩＯＩＬ３ＦＱＶＡＬＵＥ， ｎＴＮＯＩＯＩＬ４ＦＱ＝＠ＴＮＯＩＯＩＬ４ＦＱＶＡＬＵＥ， ｎＩＴＴＣＮＴＬＧＰ＝＠ＩＴＴＣＮＴＬＧＰ ＶＡＬＵＥ， ｎＩＮＴＴＴＣＮＴＬＧＰ＝＠ＩＮＴＴＴＣＮＴＬＧＰ ＶＡＬＵＥ， ｎＢＴＴＣＮＴＬＧＰ＝＠ＢＴＴＣＮＴＬＧＰ ＶＡＬＵＥ， ｎＡＣＰＯＮＴＬＧＰ＝＠ＡＣＰＯＮＴ ＬＧＰ ＶＡ Ｌ Ｕ Ｅ， ｎＡＯＰＧ＝＠ＡＯＰＧ ＶＡ Ｌ Ｕ Ｅ， ｎＮＯＲＳＢＰＦ＝＠ＮＯＲＳＢＰＦ ＶＡ Ｌ Ｕ Ｅ， ｎＮＯＲＳＢ ＩＦ＝＠ＮＯＲＳＢ ＩＦ ＶＡ Ｌ Ｕ Ｅ， ｎＮＯＴＳＦ ＩＣ＝＠ＮＯＴＳＦ ＩＣ ＶＡ Ｌ Ｕ Ｅ， ｎＮＯＲＯ＝＠ＮＯＲＯ ＶＡ Ｌ Ｕ Ｅ， ｎＰＯＲＮＢＰＦ＝＠ＰＯＲＮＢＰＦ ＶＡ Ｌ Ｕ Ｅ， ｎＰＯＰＦＲＳＢ ＩＦ＝＠ＰＯＰＦＲＳＢ ＩＦ ＶＡ Ｌ Ｕ Ｅ nWHERE perm_id=' +perm_id+'
n)
nSET a4=a4+１ nEND nSET a2=a2+１ nEND nSET a１=a１+１ nEND nPRINT ''テスト完了: ''+tabnm+'' 空間インデックス: ''+idxnm+''！'' nGO</PRE>私が行ったハッキングは、インデックスヘルプストアドプロシージャのxml出力を使用して、クエリ時間に加えてより詳細な結果を書き出すことでした。 ストアドプロシージャは次のように実行されます: DECLARE BOUNDING VARCHAR(MAX) セット BOUNDING ='GEOMETRY::STGeomFromText(''POLYGON ((226805.072 3975572.527, 318101.215 3975985.165, 317894.896 3922239.073, 225360.839 3926571.772 , 226805.072 3975572.527))'',0)' DECLARE QUERY VARCHAR(MAX) セット QUERY ='GEOMETRY::STGeomFromText(''POLYGON ((247804.201 3943957.896,29932.5683943963.210,247671.3443942876.441,247684.6303943652.325,247804.2013943957.896))'',26917)' EXEC sp_tune_spatial_index TEST_GEOM , S1169_idx ,4096 ,4096 ,BOUNDING ,QUERY GO
この例では、セルサイズ4096のみをテストしていますが、8や16など任意の値範囲を使用できます。
結果は次のクエリで簡単に確認できます:
SELECT perm_id AS '順列 #' ,num_cells AS 'オブジェクトあたりのセル数' ,permut AS 'グリッド' ,g1t１ AS '全ジオメトリをクエリする時間（レベル１）ms' ,g１t２ AS '全ジオメトリをクエリする時間（レベル２）ms' ,g１t３ AS '全ジオメトリをクエリする時間（レベル 3)'
,g1t4 as '全ジオメトリをクエリするのにかかるミリ秒（レベル4）'
,g2t1 as '空間クエリを実行するのにかかるミリ秒（レベル1）'
,g2t2 as '空間クエリを実行するのにかかるミリ秒（レベル2）'
,g2t3 as '空間クエリを実行するのにかかるミリ秒（レベル3）'
,g2t4 as '空間クエリを実行するのにかかるミリ秒（レベル4）'
,PF_EFF as 'プライマリフィルタ効率'
,IF_EFF as '内部フィルタ効率'
,GRIDL1 as 'グリッドサイズ レベル1'
,GRIDL2 as 'グリッドサイズ レベル2'
,GRIDL3 as 'グリッドサイズ レベル3'
,GRIDL4 as 'グリッドサイズ レベル4'
,TPIR as 'プライマリインデックス総行数'
,TPIP as 'プライマリインデックス総ページ数'
,ANOIRPBR as 'ベース行あたりの平均インデックス行数'
,TNOOCILFQ as 'クエリサンプルのレベル0におけるオブジェクトセル総数'
,TNOOCIL3FQ as 'クエリサンプルのレベル3におけるオブジェクトセル総数'
,TNOOCIL4FQ as 'クエリサンプルのレベル4におけるオブジェクトセル総数'
,TNOOCIL0II as 'インデックス内のレベル0におけるオブジェクトセル総数'
,TNOOCIL4II as 'インデックス内のレベル4におけるオブジェクトセル総数'
,TNOIOIL3FQ as 'クエリサンプルのレベル3における内部オブジェクトセル総数'
,TNOIOIL4FQ as 'クエリサンプルのレベル4における内部オブジェクトセル総数'
,ITTCNTLGP as '葉グリッドに正規化された内部対総セル割合（パーセンテージ）'
,INTTTCNTLGP as '葉グリッドに正規化された交差対総セル割合（パーセンテージ）'
,BTTCNTLGP as '葉グリッドに正規化された境界対総セル割合（パーセンテージ）'
,ACPONTLGP as '葉グリッドに正規化されたオブジェクトあたり平均セル数'
,AOPG as '葉グリッドセルあたり平均オブジェクト数'
,NORSBPF as 'プライマリフィルタによって選択された行数'
,NORSBIF as '内部フィルタによって選択された行数'
,NOTSFIC as 'セカンダリフィルタが呼び出された回数'
,NORO as '出力された行数'
,PORNBPF as 'プライマリフィルタで選択されなかった行の割合（パーセンテージ）'
a0,POPFRSBIF as '内部フィルタによって選択されたプライマリフィルタ行の割合（パーセンテージ）'
FROM spat_idx_test_result
ORDER BY PF_EFF
| Permutation # | Cells Per Object | Grids | ms to query entire geometry (Level 1) | ms to query entire geometry (Level 2) | ms to query entire geometry (Level 3) | ms to query entire geometry (Level 4) | ms to execute spatial query (Level 1) | ms to execute spatial query (Level 2) | ms to execute spatial query (Level 3) | ms to execute spatial query (Level 4) | Primary Filter Efficiency | Internal Filter Efficiency | Grid Size Level 1 | Grid Size Level 2 | Grid Size Level 3 | Grid Size Level 4 | Total Primary Index Rows | Total Primary Index Pages | Average Number of Index Rows Per Base Row | Total Number of Object Cells in Level 0 For Query Sample | Total Number of Object Cells in Level 3 For Query Sample | Total Number of Object Cells in Level 4 For Query Sample | Total Number of Object Cells In Level 0 In Index | Total Number of Object Cells In Level 4 In Index | Total Number Of Interior ObjectCells In Level 3 For QuerySample | Total Number Of Interior ObjectCells In Level 4 For QuerySample | Interior To Total Cells Normalized To Leaf Grid Percentage | Intersecting To Total Cells Normalized To Leaf Grid Percentage | Border To Total Cells Normalized To Leaf Grid Percentage | Average Cells Per Object Normalized To Leaf Grid | Average Objects PerLeaf GridCell | Number Of Rows Selected By Primary Filter | Number Of Rows Selected By Internal Filter | Number Of Times Secondary Filter Is Called | Number Of Rows Output | Percentage Of Rows NotSelected By Primary Filter | Percentage Of Primary Filter Rows Selected By Internal Filter |
<...truncated for brevity...>
a0This この投稿はSpatial Index Tuningの表面をかすったに過ぎません。例えば、SQL 2012を使用している場合、インデックスを
Auto Gridに設定することができ、これにより8つのCell Levelsが得られ、ArcGISとの相性が良いとされています。また、Internal Filter Efficiencyなどの他のパフォーマンス要因も検討する必要があります。Spatial Index Tuningには多くのWitch MagicやSnake Oil Medicineが存在します。このブログ投稿が正しい方向へのスタートとなることを願っています！
