4

I have a table Event that has 30 million rows and a table Location that has 35 million rows in SQL Server. An event can have multiple locations.

I have a spatial index, geometry auto grid (cells per object: 4000), on the Location table. The bounding box of all the locations covers and area of about 500 000 km2.

The performance is fine when only using STContains in the WHERE clause. If I try to filter on a additional column(s) the performance takes a big hit.

The following query takes 171ms:

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[Id] = [L].[EventId]
WHERE 
    --[E].[Price] >= @Price
    --AND
    @BoundingBox.STContains([L].[Position]) = 1

If I add the filter for Price, the same query takes 10 seconds to run. If I remove the spatial index the query takes 500ms. Depending on the size of the bounding box and the value of price the query takes either like second or less or anywhere between three to thirty seconds. Only filtering on the spatial column gives good performance for all bounding box sizes.

Every article I've found about spatial queries in SQL Server never says anything about additional column(s) filters. They only describes the spatial filter and how to tune performance for the spatial index/query and it this case the spatial filter works fine with different sized bounding boxes. The problem is the additional filter columns I need.

Is it possible to get performance from a query with a spatial filter and with additional column(s) filters?

EDIT

95% of the price values are below 5000. So filtering on a high value will only return around 100 rows. I've tried with a bounding box that is 24000 sq km and contains around 4 million events. So the issue might be with the distribution of the price values. I have a second column Date which has a better distribution of values and filtering on that column is fast as long as we don't get to close the the max date.

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[Id] = [L].[EventId]
WHERE 
    [E].[Date] >= @Date
    AND
    @BoundingBox.STContains([L].[Position]) = 1

Also the geometry have srid 4326 if that makes any difference.

  • 1
    A quick and dirty option would be to insert data using the spatial index into a temp table and to perform the filtering on the temp table. Can you add actual execution plans for both queries to your question using brentozar.com/pastetheplan ? It could also be helpful to include the output of SET STATISTICS IO, TIME ON . Thanks! – Joe Obbish Mar 1 '17 at 1:09
1

The answer is yes it is possible to get performance with filters over multiple columns including spatial columns.

I've tried to replicate your tables and closely as I can to your brief description using RAND to create prices, location points and link them to events. But I can't duplicate you performance problem. Have I got the basic table details right?

--  Event table
SELECT TOP 30000000 
    N EventID, 
    RAND(CAST(NEWID() AS VARBINARY)) * 10000 + 5000 Price
    into Event
FROM Tally;

ALTER TABLE Event ALTER COLUMN EventID INT NOT NULL;
GO
ALTER TABLE Event ADD CONSTRAINT Event_PK PRIMARY KEY (EventID);
GO
CREATE INDEX Event_Price_IDX ON Event(Price);
GO

-- Location Table
SELECT TOP 35000000
    N LocationID, 
    CEILING(RAND(CAST(NEWID() AS VARBINARY)) * 30000000) EventID,
    Geometry::Point(RAND(CAST(NEWID() AS VARBINARY)) * 710000 + 40000, RAND(CAST(NEWID() AS VARBINARY)) * 710000 + 40000, 0) Location
    into Location
FROM Tally;

Running the 3 versions of your query with hopefully sensible parameters

-- Test Query
DECLARE @price FLOAT = 13000.00 -- ~20% of records
-- 10 sq km query window
DECLARE @BoundingBox Geometry = Geometry::STGeomFromText('POLYGON(( 300000 300000, 310000 300000, 310000 310000, 300000 310000, 300000 300000))',0)

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE 
    --[E].[Price] >= @Price
    --AND
    @BoundingBox.STContains([L].[Location]) = 1

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE 
    [E].[Price] >= @Price
    --AND
    --@BoundingBox.STContains([L].[Location]) = 1

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE 
    [E].[Price] >= @Price
    AND
    @BoundingBox.STContains([L].[Location]) = 1

I get 93ms, 4ms and 93ms respectively, removing the TOP 2000 from the queries causes the price filtered query to blow out to a minute or so.

Are you able to give more details (execution plans, DDL, etc) to help us replicate your issue. My initial thought was that the TOP 2000 was hiding the underlying problem. I think that may still be the case.

  • Added some more information about the distribution of the Price values. – Nexith Mar 1 '17 at 17:47
  • @Nexith I think you will need to start adding some execution plans as suggested by Joe Obbish – MickyT Mar 1 '17 at 21:17
0

Would you please try the following with spatial index on the Location-table and report back:

SELECT *
FROM (
  SELECT TOP 2000 [E].*
  FROM [dbo].[Event] AS [E]
  INNER JOIN [dbo].[Location] AS [L] ON [E].[Id] = [L].[EventId]
  WHERE [E].[Price] >= @Price
  ) AS INNER_QUERY
WHERE @BoundingBox.STContains(INNER_QUERY.[Position]) = 1

We probably encountered the same problem and the execution plan seems ridiculous. The exact circumstances our problem occured under provided the main part of this workaround, that is using regular filters in an inner query, spatial filters in the outer query and using top to limit the inner queries' results (any number will do).

EDIT:

To expand on the issue, it seems to be an adverse decision of the query optimizer under certain conditions (might be table size, i can't reproduce the issue with ~1m entries with index on filter column). The execution plan looks like it would do the spatial filter over the whole table instead of the filtered subset but the actual time suggests both filters actually do interact (A query just using the spatial filter takes a lot longer). No idea what exactly is going on. In our case query cost was times 100 of what it should be. Other than the weird workaround i posted you can also force a query to use a certain index. so that would probably be a better idea:

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E] WITH (INDEX(<INDEX_NAME>))
INNER JOIN [dbo].[Location] AS [L] ON [E].[Id] = [L].[EventId]
WHERE [E].[Price] >= @Price
AND @BoundingBox.STContains([L].[Position]) = 1

SQL Server Version used: Microsoft SQL Server 2014 (SP2-GDR)

  • 1
    Note: as the OP hasn't been on the site in about 7 months, they're unlikely to respond at this point. – RDFozz Feb 13 '18 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.