This discussion is archived
11 Replies Latest reply: Sep 7, 2012 12:45 AM by ChrisJenkins RSS

ODBC Performance is less in TimesTen

936666 Newbie
Currently Being Moderated
Hi,
I am using TimesTen database i have populated some set of data in JDBC Connection and in ODBC Connection ,the same set of code i have executed in both in JDBC connection for 100k records it takes around 11sec to populate the data, where as ODBC connection for 100k records it takes around 70sec to populate the data,why there is a huge difference in JDBC and ODBC

Thanks
  • 1. Re: ODBC Performance is less in TimesTen
    ChrisJenkins Guru
    Currently Being Moderated
    It's almost certainly your code. Maybe your ODBC code is not as efficient as your JDBC code. Perhaps you can share the code and then we could comment further. ODBC is the TimesTen native APi and so all other factors being the same C/ODBC is always significantly faster than Java/JDBC provided the code is written optimally.

    Chris
  • 2. Re: ODBC Performance is less in TimesTen
    936666 Newbie
    Currently Being Moderated
    Hi
    Here is the Code for both ODBC and JDBC ,

    ODBC CODE:_
    connectionString = "Dsn=myDatabase";

    command.CommandText = "Select to_char(TT_sysdate, '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"));

    for (int i = 0; i < count; i++)
    {
    command.CommandText = "Insert into tablename (Col1,     Col2,     ...) Values(?,     ?,...)"
    command.Prepare();
    command.Parameters.AddWithValue("?", 0);
    command.Parameters.AddWithValue("?", 0);
    .
    .
    .
    command.ExecuteNonQuery();
    command.Parameters.Clear();
    }

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

    The Same set of code has been executed in JDBC where i am able to insert faster

    JDBC CODE:_

    Connection Conn = DriverManager.getConnection("jdbc:timesten:dsn=Mydatabase");

         ResultSet RS2 = Stmt.executeQuery("Select to_char(TT_sysdate, 'YYYY-MM-DD HH24:MI:SS.FF')from dual");
    if (RS2.next())
         System.out.print("Before\n\r");
         System.out.print(RS2.getString(1));
         System.out.print("\n\r");

    PreparedStatement PrepStmt = Conn.prepareStatement("Insert into tablename (Col1,Col2,     ...) Values(?,     ?,...)"

         for (long Count = 1; Count <= lMax; Count++)
         { 
         PrepStmt.setInt(1,0);
         PrepStmt.setInt(1,0);
    .
    .
         PrepStmt.executeUpdate();
    }

         ResultSet RS2 = Stmt.executeQuery("Select to_char(TT_sysdate, 'YYYY-MM-DD HH24:MI:SS.FF')from dual");
    if (RS2.next())
         System.out.print("After\n\r");
         System.out.print(RS2.getString(1));
         System.out.print("\n\r");

    Can you comment on the above code all these are executed in same system no other services will be running when i run ODBC or JDBC.

    Thanks!
  • 3. Re: ODBC Performance is less in TimesTen
    ChrisJenkins Guru
    Currently Being Moderated
    In your ODBC code you are preparing the INSERT every time through the loop (bad and unnecessary) whereas in JDBC you are preparing it just once before the loop (good). Prepare is very expensive.

    Try moving the two lines:

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

    outside of the loop in the ODBC program.

    Also, you are not comitting in this code so either this will end up as one very large transaction (bad) or you have autocommit turned on (which is the default - but which is bad). You can speed things up even more by turning autocommit off and explicitly committing once every 'n' rows (choose a sensible value for 'n' - 256 is a good one).

    Chris

    Edited by: ChrisJenkins on Sep 5, 2012 2:25 PM
  • 4. Re: ODBC Performance is less in TimesTen
    936666 Newbie
    Currently Being Moderated
    Thanks for your replay.

    I have changed the code as per your statement
    command.CommandText = "Insert into tablename (Col1, Col2, ...) Values(?, ?,...)"
    command.Prepare();
    moving the lines up
    but there is no difference in the time i am getting the same time ,despite code change.

    How to specify commit interval for every 256 in the code(ODBC and JDBC)& where to specify.

    Thanks!
  • 5. Re: ODBC Performance is less in TimesTen
    ChrisJenkins Guru
    Currently Being Moderated
    In ODBC you disable autocommit as follows:

    SQLSetConnectoption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);

    Since you seem to be using some higher level abstraction of ODBC there may be some different way that you need to turn this off. it is a connection level property. If you don't explicitly turn it off then your commits are not having any effect.

    You commit a transaction using:

    SQLTransact(SQL_NULL_HENV, hdbc, SQL_COMMIT);

    In JDBC you turn autocommit off using the setAutocommit() method on a JDBC Connection object and you commit using the commit() method on the JDBC Connection object.

    There is no way to set an automatic 'commit interval'. Your code needs to make thsi happen by issuing a commit every 'n' inserts.

    Can you please provide more information on your ODBC program? Is this a pure C++ program or is it some kind of C# thing? Are you linking directly with the TimesTen ODBC driver or are you using a driver manager?
    Are you running the ODBC and Java programs on the same machine? Is this the same machine as where the TimesTen database is located? If so are using direct mode or client/server mode? For JDBC you are using direct mode but I cannot tell from the code what you are using for ODBC.

    Thanks,

    Chris
  • 6. Re: ODBC Performance is less in TimesTen
    936666 Newbie
    Currently Being Moderated
    For ODBC

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

    SQLSetConnectoption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
    If i use the code then i am not able to see any related dlls for SQLSetConnectoption

    You commit a transaction using:
    SQLTransact(SQL_NULL_HENV, hdbc, SQL_COMMIT);
    but where to specify the commit "n of 256".
    There is no way to set an automatic 'commit interval'. Your code needs to make thsi happen by issuing a commit every 'n' inserts.
    In JDBC
    In JDBC you turn autocommit off using the setAutocommit() method on a JDBC Connection object and you commit using the commit() method on the JDBC >Connection object.
    since i am database developer i am not sure where to use the setAutocommit() and commit() in my code

    The program is of C# and not C++ ,i am not using any driver manager ,using only ODBC driver
    Yes ODBC and Java programs have been run in same machine only when java program is running no other program will be running, no other services will be running once i complete JDBC then i will restart the system and run ODBC and makes sure that no other services are running.
    Yes TimesTen is installed in same machine i am treating is as localhost
    Direct mode is been used for both.

    Thanks!
  • 7. Re: ODBC Performance is less in TimesTen
    ChrisJenkins Guru
    Currently Being Moderated
    SQLSetConnectOption is an ODBC 2.0 call. The ODBC 3.0 equivalent is SQLSetConnectAttr(ConnectionHandle, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0); I have no idea how you would call that in C#. You may find that the connection object has a method to set autocommit...

    You are using the Driver Manager (C# always uses it) so you are paying around a 20-30% overhead because of that. What is the actual performance difference between the Java program and the C# one just out of interest? You say that Java is faster but how much faster?

    Your code doesn't show where the database connection is opened but assuming you have an ODBC connection object called 'connection' and that pbject has a commit() method then the code would look something like this:

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

    for (int i = 0; i < count; i++)
    {
    command.Parameters.AddWithValue("?", 0);
    command.Parameters.AddWithValue("?", 0);
    .
    .
    .
    command.ExecuteNonQuery();
    command.Parameters.Clear();
    if ( (i % cmtIntvl) == 0 )
    connection.commit();
    }

    connection.commit();

    Your JDBC code would look like this:

    Connection Conn = DriverManager.getConnection("jdbc:timesten:dsn=Mydatabase");
    Conn.setAutocommit(false);
    ...
    PreparedStatement PrepStmt = Conn.prepareStatement("Insert into tablename (Col1,Col2,     ...) Values(?,     ?,...)"
    long cmtIntvl = 256;

    for (long Count = 1; Count <= lMax; Count++)
    {
    PrepStmt.setInt(1,0);
    PrepStmt.setInt(1,0);
    .
    .
    PrepStmt.executeUpdate();
    if ( (Count % cmtIntvl) == 0 )
    Conn.commit();
    }
    Conn.commit();

    I would respectfully suggest that anyone developing code that accesses a relational database should have a detailed understanding of autocommit, transactions and associated topics.

    Regards,

    Chris
  • 8. Re: ODBC Performance is less in TimesTen
    936666 Newbie
    Currently Being Moderated
    Hi my ODBC code is

    /*Connection string */

    public TimesTenDatabase()
    {

    connectionString = "Dsn=Mydatabase";
    }
    the above code in TimesTenDatabase.cs
    --------------------------------------------
    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");
    }
    the above code in open.cs

    -----------------------------------------------------------------------------------
    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(TT_sysdate, '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(TT_sysdate, '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 in insert.cs
    --------------------------------------------
    so i can't able to use connection.commit();

    For JDBC the consumption for 100k record is 12 sec where for ODBC is 65 sec

    Thanks
  • 9. Re: ODBC Performance is less in TimesTen
    ChrisJenkins Guru
    Currently Being Moderated
    I'm afraid that I don't understand why you can't use connection.commit() - you did not explain why but simply stated that you can't use it. Note that I do not know if that is the correct name of the method - you need to check the C# documentation. While I am pretty good with C/ODBC and Java/JDBC I know very little abgout C#. What I do know is that you are not really just using ODBC here. There are several C# and .NET layers between your code and the TimesTen ODBC driver and I suspect that is contributing to the slowness. For sure if I wrote this code in pure C/ODBC it would be a lot faster than the Java code. Note that the TimesTen JDBC driver calls the TimesTen driver under the covers so it is clear that ODBC must be faster than JDBC in a like for like comparison.

    The Java code will go significantly faster if you do not commit after each record as I suggested. Have you tried that? To optimise the C# code you need to find yourself an expert on C# to help you. Or better yet, use pure C or C++ calling ODBC directly instead of C#/.NET :-)

    Chris
  • 10. Re: ODBC Performance is less in TimesTen
    936666 Newbie
    Currently Being Moderated
    Hi i have got some help from C# guy and i am able to do the operation

    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(TT_sysdate, '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"));

    OdbcTransaction transaction = null;
    int commitInterval = 256;
    transaction = connection.BeginTransaction();

    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();

    if (i % commitInterval == 0)
    {
    transaction.Commit();
    transaction = connection.BeginTransaction();
    command.Transaction = transaction;
    }

    }
    transaction.Commit();

    command.CommandText = "Select to_char(TT_sysdate, '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"));

    }

    but my time increases from 65 sec to 74 sec ,which i have run the code for multiple time despite getting time between 73 and 76 sec
    What I do know is that you are not really just using ODBC here.
    for creating the connection i have used
    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");
    }
    There are several C# and .NET layers between your code and the TimesTen ODBC driver and I suspect that is contributing to the slowness.
    so how can i optimize the code
  • 11. Re: ODBC Performance is less in TimesTen
    ChrisJenkins Guru
    Currently Being Moderated
    Given that you have made the normal kinds of optimisations we would apply to C/C++ code that calls ODBC directly, and assuming that the TimesTen configuration etc., is identical for the C# tests and the Java tests the inescapable conclusion is that the difference in performance is due to overheads added by the C#/.NET layer(s) and the Windows driver manager. If you really need higher performance then I suspect that you will need to switch to pure C/C++ , call ODBC functions directly and link directly with the TimesTen driver to avoid the driver manager. I do not know of any further optimisations you can make to your code (caveat - i am not a C# expert).

    If you installed the QuickStart as part of TimesTen there are several example C/ODBC programs in that including one that does exactly this kind of inserts. You can compile and test those if you like to see the sort of performance that is possible with pure C/ODBC.

    Chris

Legend

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