Hi community,

Yesterday we did roll out a new version of our application to production and since then we are experiencing slow queries (e.g. 16 seconds to complete where it was previously 0.25 seconds). 

We did add some extra lines to our SQL query so at first we thought that's where the problem started. After removing these extra lines, we see that the performance is still very bad. To give a better idea of the size of the number of orders, I would like to state that it is about 800. Also we are using a SQL query since we are using a few functions that cannot be used within aggregates. We are not sure on how to debug this process since removing lines on the SQL query does not seem to improve the performance. Also we are using a Data Action to retrieve the orders and are using a structure to minimize the number of fields fetched from the database.

Does anybody have any recommendations based on the query below (e.g. did we use any bad practice in this query? Or did we miss something in the actual query that is causing a lot of seperate (sub)queries?)? Also good to mention that we did use indexes to speed up de data retrieval.

If more information is need please let me know since this is giving me a headache to solve.

SELECT      ord.[Id],
            ord.[OrderNumber],
            ord.[OrderKindCode],
            ord.[CustomerLastName],
            ord.[CustomerFullName],
            ord.[AssemblyDate],
            ord.[AssemblyWeek],
            ord.[AssemblerCode],
            ord.[AssemblerFullName],
            ord.[CustomerManagerCode],
            ord.[CustomerManagerFullName],
            ord.[QualityManagerCode],
            ord.[QualityManagerFullName],
            ord.[ExpectedCompletionDate],
            lov.[ViewMoment],
            COUNT(vst.[Id]) As nrVisits,
            (
                SELECT  COUNT({Note}.[Id])
                FROM    {Note}
                WHERE   {Note}.[OrderId] = ord.[Id]
                AND     {Note}.[Moment] >= COALESCE(lov.[ViewMoment], '1900-01-01')
                AND     {Note}.[UserId] <> @UserId
                AND     (
                            CASE
                                WHEN (@CheckMainAssemblerRole = 1)
                                    THEN (
                                            CASE
                                                WHEN ({Note}.[VisibleForAssembler] = 1)
                                                    THEN 1
                                                ELSE 0
                                                END
                                         )
                                ELSE 1
                                END
                            ) = 1
            ) As nrUnreadNotes,
            (
                SELECT      COUNT(DISTINCT ord.[Id])
                FROM    (((  {Order} ord
                                LEFT JOIN {UserExtended} cmn
                                    ON ord.[CustomerManagerCode] = cmn.[UserNameConcent] )
                                LEFT JOIN {UserExtended} qmn
                                    ON ord.[QualityManagerCode] = qmn.[UserNameConcent] )
                                LEFT JOIN {UserExtended} acn
                                    ON ord.[AssemblerCode] = acn.[SanitaryAssemblerCodeConcent] )
                WHERE       @Where
            ) As nrOrders
        
FROM    (((((   {Order} ord
                LEFT JOIN {Visit} vst
                    ON ord.[Id] = vst.[OrderId] )
                LEFT JOIN {LastOrderView} lov
                    ON ord.[Id] = lov.[OrderId]
                    AND @UserId = lov.[UserId] )
                LEFT JOIN {UserExtended} cmn
                    ON ord.[CustomerManagerCode] = cmn.[UserNameConcent] )
                LEFT JOIN {UserExtended} qmn
                    ON ord.[QualityManagerCode] = qmn.[UserNameConcent] )
                LEFT JOIN {UserExtended} acn
                    ON ord.[AssemblerCode] = acn.[SanitaryAssemblerCodeConcent] )
                    
WHERE       @Where

GROUP BY    ord.[Id],
            ord.[OrderNumber],
            ord.[OrderKindCode],
            ord.[CustomerLastName],
            ord.[CustomerFullName],
            ord.[AssemblyDate],
            ord.[AssemblyWeek],
            ord.[AssemblerCode],
            ord.[AssemblerFullName],
            ord.[CustomerManagerCode],
            ord.[CustomerManagerFullName],
            ord.[QualityManagerCode],
            ord.[QualityManagerFullName],
            ord.[ExpectedCompletionDate],
            lov.[ViewMoment]
            
ORDER BY    ord.[AssemblyDate] ASC,
            ord.[CustomerLastName] ASC
            
OFFSET @StartIndex ROWS

FETCH NEXT @MaxRecords ROW ONLY

Thanks, 

Bart