Bind Variables Using ODP.NET

From: vivek <svivekm_at_gmail.com>
Date: 22 Feb 2006 22:51:05 -0800
Message-ID: <1140677465.135451.213360@t39g2000cwt.googlegroups.com>

Dear All,

   I am Trying to use Bind Variables using ODP.NET and calculate the performance

The Scenario at my end is:

1. Win Form application that creates Sql statements to be executed.
2. Oracle database 10g.
3. Using ODP for Data Access

The connection String
	ConnectionString="User ID=username;Data

Source=dbTest;Password=Password;enlist=false;";

i am executing 3 diffrent simple SQL statements one after another and preparing them only once

the SQL's are

select * from testTbl1 where col1= :param1 and col2 = :param2
select * from testTbl2 where col1= :param1 and col2 = :param2
select * from testTbl2 where col1= :param1 and col2 = :param2

for(int i=0;i<no_OfExecutions;i++)
{

//--New Command
//CreateSQLStatement function create one SQL depend on Value of i

        cmd=new OracleCommand(CreateSQLStatement(i),conn);

	OracleParameter param1 = new OracleParameter();
	param1.OracleDbType = OracleDbType.Varchar2;
	cmd.Parameters.Add(param1);

	OracleParameter param2 = new OracleParameter();
	param2.OracleDbType = OracleDbType.Int32;
	cmd.Parameters.Add(param2);

	if(i<3)
	{
		//preparing only once for each new SQl statement
		cmd.Prepare();
	}

	param1.Value=i.ToString();
	param2.Value=i.ToString();

	Reader OracleDataReader;
	Reader=cmd.ExecuteReader();


	//while loop for reading -- currentlly not fetching data


	Reader.Dispose();

}

When i Calculate the performance of above code and also without using BindVariables,
Its surprisinglly shows using without Bindvariable performce is better

	NO_OfExecutions   ODP withoutBind   ODP With Bind
	10 		  21.88 	    35.95
	60 		  48.45 	    79.72
	100		  98.30	    105.39

I have calculated above test using oledb and NDp in both cases Bind variables are fast.

Kindlly suggest if i am doing something wrong in above code.

Thanks and Regards

Vivek Received on Thu Feb 23 2006 - 00:51:05 CST