Go to content Go to navigation and search

Home

Current SQL Server Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Random Search Procedure (SQL Server 2008 Spatial)

Tuesday September 27 2011 at 23:38

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.

  1. USE [GISDB]  -- Or whatever your database is
  2. GO
  3. SET ANSI_NULLS ON
  4. GO
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7. /* Drop Window Type*/
  8. IF EXISTS (SELECT *
  9.              FROM      sys.types st
  10.                   JOIN sys.schemas ss
  11.                   ON st.schema_id = ss.schema_id
  12.             WHERE st.name = N't_windowarray'
  13.               AND ss.name = N'dbo')
  14.    DROP TYPE [dbo].[t_windowarray]
  15. GO
  16. /* Create Window Type */
  17. CREATE TYPE [dbo].[t_windowarray] AS TABLE(
  18.   [n] [INT] NOT NULL,
  19.   PRIMARY KEY CLUSTERED ( [n] ASC )
  20.   WITH (IGNORE_DUP_KEY = OFF)
  21. )
  22. GO
  23. /* Drop Procedure if exists */
  24. IF EXISTS (SELECT *
  25.              FROM sys.objects
  26.             WHERE object_id = OBJECT_ID(N'[dbo].[RandomSearchByExtent]')
  27.               AND TYPE IN (N'P', N'PC'))
  28.    DROP PROCEDURE [dbo].[RandomSearchByExtent]
  29. GO
  30. /* Now create the actual procedure */
  31. CREATE PROCEDURE RandomSearchByExtent(
  32.    @p_schema           nvarchar(128),  -- Schema in which table for gathering search stats exists
  33.    @p_table            nvarchar(128),  -- Table for which we want to gather search stats
  34.    @p_column           nvarchar(128),  -- spatial column in table for which we want to gather stats
  35.    @p_number_searches  INT,            -- Number of times to execute each search
  36.    @p_searchWindowList t_windowarray READONLY,
  37.    @p_debug            INT = 0,        -- If 0 then only returns search totals, else prints full detail of procedure execution
  38.    @p_no_zeros         INT = 1 )       -- If 1 then searches returning zero features are Not included
  39. AS
  40. BEGIN
  41. DECLARE
  42.   @v_avgFeatures        DECIMAL(10,3),
  43.   @v_count              INT = 0,
  44.   @v_end_time           datetime,
  45.   @v_esql               nvarchar(1000),
  46.   @v_length             INT = 20,
  47.   @v_lower_x            FLOAT = 0.0,
  48.   @v_lower_y            FLOAT = 0.0,
  49.   @v_max_x              FLOAT = 0.0,
  50.   @v_max_y              FLOAT = 0.0,
  51.   @v_min_x              FLOAT = 0.0,
  52.   @v_min_y              FLOAT = 0.0,
  53.   @v_params             nvarchar(500),
  54.   @v_r                  INT = 0,
  55.   @v_rand_x             FLOAT = 0,
  56.   @v_rand_y             FLOAT = 0,
  57.   @v_range_x            FLOAT = 0.0,
  58.   @v_range_y            FLOAT = 0.0,
  59.   @v_result_line        VARCHAR(1000),
  60.   @v_scale              INT = 8,
  61.   @v_searchWindowSize   INT = 100,
  62.   @v_seconds            FLOAT = 0,
  63.   @v_spatial_index      nvarchar(128),
  64.   @v_sql                nvarchar(1000),
  65.   @v_start_time         datetime,
  66.   @v_tesselation_schema VARCHAR(100),
  67.   @v_totalFeatures      INT = 0,
  68.   @v_totalSearches      INT = 0,
  69.   @v_totalSeconds       FLOAT = 0,
  70.   @v_upper_x            FLOAT = 0.0,
  71.   @v_upper_y            FLOAT = 0.0,
  72.   @v_wkt                nvarchar(4000);
  73.   DECLARE @results AS TABLE ( id INT IDENTITY, result_line nvarchar(1000) );
  74. BEGIN
  75.   -- Check parameters
  76.   IF ( @p_schema IS NULL OR @p_table IS NULL OR @p_column IS NULL )
  77.   BEGIN
  78.      IF ( @p_schema IS NULL )
  79.         INSERT INTO @results ( [result_line] ) VALUES ( 'p_schema may not be NULL' );
  80.      IF ( @p_table IS NULL )
  81.         INSERT INTO @results ( [result_line] ) VALUES ( 'p_table may not be NULL' );
  82.      IF ( @p_column IS NULL )
  83.         INSERT INTO @results ( [result_line] ) VALUES ( 'p_column may not be NULL' );
  84.      SELECT * FROM @results;
  85.      RETURN;
  86.   END;
  87.   -- Find spatial index name
  88.   --
  89.   SELECT @v_spatial_index = i.name
  90.     FROM sys.schemas s,
  91.          sys.TABLES  t,
  92.          sys.spatial_indexes i
  93.    WHERE UPPER(s.name) = UPPER(@p_schema)
  94.      AND t.schema_id = s.schema_id
  95.      AND UPPER(t.name) = UPPER(@p_table)
  96.      AND i.object_id = t.object_id;
  97.   IF ( @v_spatial_index IS NULL )
  98.   BEGIN
  99.      INSERT INTO @results ( [result_line] ) VALUES ( 'No spatial index found for ' + UPPER(@p_schema) + '.' + UPPER(@p_table));
  100.      SELECT * FROM @results;
  101.      RETURN;
  102.   END;
  103.   -- Retrieve Spatial Extent
  104.   SELECT @v_tesselation_schema=[tessellation_scheme],
  105.          @v_lower_x=[bounding_box_xmin],
  106.          @v_lower_y=[bounding_box_ymin],
  107.          @v_upper_x=[bounding_box_xmax],
  108.          @v_upper_y=[bounding_box_ymax]
  109.     FROM [GISDB].[sys].[spatial_index_tessellations]
  110.     WHERE object_id IN (SELECT i.object_id
  111.                         FROM sys.spatial_indexes i
  112.                              INNER JOIN
  113.                              sys.TABLES t
  114.                              ON ( t.object_id = i.object_id )
  115.                        WHERE UPPER(t.name) = UPPER(@p_table));
  116.   -- Check if geography_grid spatial index
  117.   SET @v_scale = 3;
  118.   SET @v_length = 20;
  119.   IF @v_tesselation_schema = 'GEOGRAPHY_GRID'
  120.   BEGIN
  121.      INSERT INTO @results ( [result_line] ) VALUES ( 'Not yet supported on GEOGRAPHY_GRID spatial indexes' );
  122.      SELECT * FROM @results;
  123.      RETURN;
  124.   END;
  125.   -- Calculate data ranges
  126.   SET @v_range_x = @v_upper_x - @v_lower_x;
  127.   SET @v_range_y = @v_upper_y - @v_lower_y;
  128.   -- Log information gained so far
  129.   IF ( @p_debug <> 0 )
  130.   BEGIN
  131.      INSERT INTO @results ( [result_line] )
  132.           VALUES ( N'Table MBR (' + LTRIM(STR(@v_lower_x, @v_length, @v_scale)) + N',' + LTRIM(STR(@v_lower_y, @v_length, @v_scale)) + N')(' +
  133.                                     LTRIM(STR(@v_upper_x, @v_length, @v_scale)) + N',' + LTRIM(STR(@v_upper_y, @v_length, @v_scale)) + N')' +
  134.                        N' Range(' + LTRIM(STR(@v_range_x, @v_length, @v_scale)) + N',' + LTRIM(STR(@v_range_y, @v_length, @v_scale)) + N')');
  135.   END;
  136.   -- Create basic SQL search statement and parameters
  137.   SET @v_sql = N'With sg As ( ' +
  138.                N' SELECT geometry::STPolyFromText(@wkt,a.' + @p_column + N'.STSrid) as geom ' +
  139.                N'   FROM (SELECT TOP 1 ' + @p_column +
  140.                          N' FROM ' + @p_schema + N'.' + @p_table + N' ) A ' +
  141.                N')' +
  142.                N'SELECT @count_out = COUNT(*) ' +
  143.                N'  FROM sg a, ' +
  144.                N'      ' + @p_schema + N'.' + @p_table + N' b ' +
  145.                N'  WITH (INDEX (' + @v_spatial_index + ')) ' +
  146.                N' WHERE b.' + @p_column + N'.STIntersects(a.geom) = 1 ';
  147.   -- Create parameters for the search query
  148.   SET @v_params = N'@wkt nvarchar(4000), @count_out INT OUTPUT';
  149.   IF ( @p_debug <> 0 )
  150.      INSERT INTO @results ( [result_line] ) VALUES ( N'SQL: ' + @v_sql);
  151.   ELSE
  152.      INSERT INTO @results ( [result_line] ) VALUES ( N'SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond');
  153.   -- Create cursor to walk over windows
  154.   DECLARE WindowsCursor CURSOR FOR
  155.     SELECT n
  156.       FROM @p_searchWindowList
  157.      ORDER BY n;
  158.   -- Process windows
  159.   OPEN WindowsCursor;
  160.   FETCH NEXT FROM WindowsCursor INTO @v_searchWindowSize;
  161.   WHILE (@@FETCH_STATUS <> -1)
  162.   BEGIN
  163.     IF ( @p_debug <> 0 )
  164.     BEGIN
  165.        SET @v_result_line = 'Searching ' + CONVERT(VARCHAR(5), @p_number_searches) + ' times using ' + LTRIM(STR(@v_searchWindowSize,5,0));
  166.        INSERT INTO @results ( [result_line] ) VALUES ( @v_result_line );
  167.     END;
  168.     SET @v_totalFeatures = 0;
  169.     SET @v_totalSeconds  = 0.0;
  170.     SET @v_r = 1;
  171.     WHILE ( @v_r <= @p_number_searches )
  172.     BEGIN
  173.       SET @v_count = -1;
  174.       -- Loop until we get a valid search
  175.       WHILE ( @v_count = -1 OR (@v_count = 0 AND @p_no_zeros = 1 ) )
  176.       BEGIN
  177.         SET @v_rand_x = ROUND(@v_range_x * RAND(CHECKSUM(NEWID())) + @v_lower_x,2);
  178.         SET @v_rand_y = ROUND(@v_range_y * RAND(CHECKSUM(NEWID())) + @v_lower_Y,2);
  179.         SET @v_min_x = @v_rand_x - (@v_searchWindowSize / 2 );
  180.         SET @v_min_y = @v_rand_y - (@v_searchWindowSize / 2 );
  181.         SET @v_max_x = @v_rand_x + (@v_searchWindowSize / 2 );
  182.         SET @v_max_y = @v_rand_y + (@v_searchWindowSize / 2 );
  183.         SET @v_wkt = N'POLYGON((' + LTRIM(STR(@v_min_x, @v_length, @v_scale)) + ' ' + LTRIM(STR(@v_min_y, @v_length, @v_scale)) + N',' +
  184.                                     LTRIM(STR(@v_max_x, @v_length, @v_scale)) + ' ' + LTRIM(STR(@v_min_y, @v_length, @v_scale)) + N',' +
  185.                                     LTRIM(STR(@v_max_x, @v_length, @v_scale)) + ' ' + LTRIM(STR(@v_max_y, @v_length, @v_scale)) + N',' +
  186.                                     LTRIM(STR(@v_min_x, @v_length, @v_scale)) + ' ' + LTRIM(STR(@v_max_y, @v_length, @v_scale)) + N',' +
  187.                                     LTRIM(STR(@v_min_x, @v_length, @v_scale)) + ' ' + LTRIM(STR(@v_min_y, @v_length, @v_scale)) + N'))';
  188.         --INSERT INTO @results ( [result_line] ) VALUES(@v_esql);
  189.         SET @v_start_time = getdate();
  190.         EXEC sp_executesql @statement = @v_sql,
  191.                            @params    = @v_params,
  192.                            @wkt       = @v_wkt,
  193.                            @count_out = @v_count OUTPUT;
  194.         SET @v_end_time = getdate();
  195.       END;
  196.       SET @v_totalFeatures  = @v_totalFeatures + @v_count;
  197.       SET @v_totalSeconds   = @v_totalSeconds + ( DATEDIFF(ms, @v_start_time, @v_end_time) / 1000.0);
  198.       SET @v_totalSearches  = @v_totalSearches + 1;
  199.       IF ( @p_debug <> 0 )
  200.       BEGIN
  201.          INSERT INTO @results ( [result_line] )
  202.               VALUES ( 'Found ' + CONVERT(VARCHAR(5), @v_count) +
  203.                 ' features in ' + LTRIM(STR(DATEDIFF(ms, @v_start_time, @v_end_time) / 1000.0, 9, 3)) +
  204.                      ' seconds' );
  205.       END;
  206.       SET @v_r = @v_r + 1;
  207.     END; -- While Loop
  208.     -- Log final results
  209.     SET @v_avgFeatures = ROUND(@v_totalFeatures / @v_totalSearches,3);
  210.     SET @v_result_line = CONVERT(VARCHAR(10),@v_searchWindowSize) + ',' +
  211.                          CONVERT(VARCHAR(10),@v_totalSearches)    + ',' +
  212.                          CONVERT(VARCHAR(20),@v_totalFeatures)    + ',' +
  213.                          LTRIM(STR(@v_totalSeconds,9,2))          + ',' +
  214.                          LTRIM(STR(@v_avgFeatures ,9,3));
  215.     IF ( @p_debug <> 0 )
  216.        INSERT INTO @results ( [result_line] ) VALUES ( N'SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond');
  217.     INSERT INTO @results ( [result_line] ) VALUES ( @v_result_line );
  218.     FETCH NEXT FROM WindowsCursor INTO @v_searchWindowSize;
  219.   END;
  220.  END;
  221.  CLOSE WindowsCursor;
  222.  DEALLOCATE WindowsCursor;
  223.  SELECT [result_line] FROM @results ORDER BY ID;
  224. END
  225. GO

1. Test the procedure with debugging:

  1. BEGIN
  2.   DECLARE
  3.     @v_searchWindowList   t_windowarray;
  4.   BEGIN
  5.     INSERT INTO @v_searchWindowList(n) VALUES(5000),(15000),(30000);
  6.     EXEC dbo.RandomSearchByExtent @p_schema='dbo',
  7.                                   @p_table='admin_sorted',
  8.                                   @p_column='Geom',
  9.                                   @p_number_searches=10,
  10.                                   @p_searchWindowList=@v_searchWindowList,
  11.                                   @p_debug=1,
  12.                                   @p_no_zeros=1;
  13.   END;
  14. END
  15. GO
  16. -- Result
  17. TABLE MBR (227154.990,627108.330)(5165171.320,5640437.310) Range(4938016.330,5013328.980)
  18. 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
  19. Searching 10 times USING 5000
  20. Found 20 features IN 0.220 seconds
  21. Found 10 features IN 0.220 seconds
  22. Found 1 features IN 0.156 seconds
  23. Found 15 features IN 0.140 seconds
  24. Found 7 features IN 0.330 seconds
  25. Found 15 features IN 0.313 seconds
  26. Found 10 features IN 0.233 seconds
  27. Found 6 features IN 0.216 seconds
  28. Found 10 features IN 1.220 seconds
  29. Found 32 features IN 0.390 seconds
  30. SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond
  31. 5000,10,126,3.44,12.000
  32. Searching 10 times USING 15000
  33. Found 177 features IN 0.390 seconds
  34. Found 687 features IN 1.420 seconds
  35. Found 159 features IN 0.373 seconds
  36. Found 73 features IN 0.280 seconds
  37. Found 173 features IN 0.390 seconds
  38. Found 14 features IN 0.233 seconds
  39. Found 51 features IN 0.250 seconds
  40. Found 221 features IN 0.390 seconds
  41. Found 187 features IN 0.440 seconds
  42. Found 303 features IN 0.703 seconds
  43. SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond
  44. 15000,20,2045,4.87,102.000
  45. Searching 10 times USING 30000
  46. Found 1 features IN 0.076 seconds
  47. Found 3 features IN 0.093 seconds
  48. Found 270 features IN 0.763 seconds
  49. Found 7 features IN 0.126 seconds
  50. Found 549 features IN 0.626 seconds
  51. Found 420 features IN 0.656 seconds
  52. Found 246 features IN 0.513 seconds
  53. Found 329 features IN 0.423 seconds
  54. Found 168 features IN 0.343 seconds
  55. Found 719 features IN 0.830 seconds
  56. SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond
  57. 30000,30,2712,4.45,90.000

2. Test the procedure with no debugging:

  1. BEGIN
  2.   DECLARE
  3.     @v_searchWindowList   t_windowarray;
  4.   BEGIN
  5.     INSERT INTO @v_searchWindowList(n) VALUES(5000),(15000);
  6.     EXEC dbo.RandomSearchByExtent @p_schema='dbo',
  7.                                   @p_table='admin_sorted',
  8.                                   @p_column='Geom',
  9.                                   @p_number_searches=5,
  10.                                   @p_searchWindowList=@v_searchWindowList,
  11.                                   @p_debug=0,
  12.                                   @p_no_zeros=1;
  13.   END;
  14. END
  15. GO
  16. -- Results
  17. SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond
  18. 5000,5,76,0.60,15.000
  19. 15000,10,1550,1.88,155.000

I hope this is useful to someone.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment