Performance implication of Parameters

[greg]

Hi.

I was recently talking to a colleague who uses SQL Server as a backend to
his applications. He mentioned that adding parameters causes SQL Server to
determine the datatype and can cause performance issues, unless you
explicitly specify the datatype.

Does this also apply to Oracle, and if so, how can I specify it? Right now I
am doing the following:

int a = 4;
String b = "test";
DateTime today = System.DateTime.Today;

String sql = "insert into something (a, b, c) values a, :b, :c)";
OracleCommand aCommand = ... using above sql ...

aCommand.Parameters.Add("a", a);
aCommand.Parameters.Add("b", b);
aCommand.Parameters.Add("c", c);

Is there another way I could be doing this that would speed things up?

 

[William Vaughn]

In my experience (echoed by any number of high-performance customer
systems), it's not how fast you ask the question that results in a speedy
answer.
I would focus more on database design, appropriate query plans, index tuning
and understanding how the DBMS works and less on the niddly piddly details
of how a SP is called.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[greg]

Thanks. I would agree, it's just easier to plan these things out from the
start rather then after the fact

 

[Michael O]

Does this also apply to Oracle, and if so, how can I specify it? Right now I
am doing the following:

In Oracle you can not define stored procedures without a datatype. In
ADO.NET and Oracle providers you cannot avoid specifying a parameter
type enumeration when adding a parameter. If there is an overload to
add a parameter without specifying a parameter type enumeration, you
can rest assured that the documentation for that provider and method
says there's a default enumeration used, probably VarChar/Varchar2
(depending on your provider).

Although you should strive to add your parameters with the appropriate
ADO.NET parameter type, you can achieve non-deterministic successful
results with the wrong parameter type because implicit datatype
conversions may occur.

For example, if your stored procedure PROCEDURE whatever (NUMBER_PARAM
IN NUMBER)... was defined and you used and you added a parameter
aCommand.Parameters("NUMBER_PARAM", aNumberVariable)

What I'm fairly confident would happen is that under the ADO.NET
covers, the parameter type enumeration would be VarChar (or VarChar2
if using ODP.NET) and the second parameter would be cast as a string
object and passed along to the RDMBS as string. The stored procedure
would then implicitly cast it back to a number and probably execute
without a problem assuming its value was within the database
datatype's domain of acceptable range. Almost every .NET type can be
cast to a string in some way and the RDBMS can very possibly succeed
with implicitly converting a string to most of it's data types.
Nonetheless this implicitness is not optimally performing nor
deterministic in its nature.

So maybe your friend was saying that it was best to be explicit in
your practices - whereas you could avoid the double implicit (once
in .NET and once in the RDBMS) datatype casting I've tried to
illuminate happening above.

Hope that helps,

Michael O
http://blog.crisatunity.com

 

[WenYuan Wang [MSFT]]

Hello thefish,

It seems Willam and Michael have given you much helpful suggestion/idea.
Do you still have more concern so far? I just want to check if there is
anything we can help with.
Please feel free to update here again. We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.