CODEWITHSUNDEEP
sql-serversql-server-2012spatial
influent
influent

Reputation: 1377

Simultaneous spatial SQL queries successively slower

I have a fairly simple spatial query that completes in around 1 second normally. As part of my load testing I'm sending the query from 10 different threads, and most of the ten take a lot longer, up to 30 seconds on occasion. If I add more threads it goes up to a few minutes. The wait type I'm seeing is async_network_io but there's not a super large amount of data being returned and SQL Sentry says my server is only using 0.2% of its network pipe. My workstation's pipe never spikes above 15%. Can somebody explain to me what I can do to get the queries to all execute in a second (up to a reasonable number of threads)?

Here's an example:

SELECT "Lon","Lat","ParcelID","ParcelPolygon".STAsBinary() as "ParcelPolygon" 
FROM "ParcelData" WITH(INDEX("IX_ParcelData_SpatialHigh")) 
WHERE  "ParcelPolygon".Filter(geometry::STGeomFromText('POLYGON ((-149.61737394332886 61.52266425353893,
 -149.61737394332886 61.525344727039275, -149.61175203323364 61.525344727039275, -149.61175203323364
 61.52266425353893, -149.61737394332886 61.52266425353893))', 4326)) = 1

Upvotes: 0

Views: 88

Answers (1)

influent
influent

Reputation: 1377

Looks like the problem really was that SQL Server was trying to return data faster than the application could handle it. I found a couple different ways to mitigate the problem.

  1. Use the Reduce() function on the geometry column. I had to use a factor of .00003 in order for the polygons to remain intact but that still gave a 6x decrease in dataset size.
  2. Turn off the map layer by default at higher zoom levels. A lower zoom level means a smaller dataset.

Together those solutions made a big difference, but I still see delays with 20+ users simultaneously using the map layer.

Upvotes: 1

Related Questions