Using Oracle Bind Variables in Asp.net code = Performance + less memory Overhead

BIND Variables
Concept of Bind Variables often came across when we start developing .Net ( or any application ) with Oracle as database , mainly we can say its oracle concept for high performance.

To Understand the Concept of Bind Variables , we first need to understand how the Oracle is executing the Queries passed to it.All Queries passed to Oracle are passed to Shared Pool for creation of Execution plan we will take look at it in brief.Oracle Shared Pool contains Library Cache which contains Shared SQL Area works as buffer for SQL statements ,responsible for Collection,parsing,interpreting and execution of the SQL Statements passed to Oracle Database.Parsing Algorithm in this area works in such a ways that identical SQL statements dont getting parsed each time , they are getting parsed once, first time and next time execution plan is ready for them irrespective of users who initiate the query.In simple words Oracle is using the concept of the reusability.
Oracle uses a algorithm to assign a hash value to each SQL statement based on the characters in the SQL statement. Any change in a statement will need to created new HASH value and thus Oracle assumes it is a new statement. Each new statement must be verified, parsed,interpreted and have an execution plan generated and stored, all causes extra memory overhead, and if we are using the bind variable then actual query remains the same each time we gave call and Execution plan ready already so quick execution.

Where to Use Bind Variables : –
We can use Bind Variables in cases where we are generating the query depending up on the user inputs such as dropdownlist ,listbox selection ,logged in user name,value from Calendar control , Value entered in Textbox.Let me explain you.In the above examples when we are creating the query then only user input / logged in user getting changed but rest of the query remains the same so here we can use the bind variables using which we can avoid the query to get treated as new statement followed by the Parsing and hash generation.

eg. SELECT * FROM Tb_TestTable Where UserId = ‘ABC’ AND ValueByUser = ‘UserValue’
For the above statement for each user and for each selected value , Oracle created the seperate statment in Shared pool even for the same user but different selected values, which is also override to memory,we can use Bind variables here

SELECT * FROM Tb_TestTable Where UserId = :UserID AND ValueByUser = :UserValue

where UserID & UserValue are bindvariables begins with a single colon (“:”); this is how a bind variable is represented in a SQL statement.

How to Use Bind Variables in Asp.net We can use Bind Variables using ODP.NET and also Microsoft Application Block (Data Access block,using OracleHelper) To implement Bind Variables in Asp.net we can user Either OraCleHelper/OracleCommand class of Appliication blocks provided by Microsoft or using ODP.Net provided by Oracle

I am here going to use OracleHelper Class for my Sample Code for that we need use/reference two namespacesMicrosoft.ApplicationBlocks.Data & System.Data.OracleClient; Lets start with normal query where we need to apply bind variables here it is the case that we are having two dropdownlist on page and we are generating the query for filling the other Datacontrols like Repeater/Gridview and filling the datacontrol on index changed.

OracleConnection conn = new OracleConnection(Settings.ConnectionString);

conn.Open();

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;
string SqlQry = “SELECT * FROM TB_TABLE WHERE VALUE1 = “ValueFromFirstDropDownList” AND VALUE2 = “ValueFromSecondDropDownList”
cmd.CommandText = SqlQry;

DataSet ds = new DataSet();

OracleDataAdapter da = new OracleDataAdapter(cmd);

da.Fill(ds);
when each time the new values are selected in the dropdownlist Datacontrol needs to filled but when we selected the new values then query gets changed and each time Oracle treats it as new query although only Value1,value2 are changed so each time what oracle do is it goes through all the procedures like parsing,interpreting,Hash value creation.
we can avoid this by using Bind Variables here, so each time when we change the selected values then it will be get the same query the Shared Pool and the time & memeory override can be saved.
OracleConnection conn = new OracleConnection(Settings.ConnectionString);

conn.Open();

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;

cmd.Parameters.Add(new OracleParameter(“SelectedVALUE1”, dropdownlist1.SelectedValue));

cmd.Parameters.Add(new OracleParameter(“SelectedVALUE2”, dropdownlist2.SelectedValue));
string SqlQry = “SELECT * FROM TB_TABLE WHERE VALUE1 = :SelectedVALUE1 AND VALUE2 = :SelectedVALUE2”;
cmd.CommandText = SqlQry;

DataSet ds = new DataSet();

OracleDataAdapter da = new OracleDataAdapter(cmd);

da.Fill(ds);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s