sp_executesql vs. stored proc.

[LineVoltageHalogen]

Greetings All, currentley there is a heated discussion in my place of
work over which method is better/more efficient for simple selects.

Background:
1.) Simple Application that uses sql server for backened db.
2.) The application is only inserting and selecting data from the db.
3.) The developers want to use sp_executesql for simple selects and
the dba's want to use a stored proc.

From my reading it seems that sp_executesql has a bit of overhead with

it and it is not as efficient as stored procs.

I would appreciate anyone's input on which would be better for simple
repetitive inserts to the db: Stored Proc, or sp_executesql?

Regards, TFD.

[Erland Sommarskog]

LineVoltageHalo gen (tr************ ****@yahoo.com) writes:

Greetings All, currentley there is a heated discussion in my place of
work over which method is better/more efficient for simple selects.

Background:
1.) Simple Application that uses sql server for backened db.
2.) The application is only inserting and selecting data from the db.
3.) The developers want to use sp_executesql for simple selects and
the dba's want to use a stored proc.

From my reading it seems that sp_executesql has a bit of overhead with

it and it is not as efficient as stored procs.

I would appreciate anyone's input on which would be better for simple
repetitive inserts to the db: Stored Proc, or sp_executesql?

From a performance perspective, there is not that extreme difference. If
you use sp_executesql, the plan parameterized query will be cached and
reused, just like the query plan for a stored procedure. But the it does
require that the SQL statement is the same. These three will give different
entries in the cache:

SELECT * FROM tbl WHERE col = @par
select * from tbl where col = @par
SELECT * FROM tbl WHERE col = @par

The cache is both space- and case-sensitive.

There is also a certain overhead for looking up the cached query in the
plan, but normally it is neglible.

But there is also the security aspect of things. Stored procedures permits
you to revoke direct access to the tables, and only provide controlled
access through stored procedures.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[LineVoltageHalogen]

Erland Sommarskog wrote:

LineVoltageHalo gen (tr************ ****@yahoo.com) writes:

Greetings All, currentley there is a heated discussion in my place of work over which method is better/more efficient for simple selects.

Background:
1.) Simple Application that uses sql server for backened db.
2.) The application is only inserting and selecting data from the db. 3.) The developers want to use sp_executesql for simple selects and
the dba's want to use a stored proc.

From my reading it seems that sp_executesql has a bit of overhead

with it and it is not as efficient as stored procs.

I would appreciate anyone's input on which would be better for simple repetitive inserts to the db: Stored Proc, or sp_executesql?
From a performance perspective, there is not that extreme difference.

If you use sp_executesql, the plan parameterized query will be cached and reused, just like the query plan for a stored procedure. But the it does require that the SQL statement is the same. These three will give different entries in the cache:

SELECT * FROM tbl WHERE col = @par
select * from tbl where col = @par
SELECT * FROM tbl WHERE col = @par

The cache is both space- and case-sensitive.

There is also a certain overhead for looking up the cached query in the plan, but normally it is neglible.

But there is also the security aspect of things. Stored procedures permits you to revoke direct access to the tables, and only provide controlled access through stored procedures.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

You say it is neglible. However, what if this process runs 50 - 100
thousand times during each load process? EAch of those small neglible
pieces will add up to something measurable?

L

[Erland Sommarskog]

LineVoltageHalo gen (tr************ ****@yahoo.com) writes:

You say it is neglible. However, what if this process runs 50 - 100
thousand times during each load process? EAch of those small neglible
pieces will add up to something measurable?

If you really want to know, you would have to benchmark. But say that
you have one single statement in the cache of the type:

INSERT dbo.tbl(col1, col2, ...) VALUES (@par1, @par2, ...)

Then the overhead compared to find the stored procedure load_one_row is the
longer time it takes to compute the hash bucket to find it the cache. I
would guess that is miniscule. Note, though, the dbo part. That may be
important.

And, assume that you have a case-insensitive database, and your beloved
programmer has spelt the procedure Load_one_row. Now you will first get a
cache miss, even if you prefix with dbo, because the cache is case
sensitive. After a lookup in the catalog you will eventually hit the cache
anyway.)

Also stored procedures should be prefixed with dbo. when you call them.
(Provided that they are owned by dbo, that is!)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp