In response to https://community.esri.com/migration-blogpost/54807
which adds some bling and zing to your SDE database, I've gotten a few comments regarding doing the same type of tuning with the Geography Storage type. Easy Cheesy, just note that this alteration simply replaces geometry with geography, and removes the bounding box from the index creation. Happy tuning!
--ORIGINAL SQL CODE FROM
--http://stackoverflow.com/users/2250424/greengeo
--MODIFIFIED TO INCLUDE SPATIAL INDEX HELP OUTPUT
--IN TABLE
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--LOOK FOR THE EXCLAMATION POINTS IN THIS CODE
--THERE ARE SEVERAL USER-SUPPLIED VARIABLES THAT MUST
--BE INPUT FOR THIS SP TO WORK
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
USE [GPS_COLLAR]
GO
ALTER PROCEDURE dbo.sp_tune_spatial_index
(
@tabnm VARCHAR(MAX), -- This parameter stores the name of the spatial table for which you are tuning the index
@idxnm VARCHAR(MAX), -- This parameter stores the name of the spatial index of the named table
@min_cells_per_obj INT, -- Minimum Cells Per Object to test on. Suggested to start at 2.
@max_cells_per_obj INT, -- Maximum Cells Per Object to test on.
/* The test requires two geography instances to use in test query 1 and 2.
The first one should cover the area of default extent. The second should
cover an area roughly the size of the area shown when zoomed in, panning
around. It is required that the variable store a string that will create
the geography instance since this will be done within the procedure and
cannot be a variable of type: GEOGRAPHY. The SRID of these instances must
match that of the table you are testing. */
@testgeom1 VARCHAR(MAX), -- This parameter stores the first geography instance creation string that will be used in the test
@testgeom2 VARCHAR(MAX) -- This parameter stores the second geography instance creation string that will be used in the test
)
AS
SET NOCOUNT ON;
/* Prior to running this procedure, two tables are required. These tables are
created here to prepare for running the procedure. */
PRINT 'Checking for required tables...'
IF EXISTS(SELECT 1 FROM sysobjects WHERE name IN ('cell_opt_perm', 'spat_idx_test_result'))
BEGIN
PRINT '... The "cell_opt_perm" and "spat_idx_test_result" tables exist.'
END
ELSE
BEGIN
PRINT '... Creating "cell_opt_perm" and "spat_idx_test_result" tables.'
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','LOW','LOW'),
(29,'MLLM','MEDIUM','LOW','LOW','MEDIUM'),
(30,'MLLH','MEDIUM','LOW','LOW','HIGH'),
(31,'MLML','MEDIUM','LOW','MEDIUM','LOW'),
(32,'MLMM','MEDIUM','LOW','MEDIUM','MEDIUM'),
(33,'MLMH','MEDIUM','LOW','MEDIUM','HIGH'),
(34,'MLHL','MEDIUM','LOW','HIGH','LOW'),
(35,'MLHM','MEDIUM','LOW','HIGH','MEDIUM'),
(36,'MLHH','MEDIUM','LOW','HIGH','HIGH'),
(37,'MMLL','MEDIUM','MEDIUM','LOW','LOW'),
(38,'MMLM','MEDIUM','MEDIUM','LOW','MEDIUM'),
(39,'MMLH','MEDIUM','MEDIUM','LOW','HIGH'),
(40,'MMML','MEDIUM','MEDIUM','MEDIUM','LOW'),
(41,'MMMM','MEDIUM','MEDIUM','MEDIUM','MEDIUM'),
(42,'MMMH','MEDIUM','MEDIUM','MEDIUM','HIGH'),
(43,'MMHL','MEDIUM','MEDIUM','HIGH','LOW'),
(44,'MMHM','MEDIUM','MEDIUM','HIGH','MEDIUM'),
(45,'MMHH','MEDIUM','MEDIUM','HIGH','HIGH'),
(46,'MHLL','MEDIUM','HIGH','LOW','LOW'),
(47,'MHLM','MEDIUM','HIGH','LOW','MEDIUM'),
(48,'MHLH','MEDIUM','HIGH','LOW','HIGH'),
(49,'MHML','MEDIUM','HIGH','MEDIUM','LOW'),
(50,'MHMM','MEDIUM','HIGH','MEDIUM','MEDIUM'),
(51,'MHMH','MEDIUM','HIGH','MEDIUM','HIGH'),
(52,'MHHL','MEDIUM','HIGH','HIGH','LOW'),
(53,'MHHM','MEDIUM','HIGH','HIGH','MEDIUM'),
(54,'MHHH','MEDIUM','HIGH','HIGH','HIGH'),
(55,'HLLL','HIGH','LOW','LOW','LOW'),
(56,'HLLM','HIGH','LOW','LOW','MEDIUM'),
(57,'HLLH','HIGH','LOW','LOW','HIGH'),
(58,'HLML','HIGH','LOW','MEDIUM','LOW'),
(59,'HLMM','HIGH','LOW','MEDIUM','MEDIUM'),
(60,'HLMH','HIGH','LOW','MEDIUM','HIGH'),
(61,'HLHL','HIGH','LOW','HIGH','LOW'),
(62,'HLHM','HIGH','LOW','HIGH','MEDIUM'),
(63,'HLHH','HIGH','LOW','HIGH','HIGH'),
(64,'HMLL','HIGH','MEDIUM','LOW','LOW'),
(65,'HMLM','HIGH','MEDIUM','LOW','MEDIUM'),
(66,'HMLH','HIGH','MEDIUM','LOW','HIGH'),
(67,'HMML','HIGH','MEDIUM','MEDIUM','LOW'),
(68,'HMMM','HIGH','MEDIUM','MEDIUM','MEDIUM'),
(69,'HMMH','HIGH','MEDIUM','MEDIUM','HIGH'),
(70,'HMHL','HIGH','MEDIUM','HIGH','LOW'),
(71,'HMHM','HIGH','MEDIUM','HIGH','MEDIUM'),
(72,'HMHH','HIGH','MEDIUM','HIGH','HIGH'),
(73,'HHLL','HIGH','HIGH','LOW','LOW'),
(74,'HHLM','HIGH','HIGH','LOW','MEDIUM'),
(75,'HHLH','HIGH','HIGH','LOW','HIGH'),
(76,'HHML','HIGH','HIGH','MEDIUM','LOW'),
(77,'HHMM','HIGH','HIGH','MEDIUM','MEDIUM'),
(78,'HHMH','HIGH','HIGH','MEDIUM','HIGH'),
(79,'HHHL','HIGH','HIGH','HIGH','LOW'),
(80,'HHHM','HIGH','HIGH','HIGH','MEDIUM'),
(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
/* delete all rows from "spat_idx_test_result" table. This makes it ready to stuff in new results.
!!!WARNING!!! if your test was interupted, the table will be cleared out and the test will
begin from the beginning. You could try to modify this to start where you left off but
I didn't have time and this worked well enough for me. */
DELETE FROM spat_idx_test_result
WHERE perm_id != 0
/* set up counters */
DECLARE @a1 INT
DECLARE @a2 INT
DECLARE @a3 INT
DECLARE @a4 INT
/* set up variables to hold high/medium/low values and permutation to use in rebuilding
the spatial index and recording stats */
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
/* create variables to hold timestamps for beginning and ending of test queries */
DECLARE @start_t DATETIME
DECLARE @end_t DATETIME
DECLARE @elapse_t INT
/* begin looping through cell option permutations */
SET @a1 = @min_cells_per_obj
WHILE @a1 <= @max_cells_per_obj
BEGIN
SET @a2 = 1
PRINT 'Started Testing for ' +CAST(@a1 AS VARCHAR(10)) +' cells per object'
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 GEOGRAPHY_GRID
WITH
(
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--MAKE SURE YOU EDIT THE BOUNDING BOX TO BE EXACTLY EQUAL TO THE BOUNDING
--BOX OF YOUR SPATIAL TABLE
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
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 'Re-built index to ' +@permut
SET @a3 = 1
SET @a4 = 1
WHILE @a3 < 5
BEGIN
SET @start_t = GETDATE()
EXEC
(
'CREATE TABLE #tmp_tab (shp GEOGRAPHY)
DECLARE @g1 GEOGRAPHY
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
(
'CREATE TABLE #tmp_tab (shp GEOGRAPHY)
DECLARE @g2 GEOGRAPHY
SET @g2 = ' +@testgeom2 +'
INSERT #tmp_tab (shp)
SELECT
r.Shape AS shp
FROM
' +@tabnm +' r
WHERE
r.SHAPE.STIntersects(@g2) = 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))
EXEC
(
'
DECLARE @geom geography
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
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--MAKE SURE YOU EDIT GEOGRAPHY VARIABLE BELOW TO REPRESENT A POLYGON
--THAT IS WITHIN YOUR BOUNDING BOX
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SET @geom = geography::STPolyFromText(''POLYGON ((-83.883490 35.440092, -83.896426 35.715280, -83.121432 35.787017, -83.124960 35.510653, -83.883490 35.440092))'', 4269)
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--MAKE SURE YOU SPECIFY THE NAME OF YOUR SPATIAL TABLE
--AND THE NAME OF THE SPATIAL INDEX
-- IN THE sp_help_spatial_geography_index_xml VARIABLES
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
exec sp_help_spatial_geography_index_xml GRSM_TRAILS , GRSM_TRAILS_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 = @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_result
SET
num_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 = @POPFRSBIFVALUE
WHERE perm_id = ' +@perm_id
)
SET @a4 = @a4 + 1
END
SET @a2 = @a2 + 1
END
SET @a1 = @a1 + 1
END
PRINT 'Testing of ' +@tabnm +' spatial index: ' +@idxnm +' is complete!'
GO
And execute like so:
DECLARE @BOUNDING VARCHAR(MAX)
SET @BOUNDING = 'geography::STPolyFromText(''POLYGON ((-83.883490 35.440092, -83.896426 35.715280, -83.121432 35.787017, -83.124960 35.510653, -83.883490 35.440092))'', 4269)'
DECLARE @QUERY VARCHAR(MAX)
SET @QUERY = 'geography::STPolyFromText(''POLYGON ((-83.883490 35.440092, -83.896426 35.715280, -83.121432 35.787017, -83.124960 35.510653, -83.883490 35.440092))'', 4269)'
EXEC sp_tune_spatial_index 'GRSM_TRAILS', 'GRSM_TRAILS_IDX', 4096, 4096, @BOUNDING, @QUERY
GO
