Azure / Azure/data-api-builder
Optimize Redundant SQL Query
- Dominant language
- C#
- Stars
- 1.5k
- Forks
- 370
- Avg merge
- 3d 17h
- Merged PRs (30d)
- 8
Description
### 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
Contributor guide
Assessment
This issue has not been assessed yet.