How to use bind variables effectively for Sql performance?

[Sunil Menon]

Dear All,
We are evaluating a few techniques to optimise our Sql statements
being executed at the database.
Scenario:
1. We have a WinForm application that creates Sql statements to be
executed.
2. We have an Oracle database 10g.
3. We plan to use either .net provided provider (NDP) or use ODP.
4. Currently all our Sql statements gets parsed every time at the
database. (Found out by using SqlTrace).
5. We have optimised our Sqls and the only point left to tackle is
parsing.

In our case study we have observed the following:
1. Even if we use bind variables and execute them without preparing
(CommandObject.Prepare) it parses always.
2. If we use bind variables and prepare every time it parses always.
3. If we use bind variables and prepare only once but always use a new
Command object it parses only once. (Not confirmed as we are not able
to take SqlTrace) but performance is fast.

Question:
1. How do we effectively use Bind variables? Do I need to keep track of
every Sql that has been prepared so that I do not prepare it again?
2. Using bind variables using ODP is slower than using bind variables
in NDP. Is there some optimisation string to be put in ConnectionString
missing?
3. Is using Stored procedure better than using bind variables? Does
this mean that we need to create a SP for every Sql statement? or can
we have a generic SP that takes variable parameters. What is the
industry standard followed?
4. I am not able to trace my Sqls if I prepare the Sql statement only
once and use a new Command Object. The application hangs. In the
connection string I have put enlist = false, whereby it stopped giving
gpf (ORA-00603, ORA-00604).

Kindly advice.

Thanks and regards
Sunil

 

[Cor Ligthert [MVP]]

Sunil,

Be aware that your message subject can be confusing. In this newsgroup SQL
can be a shortcut for SQL server. While your message is complete about
Oracle, where Binding has a different meaning than in ADONET.

Just to inform you and others before they start reading like me.

Cor

 

[Paul Clement]

¤ Sunil,
¤
¤ Be aware that your message subject can be confusing. In this newsgroup SQL
¤ can be a shortcut for SQL server. While your message is complete about
¤ Oracle, where Binding has a different meaning than in ADONET.
¤

No Cor it's the same. However, using "bind variables" is not the same as data binding. The
implementation of bind variables involves the use of the question mark placeholders in SQL
statements supporting parameters. If you use this syntax in Oracle, the SQL statements can be cached
for subsequent use. Not sure if SQL Server supports this type of caching mechanism, but it's
preferable to using SQL statements with parameters that are coded using variable substitution.

In any event, you will still get better performance using a parameterized stored procedure instead
of a SQL statement that implements bind variables.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Cor Ligthert [MVP]]

Paul,

I knew that it was different, however not what the term did mean and
therefore is as well in OleDB.

So thanks for your explanation, I will not forget it.

However I tried to explain that it could confuse.

Cor

 

[Sunil Menon]

Thanks Paul, Cor
your replies have put in some light to my questions...but some of my
questions are still unanswered...please help....
Question:
1. How do we effectively use Bind variables? Do I need to keep track of

every Sql that has been prepared so that I do not prepare it again?
2. Using bind variables using ODP is slower than using bind variables
in NDP. Is there some optimisation string to be put in ConnectionString

missing?
3. Is using Stored procedure better than using bind variables? Does
this mean that we need to create a SP for every Sql statement? or can
we have a generic SP that takes variable parameters. What is the
industry standard followed?
4. I am not able to trace my Sqls if I prepare the Sql statement only
once and use a new Command Object. The application hangs. In the
connection string I have put enlist = false, whereby it stopped giving
gpf (ORA-00603, ORA-00604).


Thanks & regards
Sunil

 

[Cor Ligthert [MVP]]

Sunil.

Place a new message, that was the meaning from my reply.

And place somewhere in the subject "Oracle", there are people in active in
this newsgroup who especially often answers those questions.

Cor

 

[Paul Clement]

¤ Thanks Paul, Cor
¤ your replies have put in some light to my questions...but some of my
¤ questions are still unanswered...please help....
¤ Question:
¤ 1. How do we effectively use Bind variables? Do I need to keep track of
¤ every Sql that has been prepared so that I do not prepare it again?

My understanding is that Oracle handles this automatically upon execution. Reuse of the execution
plan occurs when you submit a query, using bind variables, that is identical to one that is
currently in the cache (shared memory pool area).

¤ 2. Using bind variables using ODP is slower than using bind variables
¤ in NDP. Is there some optimisation string to be put in ConnectionString
¤
¤ missing?

If NDP is Microsoft's .NET provider for Oracle, I'm afraid I can't explain why it's faster than
Oracle's. You may need to submit a question to Oracle concerning the performance issue.

In addition, you may want to post a small bind variable example that you are using.

¤ 3. Is using Stored procedure better than using bind variables? Does
¤ this mean that we need to create a SP for every Sql statement? or can
¤ we have a generic SP that takes variable parameters. What is the
¤ industry standard followed?

Stored procedures are typically more efficient and parameters are easier to work with than bind
variables. I generally create a stored procedure for each SQL statement I require. I'm not aware of
any industry standard with respect to the use of stored procs. You may want to check out some books
on Oracle PL/SQL or check the Oracle MetaLink.

¤ 4. I am not able to trace my Sqls if I prepare the Sql statement only
¤ once and use a new Command Object. The application hangs. In the
¤ connection string I have put enlist = false, whereby it stopped giving
¤ gpf (ORA-00603, ORA-00604).

I don't think using Prepare is really going to help here as Oracle should be storing and reusing the
execution plans automatically.


Paul
~~~~
Microsoft MVP (Visual Basic)