Optimize Redundant SQL Query

[abhishekkumams]

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

[JerryNixon]

@sezal98 should this be part of 1.4?