8 Replies Latest reply: Nov 1, 2012 10:19 PM by 936666 RSS

    Difference in Oracle TimesTen in with connections

    936666
      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
          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
            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
              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
                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
                  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
                    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
                      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