This discussion is archived
0 Replies Latest reply: Sep 7, 2012 2:39 AM by 936666 RSS

ODBC Performance is less in Oracle

936666 Newbie
Currently Being Moderated
Hi,
I am using oracle database and using C#.

ODBC CODE with Oracle database:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.IO;

public void Open()
{
connection = new OdbcConnection(connectionString);
command = connection.CreateCommand();
connection.Open();
Console.WriteLine("Connnection Open");
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.IO;

public override void Insert()
{
base.Insert();

command.CommandText = "Select to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF')from dual";
Object oStartTime = command.ExecuteScalar();
StartTime = DateTime.Parse(oStartTime.ToString());/*Getting the StartTime*/
Console.WriteLine(StartTime.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));

command.CommandText = "Insert into tablename (Col1, Col2, ...) Values(?, ?,...)"
command.Prepare();

for (int i = 0; i < count; i++)
{
command.Parameters.AddWithValue("?", 0);
command.Parameters.AddWithValue("?", 0);
.
.
.
command.ExecuteNonQuery();
command.Parameters.Clear();

//connection.commit();

}
}

command.CommandText = "Select to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF')from dual";
Object oStartTime = command.ExecuteScalar();
StartTime = DateTime.Parse(oStartTime.ToString());/*Getting the EndTime*/
Console.WriteLine(StartTime.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));

The above code connects to my Oracle database and it populates the data,but the data which is populated is very slow when the same set of query is executed in SQL server with appropriate code change to SQL server but the concept of insertion is same.The time taken for SQL server with 10k records it takes around 5min to insert in SQL server,where it takes 8min in Oracle.What is the reason for change in time, i have used ODBC Connection for both the database.

What needs to be done here.

Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points