Monthly Archives: September 2014

I was working on a stored procedure when suddenly test runs started to timeout (>30 seconds). I extracted the procedure, replaced the parameters with the same constants, and ran it. Finished instantly. I spent a lot of time focusing on the “stored procedure” aspect of it (trying things such as WITH RECOMPILE and shadow variables), but the problem turned out to revolve around the “parameterized” aspect of it.

We ended up reducing the query to this small query, where each table/view has at most 10,000 rows:

SELECT *
  FROM tbl6ControlEntity CE 
  LEFT JOIN dbo.tbl7Accounts AS PM 
  ON PM.record_gid = CE.program_manager_gid
WHERE 																					
  (@Insp = 0 OR CE.control_entity_gid IN
    (SELECT XIP.control_entity_gid FROM 
    viewWSBIS_GetControlEntities_Inspections XIP WHERE 
    XIP.master_cd_flag = 1 AND XIP.cert_no = 
      (SELECT AC.inspector_cert_no FROM tbl7Accounts AC 
       WHERE AC.record_gid = @InspGid)))
ORDER BY CE.sort_br_no	

When we put in constants for the parameters @Insp and @InspGid, the query ran in less than 1 second. When we declared and set them in the query window, it took indefinite amounts of time. Using a temp table instead of the view helped, but wasn’t a complete solution. We tried replacing the inner-most uncorrelated subquery with a join. No impact.

I tried most of the common suggestions, such as OPTIMIZE FOR @Insp = 1 and OPTIMIZE FOR UNKNOWN. No impact.

Finally, I added OPTION (RECOMPILE) to the query, which fixed the problem. I’m surprised the WITH RECOMPILE on the stored procedure did not, however.

In general, the need for OPTION (RECOMPILE) indicates a need to rebuild statistics, however, in the cases where flags are used to control execution of certain subconditions, I’m not sure the statistics can cope.

See also: OPTION (RECOMPILE) is Always Faster; Why?

When writing adhoc queries, I find myself writing UPDATEs and DELETEs where I know how many rows should be affected, or, in a stored procedure, maybe I have a variable that I can reference that tells me that. With LINQ, we can use .Single() to throw an exception if there is more than one result, for example. It would be helpful if SQL had an extension that let us specify exactly how many rows should be affected, and if not, the whole command is canceled.

For example, imagine we could

UPDATE EXACTLY 1 table SET field = value WHERE key = whatever

or

DELETE EXACTLY 16 FROM table WHERE field = whatever

And those commands would fail and do nothing if they would affect more or less than 1 and 16 rows respectively.

Of course, SQL doesn’t support this syntax. A workaround is to use the output clause and a transaction. Alternatively, @@ROWCOUNT and a transaction can also be used. However, writing these transaction wrappers around a statement is a lot of text and space for a small benefit. Could these statements be automated somehow?

We can take advantage of implicit transactions in triggers to control success of the overall command. By executing a rollback within the trigger, the update or delete command itself will be rolled back, without an explicit begin transaction.

So, in use, this looks like:

EXEC RowCountExactly 'tblBridges', 2
UPDATE tblBridges SET fips_code = 12345;

In this case, more than 2 rows would have been updated, so the command fails:

Unexpected number of rows affected; 156507 vs 2 expected
Msg 3609, Level 16, State 1, Line 6
The transaction ended in the trigger. The batch has been aborted.

The implementation is a stored proc “RowCountExactly” that creates a trigger on the specified table. The trigger checks if the rowcount differs from the specified value. If so, it rolls back the batch. Either way (succeed or fail), the trigger also removes itself, so that it only runs a single time.

IF OBJECT_ID ( 'dbo.RowCountExactly', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.RowCountExactly;
GO
CREATE PROCEDURE dbo.RowCountExactly
(
	@TableName nvarchar(MAX),
	@RowCount int
)
AS
BEGIN
	IF OBJECT_ID ('dbo.RowCountCheck','TR') IS NOT NULL
		EXEC('DROP TRIGGER dbo.RowCountCheck');
	
	DECLARE @trigger varchar(max);

	SET @trigger = 'CREATE TRIGGER dbo.RowCountCheck ON ' + 
	  CONVERT(varchar(max), @TableName) + 
	' AFTER INSERT, UPDATE, DELETE
	AS	
	BEGIN
		DECLARE @actual int;
		SET @actual = @@ROWCOUNT;		

		IF @actual <> ' + CONVERT(varchar(max), @RowCount) + '
		BEGIN
			ROLLBACK;
			IF OBJECT_ID (''dbo.RowCountCheck'',''TR'') IS NOT NULL
				EXEC(''DROP TRIGGER dbo.RowCountCheck'');

			RAISERROR (N''Unexpected number of rows affected; %d vs %d expected'',
				10, 0, 
				@actual, ' + CONVERT(varchar(max), @RowCount) + ');
		END
		ELSE
			IF OBJECT_ID (''dbo.RowCountCheck'',''TR'') IS NOT NULL
				EXEC(''DROP TRIGGER dbo.RowCountCheck'');
	END		
	';

	EXEC(@trigger);
	

END;
GO