RandomSearchByExtent: Random Search Procedure (2008 Spatial)
I have had the need, over the years, to produce statistics that helped me decide how effective a change to a property of a spatial table or index might be.
For example, with SQL Server 2008’s Spatial Index, for most sites, setting the right properties to get the best performance is a trial and error process (though new developments post-SQL Server 2008 R2 will help to automate the problem).
Similarly, changing storage properties of a table should not be done without careful analysis of the results.
A procedure like the following can help in such situations.
USE [GISDB] -- Or whatever your database is
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Drop Window Type*/
IF EXISTS (SELECT *
FROM sys.types st
JOIN sys.schemas ss
ON st.schema_id = ss.schema_id
WHERE st.name = N't_windowarray'
AND ss.name = N'dbo')
DROP TYPE [dbo].[t_windowarray]
GO
/* Create Window Type */
CREATE TYPE [dbo].[t_windowarray] AS TABLE(
[n] [INT] NOT NULL,
PRIMARY KEY CLUSTERED ( [n] ASC )
WITH (IGNORE_DUP_KEY = OFF)
)
GO
/* Drop Procedure if exists */
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[RandomSearchByExtent]')
AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[RandomSearchByExtent]
GO
/* Now create the actual procedure */
CREATE PROCEDURE RandomSearchByExtent(
@p_schema nvarchar(128), -- Schema in which table for gathering search stats exists
@p_table nvarchar(128), -- Table for which we want to gather search stats
@p_column nvarchar(128), -- spatial column in table for which we want to gather stats
@p_number_searches INT, -- Number of times to execute each search
@p_searchWindowList t_windowarray READONLY,
@p_debug INT = 0, -- If 0 then only returns search totals, else prints full detail of procedure execution
@p_no_zeros INT = 1 ) -- If 1 then searches returning zero features are Not included
AS
BEGIN
DECLARE
@v_avgFeatures DECIMAL(10,3),
@v_count INT = 0,
@v_end_time datetime,
@v_esql nvarchar(1000),
@v_length INT = 20,
@v_lower_x FLOAT = 0.0,
@v_lower_y FLOAT = 0.0,
@v_max_x FLOAT = 0.0,
@v_max_y FLOAT = 0.0,
@v_min_x FLOAT = 0.0,
@v_min_y FLOAT = 0.0,
@v_params nvarchar(500),
@v_r INT = 0,
@v_rand_x FLOAT = 0,
@v_rand_y FLOAT = 0,
@v_range_x FLOAT = 0.0,
@v_range_y FLOAT = 0.0,
@v_result_line VARCHAR(1000),
@v_scale INT = 8,
@v_searchWindowSize INT = 100,
@v_seconds FLOAT = 0,
@v_spatial_index nvarchar(128),
@v_sql nvarchar(1000),
@v_start_time datetime,
@v_tesselation_schema VARCHAR(100),
@v_totalFeatures INT = 0,
@v_totalSearches INT = 0,
@v_totalSeconds FLOAT = 0,
@v_upper_x FLOAT = 0.0,
@v_upper_y FLOAT = 0.0,
@v_wkt nvarchar(4000);
DECLARE @results AS TABLE ( id INT IDENTITY, result_line nvarchar(1000) );
BEGIN
-- Check parameters
IF ( @p_schema IS NULL OR @p_table IS NULL OR @p_column IS NULL )
BEGIN
IF ( @p_schema IS NULL )
INSERT INTO @results ( [result_line] ) VALUES ( 'p_schema may not be NULL' );
IF ( @p_table IS NULL )
INSERT INTO @results ( [result_line] ) VALUES ( 'p_table may not be NULL' );
IF ( @p_column IS NULL )
INSERT INTO @results ( [result_line] ) VALUES ( 'p_column may not be NULL' );
SELECT * FROM @results;
RETURN;
END;
-- Find spatial index name
--
SELECT @v_spatial_index = i.name
FROM sys.schemas s,
sys.TABLES t,
sys.spatial_indexes i
WHERE UPPER(s.name) = UPPER(@p_schema)
AND t.schema_id = s.schema_id
AND UPPER(t.name) = UPPER(@p_table)
AND i.object_id = t.object_id;
IF ( @v_spatial_index IS NULL )
BEGIN
INSERT INTO @results ( [result_line] ) VALUES ( 'No spatial index found for ' + UPPER(@p_schema) + '.' + UPPER(@p_table));
SELECT * FROM @results;
RETURN;
END;
-- Retrieve Spatial Extent
SELECT @v_tesselation_schema=[tessellation_scheme],
@v_lower_x=[bounding_box_xmin],
@v_lower_y=[bounding_box_ymin],
@v_upper_x=[bounding_box_xmax],
@v_upper_y=[bounding_box_ymax]
FROM [GISDB].[sys].[spatial_index_tessellations]
WHERE object_id IN (SELECT i.object_id
FROM sys.spatial_indexes i
INNER JOIN
sys.TABLES t
ON ( t.object_id = i.object_id )
WHERE UPPER(t.name) = UPPER(@p_table));
-- Check if geography_grid spatial index
SET @v_scale = 3;
SET @v_length = 20;
IF @v_tesselation_schema = 'GEOGRAPHY_GRID'
BEGIN
INSERT INTO @results ( [result_line] ) VALUES ( 'Not yet supported on GEOGRAPHY_GRID spatial indexes' );
SELECT * FROM @results;
RETURN;
END;
-- Calculate data ranges
SET @v_range_x = @v_upper_x - @v_lower_x;
SET @v_range_y = @v_upper_y - @v_lower_y;
-- Log information gained so far
IF ( @p_debug <> 0 )
BEGIN
INSERT INTO @results ( [result_line] )
VALUES ( N'Table MBR (' + LTRIM(STR(@v_lower_x, @v_length, @v_scale)) + N',' + LTRIM(STR(@v_lower_y, @v_length, @v_scale)) + N')(' +
LTRIM(STR(@v_upper_x, @v_length, @v_scale)) + N',' + LTRIM(STR(@v_upper_y, @v_length, @v_scale)) + N')' +
N' Range(' + LTRIM(STR(@v_range_x, @v_length, @v_scale)) + N',' + LTRIM(STR(@v_range_y, @v_length, @v_scale)) + N')');
END;
-- Create basic SQL search statement and parameters
SET @v_sql = N'With sg As ( ' +
N' SELECT geometry::STPolyFromText(@wkt,a.' + @p_column + N'.STSrid) as geom ' +
N' FROM (SELECT TOP 1 ' + @p_column +
N' FROM ' + @p_schema + N'.' + @p_table + N' ) A ' +
N')' +
N'SELECT @count_out = COUNT(*) ' +
N' FROM sg a, ' +
N' ' + @p_schema + N'.' + @p_table + N' b ' +
N' WITH (INDEX (' + @v_spatial_index + ')) ' +
N' WHERE b.' + @p_column + N'.STIntersects(a.geom) = 1 ';
-- Create parameters for the search query
SET @v_params = N'@wkt nvarchar(4000), @count_out INT OUTPUT';
IF ( @p_debug <> 0 )
INSERT INTO @results ( [result_line] ) VALUES ( N'SQL: ' + @v_sql);
ELSE
INSERT INTO @results ( [result_line] ) VALUES ( N'SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond');
-- Create cursor to walk over windows
DECLARE WindowsCursor CURSOR FOR
SELECT n
FROM @p_searchWindowList
ORDER BY n;
-- Process windows
OPEN WindowsCursor;
FETCH NEXT FROM WindowsCursor INTO @v_searchWindowSize;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF ( @p_debug <> 0 )
BEGIN
SET @v_result_line = 'Searching ' + CONVERT(VARCHAR(5), @p_number_searches) + ' times using ' + LTRIM(STR(@v_searchWindowSize,5,0));
INSERT INTO @results ( [result_line] ) VALUES ( @v_result_line );
END;
SET @v_totalFeatures = 0;
SET @v_totalSeconds = 0.0;
SET @v_r = 1;
WHILE ( @v_r <= @p_number_searches )
BEGIN
SET @v_count = -1;
-- Loop until we get a valid search
WHILE ( @v_count = -1 OR (@v_count = 0 AND @p_no_zeros = 1 ) )
BEGIN
SET @v_rand_x = ROUND(@v_range_x * RAND(CHECKSUM(NEWID())) + @v_lower_x,2);
SET @v_rand_y = ROUND(@v_range_y * RAND(CHECKSUM(NEWID())) + @v_lower_Y,2);
SET @v_min_x = @v_rand_x - (@v_searchWindowSize / 2 );
SET @v_min_y = @v_rand_y - (@v_searchWindowSize / 2 );
SET @v_max_x = @v_rand_x + (@v_searchWindowSize / 2 );
SET @v_max_y = @v_rand_y + (@v_searchWindowSize / 2 );
SET @v_wkt = N'POLYGON((' + LTRIM(STR(@v_min_x, @v_length, @v_scale)) + ' ' + LTRIM(STR(@v_min_y, @v_length, @v_scale)) + N',' +
LTRIM(STR(@v_max_x, @v_length, @v_scale)) + ' ' + LTRIM(STR(@v_min_y, @v_length, @v_scale)) + N',' +
LTRIM(STR(@v_max_x, @v_length, @v_scale)) + ' ' + LTRIM(STR(@v_max_y, @v_length, @v_scale)) + N',' +
LTRIM(STR(@v_min_x, @v_length, @v_scale)) + ' ' + LTRIM(STR(@v_max_y, @v_length, @v_scale)) + N',' +
LTRIM(STR(@v_min_x, @v_length, @v_scale)) + ' ' + LTRIM(STR(@v_min_y, @v_length, @v_scale)) + N'))';
--INSERT INTO @results ( [result_line] ) VALUES(@v_esql);
SET @v_start_time = getdate();
EXEC sp_executesql @statement = @v_sql,
@params = @v_params,
@wkt = @v_wkt,
@count_out = @v_count OUTPUT;
SET @v_end_time = getdate();
END;
SET @v_totalFeatures = @v_totalFeatures + @v_count;
SET @v_totalSeconds = @v_totalSeconds + ( DATEDIFF(ms, @v_start_time, @v_end_time) / 1000.0);
SET @v_totalSearches = @v_totalSearches + 1;
IF ( @p_debug <> 0 )
BEGIN
INSERT INTO @results ( [result_line] )
VALUES ( 'Found ' + CONVERT(VARCHAR(5), @v_count) +
' features in ' + LTRIM(STR(DATEDIFF(ms, @v_start_time, @v_end_time) / 1000.0, 9, 3)) +
' seconds' );
END;
SET @v_r = @v_r + 1;
END; -- While Loop
-- Log final results
SET @v_avgFeatures = ROUND(@v_totalFeatures / @v_totalSearches,3);
SET @v_result_line = CONVERT(VARCHAR(10),@v_searchWindowSize) + ',' +
CONVERT(VARCHAR(10),@v_totalSearches) + ',' +
CONVERT(VARCHAR(20),@v_totalFeatures) + ',' +
LTRIM(STR(@v_totalSeconds,9,2)) + ',' +
LTRIM(STR(@v_avgFeatures ,9,3));
IF ( @p_debug <> 0 )
INSERT INTO @results ( [result_line] ) VALUES ( N'SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond');
INSERT INTO @results ( [result_line] ) VALUES ( @v_result_line );
FETCH NEXT FROM WindowsCursor INTO @v_searchWindowSize;
END;
END;
CLOSE WindowsCursor;
DEALLOCATE WindowsCursor;
SELECT [result_line] FROM @results ORDER BY ID;
END
GO
1. Test the procedure with debugging:
BEGIN DECLARE @v_searchWindowList t_windowarray; BEGIN INSERT INTO @v_searchWindowList(n) VALUES(5000),(15000),(30000); EXEC dbo.RandomSearchByExtent @p_schema='dbo', @p_table='admin_sorted', @p_column='Geom', @p_number_searches=10, @p_searchWindowList=@v_searchWindowList, @p_debug=1, @p_no_zeros=1; END; END GO -- Result TABLE MBR (227154.990,627108.330)(5165171.320,5640437.310) Range(4938016.330,5013328.980) SQL: WITH sg AS ( SELECT geometry::STPolyFromText(@wkt,a.Geom.STSrid) AS geom FROM (SELECT TOP 1 Geom FROM dbo.admin_sorted ) A )SELECT @count_out = COUNT(*) FROM sg a, dbo.admin_sorted b WITH (INDEX (admin_sorted_geom)) WHERE b.Geom.STIntersects(a.geom) = 1 Searching 10 times USING 5000 Found 20 features IN 0.220 seconds Found 10 features IN 0.220 seconds Found 1 features IN 0.156 seconds Found 15 features IN 0.140 seconds Found 7 features IN 0.330 seconds Found 15 features IN 0.313 seconds Found 10 features IN 0.233 seconds Found 6 features IN 0.216 seconds Found 10 features IN 1.220 seconds Found 32 features IN 0.390 seconds SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond 5000,10,126,3.44,12.000 Searching 10 times USING 15000 Found 177 features IN 0.390 seconds Found 687 features IN 1.420 seconds Found 159 features IN 0.373 seconds Found 73 features IN 0.280 seconds Found 173 features IN 0.390 seconds Found 14 features IN 0.233 seconds Found 51 features IN 0.250 seconds Found 221 features IN 0.390 seconds Found 187 features IN 0.440 seconds Found 303 features IN 0.703 seconds SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond 15000,20,2045,4.87,102.000 Searching 10 times USING 30000 Found 1 features IN 0.076 seconds Found 3 features IN 0.093 seconds Found 270 features IN 0.763 seconds Found 7 features IN 0.126 seconds Found 549 features IN 0.626 seconds Found 420 features IN 0.656 seconds Found 246 features IN 0.513 seconds Found 329 features IN 0.423 seconds Found 168 features IN 0.343 seconds Found 719 features IN 0.830 seconds SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond 30000,30,2712,4.45,90.000
2. Test the procedure with no debugging:
BEGIN DECLARE @v_searchWindowList t_windowarray; BEGIN INSERT INTO @v_searchWindowList(n) VALUES(5000),(15000); EXEC dbo.RandomSearchByExtent @p_schema='dbo', @p_table='admin_sorted', @p_column='Geom', @p_number_searches=5, @p_searchWindowList=@v_searchWindowList, @p_debug=0, @p_no_zeros=1; END; END GO -- Results SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond 5000,5,76,0.60,15.000 15000,10,1550,1.88,155.000
I hope this is useful to someone.