sql-server - MS SQL 空间索引和执行时间

Question

我有下表：

CREATE TABLE [dbo].[CollectionSite](
    [SiteCode] [nvarchar](32) NOT NULL,
    [AddressId] [int] NOT NULL,
    //...
    [Location] [geography] NOT NULL,

表有大约 10000 条记录。

我有以下 linq 查询：

        var list = dataContext.CollectionSites.Where(p => p.Location.Distance(myLocation)<3000000).OrderBy(p => p.Location.Distance(myLocation))
            .ToList();

它正在执行大约 12-15 秒。然后我尝试创建一个空间索引：

CREATE SPATIAL INDEX SIX_CollectionSite_SiteCode ON CollectionSite([Location]) USING GEOGRAPHY_GRID  

它已成功创建。但是执行的时间是一样的。为什么索引不起作用？

EF Core 的版本是 2.2.4

添加于 2020 年 1 月 1 日

它生成以下sql：

exec sp_executesql N'SELECT [p].[SiteCode], [p].[AddressId], [p].[Location]
FROM [CollectionSite] AS [p]
WHERE [p].[Location].STDistance(@__myLocation_0) < 3000000.0E0
ORDER BY [p].[Location].STDistance(@__myLocation_0)',N'@__myLocation_0 varbinary(22)',@__myLocation_0=0xE6100000010C0403081F4ACA4040ACADD85F76FB5BC0

它从 .NET 应用程序执行 12-15 秒，但直接不到 1 秒

下一步。我在以下位置添加了时间标记：

        var query = dataContext.CollectionSites
            .Where(p => p.Location.Distance(myLocation) < 3000000)
            .OrderBy(p => p.Location.Distance(myLocation))
            .Select(p => new { p.SiteCode, p.AddressId, p.Location })
            ;

        var ms11 = sw.ElapsedMilliseconds;

        var list = query.ToList();

而这个 ms11 有这 12-15 秒，ToList()正在执行不到 1 秒！为什么查询被合并这么长时间？

标签： sql-serverindexinggeographyspatial-index

Answer 1

其他事情正在发生。试试这个简单的复制。它在 1 秒内搜索 10000个没有空间索引的地理结果。使用空间索引，它甚至更快。

using Microsoft.EntityFrameworkCore;
using Microsoft.Samples.EFLogging;
using NetTopologySuite;
using NetTopologySuite.Geometries;
using System;
using System.Diagnostics;
using System.Linq;

namespace EfTest
{
    public class CollectionSite
    {
        public int Id { get; set; }
        public string SiteCode { get; set; }

        public int AddressId { get; set; }

        public Geometry Location { get; set; }
    }
    public class Db : DbContext
    {
        private string connectionString;

        public Db() : this("Server=.;Database=EfCoreTest;Integrated Security=true")
        {
        }
        public Db(string connectionString)
        {
            this.connectionString = connectionString;

        }

        public DbSet<CollectionSite> CollectionSites { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
                base.OnModelCreating(modelBuilder);
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(connectionString, b => b.UseRelationalNulls().UseNetTopologySuite());

            base.OnConfiguring(optionsBuilder);

        }
    }



public class Program
    {



        static void Main(string[] args)
        {

            var gf = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);

            using (var db = new Db())
            {
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();
            }
            using (var db = new Db())
            {

                for (int i = 0; i < 10000; i++)
                {
                    var loc = new CollectionSite()
                    {
                        Location = gf.CreatePoint(new Coordinate(-90 + i / 1000.0, 45 + i / 1000.0)),
                        AddressId = i,
                        SiteCode = "site" + i
                    };

                    db.CollectionSites.Add(loc);

                }
                db.SaveChanges();

            }

            using (var db = new Db())
            {
                db.ConfigureLogging( s => Console.WriteLine(s));
                var myLocation = gf.CreatePoint(new Coordinate(-90, 45));

                var sw = new Stopwatch();
                sw.Start();

                var list = db.CollectionSites
                             .Where(p => p.Location.Distance(myLocation) < 30000)
                             .OrderBy(p => p.Location.Distance(myLocation))
                             .ToList();

                Console.WriteLine($"Found {list.Count} sites in {sw.ElapsedMilliseconds}ms");

                Console.WriteLine("Hit any key to exit");
                Console.ReadKey();

            }
        }
    }

}

输出

Found 221 sites in 982ms
Hit any key to exit

它生成的查询是：

SELECT [c].[Id], [c].[AddressId], [c].[Location], [c].[SiteCode]
FROM [CollectionSites] AS [c]
WHERE [c].[Location].STDistance(@__myLocation_0) < 3000.0E0
ORDER BY [c].[Location].STDistance(@__myLocation_0)