CODEWITHSUNDEEP
sqlsql-serversql-server-2008query-optimizationquery-performance
Hennie Francis
Hennie Francis

Reputation: 124

SQL Query Optimization with Union Select

I have this query, it returns 570 rows, but runs 2m 35s. In SQL the query execute, but in my solution, it gives a timeout. How can I optimize this to run under 1m, pref 30s.

SELECT  [Region] = Region.FirstName,
        [Patient] = Patient.Name,
        [PatientStatus] = AccountRating.Name,
        [MedicalAid] = AccountType.Name,
        [QuoteAmount] = (   SELECT TOP 1 A.Response
                            FROM dbo.Questionnaire Q
                            JOIN dbo.QuestionnaireDefinition QRD
                                ON QRD.QuestionnaireDefinitionID = Q.QuestionnaireDefinitionID
                                AND QRD.Name = 'Internal Admin'
                            LEFT JOIN QuestionDefinition QD
                                ON Q.QuestionnaireDefinitionID = QD.QuestionnaireDefinitionID
                                AND QD.QuestionDefinitionID = 5966
                            LEFT OUTER JOIN Answer A
                                ON A.QuestionnaireID = Q.QuestionnaireID
                                AND A.QuestionDefinitionID = QD.QuestionDefinitionID
                            WHERE Q.IsActive = 1
                            AND Q.SubscriberID = 240
                            AND Q.AccountID = Patient.AccountID
                        ),
        [InvoiceAmount] =   (   SELECT TOP 1 A.Response
                                FROM dbo.Questionnaire Q
                                JOIN dbo.QuestionnaireDefinition QRD
                                    ON QRD.QuestionnaireDefinitionID = Q.QuestionnaireDefinitionID
                                    AND QRD.Name = 'Internal Admin'
                                LEFT JOIN QuestionDefinition QD
                                    ON Q.QuestionnaireDefinitionID = QD.QuestionnaireDefinitionID
                                    AND QD.QuestionDefinitionID = 5969
                                LEFT OUTER JOIN Answer A
                                    ON A.QuestionnaireID = Q.QuestionnaireID
                                    AND A.QuestionDefinitionID = QD.QuestionDefinitionID
                                WHERE Q.IsActive = 1
                                AND Q.SubscriberID = 240
                                AND Q.AccountID = Patient.AccountID
                            ),
        [DateSubmitted] =   (   SELECT TOP 1 A.Response
                                FROM dbo.Questionnaire Q
                                JOIN dbo.QuestionnaireDefinition QRD
                                    ON QRD.QuestionnaireDefinitionID = Q.QuestionnaireDefinitionID
                                    AND QRD.Name = 'Internal Admin'
                                LEFT JOIN QuestionDefinition QD
                                    ON Q.QuestionnaireDefinitionID = QD.QuestionnaireDefinitionID
                                    AND QD.QuestionDefinitionID = 5965
                                LEFT OUTER JOIN Answer A
                                    ON A.QuestionnaireID = Q.QuestionnaireID
                                    AND A.QuestionDefinitionID = QD.QuestionDefinitionID
                                WHERE Q.IsActive = 1
                                AND Q.SubscriberID = 240
                                AND Q.AccountID = Patient.AccountID
                            ),
        [DateApprovedDeclined] =    (   SELECT TOP 1 A.Response
                                        FROM dbo.Questionnaire Q
                                        JOIN dbo.QuestionnaireDefinition QRD
                                            ON QRD.QuestionnaireDefinitionID = Q.QuestionnaireDefinitionID
                                            AND QRD.Name = 'Internal Admin'
                                        LEFT JOIN QuestionDefinition QD
                                            ON Q.QuestionnaireDefinitionID = QD.QuestionnaireDefinitionID
                                            AND QD.QuestionDefinitionID = 5968
                                        LEFT OUTER JOIN Answer A
                                            ON A.QuestionnaireID = Q.QuestionnaireID
                                            AND A.QuestionDefinitionID = QD.QuestionDefinitionID
                                        WHERE Q.IsActive = 1
                                        AND Q.SubscriberID = 240
                                        AND Q.AccountID = Patient.AccountID
                                    ),
        [IntAdmFormCreatedDate]= Q.DateCreated,
        [HasAdminForm] = 'Yes',
        [CreatedByUser] = PatientCreatedBy.Name
FROM dbo.Account AS Patient
JOIN dbo.AccountRating
    ON Patient.AccountRatingID = AccountRating.AccountRatingID
JOIN dbo.AccountType
    ON Patient.AccountTypeID = AccountType.AccountTypeID
JOIN dbo.[User] Region
    ON Patient.UserID = Region.UserID
JOIN dbo.[User] PatientCreatedBy
    ON Patient.CreatedBy = PatientCreatedBy.UserID
JOIN dbo.Questionnaire Q
    ON Patient.AccountID = Q.AccountID

WHERE Patient.SubscriberID = 240
    AND (Q.DateCreated < DATEADD(D, 26, DATEADD(MONTH, DATEDIFF(MONTH, CONVERT(DATETIME, '1900-01-01 00:00:00', 102), GETDATE()), CONVERT(DATETIME, '1900-01-01 00:00:00', 102))))
    AND Q.QuestionnaireDefinitionID = 235
    AND Q.IsActive = 1
    AND Region.FirstName <> 'Rubbish'

UNION SELECT    [Region] = Region.FirstName, 
                [Patient] = Patient.Name, 
                [PatientStatus] = AccountRating.Name, 
                [MedicalAid] = AccountType.Name, 
                [QuoteAmount] = '0', 
                [InvoiceAmount] = '0', 
                [DateSubmitted] = '', 
                [DateApprovedDeclined] = '',
                [IntAdmFormCreatedDate] = '',
                [HasAdminForm] = 'No',
                [CreatedByUser] = PatientCreatedBy.Name 
FROM dbo.Account AS Patient 
JOIN dbo.AccountRating
    ON Patient.AccountRatingID = AccountRating.AccountRatingID 
JOIN dbo.AccountType
    ON Patient.AccountTypeID = AccountType.AccountTypeID 
JOIN dbo.[User] AS Region
    ON Patient.UserID = Region.UserID 
JOIN dbo.[User] AS PatientCreatedBy
    ON Patient.CreatedBy = PatientCreatedBy.UserID
WHERE NOT EXISTS(   SELECT * 
                    FROM Questionnaire AS Q 
                    WHERE Patient.AccountID = Q.AccountID 
                    AND Q.QuestionnaireDefinitionID = 235
                    AND Patient.SubscriberID = 240
                    AND Q.SubscriberID = 240
                )
    AND Patient.SubscriberID = 240
    AND Patient.DateCreated < DATEADD(D, 26, DATEADD(MONTH, DATEDIFF(MONTH, CONVERT(DATETIME, '1900-01-01 00:00:00', 102), GETDATE()), CONVERT(DATETIME, '1900-01-01 00:00:00', 102)))
    AND Region.FirstName <> 'Rubbish'

Here is another version of the query I tried, but also runs the same time.

SELECT  [Region] = Region.FirstName,
        [Patient] = Patient.Name,
        [PatientStatus] = AccountRating.Name,
        [MedicalAid] = AccountType.Name,
        [QuoteAmount] = Q1.Response,
        [InvoiceAmount] = Q2.Response,
        [DateSubmitted] = Q3.Response,
        [DateApprovedDeclined] = Q4.Response,
        [IntAdmFormCreatedDate]= Q.DateCreated,
        [HasAdminForm] = 'Yes',
        [CreatedByUser] = PatientCreatedBy.Name
FROM dbo.Account AS Patient
JOIN dbo.AccountRating
    ON Patient.AccountRatingID = AccountRating.AccountRatingID
JOIN dbo.AccountType
    ON Patient.AccountTypeID = AccountType.AccountTypeID
JOIN dbo.[User] Region
    ON Patient.UserID = Region.UserID
JOIN dbo.[User] PatientCreatedBy
    ON Patient.CreatedBy = PatientCreatedBy.UserID
JOIN dbo.Questionnaire Q
    ON Patient.AccountID = Q.AccountID
OUTER APPLY
(
    SELECT TOP 1    Q.AccountID,
            A.Response
    FROM dbo.Questionnaire Q
    JOIN dbo.QuestionnaireDefinition QRD
        ON QRD.QuestionnaireDefinitionID = Q.QuestionnaireDefinitionID
        AND QRD.Name = 'Internal Admin'
    LEFT JOIN QuestionDefinition QD
        ON Q.QuestionnaireDefinitionID = QD.QuestionnaireDefinitionID
        AND QD.QuestionDefinitionID = 5966
    LEFT OUTER JOIN Answer A
        ON A.QuestionnaireID = Q.QuestionnaireID
        AND A.QuestionDefinitionID = QD.QuestionDefinitionID
    WHERE Q.IsActive = 1
    Q.SubscriberID = 240
    AND Q.AccountID = Patient.AccountID
) Q1
OUTER APPLY
(
    SELECT TOP 1    Q.AccountID,
                    A.Response
    FROM dbo.Questionnaire Q
    JOIN dbo.QuestionnaireDefinition QRD
        ON QRD.QuestionnaireDefinitionID = Q.QuestionnaireDefinitionID
        AND QRD.Name = 'Internal Admin'
    LEFT JOIN QuestionDefinition QD
        ON Q.QuestionnaireDefinitionID = QD.QuestionnaireDefinitionID
        AND QD.QuestionDefinitionID = 5969
    LEFT OUTER JOIN Answer A
        ON A.QuestionnaireID = Q.QuestionnaireID
        AND A.QuestionDefinitionID = QD.QuestionDefinitionID
    WHERE Q.IsActive = 1
    AND Q.AccountID = Patient.AccountID
) Q2
OUTER APPLY
(
    SELECT TOP 1    Q.AccountID,
            A.Response
    FROM dbo.Questionnaire Q
    JOIN dbo.QuestionnaireDefinition QRD
        ON QRD.QuestionnaireDefinitionID = Q.QuestionnaireDefinitionID
        AND QRD.Name = 'Internal Admin'
    LEFT JOIN QuestionDefinition QD
        ON Q.QuestionnaireDefinitionID = QD.QuestionnaireDefinitionID
        AND QD.QuestionDefinitionID = 5965
    LEFT OUTER JOIN Answer A
        ON A.QuestionnaireID = Q.QuestionnaireID
        AND A.QuestionDefinitionID = QD.QuestionDefinitionID
    WHERE Q.IsActive = 1
    AND Q.AccountID = Patient.AccountID
) Q3
OUTER APPLY
(
    SELECT TOP 1    Q.AccountID,
                    A.Response
    FROM dbo.Questionnaire Q
    JOIN dbo.QuestionnaireDefinition QRD
        ON QRD.QuestionnaireDefinitionID = Q.QuestionnaireDefinitionID
        AND QRD.Name = 'Internal Admin'
    LEFT JOIN QuestionDefinition QD
        ON Q.QuestionnaireDefinitionID = QD.QuestionnaireDefinitionID
        AND QD.QuestionDefinitionID = 5968
    LEFT OUTER JOIN Answer A
        ON A.QuestionnaireID = Q.QuestionnaireID
        AND A.QuestionDefinitionID = QD.QuestionDefinitionID
    WHERE Q.IsActive = 1
    AND Q.AccountID = Patient.AccountID
) Q4
WHERE Patient.SubscriberID = 240
    AND (Q.DateCreated < DATEADD(D, 26, DATEADD(MONTH, DATEDIFF(MONTH, CONVERT(DATETIME, '1900-01-01 00:00:00', 102), GETDATE()), CONVERT(DATETIME, '1900-01-01 00:00:00', 102))))
    AND Q.QuestionnaireDefinitionID = 235
    AND Q.IsActive = 1
    AND Region.FirstName <> 'Rubbish'

UNION SELECT    [Region] = Region.FirstName, 
                [Patient] = Patient.Name, 
                [PatientStatus] = AccountRating.Name, 
                [MedicalAid] = AccountType.Name, 
                [QuoteAmount] = '0', 
                [InvoiceAmount] = '0', 
                [DateSubmitted] = '', 
                [DateApprovedDeclined] = '',
                [IntAdmFormCreatedDate] = '',
                [HasAdminForm] = 'No',
                [CreatedByUser] = PatientCreatedBy.Name 
FROM dbo.Account AS Patient 
JOIN dbo.AccountRating
    ON Patient.AccountRatingID = AccountRating.AccountRatingID 
JOIN dbo.AccountType
    ON Patient.AccountTypeID = AccountType.AccountTypeID 
JOIN dbo.[User] AS Region
    ON Patient.UserID = Region.UserID 
JOIN dbo.[User] AS PatientCreatedBy
    ON Patient.CreatedBy = PatientCreatedBy.UserID
WHERE NOT EXISTS(   SELECT * 
                    FROM Questionnaire AS Q 
                    WHERE Patient.AccountID = Q.AccountID 
                    AND Q.QuestionnaireDefinitionID = 235
                    AND Patient.SubscriberID = 240
                    AND Q.SubscriberID = 240
                )
    AND Patient.SubscriberID = 240
    AND Patient.DateCreated < DATEADD(D, 26, DATEADD(MONTH, DATEDIFF(MONTH, CONVERT(DATETIME, '1900-01-01 00:00:00', 102), GETDATE()), CONVERT(DATETIME, '1900-01-01 00:00:00', 102)))
    AND Region.FirstName <> 'Rubbish'

Upvotes: 0

Views: 64

Answers (1)

dnoeth
dnoeth

Reputation: 60472

Your Scalar Subqueries all share the same joins, just the QD.QuestionDefinitionID differs.

You can rewrite those 4 TOPs with a single Derived Table and join to it instead:

...
LEFT JOIN
 (
   SELECT 
       Q.AccountID,
       MAX(CASE WHEN QD.QuestionDefinitionID = 5966 THEN A.Response END) AS [DateSubmitted]
       MAX(CASE WHEN QD.QuestionDefinitionID = 5968 THEN A.Response END) AS [DateApprovedDeclined]
       ...
   FROM dbo.Questionnaire Q
   JOIN dbo.QuestionnaireDefinition QRD
       ON QRD.QuestionnaireDefinitionID = Q.QuestionnaireDefinitionID
       AND QRD.NAME = 'Internal Admin'
   LEFT JOIN QuestionDefinition QD
       ON Q.QuestionnaireDefinitionID = QD.QuestionnaireDefinitionID
   LEFT OUTER JOIN Answer A
       ON A.QuestionnaireID = Q.QuestionnaireID
       AND A.QuestionDefinitionID = QD.QuestionDefinitionID
   WHERE Q.IsActive = 1
   AND Q.SubscriberID = 240
   GROUP BY Q.AccountID
 ) AS Q 
ON Q.AccountID = Patient.AccountID

I used MAX because you didn't have ORDER BY in you subqueries, so the exact value either doesn't matter or there's only a single row per value.

Upvotes: 2

Related Questions