Select in SP slower than non-SP ?

[Saxet]

I have this SQL Server 2012 trivial stored procedure:

CREATE PROCEDURE usp_SelectCompany
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM Company
END

and in a SSMS window, I execute this commands below to discover my SP is much slower than directly executing SELECT * from Company, even after the SP is run once to compile it.

-- create temp table so SSMS results output doesn't interfere too much with with query execution time
SELECT * INTO #Company FROM Company WHERE 1=0

-- run the SP once to ensure that is is compiled
INSERT #Company EXEC usp_SelectCompany

PRINT 'Start Time: ' + cast(sysdatetime() as varchar)
INSERT #Company EXEC usp_SelectCompany
PRINT 'SP Time: ' + cast(sysdatetime() as varchar)
INSERT #Company SELECT * FROM Company
PRINT 'Select Time: ' + cast(sysdatetime() as varchar)

Here is the output:

(0 row(s) affected)

(127733 row(s) affected)
Start Time: 2013-05-22 11:56:57.7208741

(127733 row(s) affected)
SP Time: 2013-05-22 11:57:03.2067327

(127733 row(s) affected)
Select Time: 2013-05-22 11:57:04.0015524

I note that the SP executes in 5.4858586 seconds, and the direct "select" statement only takes 0.7948197 seconds, or about 7 times slower. Can anyone explain this behavior?

[Lost User]

Saxet wrote:

Can anyone explain this behavior?

I can't, but a wild guess would be the optimizer (select and insert vs. insert and exec some proc). What's the timing when you move the insert-statement inside the sproc?

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

[Saxet]

It's better (1.0295934 seconds), but still slower than the select in the SSMS window.

Start Time: 2013-05-22 12:51:03.6580584
End Time: 2013-05-22 12:51:04.6876518

[Lost User]

Saxet wrote:

It's better (1.0295934 seconds), but still slower than the select in the SSMS window.

The server has to do some things that can be skipped when being fed the statement directly, like looking up the access-rights to the sproc. So yes, compared to a simple query, it'd be a (small) bit slower.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

[PIEBALDconsult]

I'm not entirely surprised, but the difference is probably not accurate. What you might try is simply using ExecuteReader from code on each and see how long it takes to do -- without reading the data. I also wonder whether it's a local database or remote.

[Bernhard Hiller]

SQL Server can cache execution plans and results.

[Saxet]

ExecuteReader performs about the same with either a select statement or a stored procedure. It appears to me that there is some penalty (lack of optimization?) when inserting the output from a stored procedure to a table that isn't present when inserting the output of a select statement. SSMS produces a different "Actual Execution Plan" for the two, but I'm not skilled enough to understand what the differences mean. The execution plan breaks up the stored procedure into two queries, where the select statement is just one.

[PIEBALDconsult]

Right. About what I'd expect.