SQL Server 空間インデックスのパラメータ


空間インデックスの概要
http://technet.microsoft.com/ja-jp/library/bb895265.aspx

主に、グリッド密度/オブジェクトごとのセル数で配属が決まるので速度差を確認してみた。
データと使い方に応じて特化設定の指針用にはなるか。

CREATE SPATIAL INDEX めいしょう ON てーぶる
(
    からむめい
)USING  GEOMETRY_GRID 
WITH (
BOUNDING_BOX =(-90, -180, 90, 180),
GRIDS =(LEVEL_1 = LOW,LEVEL_2 = LOW,LEVEL_3 = LOW,LEVEL_4 = LOW), 
CELLS_PER_OBJECT = 1024) ON PRIMARY

赤い部分を変更していきクエリの実行速度を調査していく。

データ:市区町村界
Bounds:北緯15~55東経115~155
オブジェクトごとのセル数:8,16,32,64,128,256,512
確認用クエリ (上からQ0~Q3)

SELECT* FROM てーぶる WHERE SHAPE.STIntersects(GEOMETRY::STPolyFromText(N'POLYGON ((120 20, 120 50,150 50, 150 20, 120 20))', 4612))=1
SELECT* FROM てーぶる WHERE SHAPE.STIntersects(GEOMETRY::STPolyFromText(N'POLYGON ((130 30, 130 40,140 40, 140 30, 130 30))', 4612))=1
SELECT* FROM てーぶる WHERE SHAPE.STIntersects(GEOMETRY::STPolyFromText(N'POLYGON ((132.5 32.5, 132.5 37.5,137.5 37.5, 137.5 32.5, 132.5 32.5))', 4612))=1
SELECT* FROM てーぶる WHERE SHAPE.STIntersects(GEOMETRY::STPolyFromText(N'POLYGON ((134 34, 134 35,135 35, 135 34, 134 34))', 4612))=1

複数回で結構速度は揺れるし、サイズによってグリッドの最適解も変わるのでデータごとに計測して、表示範囲決めて最適解を探る形か。。
試したデータとクエリで比較的良かった成績

CELL L1 L2 L3 L4 Q0 Q1 Q2 Q3 AVG
32 HIGH LOW LOW LOW 104 112 65 28 77.25
32 MEDIUM HIGH MEDIUM HIGH 101 158 51 26 84
32 MEDIUM HIGH MEDIUM MEDIUM 100 174 71 41 96.5

同じクエリで悪いのでは実行1.4秒とかになり、上記のように110ミリ秒以内にもなる。
ただ試行回数をもっと増やして平均とるべき、結構結果が揺れる。
実行クエリとデータ揃えて計測してみるとパラメータの最適解を探れなくはないかな。

コードは下記

密度


public enum Level
{
  LOW,
  MEDIUM,
  HIGH
}//end enum

グリッド


public struct Grids
{
  public Level L1;
  public Level L2;
  public Level L3;
  public Level L4;

  public override string ToString()
  {
    const string gridsBase =
@"    LEVEL_1 = {0}, 
LEVEL_2 = {1}, 
LEVEL_3 = {2}, 
LEVEL_4 = {3}";
    return string.Format
        (gridsBase,
        this.L1.ToString("G"),
        this.L2.ToString("G"),
        this.L3.ToString("G"),
        this.L4.ToString("G"));
  }//end method
}//end struct
using System.Collections.Generic;
using System.Linq;

public static class Permutation
{
  
  public static IEnumerable<T[]> GetElements<T>(IEnumerable<T> items, int m, bool withRedandant)
  {
    if (m == 1)
    {
      foreach (var n in items)
      {
        yield return new T[] { n };
      }//end loop
      yield break;
    }//end if

    foreach (var atom in items)
    {
      List<T> templist = items.ToList();

      if (!withRedandant)
        templist.Remove(atom);

      var elements = GetElements(templist, m - 1, withRedandant);

      foreach (var elem in elements)
      {
        var newelem = (new T[] { atom }).Concat(elem).ToArray();

        yield return newelem;
      }//end loop
    }//end loop
  }//end method
}//end class
using System;
using System.Collections.Generic;

using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

public class IndexTester
{
  const string CREATE_INX_BASE =
@"CREATE SPATIAL INDEX {0}
  ON {1} ({2})
  USING GEOMETRY_GRID
  WITH (
    BOUNDING_BOX = ( {3} ),
    GRIDS = (
      {4}),
    CELLS_PER_OBJECT = {5}
  )";

  const string DROP_INX_BASE =
"DROP INDEX {0} ON {1}";

  public string ConString { get; set; }
  public string TabName { get; set; }
  public string ColName { get; set; }
  public string BBox { get; set; }
  public string[] CellPerObj { get; set; }
  public string InxName { get; set; }
  public string[] Querys { get; set; }

  public int CmdTimeout { get; set; }

  public void Test()
  {
    Console.Write("CELL,L1,L2,L3,L4");
    for (int i = 0; i < this.Querys.Length; i++)
    {
      Console.Write(",Q{0}", i);
    }
    Console.Write(Environment.NewLine);

    foreach (var cpo in this.CellPerObj)
    {
      foreach (var grid in this.getGrids())
      {

        this.innerTest(cpo, grid);

      }//end loop
    }//end loop


  }//end method

  private void innerTest(string cellPO, Grids grid)
  {
    var createQuery = string.Format(CREATE_INX_BASE,
         this.InxName //0
       , this.TabName //1
       , this.ColName //2
       , this.BBox    //3
       , grid         //4
       , cellPO);     //5

    var dropQuery = string.Format(DROP_INX_BASE, this.InxName, this.TabName);

    var con = new SqlConnection(this.ConString);
    con.Open();

    using (con)
    using (var cmd = con.CreateCommand())
    {
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = createQuery;
      if (this.CmdTimeout != 0)
        cmd.CommandTimeout = this.CmdTimeout;

      cmd.ExecuteNonQuery();

      Stopwatch spWatch = new Stopwatch();


      Dictionary<int, long> inxMSec = new Dictionary<int, long>(this.Querys.Length);
      int cnt = 0;
      foreach (var query in this.Querys)
      {
        spWatch.Reset();
        spWatch.Start();

        cmd.CommandText = query;
        using (var reader = cmd.ExecuteReader())
        {
          while (reader.Read())
          {
          }//end while
        }//end reader

        spWatch.Stop();
        inxMSec.Add(cnt, spWatch.ElapsedMilliseconds);
        cnt++;
      }//end loop query
      Console.Write("{0},{1},{2},{3},{4}", cellPO, grid.L1, grid.L2, grid.L3, grid.L4);
      foreach (var ms in inxMSec.Values)
      {
        Console.Write(",{0}", ms);
      }
      Console.Write(Environment.NewLine);

      cmd.CommandText = dropQuery;
      cmd.ExecuteNonQuery();
    }//end con
  }//end method



  private IEnumerable<Grids> getGrids()
  {
    var enumLevel = Permutation.GetElements(new Level[] { Level.LOW, Level.MEDIUM, Level.HIGH }, 4, true);
    foreach (var levels in enumLevel)
    {
      yield return new Grids()
      {
        L1 = levels[0],
        L2 = levels[1],
        L3 = levels[2],
        L4 = levels[3]
      };
    }//end loop
  }///end method


}//end class
カテゴリー: 運用, 設計 タグ: パーマリンク