public
anonymous / gist:5322650
Created

SpatialIdxTune

  • Download Gist
gistfile1.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301
 
USE sde_apps
GO
 
 
 
CREATE 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 geometry 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 geometry instance since this will be done within the procedure and
cannot be a variable of type: GEOMETRY. The SRID of these instances must
match that of the table you are testing. */
@testgeom1 VARCHAR(MAX), -- This parameter stores the first geometry instance creation string that will be used in the test
@testgeom2 VARCHAR(MAX) -- This parameter stores the second geometry 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
)
INSERT INTO dbo.spat_idx_test_result
VALUES (0,16,0,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)
 
/* 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 GEOMETRY_GRID
WITH
(
BOUNDING_BOX =(2422068, 527322, 2565405, 781170),
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 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
(
'CREATE TABLE #tmp_tab (shp GEOMETRY)
DECLARE @g2 GEOMETRY
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
(
'UPDATE spat_idx_test_result
SET
num_cells = ' +@num_cell +',
permut = ' +@permut +',
g2t' +@a4 +' = ' +@time_str +'
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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.