--MODIFIZIERT UM SPATIAL INDEX HILFE AUSGABE EINZUSCHLIESSEN
--IN TABELLE
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--SUCHEN SIE NACH DEN AUSRUFEZEICHEN IN DIESER CODE
--ES GIBT MEHRERE VOM BENUTZER GELIEFERTE VARIABLEN, DIE
--EINGEGEBEN WERDEN MÜSSEN, DAMIT DIESE SP FUNKTIONIERT
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
USE DATBASE
GO
CREATE PROCEDURE dbo.sp_tune_spatial_index
(
@tabnm VARCHAR(MAX), -- Dieser Parameter speichert den Namen der spatial Tabelle, für die Sie den Index optimieren
@idxnm VARCHAR(MAX), -- Dieser Parameter speichert den Namen des spatial Index der benannten Tabelle
@min_cells_per_obj INT, -- Minimale Zellen pro Objekt zum Testen. Empfohlen wird mit 2 zu beginnen.
@max_cells_per_obj INT, -- Maximale Zellen pro Objekt zum Testen.
\/\* Der Test benötigt zwei Geometry-Instanzen, die in Testabfrage 1 und 2 verwendet werden.
Die erste sollte den Bereich des Standard-Extents abdecken. Die zweite sollte
einen Bereich ungefähr der Größe des Bereichs abdecken, der beim Hereinzoomen und Verschieben angezeigt wird.
Es ist erforderlich, dass die Variable einen String speichert, der die Geometry-Instanz erzeugt,
da dies innerhalb der Prozedur erfolgt und
keine Variable vom Typ: GEOMETRY sein kann. Die SRID dieser Instanzen muss
mit der der Tabelle übereinstimmen, die Sie testen. *\/
@testgeom1 VARCHAR(MAX), -- Dieser Parameter speichert den Erstellungsstring der ersten Geometry-Instanz, die im Test verwendet wird
@testgeom2 VARCHAR(MAX) -- Dieser Parameter speichert den Erstellungsstring der zweiten Geometry-Instanz, die im Test verwendet wird
)
AS
SET NOCOUNT ON;
\/* Vor dem Ausführen dieser Prozedur werden zwei Tabellen benötigt. Diese Tabellen werden
hier erstellt, um die Ausführung der Prozedur vorzubereiten. *\/
PRINT 'Überprüfe erforderliche Tabellen...'
IF EXISTS(SELECT 1 FROM sysobjects WHERE name IN ('cell_opt_perm', 'spat_idx_test_result'))
BEGIN
PRINT '... Die Tabellen "cell_opt_perm" und "spat_idx_test_result" existieren.'
END
ELSE
BEGIN
PRINT '... Erstelle Tabellen "cell_opt_perm" und "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','HIGH','HIGH'),
(28,'MLLL','MEDIUM','LOW','','','','') 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 'Index neu aufgebaut für ' +@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;
inserttmp_tab(shp)
sselectr.ShapeASshpfrom'+@tabnm+'rwhere r.SHAPE.STIntersects(@g2)=1drop table#tmp_tab'
nbsp;
't)
sset@end_t=GETDATE()
sset@elapse_t=(selectDATEDIFF(MS,@start_t,@end_t))
sset@num_cell=cast(@a1asvarchar(6))
sset@time_str=cast(@elapse_tasvarchar(20))
sEXEC(
sDECLARE@geomgeometry
declare@xxmldeclare@PFVALUEfloat
declare@IFVALUEfloat
declare@GRIDL1VALUEint
declare@GRIDL2VALUEint
declare@GRIDL3VALUEint
declare@GRIDL4VALUEint
declare@TPIRVALUEbigint
declare@TPIPVALUEbigint
declare@ANOIRPBRVALUEbigint
declare@TNOOCILFQVALUEbigint
declare@TNOOCIL0IIVALUEbigint
declare@TNOOCIL4IIVALUEbigint
declare@TNOOCIL3FQVALUEbigint
declare@TNOOCIL4FQVALUEbigint
declare@TNOIOIL3FQVALUEbigint
declare@TNOIOIL4FQVALUEbigint
declare@ITTCNTLGPVALUEfloat
declare@INTTTCNTLGPVALUEfloat
declare@BTTCNTLGPVALUEfloat
declare@ACPONTLGPVALUEfloat
declare@AOPGVALUEfloat
declare@NORSBPFVALUEbigint
declare@NORSBIFVALUEbigint
declare@NOTSFICVALUEbigint
declare@NOROVALUEbigint
declare@PORNBPFVALUEfloat
declare@POPFRSBIFVALUEfloat--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!--STELLEN SIE SICHER, DASS SIE DIE GEOMETRIE-VARIABLE UNTEN BEARBEITEN, UM EIN POLYGON DARZUSTELLEN--DAS INNERHALB IHRES BOUNDING BOX LIEGT--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!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)--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!--STELLEN SIE SICHER, DASS SIE DEN NAMEN IHRER SPATIAL-TABELLE ANGEGEBEN HABEN--UND DEN NAMEN DES SPATIAL INDEX--IN DEN sp_help_spatial_geometry_index_xml VARIABLEN--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!exec sp_help_spatial_geometry_index_xml TEST_GEOM , S1169_idx , 1, @geom, @x outputSET @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= @x.value('(\/Number_Of_Rows_Selected_By_Internal_Filter\/text())[1]', 'bigint')SET @NOTSFICVALUE= @x.value('(\/Number_Of_Times_Secondary_Filter_Is_Called\/text())[1]', 'bigint')SET @NOROVALUE= @x.value('(\/Number_Of_Rows_Output\/text())[1]', 'bigint')SET @PORNBPFVALUE= @x.value('(\/Percentage_Of_Rows_NotSelected_By_Primary_Filter\/text())[1]', 'float')SET @POPFRSBIFVALUE= @x.value('(\/Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter\/text())[1]', 'float')UPDATE spat_idx_test_resultSETnum_cells=' +@num_cell+',permut=' +@permut+',g2t' +@a4 +'=' +@time_str+',PF_EFF=@PFVALUE,IF_EFF=@IFVALUE,GRIDL1=@GRIDL1VALUE,GRIDL2=@GRIDL2VALUE,GRIDL3=@GRIDL3VALUE,GRIDL4=@GRIDL4VALUE,TPIR=@TPIRVALUE,TPIP=@TPIPVALUE,ANOIRPBR=@ANOIRPBRVALUE,TNOOCILFQ=@TNOOCILFQVALUE,TNOOCIL0II=@TNOOCIL0IIVALUE,TNOOCIL4II=@TNOOCIL4IIVALUE,TNOOCIL3FQ=@TNOOCIL3FQVALUE,TNOOCIL4FQ=@TNOOCIL4FQVALUE,TNOIOIL3FQ=@TNOIOIL3FQVALUE,TNOIOIL4FQ=@TNOIOIL4FQVALUE,ITTCNTLGP=@ITTCNTLGPVALUE,INTTTCNTLGP=@INTTTCNTLGPVALUE,BTTCNTLGP=@BTTCNTLGPVALUE,ACPONTLGP=@ACPONTLGPVALUE,AOPG=@AOPGVALUE,NORSBPF=@NORSBPFVALUE,NORSBIF=@NORSBIFVALUE,NOTSFIC=@NOTSFICVALUE,NORO=@NOROVALUE,PORNBPF=@PORNBPFVALUE,POPFRSBIF=@POPFRSBIFVALUWHERE perm_id=' +@perm_id+')
sset a4=a4+ 1
sEND
sset a2=a2+ 1
sEND
sset a1=a1+ 1
sEND
PRINT'Test des spatial index von '+tabnm+' : '+idxnm+' ist abgeschlossen!'
goDas Hacken, das ich gemacht habe, beinhaltete die Verwendung der xml-Ausgabe des Index-Hilfe-gespeicherten Verfahrens, um zusätzlich zur Abfragezeit einige beschreibendere Ergebnisse in die Ausgabe zu schreiben.
Das gespeicherte Verfahren wird wie folgt ausgeführt:
DECLARE BOUNDING VARCHAR(MAX)
SET 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)
SET QUERY ='GEOMETRY::STGeomFromText('POLYGON ((247804.201 3943957.896, 29932.568 3943963.210, 247671.344 3942876.441, 247684.630 3943652.325, 247804.201 3943957.896))',26917)'
EXEC sp_tune_spatial_index'TEST_GEOM','S1169_idx',4096,4096,@BOUNDING,@QUERY
go
In diesem Beispiel teste ich nur eine Zellgröße:4096 , aber Sie könnten jeden Wertebereich wie8 ,16 verwenden.
Ergebnisse können schön mit folgendem überprüft werden:
SELECT
perm_id as'Permutation #'
,num_cells'Zellen pro Objekt'
,permut as'Raster'
,g1t1 as'ms zum Abfragen der gesamten Geometrie (Ebene Level )' 3)'
,g1t4 als 'ms zum Abfragen der gesamten Geometrie (Level 4)'
,g2t1 als 'ms zur Ausführung der räumlichen Abfrage (Level 1)'
,g2t2 als 'ms zur Ausführung der räumlichen Abfrage (Level 2)'
,g2t3 als 'ms zur Ausführung der räumlichen Abfrage (Level 3)'
,g2t4 als 'ms zur Ausführung der räumlichen Abfrage (Level 4)'
,PF_EFF als 'Primäre Filtereffizienz'
,IF_EFF als 'Interne Filtereffizienz'
,GRIDL1 als 'Rastergröße Level 1'
,GRIDL2 als 'Rastergröße Level 2'
,GRIDL3 als 'Rastergröße Level 3'
,GRIDL4 als 'Rastergröße Level 4'
,TPIR als 'Gesamtzahl der primären Indexzeilen'
,TPIP als 'Gesamtzahl der primären Indexseiten'
,ANOIRPBR als 'Durchschnittliche Anzahl von Indexzeilen pro Basiszeile'
,TNOOCILFQ als 'Gesamtzahl der Objektzellen in Level 0 für Abfragebeispiel'
,TNOOCIL3FQ als 'Gesamtzahl der Objektzellen in Level 3 für Abfragebeispiel'
,TNOOCIL4FQ als 'Gesamtzahl der Objektzellen in Level 4 für Abfragebeispiel'
,TNOOCIL0II als 'Gesamtzahl der Objektzellen in Level 0 im Index'
,TNOOCIL4II als 'Gesamtzahl der Objektzellen in Level 4 im Index'
,TNOIOIL3FQ als 'Gesamtzahl der inneren Objektzellen in Level 3 für Abfragebeispiel'
,TNOIOIL4FQ als 'Gesamtzahl der inneren Objektzellen in Level 4 für Abfragebeispiel'
,ITTCNTLGP als 'Innen-zu-Gesamtzellen normalisierter Prozentsatz bezogen auf Blatt-Raster'
,INTTTCNTLGP als 'Schnittmengen-zu-Gesamtzellen normalisierter Prozentsatz bezogen auf Blatt-Raster'
,BTTCNTLGP als 'Rand-zu-Gesamtzellen normalisierter Prozentsatz bezogen auf Blatt-Raster'
,ACPONTLGP als 'Durchschnittliche Zellen pro Objekt normalisiert auf Blatt-Raster'
,AOPG als 'Durchschnittliche Objekte pro Blatt-Rasterzelle'
,NORSBPF als 'Anzahl der durch den primären Filter ausgewählten Zeilen'
,NORSBIF als 'Anzahl der durch den internen Filter ausgewählten Zeilen'
,NOTSFIC als 'Anzahl der Aufrufe des sekundären Filters'
 .& nbsp;,NORO als 'Anzahl der ausgegebenen Zeilen'
 .& nbsp;,PORNBPF als 'Prozentsatz der vom primären Filter nicht ausgewählten Zeilen'
 .& nbsp;,POPFRSBIF als 'Prozentsatz der vom internen Filter ausgewählten primären Filterzeilen'
FROM spat_idx_test_result
ORDER BY PF_EFF
