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

Question

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,
    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);

UPDATE: The azure SQL Server Service Tier I’m using is Standard S0 (10 DTUs)

Hi, Adolfo! Could edit your question to include the service tier of Azure SQL Database you're using? The numbers in that execution plan indicate that your queries are being throttled pretty heavily in terms of CPU. — Josh Darnell, Commented May 7, 2020 at 3:32
@JoshDarnell thanks. It’s done. I don’t know if that’s the case. I’ll try to upgrade that tomorrow cause I don’t have access from my home. But it does not seems to be related to the query itself? I mean, it’s not too heavy by itself? Also, I have to say that the application is in a dev environment right now, no more than 5 concurrent users in a single point of the day. 15 seconds for a single call is too long. — Adolfo F. Ibarra Landeo, Commented May 7, 2020 at 3:54

Josh Darnell · Accepted Answer · 2022-10-19 12:04:18Z

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:

<WaitStats>
  <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="939" WaitCount="20" />
  <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="858" WaitCount="61" />
</WaitStats>

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:

<QueryPlan ... CompileTime="711" CompileCPU="67" ... >

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.

Stack Exchange Network

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

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
performance
optimization
query
azure-sql-database
or ask your own question.

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
performance
optimization
query
azure-sql-database
or ask your own question.