1 2 Previous Next 15 Replies Latest reply: Jul 12, 2013 2:24 AM by Chrisjenkins-Oracle RSS

    The performance of timesten when using ODP.net Access

    112a9aa7-4189-4ceb-a322-c4cbb8ef5219

      Hi,dear db experts:

           Recently,I study the timesten and write a simple program by c#.This program just inserts 10000 records into timesten.But I find the performance is unsatisfactory.It takes about 1456ms although I perpare statements in advance.This program is just like this:

      command = conn.CreateCommand();
      
                  command.Parameters.Add(new OracleParameter(":intkey1", OracleDbType.Int32));
                  command.Parameters.Add(new OracleParameter(":strkey1", OracleDbType.Varchar2, 30));
                  command.Parameters.Add(new OracleParameter(":strkey2", OracleDbType.Varchar2, 30));
                  command.Parameters.Add(new OracleParameter(":strkey3", OracleDbType.Varchar2, 30));
                  command.Parameters.Add(new OracleParameter(":strkey4", OracleDbType.Varchar2, 30));
                  command.Parameters.Add(new OracleParameter(":strkey5", OracleDbType.Varchar2, 30));
                  command.Parameters.Add(new OracleParameter(":strkey6", OracleDbType.Varchar2, 30));
                  command.Parameters.Add(new OracleParameter(":strkey7", OracleDbType.Varchar2, 30));
                  command.Parameters.Add(new OracleParameter(":strkey8", OracleDbType.Varchar2, 30));
                  command.Parameters.Add(new OracleParameter(":strkey9", OracleDbType.Varchar2, 30));
                  command.Parameters.Add(new OracleParameter(":strkey10", OracleDbType.Varchar2, 30));
                  for (int i = 0; i < 10000; i++)
                  {
                      try
                      {                  
                          sql = "insert into TestXN values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)";
                          command.CommandText = sql;
                          command.Parameters[0].Value = i;
                          for (int j = 0; j < 10; j++)
                          {
                              command.Parameters[j + 1].Value = "aaaaaaaaaaaaaaaaaaaaaaaaaa";
                          }                 
                          command.ExecuteNonQuery();
                      }
                      catch (OracleException ex)
                      {
                          Console.WriteLine(ex.Message);
                      }
                  }
      
      

          The table schema is like this:

        create table TestXN
                 (
                    intkey int,
                    strkey1 varchar(30),
                    strkey2 varchar(30),
                    strkey3 varchar(30),
                    strkey4 varchar(30),
                    strkey5 varchar(30),
                    strkey6 varchar(30),
                    strkey7 varchar(30),
                    strkey8 varchar(30),
                    strkey9 varchar(30),
                    strkey10 varchar(30)
                 )
      
      

               

                  After I insert 10000 records into this table,I query sys.monitor:

      select DS_CHECKPOINTS,PERM_ALLOCATED_SIZE,PERM_IN_USE_SIZE,PERM_IN_USE_HIGH_WATER,TEMP_ALLOCATED_SIZE,TEMP_IN_USE_SIZE,TEMP_IN_USE_HIGH_WATER,XACT_COMMITS,XACT_D_COMMITS,CMD_PREPARES,CMD_REPREPARES,LOG_BUFFER_WAITS from sys.monitor
      
      

       

       

                The query result is:

      DS_CHECKPOINTS=0

      PERM_ALLOCATED_SIZE=655360

      PERM_IN_USE_SIZE=18202

      PERM_IN_USE_HIGH_WATER=18202

      TEMP_ALLOCATED_SIZE=65536

      TEMP_IN_USE_SIZE=16385

      TEMP_IN_USE_HIGH_WATER=17334

      XACT_COMMITS=10024

      XACT_D_COMMITS=0

      CMD_PREPARES=25

      CMD_REPREPARES=0

      LOG_BUFFER_WAITS=0

       

       

               In addition,the settings of this timesten database is just as follows:

              PermSize:640

              TempSize:64

              CkptFrequency:   0(disable checkpoint)

              CkptLogVolume:    0 (disable checkpoint)

              LogBufMB :   256

              LogFileSize:   260

              LogBufParallelism:   4

              LogFlushMethod:  2

              Isolation: 0

             Other settings are default value.

       

             I want to know why the performance is unsatisfactory.From the result of monitor,there is no checkpoint-write to the disk and the log buffer is enough.Thanks for your time!

        • 1. Re: The performance of timesten when using ODP.net Access
          Jspalmer-Oracle

          It looks like you're committing after every insert

           

          XACT_COMMITS=10024

           

          You could try committing once after the 10,000 Inserts and see if this improves things? Also - how many indexes are there on the table? Too many indexes results in high index maintenance.

           

          A general rule for measuring performance is that you need millions of iterations to really get a good idea of how fast something is, rather than a small test like this.

          • 2. Re: The performance of timesten when using ODP.net Access
            112a9aa7-4189-4ceb-a322-c4cbb8ef5219

            Thanks for your response,there is no index in this table.I want to know is the result normal for timesten when I insert into a table with every sql commit?

            • 3. Re: The performance of timesten when using ODP.net Access
              Chrisjenkins-Oracle

              It is not normal to take nearly 1.5 seconds to insert 10,000 rows. On my laptop via ODBC I can insert almost 500,000 rows per second. Now of course ODP .NET is  less efficient than pure ODBC but still this is too large a difference I think. Some questions...

               

              1.    Are you using the default ramPolicy of 'inUse'? (ttAdmin -query DSN)

               

              2.    Was anything else connected to the database (for example a ttIsql session) while you ran the test program?

               

              3.    What version of Windows is this? Is it 32 or 64 bit? How much RAM does the machine actually have?

               

              4.    Why are you using LogFlushMethod=2?

               

              Thanks,

               

              Chris

              • 4. Re: The performance of timesten when using ODP.net Access
                112a9aa7-4189-4ceb-a322-c4cbb8ef5219

                I am very grateful for your answers!

                 

                For your 4 questions:

                 

                1.the result of ttAdmin -query DSN is

                 

                RAM Residence Policy            : inUse

                Replication Agent Policy        : manual

                Replication Manually Started    : False

                Cache Agent Policy              : manual

                Cache Agent Manually Started    : False


                2、Only my test program connected to the database


                3、The version of the OS is windows server 2008 R2 standard 64 bit(6.1 version 7601) and the size of ram is 4GB


                4、There is no certain reasons for the settings of the LogFlushMethod.Even if I use LogFlushMethod=0(the default value) or LogFlushMethod=1,it takes about 1038ms.


                In addtion,can your tell me the settings of the database when you tested in your laptop?And what the schema of the tested table was?And if it is possible,can you give me your codes?


                Thanks very much!

                • 5. Re: The performance of timesten when using ODP.net Access
                  Chrisjenkins-Oracle

                  I think the problem here is that by default a TimesTen database is only in memory when it is being used. So, in your test you are not just timing the inserts but also thw tiem to load the database into memory at the start of the test and the time to unload it at the end (both of which will be significant). Please try the following:

                   

                  -   In a command window use ttIsql to connect to the database. Leave this connected.

                   

                  -  Run your test program (in a different window if it is a command line program)

                   

                  Let me know the results.

                   

                  The test in my laptop is simply the bulkinsert demo program in <tt_install_dir>/quickstart/sample_code/odbc. Database settings are the defaults.

                   

                  Chris

                  • 6. Re: The performance of timesten when using ODP.net Access
                    112a9aa7-4189-4ceb-a322-c4cbb8ef5219

                    Hi,Chris!Thanks for your reply!It still takes about 1107.602ms accroding to your steps.

                    • 7. Re: The performance of timesten when using ODP.net Access
                      Chrisjenkins-Oracle

                      Can yu please provide all your DSN settings for the DSN your program is running against. The easiest way to do this is to connect to the DSN using ttIsql and then run the command:

                       

                      call ttConfiguration;

                       

                      Capture the output and post it.

                       

                      Also, please try the bulk insert demo program in quickstart/sample_code/odbc as follows:

                       

                      bulkinsert -r 100000 -s 1

                       

                      Unless you have a very slow machine you should achieve > 100,000 inserts per second.

                       

                      Please also post the results of this test.

                       

                      Thanks,

                       

                      Chris

                      • 8. Re: The performance of timesten when using ODP.net Access
                        112a9aa7-4189-4ceb-a322-c4cbb8ef5219

                        Thanks,Chiris!

                        1、My DSN  settings are as follows:

                        Command> connect TestWt;
                        Connection successful: DSN=TestWt;UID=Administrator;DataStore=C:\Test\data\testw
                        t\TestWt;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;LogFileSi
                        ze=650;DRIVER=C:\TimesTen\TT1122~1\bin\ttdv1122.dll;PermSize=640;TempSize=64;Log
                        FlushMethod=1;CkptFrequency=0;CkptLogVolume=0;TypeMode=0;PLSCOPE_SETTINGS=IDENTI
                        FIERS:NONE;LogBufMB=640;LogBufParallelism=4;
                        (Default setting AutoCommit=1)
                        Command> call ttConfiguration;
                        < CacheAwtMethod, 1 >
                        < CacheAwtParallelism, 1 >
                        < CacheGridEnable, 1 >
                        < CacheGridMsgWait, 60 >
                        < CkptFrequency, 0 >
                        < CkptLogVolume, 0 >
                        < CkptRate, 0 >
                        < ConnectionCharacterSet, ZHS16GBK >
                        < ConnectionName, testwt >
                        < Connections, 2000 >
                        < DDLCommitBehavior, 0 >
                        < DDLReplicationAction, INCLUDE >
                        < DDLReplicationLevel, 2 >
                        < DataBaseCharacterSet, ZHS16GBK >
                        < DataStore, c:\test\data\testwt\TestWt >
                        < DynamicLoadEnable, 1 >
                        < DuplicateBindMode, 0 >
                        < DurableCommits, 0 >
                        < DynamicLoadErrorMode, 0 >
                        < Isolation, 1 >
                        < LockLevel, 0 >
                        < LockWait, 10 >
                        < LogAutoTruncate, 1 >
                        < LogBufMB, 640 >
                        < LogBufParallelism, 4 >
                        < LogDir, c:\test\data\testwt\ >
                        < LogFileSize, 650 >
                        < LogFlushMethod, 1 >
                        < LogPurge, 1 >
                        < Logging, 1 >
                        < MemoryLock, 0 >
                        < NLS_LENGTH_SEMANTICS, BYTE >
                        < NLS_NCHAR_CONV_EXCP, 0 >
                        < NLS_SORT, BINARY >
                        < OracleNetServiceName, <NULL> >
                        < PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
                        < PLSQL, 1 >
                        < PLSQL_CCFLAGS, <NULL> >
                        < PLSQL_CODE_TYPE, INTERPRETED >
                        < PLSQL_CONN_MEM_LIMIT, 100 >
                        < PLSQL_MEMORY_ADDRESS, 0x000000005b8c0000 >
                        < PLSQL_MEMORY_SIZE, 32 >
                        < PLSQL_OPTIMIZE_LEVEL, 2 >
                        < PLSQL_TIMEOUT, 30 >
                        < PassThrough, 0 >
                        < PermSize, 640 >
                        < PermWarnThreshold, 90 >
                        < Preallocate, 0 >
                        < PrivateCommands, 0 >
                        < QueryThreshold, 0 >
                        < RACCallback, 1 >
                        < ReceiverThreads, 1 >
                        < RecoveryThreads, 1 >
                        < ReplicationApplyOrdering, 0 >
                        < ReplicationParallelism, 1 >
                        < ReplicationParallelismBufferMB, 64 >
                        < ReplicationTrack, -1 >
                        < SQLQueryTimeout, 0 >
                        < TempSize, 64 >
                        < TempWarnThreshold, 90 >
                        < Temporary, 0 >
                        < TypeMode, 0 >
                        < UID, ADMINISTRATOR >
                        63 rows found.
                        
                        

                        2、The result of the bulkinsert -r 100000 -s 1 test is:

                        C:\TimesTen\tt1122_64\quickstart\sample_code\odbc>bulkinsert -r 100000 -s 1
                        Connecting to DB with connect string DSN=sampledb_1122;UID=appuser
                        Enter password for 'appuser':
                        Loading 100000 records with batch size 1
                        Load time:               1.6 seconds
                        Load rate:           63881.2 records/second (12726 KB/sec)
                        
                        

                        The DSN which this demo is running  against is sampledb_1122.And the settings of this database are defaults.The DSN settings in detail as follows:

                        Command> connect sampledb_1122;
                        Connection successful: DSN=sampledb_1122;UID=Administrator;DataStore=C:\Users\AD
                        MINI~1\AppData\Roaming\TimesTen\DEMODA~1\sampledb_1122;DatabaseCharacterSet=WE8M
                        SWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=C:\TimesTen\TT1122~1\bin\ttdv112
                        2.dll;PermSize=40;TempSize=32;TypeMode=0;
                        (Default setting AutoCommit=1)
                        Command> call ttConfiguration;
                        < CacheAwtMethod, 1 >
                        < CacheAwtParallelism, 1 >
                        < CacheGridEnable, 1 >
                        < CacheGridMsgWait, 60 >
                        < CkptFrequency, 600 >
                        < CkptLogVolume, 0 >
                        < CkptRate, 0 >
                        < ConnectionCharacterSet, US7ASCII >
                        < ConnectionName, sampledb_1122 >
                        < Connections, 2000 >
                        < DDLCommitBehavior, 0 >
                        < DDLReplicationAction, INCLUDE >
                        < DDLReplicationLevel, 2 >
                        < DataBaseCharacterSet, WE8MSWIN1252 >
                        < DataStore, c:\users\admini~1\appdata\roaming\timesten\demoda~1\sampledb_1122 >
                        < DynamicLoadEnable, 1 >
                        < DuplicateBindMode, 0 >
                        < DurableCommits, 0 >
                        < DynamicLoadErrorMode, 0 >
                        < Isolation, 1 >
                        < LockLevel, 0 >
                        < LockWait, 10 >
                        < LogAutoTruncate, 1 >
                        < LogBufMB, 64 >
                        < LogBufParallelism, 4 >
                        < LogDir, c:\users\admini~1\appdata\roaming\timesten\demoda~1\ >
                        < LogFileSize, 64 >
                        < LogFlushMethod, 1 >
                        < LogPurge, 1 >
                        < Logging, 1 >
                        < MemoryLock, 0 >
                        < NLS_LENGTH_SEMANTICS, BYTE >
                        < NLS_NCHAR_CONV_EXCP, 0 >
                        < NLS_SORT, BINARY >
                        < OracleNetServiceName, <NULL> >
                        < PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
                        < PLSQL, 1 >
                        < PLSQL_CCFLAGS, <NULL> >
                        < PLSQL_CODE_TYPE, INTERPRETED >
                        < PLSQL_CONN_MEM_LIMIT, 100 >
                        < PLSQL_MEMORY_ADDRESS, 0x000000005b8c0000 >
                        < PLSQL_MEMORY_SIZE, 32 >
                        < PLSQL_OPTIMIZE_LEVEL, 2 >
                        < PLSQL_TIMEOUT, 30 >
                        < PassThrough, 0 >
                        < PermSize, 40 >
                        < PermWarnThreshold, 90 >
                        < Preallocate, 0 >
                        < PrivateCommands, 0 >
                        < QueryThreshold, 0 >
                        < RACCallback, 1 >
                        < ReceiverThreads, 1 >
                        < RecoveryThreads, 1 >
                        < ReplicationApplyOrdering, 0 >
                        < ReplicationParallelism, 1 >
                        < ReplicationParallelismBufferMB, 64 >
                        < ReplicationTrack, -1 >
                        < SQLQueryTimeout, 0 >
                        < TempSize, 32 >
                        < TempWarnThreshold, 90 >
                        < Temporary, 0 >
                        < TypeMode, 0 >
                        < UID, ADMINISTRATOR >
                        63 rows found.
                        

                        3、In additon,if I create table TestXN which my program used in the DSN sampledb_1122.And run my program against the table TestXN in DSN sampledb_1122,the result is about 1001.0572ms.

                        • 9. Re: The performance of timesten when using ODP.net Access
                          Chrisjenkins-Oracle

                          Well, everything looks okay although your numbers for the bulkinsert test are rather low so it seems this machine is fairly slow. Anyway, that figure, 63881 inserts per second, is really the fastest that you are likely to go on this machine for singleton inserts committed after each insert (which is what the bulkinsert test does when run with those parameters). Now ODP .NET uses the Windows driver manager so that adds 20%+ overhead immediately so that would reduce the number to around 51,000. The multiple layers of .NET stuff plus all the other object stuff will add quite a lot of additional overhead but it is hard to quantify.

                           

                          I would suggest three things:

                           

                          1.   Check your code to see how you are timing the inserts. Be sure that you are only timing the execution of the actual inserts (including commits) and that you are *not* including things like connect/disconnect/prepare in your timings.

                           

                          2.    Try with a much larger number of rows (maybe 100,000 or even 1,000,000). 10,000 rows is not many and short duration timings are less reliable than longer ones.

                           

                          3.    Try running the test multiple times and see if the time varies.

                           

                          If you still see the same results then either there is some terrible inefficiency in your code (though I can't see what it might be from the code snippet provided) or this is just the kind of performance you get with ODP .NET.

                           

                          Chris

                          • 10. Re: The performance of timesten when using ODP.net Access
                            112a9aa7-4189-4ceb-a322-c4cbb8ef5219

                            Maybe you are right.This is just the kind of performance I get with ODP .NET.But if I program with java and connect to timesten with jdbc,its performance is also like this.Is it normal?And did you have experience in connecting timesten with odbc or other drivers and programing with c#?Could it acheive a good performance?

                            • 11. Re: The performance of timesten when using ODP.net Access
                              Chrisjenkins-Oracle

                              I'm afraid I do not have any real experience with C#. I am a C and Java developer. Are you saying that you see similar performance with Java as you do with ODP .NET? If so then there is definitely something wrong. Java/JDBC performance will be lower than C/ODBC but only by 10-20% typically. Are you able to provide, either by posting or by direct e-mail, the following:

                               

                              -    The test table definition, including any indexes.

                               

                              -    The complete source of the Java test program.

                               

                              -     Details on the Java version that you are using

                               

                              Thanks,

                               

                              Chris

                              • 12. Re: The performance of timesten when using ODP.net Access
                                112a9aa7-4189-4ceb-a322-c4cbb8ef5219

                                Yes,I see similar performance with Java as I do with ODP .NET.

                                1)The test table definition is the same as the table I used in the c# test:

                                create table TestXN  
                                (
                                              intkey int,  
                                              strkey1 varchar(30),  
                                              strkey2 varchar(30),  
                                              strkey3 varchar(30),  
                                              strkey4 varchar(30),  
                                              strkey5 varchar(30),  
                                              strkey6 varchar(30),  
                                              strkey7 varchar(30),  
                                              strkey8 varchar(30),  
                                              strkey9 varchar(30),  
                                            strkey10 varchar(30)  
                                )
                                
                                
                                

                                2)The java source is :

                                import java.sql.Connection;
                                import java.sql.DriverManager;
                                import java.sql.SQLException;
                                import java.sql.Statement;
                                
                                public class MyTest2 {
                                public static Connection con = null;
                                 public static Statement stmt;
                                 public static String sql = "";
                                 
                                 /**
                                  * @param args
                                  */
                                 public static void main(String[] args) {
                                  // TODO Auto-generated method stub
                                  // TODO Auto-generated method stub
                                  String timestenDriver = "com.timesten.jdbc.TimesTenDriver";  
                                  System.out.println("\nLoading Driver " + timestenDriver);
                                  try {
                                   Class.forName(timestenDriver);
                                   String URL = "jdbc:timesten:direct:TestWt";
                                   con = DriverManager.getConnection(URL, "admin", "admin");
                                  } catch (ClassNotFoundException e) {
                                   // TODO Auto-generated catch block
                                   e.printStackTrace();
                                  } catch (SQLException e) {
                                   // TODO Auto-generated catch block
                                   e.printStackTrace();
                                  }
                                  
                                  
                                  while (true) {
                                   long startTime = System.currentTimeMillis(); 
                                   for (int i = 0; i < 10000; i++) {
                                    try {
                                     stmt = con.createStatement();
                                     sql = "insert into TestXN values (";
                                     sql += i;
                                     for (int j = 0; j < 10; j++) {
                                      sql += ",'aaaaaaaaaaaaaaaaaaaaaaaaaa'";
                                     }
                                     sql += ")";
                                     stmt.execute(sql);
                                    } catch (SQLException e) {
                                     // TODO Auto-generated catch block
                                     e.printStackTrace();
                                    }
                                   }
                                   long endTime = System.currentTimeMillis(); 
                                   long l = endTime - startTime;
                                   System.out.println("Take time:" + l);
                                   try {
                                    Thread.sleep(1000);
                                   } catch (InterruptedException e) {
                                    // TODO Auto-generated catch block
                                    e.printStackTrace();
                                   }
                                  }
                                  
                                 }
                                }
                                
                                
                                

                                The result is like this:

                                Loading Driver com.timesten.jdbc.TimesTenDriver

                                Take time:8591

                                Take time:3096

                                Take time:4283

                                Take time:4040

                                Take time:3470

                                Take time:4284

                                Take time:5555

                                Take time:4291

                                Take time:4578

                                Take time:5239

                                Take time:4878

                                Take time:4761

                                Take time:5252

                                Take time:4989

                                Take time:4896

                                Take time:4676

                                Take time:3435

                                Take time:3963

                                Take time:3404

                                Take time:4123

                                Take time:4519

                                Take time:3568

                                Take time:4095

                                Take time:3498

                                3)The Java version that I am using is:

                                C:\Users\Administrator>java -version

                                Picked up JAVA_TOOL_OPTIONS: -agentlib:jvmhook

                                Picked up _JAVA_OPTIONS: -Xrunjvmhook -Xbootclasspath/a:C:\PROGRA~2\HP\QUICKT~1\

                                bin\JAVA_S~1\classes;C:\PROGRA~2\HP\QUICKT~1\bin\JAVA_S~1\classes\jasmine.jar

                                java version "1.6.0_43"

                                Java(TM) SE Runtime Environment (build 1.6.0_43-b01)

                                Java HotSpot(TM) 64-Bit Server VM (build 20.14-b01, mixed mode)

                                • 13. Re: The performance of timesten when using ODP.net Access
                                  Chrisjenkins-Oracle

                                  Yes, but the problem here is not TimesTen. The problem is that this is an extremely inefficient program that spends most of its time in assembling the SQL statement in Java and then gettign TimesTen to (re)parse it on every execution. When I run this in my environment the average time (averaged over 20 iterations) to insert 10,000 rows is 2143 ms.

                                   

                                  Re-writing the program as it should be done using a prepared,parameterised statement results in an average time for 10,000 inserts of 177 ms (12x improvement)

                                   

                                  import java.sql.Connection;

                                  import java.sql.DriverManager;

                                  import java.sql.SQLException;

                                  import java.sql.PreparedStatement;

                                   

                                   

                                  public class MyTest3 {

                                       public static Connection con = null;

                                       public static PreparedStatement pstmt;

                                       public static String sql = "insert into testxn values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                                       public static int numIterations = 20;

                                       public static long totalTime = 0;

                                   

                                  /**

                                    * @param args

                                    */

                                  public static void main(String[] args) {

                                    String timestenDriver = "com.timesten.jdbc.TimesTenDriver"; 

                                    System.out.println("\nLoading Driver " + timestenDriver);

                                    try {

                                     Class.forName(timestenDriver);

                                     String URL = "jdbc:timesten:direct:sampledb_1122";

                                     con = DriverManager.getConnection(URL, "scott", "tiger");

                                    } catch (ClassNotFoundException e) {

                                     e.printStackTrace();

                                    } catch (SQLException e) {

                                     e.printStackTrace();

                                    }

                                   

                                   

                                    try {

                                        con.setAutoCommit(false);

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                   

                                    try {

                                        pstmt = con.prepareStatement(sql);

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                    int count = 0;

                                    while (count < numIterations) {

                                     long startTime = System.currentTimeMillis();

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

                                      try {

                                       pstmt.setInt(1,i);

                                       for (int j = 2; j <= 11; j++) {

                                        pstmt.setString(j,"aaaaaaaaaaaaaaaaaaaaaaaaaa");

                                       }

                                       pstmt.execute();

                                       con.commit();

                                      } catch (SQLException e) {

                                       e.printStackTrace();

                                      }

                                     }

                                     long endTime = System.currentTimeMillis();

                                     long l = endTime - startTime;

                                     totalTime = totalTime + l;

                                     System.out.println("Time taken:" + l + " ms");

                                     try {

                                      Thread.sleep(1000);

                                     } catch (InterruptedException e) {

                                      e.printStackTrace();

                                     }

                                     count++;

                                    }

                                   

                                   

                                    double avgTime = (double)totalTime / (double)count;

                                    System.out.println("Average time for 10,000 inserts: " + avgTime + " ms");

                                   

                                   

                                    try {

                                        pstmt.close();

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                   

                                    try {

                                        con.close();

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                  }

                                   

                                   

                                  }

                                   

                                  If one further optimises this to commit only every 256 inserts instead of every insert the avergae tiem reduces further to 143 ms.

                                   

                                  import java.sql.Connection;

                                  import java.sql.DriverManager;

                                  import java.sql.SQLException;

                                  import java.sql.PreparedStatement;

                                   

                                   

                                  public class MyTest4 {

                                       public static Connection con = null;

                                       public static PreparedStatement pstmt;

                                       public static String sql = "insert into testxn values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                                       public static int numIterations = 20;

                                       public static long totalTime = 0;

                                       public static int commitInterval = 256;

                                   

                                  /**

                                    * @param args

                                    */

                                  public static void main(String[] args) {

                                    String timestenDriver = "com.timesten.jdbc.TimesTenDriver"; 

                                    System.out.println("\nLoading Driver " + timestenDriver);

                                    try {

                                     Class.forName(timestenDriver);

                                     String URL = "jdbc:timesten:direct:sampledb_1122";

                                     con = DriverManager.getConnection(URL, "scott", "tiger");

                                    } catch (ClassNotFoundException e) {

                                     e.printStackTrace();

                                    } catch (SQLException e) {

                                     e.printStackTrace();

                                    }

                                   

                                   

                                    try {

                                        con.setAutoCommit(false);

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                   

                                    try {

                                        pstmt = con.prepareStatement(sql);

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                    int i, count = 0;

                                    while (count < numIterations) {

                                     long startTime = System.currentTimeMillis();

                                     for (i = 1; i <= 10000; i++) {

                                      try {

                                       pstmt.setInt(1,i);

                                       for (int j = 2; j <= 11; j++) {

                                        pstmt.setString(j,"aaaaaaaaaaaaaaaaaaaaaaaaaa");

                                       }

                                       pstmt.execute();

                                       if ( (i % commitInterval) == 0  )

                                           con.commit();

                                      } catch (SQLException e) {

                                       e.printStackTrace();

                                      }

                                     }

                                     if ( (i % commitInterval) != 0  )

                                        try {

                                            con.commit();

                                        } catch (SQLException e) {

                                         e.printStackTrace();

                                        }

                                     long endTime = System.currentTimeMillis();

                                     long l = endTime - startTime;

                                     totalTime = totalTime + l;

                                     System.out.println("Time taken:" + l + " ms");

                                     try {

                                      Thread.sleep(1000);

                                     } catch (InterruptedException e) {

                                      e.printStackTrace();

                                     }

                                     count++;

                                    }

                                   

                                   

                                    double avgTime = (double)totalTime / (double)count;

                                    System.out.println("Average time for 10,000 inserts: " + avgTime + " ms");

                                   

                                   

                                    try {

                                        pstmt.close();

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                   

                                    try {

                                        con.close();

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                  }

                                   

                                   

                                  }

                                   

                                  And lastly if one uses JDBC batch execution with the optimal (for TimesTen) batch size of 256 operations then the average reduces to 94 ms which is over 22x faster than the original.

                                   

                                  import java.sql.Connection;

                                  import java.sql.DriverManager;

                                  import java.sql.SQLException;

                                  import java.sql.PreparedStatement;

                                   

                                   

                                  public class MyTest5 {

                                       public static Connection con = null;

                                       public static PreparedStatement pstmt;

                                       public static String sql = "insert into testxn values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                                       public static int numIterations = 20;

                                       public static long totalTime = 0;

                                       public static int batchSize = 256;

                                   

                                  /**

                                    * @param args

                                    */

                                  public static void main(String[] args) {

                                    String timestenDriver = "com.timesten.jdbc.TimesTenDriver"; 

                                    System.out.println("\nLoading Driver " + timestenDriver);

                                    try {

                                     Class.forName(timestenDriver);

                                     String URL = "jdbc:timesten:direct:sampledb_1122";

                                     con = DriverManager.getConnection(URL, "scott", "tiger");

                                    } catch (ClassNotFoundException e) {

                                     e.printStackTrace();

                                    } catch (SQLException e) {

                                     e.printStackTrace();

                                    }

                                   

                                   

                                    try {

                                        con.setAutoCommit(false);

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                   

                                    try {

                                        pstmt = con.prepareStatement(sql);

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                    int i, count = 0;

                                    while (count < numIterations) {

                                     long startTime = System.currentTimeMillis();

                                     try {

                                         pstmt.clearBatch();

                                      } catch (SQLException e) {

                                         e.printStackTrace();

                                      }

                                     for (i = 1; i <= 10000; i++) {

                                      try {

                                       pstmt.setInt(1,i);

                                       for (int j = 2; j <= 11; j++) {

                                        pstmt.setString(j,"aaaaaaaaaaaaaaaaaaaaaaaaaa");

                                       }

                                       pstmt.addBatch();

                                       if ( (i % batchSize) == 0  )

                                       {

                                           pstmt.executeBatch();

                                           con.commit();

                                       }

                                      } catch (SQLException e) {

                                       e.printStackTrace();

                                      }

                                     }

                                     if ( (i % batchSize) != 0  )

                                        try {

                                            pstmt.executeBatch();

                                            con.commit();

                                        } catch (SQLException e) {

                                         e.printStackTrace();

                                        }

                                     long endTime = System.currentTimeMillis();

                                     long l = endTime - startTime;

                                     totalTime = totalTime + l;

                                     System.out.println("Time taken:" + l + " ms");

                                     try {

                                      Thread.sleep(1000);

                                     } catch (InterruptedException e) {

                                      e.printStackTrace();

                                     }

                                     count++;

                                    }

                                   

                                   

                                    double avgTime = (double)totalTime / (double)count;

                                    System.out.println("Average time for 10,000 inserts: " + avgTime + " ms");

                                   

                                   

                                    try {

                                        pstmt.close();

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                   

                                    try {

                                        con.close();

                                    } catch (SQLException e) {

                                       e.printStackTrace();

                                    }

                                   

                                  }

                                   

                                   

                                  }

                                   

                                  Chris

                                  • 14. Re: The performance of timesten when using ODP.net Access
                                    112a9aa7-4189-4ceb-a322-c4cbb8ef5219

                                    Thanks for your reply,if we use prepare statement,maybe all these 10,000 sqls use one excution plan.But why I do this test is because of our team develop a trading system rencently,and we need a high performance.Meanwhile our system process a order with 10~20 different sqls,so it is impossible for all these sqls to share one excution plan. I assemble each SQL statement in my test in order to construct 10,000 'different' sqls.Is the result normal for java when I insert 1000,0 different sqls with 10,000 different excution plans into a table?

                                    1 2 Previous Next