473,320 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

sp_executesql vs. stored proc.

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.

Jul 23 '05 #1
3 3415
LineVoltageHalogen (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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

Erland Sommarskog wrote:
LineVoltageHalogen (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****@sommarskog.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

Jul 23 '05 #3
LineVoltageHalogen (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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: cliverama | last post by:
help! fried brains.... asp calling a sqlserver7 stored proc which dynamically builds a sqlstatement & passes it to sp_executesql asp page gives the operation not allowed when object is closed...
3
by: Clausmeyer | last post by:
I want to execute a dynamically generated sql-statement from inside an user-defined-function. Calling functions and extended stored-procs is allowed so I tried sp_executesql as well as...
3
by: thomson | last post by:
Hi all, Can sp_executesql used inside a user defined function, i tried but it has compiled well, but when i call the functio it shows Only functions and extended stored procedures can be executed...
1
by: LineVoltageHalogen | last post by:
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...
7
by: LineVoltageHalogen | last post by:
Greetings All, I have a very large query that uses dynamic sql. The sql is very large and it requires it to be broken into three components to avoid the nvarchar(4000) issue: SET @v_SqlString(...
2
by: Highlander416 | last post by:
This is driving me crazy. I need to create a UDF that would return a TRUE/FALSE (bit) value based on a comparison it does. CREATE FUNCTION dbo.SelectedByApplication ( @ApplicationID int,...
0
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE...
1
by: satishchandrat | last post by:
Hi, This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure. I have my SQL string exeeding more than 4000...
0
by: mirandacascade | last post by:
Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.