question

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'