Magia de Brujas, Medicina de Aceite de Serpiente y Ajuste de Índice Espacial

ThomasColson

<\/HEAD>

Hay aproximadamente tantas opiniones y recursos sobre la Optimización de Índices Espaciales SQL como sobre por qué el uso de un GUID Secuencial es bueno o malo. Está El Arte Negro de la Optimización de Índices Espaciales en SQL Server | boomphisto<\/A> , Ayuda ArcGIS 10.1, <\/A>Resumen de Indexación Espacial<\/A> ,<\/A> Indexación Espacial: De 4 Días a 4 Horas - CSS SQL Server Engineers - Sitio Principal - Blogs MSDN<\/A>, sp_help_spatial_geography_histogram e Indexación de datos geográficos en SQL Server Denali | Alastair Aitchison<\/A> , Cuadrículas Multinivel Básicas - Isaac @ MSDN - Sitio Principal - Blogs MSDN<\/A> , http:\/\social.technet.microsoft.com\wiki\contents\articles\9694.tuning-spatial-point-data-queries-in-sql-server-2012.asp
6...<\/_blank>,  y por supuesto, si alguna vez has tenido problemas con un índice espacial que funciona mal y publicaste sobre ello en internet, lo más probable es que hayas recibido una respuesta de este tipo<\/_A>. <\/_SPAN><\/_P>

<\/_P>

Desafortunadamente, la mayoría de nosotros no somos programadores de bases de datos, ni soñamos en sintaxis SQL. SQL es como aprender francés leyendo un diccionario chino. No sé tú, pero yo no puedo entender ni una sola línea de código SQL. Estoy feliz de copiar fragmentos SQL de otras personas y modificarlos hasta que funcionen. Me encanta especialmente esta afirmación de la Ayuda ArcGIS: "Si creas tus datos a través de <\/_SPAN>ArcGIS for Desktop<\/_SPAN>, el índice espacial de cuadrícula se calcula por ti." Eso es como decir "Si pones la llave en el encendido, tu coche irá a la tienda, comprará leche y NO matará un mapache en el camino". A menos que seas conductor en Massachusetts, obviamente hay algunas cosas que tienes que ajustar, como girar el volante y pisar el acelerador en combinaciones infinitamente únicas para llegar a la tienda de leche y esquivar al mapache. Lo mismo ocurre con los Índices Espaciales SQL en una base de datos SDE. Que el software los habilite por defecto no implica que el índice esté optimizado para tu entorno particular de datos.<\/_SPAN><\/_SPAN><\/_P><\/_P>Por defecto, ArcGIS crea un índice espacial con 16 Celdas Por Objeto con los cuatro niveles configurados en Niveles Medios de Cuadrícula.<\/_SPAN><\/_P><\/_P><\/_SPAN><\/_P><\/_P>Observa cómo el tipo de almacenamiento es Goemetry, que algunos están descubriendo es el formato "Nuevo" predeterminado de almacenamiento ESRI.<\/_P><\/_P>Veamos cómo funciona el índice espacial "Por Defecto". Una forma rápida y fácil de probar un índice espacial es con los

Procedimientos Almacenados para Índices Espaciales<\/_A>.<\/_P>

<\/_P>

CREATE SPATIAL INDEX \n--ESTE ES EL NOMBRE POR DEFECTO DEL ÍNDICE\n--CREADO POR ARCGIS\n[S1169_idx] \nON \n--Y ESTE ES EL NOMBRE DE LA TABLA\n--PROPORCIONADO POR EL USUARIO EN ARCGIS\n--EN EL MOMENTO DE LA CREACIÓN\n[dbo].[TEST_GEOM]\n(\n  [SHAPE]\n)USING  GEOMETRY_GRID \nWITH (BOUNDING_BOX =(227166.13, 3925740.74, 314851.6915, 3968047.64), \nGRIDS =(\nLEVEL_1 = MEDIUM,\nLEVEL_2 = MEDIUM,\nLEVEL_3 = MEDIUM,\nLEVEL_4 = MEDIUM), \nCELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,\n--DROP_EXISTING = ON NOS PERMITE HACER ESTO TODO EL DÍA SIN TENER QUE BORRAR EL IDX \nDROP_EXISTING = ON, \nONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]\nGO\nDECLARE @geom geometry\n--EL POLÍGONO ES UN ÁREA MÁS PEQUEÑA QUE EL CUADRO LIMITANTE\nSET @geom = GEOMETRY::STGeomFromText('POLYGON ((247804.201 3943957.896, 29932.568 3943963.210, 247671.344 3942876.441, 247684.630 3943652.325,247804.201 3943957.896))', 26917)jjj\nexec sp_help_spatial_geometry_index 'TEST_GEOM', 'S1169_idx', 1, @geom<PRE><P>Hay muchísima salida. Lo primero que veo es <TABLE><TBODY><TR><TD style= "text-align: center;">Primary_Filter_Efficiency</TD><TD style= "text-align: center;">19.634703196347</TD></TR></TBODY></TABLE><P>y<TABLE><TBODY><TR><TD>Internal_Filter_Efficiency</TD><TD>0</TD></TR></TBODY></TABLE><P>No soy experto en SQL ni índices espaciales, pero no creo que esos números sean buenos. No es de extrañar que mis tiempos de dibujo sean lentos, las consultas espaciales sean lentas y en general la base de datos funcione horrible. ¡Cada tabla creada por ArcGIS tiene los mismos parámetros del índice!</P>Solo por diversión, cambiemos las celdas por objeto alterando solo esto en el fragmento de código anterior:<PRE class= "lia-code-sample line-numbers language-none">CELLS_PER_OBJECT = 4096</P>Mira el aumento en las eficiencias!</P>Internal_Filter_Efficiency</TD>76.7441860465116</TD>Primary_Filter_Efficiency</TD>91.4893617021277</P>Nuevamente digo que no soy experto en Optimización de Índices Espaciales SQL pero creo que estoy en algo aquí. Resulta que mis datos de prueba consisten en "Cadenas Lineales Muy Complejas", que si no vives bajo una roca GIS es lo que son todos tus datos. Coincidentemente usar un valor de 8192 para Celdas Por Objeto en este escenario es un 

buen punto de partida.</A> </P>

Pero hay muchos (muchísimos) valores entre 16 y 8092 y luego todas las permutaciones posibles entre Bajo (Low), Medio (Medium) y Alto (High) que podrían probarse para determinar qué índice espacial es probablemente el que te dará mejor rendimiento la mayoría del tiempo. ¿Y si hubiera una forma automática para probar configuraciones del índice espacial y mágicamente determinar qué parámetros se ajustan mejor a tu escenario?<
/P>

Entra el Hacker SDE....<
/P>

Me topé con esta publicación

geoespacial - Seleccionando un buen índice espacial SQL Server 2008 con polígonos grandes - Stack Overflow  donde alguien publicó un procedimiento almacenado SQL que recorre tamaños de celda y niveles de cuadrícula para reportar resultados espaciales para cada permutación (proporcionada por el usuario). Como soy curioso e inquieto rompí el código tratando de añadir mucha salida adicional hasta lograr hacerlo funcionar finalmente. Aquí está:<
/P>



--CÓDIGO SQL ORIGINAL DE
--http://stackoverflow.com/users/2250424/greengeo
--MODIFICADO PARA INCLUIR SALIDA DE AYUDA DE ÍNDICE ESPACIAL
--EN TABLA
  --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
  --BUSCA LOS SIGNOS DE EXCLAMACIÓN EN ESTE CÓDIGO
  --HAY VARIAS VARIABLES PROPORCIONADAS POR EL USUARIO QUE DEBEN
  --SER ENTRADAS PARA QUE ESTE SP FUNCIONE
  --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


USE DATBASE
GO



CREATE PROCEDURE dbo.sp_tune_spatial_index
(
  @tabnm VARCHAR(MAX), -- Este parámetro almacena el nombre de la tabla espacial para la cual está ajustando el índice
  @idxnm VARCHAR(MAX), -- Este parámetro almacena el nombre del índice espacial de la tabla nombrada
  @min_cells_per_obj INT, -- Mínimo de Celdas Por Objeto para probar. Se sugiere comenzar en 2.
  @max_cells_per_obj INT, -- Máximo de Celdas Por Objeto para probar.

  \/\* La prueba requiere dos instancias de geometría para usar en la consulta de prueba 1 y 2.
  La primera debe cubrir el área del extent por defecto. La segunda debe
  cubrir un área aproximadamente del tamaño del área mostrada al hacer zoom, desplazándose
  alrededor. Se requiere que la variable almacene una cadena que creará
  la instancia de geometría ya que esto se hará dentro del procedimiento y
  no puede ser una variable de tipo: GEOMETRY. El SRID de estas instancias debe
  coincidir con el de la tabla que está probando. *\/
  @testgeom1 VARCHAR(MAX), -- Este parámetro almacena la primera cadena de creación de instancia de geometría que se usará en la prueba
  @testgeom2 VARCHAR(MAX) -- Este parámetro almacena la segunda cadena de creación de instancia de geometría que se usará en la prueba



)

AS

SET NOCOUNT ON;

\/* Antes de ejecutar este procedimiento, se requieren dos tablas. Estas tablas son
  creadas aquí para preparar la ejecución del procedimiento. *\/

PRINT 'Comprobando las tablas requeridas...'
IF EXISTS(SELECT 1 FROM sysobjects WHERE name IN ('cell_opt_perm', 'spat_idx_test_result'))
  BEGIN
  PRINT '... Las tablas "cell_opt_perm" y "spat_idx_test_result" existen.'
  END
ELSE
BEGIN
  PRINT '... Creando las tablas "cell_opt_perm" y "spat_idx_test_result".'
  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','','') 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 'Índice reconstruido a ' +@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
nbsp;
nbsp;
nbsp;
'tCREATE TABLE #tmp_tab (shp GEOMETRY)
nbsp;
declare @g2 GEOMETRY
nbsp;
set @g2 = ' +@testgeom2 +'
nbsp;
insert #tmp_tab (shp)
nbsp;
select
r.Shape as shp
from
' +@tabnm +' r
where
r.SHAPE.STIntersects(@g2) = 1
drop table #tmp_tab'
nbsp;
)
nbsp;
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))
sET @num_cell=CAST(@a1 AS VARCHAR(6))
sET @time_str=CAST(@elapse_t AS VARCHAR(20))
sET @num_cell=CAST(@a1 AS VARCHAR(6))
sET @time_str=CAST(@elapse_t AS VARCHAR(20))
sET @num_cell=CAST(@a1 AS VARCHAR(6))
sET @time_str=CAST(@elapse_t AS VARCHAR(20))
sET @num_cell=CAST(@a1 AS VARCHAR(6))
sET @time_str=CAST(@elapse_t AS VARCHAR(20))
sET @num_cell=CAST(@a1 AS VARCHAR(6))
sET @time_str=CAST(@elapse_t AS VARCHAR(20))
sET @num_cell=CAST(@a1 AS VARCHAR(6))
sET @time_str=CAST(@elapse_t AS VARCHAR(20))
sET @num_cell=CAST(@a1 AS VARCHAR(6))
sET @time_str=CAST(@elapse_t AS VARCHAR(20))
sET @num_cell=CAST(@a1 AS VARCHAR(6))
sET @time_str=CAST(@elapse_t AS VARCHAR(20))
sET @num_cell=CAST(@a1 AS VARCHAR(6))
sET @time_str=CAST(@elapse_t AS VARCHAR(20)) 3)'
      ,g1t4 como 'ms para consultar toda la geometría (Nivel 4)'
      ,g2t1 como 'ms para ejecutar consulta espacial (Nivel 1)'
      ,g2t2 como 'ms para ejecutar consulta espacial (Nivel 2)'
      ,g2t3 como 'ms para ejecutar consulta espacial (Nivel 3)'
      ,g2t4 como 'ms para ejecutar consulta espacial (Nivel 4)'
      ,PF_EFF como 'Eficiencia del Filtro Primario'
      ,IF_EFF como 'Eficiencia del Filtro Interno'
      ,GRIDL1 como 'Tamaño de Cuadrícula Nivel 1'
      ,GRIDL2 como 'Tamaño de Cuadrícula Nivel 2'
      ,GRIDL3 como 'Tamaño de Cuadrícula Nivel 3'
      ,GRIDL4 como 'Tamaño de Cuadrícula Nivel 4'
      ,TPIR como 'Total de Filas del Índice Primario'
      ,TPIP como 'Total de Páginas del Índice Primario'
      ,ANOIRPBR como 'Número Promedio de Filas de Índice Por Fila Base'
      ,TNOOCILFQ como 'Número Total de Celdas de Objeto en Nivel 0 Para Muestra de Consulta'
   ,TNOOCIL3FQ como 'Número Total de Celdas de Objeto en Nivel 3 Para Muestra de Consulta'
   ,TNOOCIL4FQ como 'Número Total de Celdas de Objeto en Nivel 4 Para Muestra de Consulta'
      ,TNOOCIL0II como 'Número Total de Celdas de Objeto en Nivel 0 En Índice'
      ,TNOOCIL4II como 'Número Total de Celdas de Objeto en Nivel 4 En Índice'
   ,TNOIOIL3FQ como 'Número Total De Celdas De Objeto Interior En Nivel 3 Para MuestraDeConsulta'
   ,TNOIOIL4FQ como 'Número Total De Celdas De Objeto Interior En Nivel 4 Para MuestraDeConsulta'
   ,ITTCNTLGP como 'Porcentaje Normalizado De Celdas Interiores A Celdas Totales En Cuadrícula Hoja'
   ,INTTTCNTLGP como 'Porcentaje Normalizado De Celdas Intersectantes A Celdas Totales En Cuadrícula Hoja'
   ,BTTCNTLGP como 'Porcentaje Normalizado De Celdas De Borde A Celdas Totales En Cuadrícula Hoja'
   ,ACPONTLGP como 'Promedio De Celdas Por Objeto Normalizado A Cuadrícula Hoja'
   ,AOPG como 'Promedio De Objetos Por Celda De Cuadrícula Hoja'
   ,NORSBPF como 'Número De Filas Seleccionadas Por Filtro Primario'
   ,NORSBIF como 'Número De Filas Seleccionadas Por Filtro Interno'
   ,NOTSFIC como 'Número De Veces Que Se Llama Al Filtro Secundario'
   ,NORO como 'Número De Filas Salida'
   ,PORNBPF como 'Porcentaje De Filas No Seleccionadas Por Filtro Primario'
   ,POPFRSBIF como 'Porcentaje De Filas Del Filtro Primario Seleccionadas Por Filtro Interno'
FROM spat_idx_test_resultORDER BY PF_EFF

Permutación #

Celdas Por Objeto

Cuadrículas

ms para consultar toda la geometría (Nivel 1)

ms para consultar toda la geometría (Nivel 2)

ms para consultar toda la geometría (Nivel 3)

ms para consultar toda la geometría (Nivel 4)

ms para ejecutar consulta espacial (Nivel 1)

ms para ejecutar consulta espacial (Nivel 2)

ms para ejecutar consulta espacial (Nivel 3)

ms para ejecutar consulta espacial (Nivel 4)

Eficiencia del Filtro Primario

Eficiencia del Filtro Interno

Tamaño de Cuadrícula Nivel 1

Tamaño de Cuadrícula Nivel 2

Tamaño de Cuadrícula Nivel 3

Tamaño de Cuadrícula Nivel 4

Total de Filas del Índice Primario

Total de Páginas del Índice Primario

Número Promedio de Filas de Índice Por Fila Base

Número Total de Celdas de Objeto en Nivel 0 Para Muestra de Consulta

Número Total de Celdas de Objeto en Nivel 3 Para Muestra de Consulta

Número Total de Celdas de Objeto en Nivel 4 Para Muestra de Consulta

Número Total de Celdas de Objeto en Nivel 0 En Índice

Número Total de Celdas de Objeto en Nivel 4 En Índice

Número Total De Celdas De Objeto Interior En Nivel 3 Para MuestraDeConsulta

Número Total De Celdas De Objeto Interior En Nivel 4 Para MuestraDeConsulta

Porcentaje Normalizado De Celdas Interiores A Celdas Totales En Cuadrícula Hoja

Porcentaje Normalizado De Celdas Intersectantes A Celdas Totales En Cuadrícula Hoja

Porcentaje Normalizado De Celdas De Borde A Celdas Totales En Cuadrícula Hoja

Promedio De Celdas Por Objeto Normalizado A Cuadrícula Hoja

Promedio De Objetos Por Celda De Cuadrícula Hoja

Número De Filas Seleccionadas Por Filtro Primario

Número De Filas Seleccionadas Por Filtro Interno

Número De Veces Que Se Llama Al Filtro Secundario

Número De Filas Salida

Porcentaje De Filas No Seleccionadas Por Filtro Primario

Porcentaje De Filas Del Filtro Primario Seleccionadas Por Filtro Interno

Este Esta publicación ni siquiera rasca la superficie de la Optimización del Índice Espacial. Por ejemplo, si estás usando SQL 2012, podrías configurar el índice a Auto Grid, que te ofrece 8 Niveles de Celdas y supuestamente es una buena combinación para ArcGIS. También querrás observar otros factores de rendimiento como la Eficiencia del Filtro Interno. Hay mucha Magia de Brujas y Medicina de Aceite de Serpiente en la Optimización del Índice Espacial. ¡Espero que esta publicación del blog te ayude a comenzar en la dirección correcta!

DougGreen

Very nice hacking. I am ecstatic that someone took the initial step of imperial testing to the next step of gauging it against the helper procs. I originally tested from 16 cells per object down to 1 cell per object and the performance increased as you went down from 16 so I never even considered using more cells per object; especially that high of a number. Way-to-go experimenting and acting on a hypothesis. I may now incorporate yours into mine a bit. However, with everything now on a version of SQL that has the AUTO_GRID option, it's not as big of an issue for us.