SpatialIdxTune

gistfile1.sql

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