I was trying to execute the following query on a view, from an azure sql db.
This view gets data from 4 tables, none of them have index.
Query 1:
SELECT
[ID_RM] AS [employee_RM_id]
,[EMPLOYEE_NAME] AS [employee_name]
,[TRAINING_NAME] AS [training_name]
,[DATE] AS [training_date]
,[EXPIRATION_DATE] AS [expiration_date]
,[TRAINING_STATUS] AS [status]
FROM [dbo].[P_TRAINING]
WHERE [TRAINING_STATUS] IN ('Competent','Expired')
it takes about 10 min to process this simple query.
When I add another column it takes 3 seconds to process:
Query 2:
SELECT
[ID_RM] AS [employee_RM_id]
,[EMPLOYEE_NAME] AS [employee_name]
,[TRAINING_NAME] AS [training_name]
,[DATE] AS [training_date]
,[EXPIRATION_DATE] AS [expiration_date]
,[TRAINING_STATUS] AS [status]
,[CATEGORY] AS [category]
FROM [dbo].[P_TRAINING]
WHERE [TRAINING_STATUS] IN ('Competent','Expired')
When I was analysing I've notices that the query 1, on azure portal, is a litle different when executed, it is like this:
query 3:
SELECT [T1_1].[ID_RM] AS [ID_RM],
[T1_1].[EMPLOYEE_NAME] AS [EMPLOYEE_NAME],
[T1_1].[TRAINING_NAME] AS [TRAINING_NAME],
[T1_1].[DATE] AS [DATE],
[T1_1].[EXPIRATION_DATE] AS [EXPIRATION_DATE],
[T1_1].[TRAINING_STATUS] AS [TRAINING_STATUS]
FROM (SELECT [T2_1].[ID_RM] AS [ID_RM],
[T2_1].[EMPLOYEE_NAME] AS [EMPLOYEE_NAME],
[T2_1].[TRAINING_NAME] AS [TRAINING_NAME],
[T2_1].[DATE] AS [DATE],
[T2_1].[EXPIRATION_DATE] AS [EXPIRATION_DATE],
[T2_1].[TRAINING_STATUS] AS [TRAINING_STATUS]
FROM [dbo].[P_TRAINING] AS T2_1
WHERE (([T2_1].[TRAINING_STATUS] = CAST (N'Competent' COLLATE SQL_Latin1_General_CP1_CI_AS AS VARCHAR (9)) COLLATE SQL_Latin1_General_CP1_CI_AS)
OR ([T2_1].[TRAINING_STATUS] = CAST (N'Expired' COLLATE SQL_Latin1_General_CP1_CI_AS AS VARCHAR (7)) COLLATE SQL_Latin1_General_CP1_CI_AS))) AS T1_1
this query end executing a loop for each row of the first select
][1]
This is what I think is causing the query that should run in 3 seconds to run in 10 min.
The question is how to solve this?