question

GustavoEpifanio-3553 avatar image
0 Votes"
GustavoEpifanio-3553 asked SaurabhSharma-msft commented

Remove one column from select have a very poor performance

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 ![123615-image.png][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?

azure-sql-database
image.png (4.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered SaurabhSharma-msft commented

When you write a code like this.

 SELECT ...
 FROM view1

The database engine converts it to

 SELECT ...
 FROM (SELECT .... ) as view1

That is the reason you get query 3. Views are basically string macros.

On the other hand, how many rows that table has? It must have a good amount of rows if elapsed execution time ranges between 3 seconds and 10 minutes. The appropriate solution may be to create an index to support those queries.

 CREATE NONCLUSTERED INDEX [IX_P_TRAINING__TRAINING_STATUS] ON [Facturacion].[Facturas_Detalle]
 ([TRAINING_STATUS] ASC)
 INCLUDE ([ID_RM],[EMPLOYEE_NAME],[TRAINING_NAME],[DATE],[EXPIRATION_DATE],[TRAINING_STATUS],[CATEGORY])
 GO






· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

There is only 35k rows and 7 columns. The Query 2 is the select *

But why there is a loop on the query plan when I have less columns and takes muuuch more time?!

If the index was a problem wouldn't it impact the 3s query?

0 Votes 0 ·

Please remember what I said. The database engine will convert your view to this at execution time:

SELECT ...
FROM (SELECT .... ) as view1

That is why you are seeing the JOIN operator. If you execute the query outside the View you will have a more simple plan.

On the other hand, scans of 35 K rows on Azure SQL databases (Paas) on service tiers that only have one core available and storage is not SSD can kill performance. For example, the max degree of parallelism you can have from Basic tier up to S3 tier is just 1 core.

Would you like to avoid creating an index in this scenario? To avoid paying more on Azure SQL PaaS, you may need to have your query well tuned, and scans will definitely spike CPU consumption and/or RAM usage and /or stoarge usage.

0 Votes 0 ·

Hi @GustavoEpifanio-3553 ,

Please let us know if you find above replies useful. If yes, please accept above as answer. This will help other community members facing similar query to refer to this solution.
In case If you already found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.

Thanks
Saurabh

0 Votes 0 ·