How much is EXEC?

Question

I inherited some inherited code in which each stored procedure is implemented twice : one for a single “case identifier” provided through the input parameter, the other for a list of “case identifiers” provided by the table type input parameter.

I was tasked with "improved maintainability" by re-implementing each "single identifier" SP (re) using a "SP list". A rather tedious, but nothing scientific rocket:

CREATE PROCEDURE [dbo].[SingleIdProc]
    @CaseId BIGINT
AS
    BEGIN
        DECLARE @TTIdsList dbo.TTIdsList
        INSERT INTO @TTIdsList(id) 
        VALUES (@CaseId)

        EXEC [dbo].[SingleIdProcByList]
             @CaseIds = @TTIdsList
    END
RETURN 0

It works (correctly!), But now the database administrator complains that it affects performance so much that he had to return all such "fixed" SPs to their previous version ...

So my questions are:

• How much does an indirect call cost?
• How can I measure the difference in performance?

Answer 1

The difference in performance is likely to be caused not EXECas such.

Most likely, the execution plan for a particular identifier is very different from the execution plan for a table parameter. Using a simple bigintparameter, the optimizer can find a better plan.

.

SSMS, / / . . (SQL Server Management Studio).

SQL Sentry Plan Explorer. . , . , .