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


I have the following schema:

DB 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,     Price Real,     Image NVARCHAR(150),      Name NVARCHAR(80),     Value NVARCHAR(150) )  INSERT INTO #temptable SELECT     [t].[Id],      [t].[Url],      [t].[GeneratedTitle] AS[Title],      [t].[Address],      [t].[Domain],      [t].[Price],     (SELECT TOP(1) ISNULL([m].[Resize1200x1200], [m].Resize730x532)      FROM [Multimedia] AS[m]      WHERE [t].[Id] = [m].[PropertyId]         and m.MultimediaType = 1      ORDER BY [m].[Order]) AS[Image],      [t].[Name],      [t].[Value] FROM     (SELECT         [p].[Id],         [p].[Url],         [p].[GeneratedTitle],         [g].[Address],         [p].[Domain],         [pr].[Amount] AS Price,         [p].[ModificationDate],         [f].[Name],         [pf].[Value]     FROM [Property] AS [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]     INNER JOIN [Operation] AS [o]          ON [p].[Id] = [o].[PropertyId]      INNER JOIN [OperationType] AS [o0]          ON [o].[OperationTypeId] = [o0].[Id]      INNER JOIN [Price] AS [pr]          ON [pr].[OperationId] = [o].[Id]      WHERE p.Id in          (Select Id from @properties)     GROUP BY [p].[Id],               [p].[Url],              [p].[GeneratedTitle],               [g].[Address],              [p].[Domain],               [pr].[Amount],              [p].[ModificationDate],              [f].[Name],              [pf].[Value]) AS[t]     ORDER BY[t].[ModificationDate] DESC  SET @cols = STUFF(                 (                     SELECT DISTINCT                             ','+QUOTENAME(c.[Name])                     FROM @features c FOR XML PATH(''), TYPE                  ).value('.', 'nvarchar(max)'), 1, 1, ''); SET @query = 'SELECT [Id],                       [Url],                       [Title],                       [Address],                       [Domain],                       [Price],                       [Image],                       ' + @cols + '                FROM (SELECT [Id],                              [Url],                              [Title],                              [Address],                              [Domain],                              [Price],                              [Image],                              [Value] AS [value],                              [Name] AS[name]                       FROM #temptable)x                       PIVOT(max(value) for name in ('+@cols+')) p'; EXECUTE(@query);  DROP TABLE #temptable 

The execution plan and Live query statistics show me the following:

Query Execution Plan

The previous query tries to obtain randomly a X number of records IDs, holding all the filter criteria to obtain only the IDs of the records which meet that criteria. The time right now is up to 15 seconds. It’s a lot if we talk about more than 400 users using concurrently the application.

Please, help me with this. I’m three weeks trying to solve this problem with no success, but a lot of advances has been made (before it was consuming 2 minutes in avg).

If it helps, I can give you access to a “dummy” deployed version of the DB with the same quantity of records to test and see directly the problem.

Thanks in advance…

===================================================================================================== INDEXES:

the indexes that are currently on the tables are the following:

GO CREATE UNIQUE NONCLUSTERED INDEX IX_Property_ModificationDate  ON [dbo].[Property] (ModificationDate DESC)  WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)  GO CREATE NONCLUSTERED INDEX [IX_Property_ParentId_StatusCode]  ON [dbo].[Property] ([ParentId] ASC, [StatusCode] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Property_ParentId_StatusCode_Id_ModificationDate]  ON [dbo].[Property] ([ParentId] ASC, [StatusCode] ASC, [Id] ASC, [ModificationDate] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Property_ParentId]     ON [dbo].[Property]([ParentId] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);      GO CREATE NONCLUSTERED INDEX [IX_Property_Identity_Domain_StatusCode]     ON [dbo].[Property]([Identity] ASC, [Domain] ASC, [StatusCode] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Property_Id_ModificationDate]  ON [dbo].[Property] (Id ASC, ModificationDate ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Property_PublisherId]     ON [dbo].[Property]([PublisherId] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);   GO CREATE NONCLUSTERED INDEX [IX_Property_RealEstateTypeId]     ON [dbo].[Property]([RealEstateTypeId] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)   GO  CREATE INDEX FIX_Property_StatusCode_Online ON [dbo].[Property](StatusCode) WHERE StatusCode = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE INDEX FIX_Property_StatusCode_Offline ON [dbo].[Property](StatusCode) WHERE StatusCode = 0 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE INDEX FIX_Property_Domain_Urbania ON [dbo].[Property](Domain) WHERE Domain = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE INDEX FIX_Property_Domain_Adondevivir ON [dbo].[Property](Domain) WHERE Domain = 2 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  GO CREATE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId_ModificationDate]  ON [dbo].[GeoLocation] (PropertyId ASC, [ModificationDate] DESC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId_Address]  ON [dbo].[GeoLocation] (PropertyId ASC, [Address] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE UNIQUE NONCLUSTERED INDEX IX_GeoLocation_ModificationDate  ON [dbo].[GeoLocation] (ModificationDate DESC)  WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE NONCLUSTERED INDEX [IX_GeoLocation_Ubigeo] ON [dbo].[GeoLocation]([Ubigeo] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)  GO CREATE UNIQUE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId]     ON [dbo].[GeoLocation]([PropertyId] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE SPATIAL INDEX SIX_GeoLocation_Coordinate ON [dbo].[GeoLocation](Coordinate) GO  CREATE INDEX FIX_GeoLocation_Domain_Urbania ON [dbo].[GeoLocation](Domain) WHERE Domain = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE INDEX FIX_GeoLocation_Domain_Adondevivir ON [dbo].[GeoLocation](Domain) WHERE Domain = 2 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  GO CREATE NONCLUSTERED INDEX [IX_Multimedia_PropertyId_Order]  ON [dbo].[Multimedia] (PropertyId ASC, [Order] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Multimedia_PropertyId]     ON [dbo].[Multimedia]([PropertyId] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Multimedia_Order]     ON [dbo].[Multimedia]([Order] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO  CREATE NONCLUSTERED INDEX [PK_Multimedia_Property]     ON [dbo].[Multimedia]([Id] ASC, [PropertyId] ASC); GO  CREATE INDEX FIX_Multimedia_MultimediaType_Image ON [dbo].[Multimedia](MultimediaType) WHERE MultimediaType = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_PropertyId_FeatureId]  ON [dbo].[PropertyFeature] (PropertyId ASC, [FeatureId] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_FeatureId]     ON [dbo].[PropertyFeature]([FeatureId] ASC);   GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_PropertyId]     ON [dbo].[PropertyFeature]([PropertyId] ASC);   GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature-FeatureId]     ON [dbo].[PropertyFeature]([Id] ASC, [FeatureId] ASC);   GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_Property]     ON [dbo].[PropertyFeature]([Id] ASC, [PropertyId] ASC);  GO CREATE NONCLUSTERED INDEX [IX_Operation_PropertyId]     ON [dbo].[Operation]([PropertyId] ASC);  GO CREATE NONCLUSTERED INDEX [IX_Operation_OperationTypeId]     ON [dbo].[Operation]([OperationTypeId] ASC);  GO CREATE NONCLUSTERED INDEX [IX_Price_OperationId]     ON [dbo].[Price]([OperationId] ASC);  GO CREATE NONCLUSTERED INDEX [IX_Price_Operation]     ON [dbo].[Price]([Id] ASC, [OperationId] ASC);