Azure / Azure/data-api-builder
Optimize Redundant SQL Query
- Lenguaje dominante
- C#
- Estrellas
- 1.5k
- Forks
- 370
- Merge medio
- 3 d 22 h
- PR fusionados (30 d)
- 9
Descripción
### What happened?
The current SQL script contains redundant SELECT COUNT(*) operations during update/delete. This can be optimized to improve performance.
## Current Generated SQL:
```
DECLARE @ROWS_TO_UPDATE int;
SET @ROWS_TO_UPDATE = (SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[todo] WHERE [dbo].[todo].[id] = @param0);
SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[todo] WHERE [dbo].[todo].[id] = @param0;
IF @ROWS_TO_UPDATE = 1
BEGIN
UPDATE [dbo].[todo]
SET [dbo].[todo].[position] = @param1, [dbo].[todo].[owner_id] = @param2
OUTPUT Inserted.[id] AS [id], Inserted.[title] AS [title], Inserted.[completed] AS [completed], Inserted.[owner_id] AS [owner_id], Inserted.[position] AS [order]
WHERE [dbo].[todo].[id] = @param0;
END
```
## Proposed Improvement:
Replace the second SELECT COUNT(*) operation with a SELECT @ROWS_TO_UPDATE to avoid redundant querying.
## Optimized Script:
```
DECLARE @ROWS_TO_UPDATE int;
SET @ROWS_TO_UPDATE = (SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[todo] WHERE [dbo].[todo].[id] = @param0);
SELECT @ROWS_TO_UPDATE as cnt_rows_to_update;
IF @ROWS_TO_UPDATE = 1
BEGIN
UPDATE [dbo].[todo]
SET [dbo].[todo].[position] = @param1, [dbo].[todo].[owner_id] = @param2
OUTPUT Inserted.[id] AS [id], Inserted.[title] AS [title], Inserted.[completed] AS [completed], Inserted.[owner_id] AS [owner_id], Inserted.[position] AS [order]
WHERE [dbo].[todo].[id] = @param0;
END
```
### Version
main
### What database are you using?
Azure SQL
### What hosting model are you using?
_No response_
### Which API approach are you accessing DAB through?
_No response_
### Relevant log output
_No response_
### Code of Conduct
- [x] I agree to follow this project's Code of Conduct
Guía de contribución
Línea de trabajo
Comienza rastreando la generación de la consulta de actualización/eliminación de Azure SQL que produce el script mostrado. Compara el SQL actual y el optimizado en el issue y, después, ejecuta las pruebas relevantes de generación de consultas si están disponibles; se considera terminado cuando el número de filas se devuelve desde @ROWS_TO_UPDATE sin una segunda consulta COUNT(*).
Escrito por el modelo de indexación a partir del texto del issue.
Evaluación
- Stack tecnológico
- azure, sql
- Área
- databases
- Tipo de issue
- Refactorización
- Dificultad
- 3/5
- Tiempo estimado
- 1-2 días
- Estado de actividad
- Estancado
- Claridad
- Bastante claro
- Aptitud para principiantes
- 35/100