This discussion is archived
8 Replies Latest reply: Nov 1, 2012 8:19 PM by 936666 RSS

Difference in Oracle TimesTen in with connections

936666 Newbie
Currently Being Moderated
Hi,
I have tried inserting the data to TimesTen with Java(JDBC) and C#(ODBC and ODP).
For all my connections i have used Preparestatement to check the data constancy

Connection Time
JDBC 15 Sec
ODBC 63 Sec
ODP 45 Sec

Why there is huge difference in this.

Thanks!
  • 1. Re: Difference in Oracle TimesTen in with connections
    ChrisJenkins Guru
    Currently Being Moderated
    What O/S are you running on? I'm guessing Windows...

    Are the application and TimesTen on the same machine?

    Are you using client/server or direct mode connections?

    For ODBC, are you linking directly with the TT driver or using the Windows driver manager?

    If everything is configured properly then it is inconceivable that C/ODBC would be slower than Java or ODP .NET. It is possible that ODP .NET could be slower than Java (there are so many software layers with ODP .NET) but again the difference seems larger than expected.

    Assuming proper configuration, use of direct mode and with the C program linked directly with the TT driver if these numbers still look the same then the answer has to be problems with your application code...

    Chris
  • 2. Re: Difference in Oracle TimesTen in with connections
    936666 Newbie
    Currently Being Moderated
    What O/S are you running on? I'm guessing Windows...
    Yes
    Are the application and TimesTen on the same machine?
    Yes
    Are you using client/server or direct mode connections?
    client/server
    For ODBC, are you linking directly with the TT driver or using the Windows driver manager?
    For all the connection i am using DSN

    have used only Preparestatement across all the connections

    Thanks!
  • 3. Re: Difference in Oracle TimesTen in with connections
    ChrisJenkins Guru
    Currently Being Moderated
    For ODBC, are you linking directly with the TT driver or using the Windows driver manager?*

    For all the connection i am using DSN*

    Yes, but that is not what I asked... When you compile and link your C/ODNC application, do you link with ODBC32.LIB or TTDV1122.LIB?

    Also, what exactly are you timing in your code? Maybe you can share the C/ODBC code?

    Chris
  • 4. Re: Difference in Oracle TimesTen in with connections
    936666 Newbie
    Currently Being Moderated
    How to check i am using

    ODBC32.LIB or TTDV1122.LIB

    I have already posted my code to you via this forum,again i'm posting here

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using Oracle.DataAccess.Client;
    using System.IO;

    private OracleConnection connection;
    private OracleCommand command;

    connectionString = "Data Source=MYDatabase;User ID=xxxxxx;Password=xxxxxxxxx";
    command = connection.CreateCommand();
    command.CommandText = "Create Table DefectTest (Col1     Char(1),Col2     Char(1))";
    command.ExecuteNonQuery();

    ----start time calculation

    for (int i = 0; i < count; i++)
    {
    command2.CommandText = "Insert into Defecttest (Col1, Col2) values ('A','B')";
    command.ExecuteNonQuery();
    }

    ----end time calculation

    Here in this example i have used only 2 columns with char data type where as for my purpose i have some 200+ columns where it contains multiple combination of data types

    Thanks!
  • 5. Re: Difference in Oracle TimesTen in with connections
    ChrisJenkins Guru
    Currently Being Moderated
    That's not C/ODBC code; that's ODP code (or some other .NET type thing). If you don;t know how your linking then you will be using the drievr manager, which will add overhead. Much more significantly though, this code is not using prepared statements and so each time around the loop it is parsing the INSERT statement. The first parse will be a full parse (extremely expense compare to the cost of executing the INSERT) and subsequent parses will be statement cache hist which are merey very expensive compared to the cost of executing the INSERT.

    I don't know how you would code it in this ODP like thing you are using but to perform well (with any database) your code should look something like this (this for sure is not something that will compile 'as is' but is just illustrating how it should be):

    command2.Commandtext = "Insert into Defecttest (Col1, Col2) values (?,?)"

    ----start time calculation

    for (int i = 0; i < count; i++)
    {
    command2.setParam(1, 'A');
    command2.setParam(2, 'B');
    command.ExecutePreparedNonQuery();
    }

    ----end time calculation

    Hopefully you get the picture... The C/ODBC and Java/JDBC code should look similar. I suspect that the Java code is already pretty much like this which is why it is so fast. If you make a real C/ODBC test program and do this then it will be a lot faster than the Java code.

    Chris
  • 6. Re: Difference in Oracle TimesTen in with connections
    936666 Newbie
    Currently Being Moderated
    I have used C# and .net and i am not working with C/ODBC since my code deals with .net related work i need to test the data with .net stuffs
    If you don;t know how your linking then you will be using the drievr manager, which will add overhead.
    how to specify for .net code to use direct linking i have checked the program of C
    CFLAGS = "/Iinstall_dir\"
    LIBSDM = ODBC32.LIB
    LIBS = tten1122.lib ttdv1122.lib (refereed below link)
    http://docs.oracle.com/cd/E11882_01/timesten.112/e21637/compiling.htm
    I don't know how you would code it in this ODP like thing you are using but to perform well (with any database) your code should look something like this (this for sure is not something that will compile 'as is' but is just illustrating how it should be):
    As i stated earlier i have sent you only the same code but in my real code it will be some thing like this

    for (int i = 0; i < count; i++)
    {
    command2.Parameters.Add(":param20", OracleDbType.Int32, 127, ParameterDirection.Input);
    command2.Parameters.Add(":param36", OracleDbType.Double, 9223372036854770000, ParameterDirection.Input);
    command.ExecutePreparedNonQuery();
    command2.Parameters.Clear();
    }
    any hows thanks for that point on specifying the slowness of performance.

    I don't deal with c/odbc since my project deals with some basic java code and rest with .net

    Thanks!
  • 7. Re: Difference in Oracle TimesTen in with connections
    ChrisJenkins Guru
    Currently Being Moderated
    Well I;m confused by your last post. This thread started off like this:

    Hi,*
    I have tried inserting the data to TimesTen with Java(JDBC) and C#(ODBC and ODP).*
    For all my connections i have used Preparestatement to check the data constancy*

    Connection Time*
    JDBC 15 Sec*
    ODBC 63 Sec*
    ODP 45 Sec*

    Why there is huge difference in this.*

    Thanks!*

    And from this point I have been trying to help you understand why your C/ODBC version of the code is running so slowly where in fact it should be the fastest. Now you say you do not work with C/ODBC. Please help me understand...

    Is your question really why ODP .NET code is so much slower than Java code? Well, I think you might need to ask Microsoft about that... I'm afraid that without seeing the whole ODP .NET code and whole Java code it is going to be very hard to answer. Maybe in the ODP .NET code you are constantly opening and closing database connections? Maybe the use of prepared statements is not correct? I really don't think this is a Timesten issue but is almost certainly a coding / API usage issue but withoiut seeing all of the 8real* code (not some very different example code) it is impossible to say.

    What I can say is that on a typical modern PC for this kind of (prepared) INSERT a C/ODBC program should be able to execute maybe 40,000 inserts per second in direct mode while the equivalent Java/JDBC program might manage 37,000 per second. I have no idea what the equivalent performance would be for ODP .NET I'm afraid.

    Chris

Legend

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