Advantage of Parameters

  • Thread starter Thread starter =?iso-8859-1?Q?Norbert_P=FCrringer?=
  • Start date Start date
?

=?iso-8859-1?Q?Norbert_P=FCrringer?=

Hello!

Is there an advantage of using parameter objects instead of using
concatenated command strings?

Example
1) use of parameter object:
OracleCommand cmd = new OracleCommand("SELECT PROJECT FROM USERS.LOGIN WHERE
UPPER(SESSIONID)=:SESSIONID", _con);

cmd .Parameters.Add(new OracleParameter("SESSIONID", OracleType.VarChar,
100)).Direction = ParameterDirection.Input;

cmd .Parameters["SESSIONID"].Value = sessionid.ToUpper();

2) use of concatenated string:

OracleCommand cmd = new OracleCommand("SELECT PROJECT FROM USERS.LOGIN WHERE
UPPER(SESSIONID) = '" + sessionid.ToUpper() + "'", _con);

Will be the second command executed with less performance than the first
command? Or never mind?

Norbert
 
Yes. If you use parameters, you can prepare the SQL statement and
execute the same SQL statement multiple times. This will make your
application run faster.


Charles Zhang
http://www.speedydb.com
(SpeedyDB ADO.NET Provider is the fastest, most secure ADO.NET Provider
over Wide Area Network)
 
¤ Hello!
¤
¤ Is there an advantage of using parameter objects instead of using
¤ concatenated command strings?

Yes, because you don't have to be concerned with special characters, such as single or double
quotes, which will result in a statement syntax error.

In addition, some database servers cache the sql execution plan for performance reasons, and it
can't be done effectively if you're using variable insertion in a SQL string.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
I may be wrong, but I think you would actually get better performance from
the method where you are concatenating the string. The reason I would say
that is because in the other case, you have to create a new reference to a
new Parameter object, and then during the execution of the Command, it has to
try and figure out the value of the parameter Session and build the string
itself. In the end, both methods are calling the same SQL statement, so
there will be no difference in performance at the point where they are
running the statement and/or returning the rows.
 
For databases such as SQL Server and ORACLE parameterizing your queries will
benefit the overall performance of your database esp. in high volume
systems.

Speaking at a high level, the query is passed to the database which builds
an execution plan, this execution plan is cashed using the query as the key.
The next time the query is executed if the query has not changed the same
execution plan can be used assuming the execution plan has not been moved
out of the cache. The problem when concactenating the parameters is that the
query string changes everytime, where with parameterized query the query
string is exactly the same.

For example your parameterized query would always look the same:
"SELECT PROJECT FROM USERS.LOGIN WHERE UPPER(SESSIONID)=:SESSIONID"

While the resulting concatenated query would be different for every session
id, resulting in multiple execution plans being cached for the different
session id's, you can best see the effect by using statspack look at the
number of hard parses performed for non-parameterized queries vs. typically
only 1 hard parse for the parameterized query.

And finally, you should be aware of the possibility of SQL Injection
vulnerabilities you are opening yourself to by using NON-parameterized
queries, I have a post on this topic
http://dotnetjunkies.com/WebLog/chris.taylor/archive/2004/10/13/28370.aspx

Hope this helps
 
I don't know about Oracle, but this is no longer true in SQL Server. All
queries are optimized and cached.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Charles Zhang said:
Yes. If you use parameters, you can prepare the SQL statement and execute
the same SQL statement multiple times. This will make your application run
faster.


Charles Zhang
http://www.speedydb.com
(SpeedyDB ADO.NET Provider is the fastest, most secure ADO.NET Provider
over Wide Area Network)

Hello!

Is there an advantage of using parameter objects instead of using
concatenated command strings?

Example
1) use of parameter object:
OracleCommand cmd = new OracleCommand("SELECT PROJECT FROM USERS.LOGIN
WHERE UPPER(SESSIONID)=:SESSIONID", _con);

cmd .Parameters.Add(new OracleParameter("SESSIONID", OracleType.VarChar,
100)).Direction = ParameterDirection.Input;

cmd .Parameters["SESSIONID"].Value = sessionid.ToUpper();

2) use of concatenated string:

OracleCommand cmd = new OracleCommand("SELECT PROJECT FROM USERS.LOGIN
WHERE UPPER(SESSIONID) = '" + sessionid.ToUpper() + "'", _con);

Will be the second command executed with less performance than the first
command? Or never mind?

Norbert
Click to expand...
Click to expand...
 
Chris sums it up well. Don't worry about the creation of Parameter objects
each time unless you're working in a "built-it-on-the-fly" application like
ASP. Build the Parameter objects ahead of time and manage them as classes.
This way they can be reused as needed. Parameters handle a bevy of
issues--many of which you'll never see until they happen to you one Saturday
afternoon...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Chris Taylor said:
For databases such as SQL Server and ORACLE parameterizing your queries
will benefit the overall performance of your database esp. in high volume
systems.

Speaking at a high level, the query is passed to the database which builds
an execution plan, this execution plan is cashed using the query as the
key. The next time the query is executed if the query has not changed the
same execution plan can be used assuming the execution plan has not been
moved out of the cache. The problem when concactenating the parameters is
that the query string changes everytime, where with parameterized query
the query string is exactly the same.

For example your parameterized query would always look the same:
"SELECT PROJECT FROM USERS.LOGIN WHERE UPPER(SESSIONID)=:SESSIONID"

While the resulting concatenated query would be different for every
session id, resulting in multiple execution plans being cached for the
different session id's, you can best see the effect by using statspack
look at the number of hard parses performed for non-parameterized queries
vs. typically only 1 hard parse for the parameterized query.

And finally, you should be aware of the possibility of SQL Injection
vulnerabilities you are opening yourself to by using NON-parameterized
queries, I have a post on this topic
http://dotnetjunkies.com/WebLog/chris.taylor/archive/2004/10/13/28370.aspx

Hope this helps

--
Chris Taylor
http://dotnetjunkies.com/weblog/chris.taylor


Norbert Pürringer said:
Hello!

Is there an advantage of using parameter objects instead of using
concatenated command strings?

Example
1) use of parameter object:
OracleCommand cmd = new OracleCommand("SELECT PROJECT FROM USERS.LOGIN
WHERE UPPER(SESSIONID)=:SESSIONID", _con);

cmd .Parameters.Add(new OracleParameter("SESSIONID", OracleType.VarChar,
100)).Direction = ParameterDirection.Input;

cmd .Parameters["SESSIONID"].Value = sessionid.ToUpper();

2) use of concatenated string:

OracleCommand cmd = new OracleCommand("SELECT PROJECT FROM USERS.LOGIN
WHERE UPPER(SESSIONID) = '" + sessionid.ToUpper() + "'", _con);

Will be the second command executed with less performance than the first
command? Or never mind?

Norbert
Click to expand...
Click to expand...
 
I may be wrong, but I think you would actually get better performance from
the method where you are concatenating the string. The reason I would say
that is because in the other case, you have to create a new reference to a
new Parameter object, and then during the execution of the Command, it has to
try and figure out the value of the parameter Session and build the string
itself. In the end, both methods are calling the same SQL statement, so
there will be no difference in performance at the point where they are
running the statement and/or returning the rows.

Norbert Pürringer said:
Hello!

Is there an advantage of using parameter objects instead of using
concatenated command strings?

Example
1) use of parameter object:
OracleCommand cmd = new OracleCommand("SELECT PROJECT FROM USERS.LOGIN WHERE
UPPER(SESSIONID)=:SESSIONID", _con);

cmd .Parameters.Add(new OracleParameter("SESSIONID", OracleType.VarChar,
100)).Direction = ParameterDirection.Input;

cmd .Parameters["SESSIONID"].Value = sessionid.ToUpper();

2) use of concatenated string:

OracleCommand cmd = new OracleCommand("SELECT PROJECT FROM USERS.LOGIN WHERE
UPPER(SESSIONID) = '" + sessionid.ToUpper() + "'", _con);

Will be the second command executed with less performance than the first
command? Or never mind?

Norbert
Click to expand...
Click to expand...

I'm not sure about that ... I don;t see why you need to create another
parameter object after you have initially created it. All you need to do in
subsequent calls is change the *value* of the original parameter.

Concatenation also have a number of other problems
- You need to do extra work for values that contain characters like
apostrophes e.g. o'brien
- You'll just be asking for SQL injection attacks
 
Back
Top