MS SQL空间索引和执行时间

发生了其他事情。试试这个简单的复制。它在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)