Parameter markers

Question

Parameter markers

Carl B.

SSCertifiable

Points: 7931
More actions
February 18, 2004 at 2:50 pm

#65787

Hi everyone,
We are in the process of re-writing the database acces layer of our applications with ADO.NET.
I looked in BOL but there is not much docs on the use of parameter markers (pros and cons).
Will the use of prameter marker help increase performance on the server side?
If we have a statement like this one:
Select * from my_db.dbo.cust where cust_id = ?
and if I successively execute the same query with multiple different cust_id will it be more efficient using a parameter marker or there is not much difference than issuing several distinct select statement differentiated only by the value for the cust_id at execution time?
Having generic statement like the one with the parameter marker can considerably reduce the number of distinct statements in the cache of the server making them less often aged out of the memory.
All input on that will be welcomed.
Best regards,
Carl

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

TriggerMe SSChampion Points: 11752 More actions · Answer 1

Parameter Marker? You mean Input Parameters to a Stored Procedure!

What are you trying to achieve here? Input Parameters to a Stored Procedure is a different kettle of fish compared to performing SELECT DISTINCT!

?????

Kindest Regards,

Carl B. SSCertifiable Points: 7931 More actions · Answer 2

Parameter markers are not limited to be used only with stored procs.

Here is an extract of what can be found in BOL about parameter markers:

"

Parameter markers are not limited to being mapped to stored procedure parameters. They can be used anywhere an input expression is used:

UPDATE Employees

SET Title = ?

WHERE EmployeeID = ?

"

What I try to achieve: I work both with Oracle and SQL Server. On Oracle you can't think of a scallable application without the use of what is called bind variables. Bind variable, in short, is the use of "place holders" in "generic" SQL Statements that are similar except for the values that are bind at execution time. The optimizer parse the statement and choose an execution plan without the actual values. Here is an example:

Select * from cust where custId = :1

The ":1" is a bind variable.

So you can execute the statement:

Select * from cust where custId = 'BOB'

And this statement:

Select * from cust where custId = 'Judy'

With the same execution plan that was determined with this "generic" statement:

Select * from cust where custId = :1

I want to know if we can achieve the same kind of performance benefit using parameter makers with SQL Server that we can with the bind variables with Oracle?

This what I want to achieve. Hope that is clear.

Carl

Carl B. SSCertifiable Points: 7931 More actions · Answer 3

I will try to use more closely related SQL Server terms:

Is it better to use (more efficient):

sp_executesql N'SELECT * FROM cust WHERE custId = "BOB"'

Or is it better to use:

sp_executesql N'SELECT * FROM cust WHERE custId = @id', N'@id varchar(6)', 'BOB'

Especially If I know that in a few minutes I will still execute the same select statement with many different values for the cusId column??

Or does it make no difference at all? Why?

Best regards,

Carl

David Burrows SSC Guru Points: 65143 More actions · Answer 4

Well the results are the same, the execution plan will be the same, so no difference. However the use of sp_executsql may be an issue. Any sql query has to be parsed, checked and a execution plan created before it is run. Send a query direct to sql, via sp_executesql or not, will involve this process although sql will cache plans there is no guarantee is will be there in the next call.

In cases like this I would create a stored procedure to do the select (with column names, not *) and pass the id to the proc. This is the most efficient method as the query has already been parsed, checked and a plan stored.

Far away is close at hand in the images of elsewhere.
Anon.

Carl B. SSCertifiable Points: 7931 More actions · Answer 5

I am aggree with you nothing guarantee that the plan will be there in the next call.

But i am not agree with you that for every sql query a plan have to be created. If a plan that math is still there it will be re-used as stated in BOL "auto-parameterization".

Thank's for your reply David.

Carl

David Burrows SSC Guru Points: 65143 More actions · Answer 6

Yes, true but also check out 'Execution Plan Caching and Reuse' in BOL. Plan reuse and aging is dependant on several factors.

Far away is close at hand in the images of elsewhere.
Anon.

Carl B. SSCertifiable Points: 7931 More actions · Answer 7

Thank's for the info David, I will take a look at that.

Have a nice day,

Carl

Calvin Lawson SSChampion Points: 11030 More actions · Answer 8

Yeah, they covered this exact issue at PASS. Using parameters instead of building a string was significantly faster, specifically due to execution plan re-use.

Another bit of advice: When doing a loop, start your transaction before the loop, execute the loop, then end your transaction. It's incredible how much this speeds things up, although you may need to get more creative if you're inserts are going to be causing table blocking (1000 at a time or something).

Signature is NULL