Optimization of a query to retrieve records randomly with multiple joins and filters

I have the following schema:

This question was posted also in StackOverflow, but I want to consult also to specialists more focused on DB administration because the nature of my project. Sorry if this is a mistake

Right now, the table Property hold more than 70K records. I'm developing an update to support more than 500 concurrent sessions. The application will support a map a to make the searches, that's why GeoLocation declares Coordinate as geography data type. Now we have a big problem, because the response time for some queries (the most important ones) is very slow. I mean, the application has to return around 1000 records at once if there are that quantity of results for the specified parameters.

The parameters are distributed on all the tables of the schema (actually, it's a portion of the schema). Being Features a table which holds all the principal "characteristics" of the properties (# of bedrooms, # of garages, etc).

With that on mind, the query that is taking so much time right now is the following:

```
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

DECLARE @properties TABLE(
[ID] INT
)

INSERT INTO @properties
SELECT p.[Id]
FROM[Property] p
INNER JOIN[GeoLocation] AS[g]
ON[p].[Id] = [g].[PropertyId]
INNER JOIN[PropertyFeature] AS[pf]
ON[pf].[PropertyId] = [p].[Id]
INNER JOIN[Feature] AS[f]
ON[pf].[FeatureId] = [f].[Id]
WHERE[g].[Address] IS NOT NULL AND(([g].[Address] <> N'') OR[g].[Address] IS NULL)
AND[pf].[FeatureId] IN(
Select ID from feature where featuretype = 1)
GROUP BY p.Id, p.ModificationDate
ORDER BY [p].ModificationDate DESC, newid()
OFFSET 0 ROWS
FETCH NEXT 1000 ROWS ONLY

DECLARE @features TABLE(
[Name] NVARCHAR(80)
)

INSERT INTO @features
select Name from feature where FeatureType = 1

CREATE TABLE #temptable
(
Id INT,
Url NVARCHAR(200),
Title NVARCHAR(300),
Address NVARCHAR(200),
Domain Tinyint,

Being on the Standard Tier (S0) is causing your queries to be throttled significantly, which is affecting the total runtime. Here are the times for all 5 statements in the batch, as viewed in Sentry One Plan Explorer:

As you can see, most all of the queries have a duration that's much longer than CPU time. This often means the queries are waiting on some resource. Taking a look at the middle query, we can see these wait stats in the XML:






The query spent basically the entire duration waiting to be scheduled on a CPU. You can look up the details of these waits in the SQL Skills wait type library.

The query even waits a long time to compile, as evidenced by stats in the "QueryPlan" element:



All 5 statements have similar characteristics (high resource waits, low CPU).

There are some improvements that could be made to the queries. For instance, there is a bad estimate in the middle query due to the use of a table variable for @properties which results in not-ideal plan choices from the beginning (a #temp table would likely be better in this situation).

However, without all the waits, this whole batch of queries would run in less than a second. So tuning the query and indexes won't help until you have more hardware available.

StackExchange Database Administrators Q#266647, answer score: 4