hi peter,
I regret in replying to your query so lately, Instead of using DBAPI code I use the sqlapi code and it give following result.
For bucket size 1009(default ) it takes 95515 nanosec /insert
For bucket size 10000 it takes 9244 nanosec /insert .
Here i am giving sample source code for insert nad select . you can test it and let me know if you have any problem .
I test the tree index,the performance is very poor,Total records are 1M rows,the average execution time is 1 second . The source codes as following:
TableDef tabDef;
tabDef.addField("f1", typeInt, 0, NULL, true );
tabDef.addField("f2", typeString, 31,NULL,true);
tabDef.addField("f3", typeInt, 0, NULL, true );
tabDef.addField("f4", typeInt, 0, NULL, true );
tabDef.addField("f5", typeInt, 0, NULL, true );
tabDef.addField("f6", typeInt, 0, NULL, true );
tabDef.addField("f7", typeInt, 0, NULL, true );
tabDef.addField("f8", typeInt, 0, NULL, true );
tabDef.addField("f9", typeInt, 0, NULL, true );
tabDef.addField("f10",typeInt, 0, NULL, true );
.......
HashIndexInitInfo *idxInfo = new HashIndexInitInfo();
strcpy(idxInfo->tableName, "t1");
idxInfo->list.append("f2");
idxInfo->indType = treeIndex;
idxInfo->bucketSize = 1000;
.......
Result:
1000000 rows inserted,the average time is 1000535 nano-second
Please give some advice,even if due to the current version .
hi peter,
I regret in replying to your query so lately, Instead of using DBAPI code I use the sqlapi code and it give following result.
For bucket size 1009(default ) it takes 95515 nanosec /insert
For bucket size 10000 it takes 9244 nanosec /insert .
Here i am giving sample source code for insert nad select . you can test it and let me know if you have any problem .
#include<AbsSqlStatement.h>
#include<SqlFactory.h>
#include<NanoTimer.h>
#define ITERATIONS 1000000
int main()
{
DbRetVal rv = OK;
AbsSqlConnection *con = SqlFactory::createConnection(CSql);
rv = con->connect("root", "manager");
if (rv != OK) return 1;
AbsSqlStatement *stmt = SqlFactory::createStatement(CSql);
stmt->setConnection(con);
char statement[1024];
int rows =0;
strcpy(statement, "CREATE TABLE t1 (f1 int, f2 char(30),f3 int,f4 int,f5 int,f6 int,f7 int,f8 int,f9 int,f10 int);");
rv = stmt->prepare(statement);
if (rv != OK) {delete stmt; delete con; return -1; }
rv = stmt->execute(rows);
if (rv != OK) {delete stmt; delete con; return -1; }
stmt->free();
printf("Table t1 created\n");
strcpy(statement, "CREATE INDEX t1idx on t1 (f1) tree size 10000;");//default bucket size is 1009
rv = stmt->prepare(statement);
if (rv != OK) {delete stmt; delete con; return -1; }
rv = stmt->execute(rows);
if (rv != OK) {delete stmt; delete con; return -1; }
stmt->free();
printf("Index created on t1(f1) \n");
strcpy(statement, "INSERT INTO t1 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
int id1 =10;
char name[196];
strcpy(name, "Rithish");
NanoTimer timer;
int f3,f4,f5,f6,f7,f8,f9,f10;
rv = stmt->prepare(statement);
if (rv != OK) {delete stmt; delete con; return -1; }
int count =0;
f3=f4=f5=f6=f7=f8=f9=f10=23;
for (int i = 0 ; i < ITERATIONS ; i++)
{
id1 = i;
strcpy(name, "Gopika");
stmt->setIntParam(1, id1);
stmt->setStringParam(2, name);
stmt->setIntParam(3, f3);
stmt->setIntParam(4, f4);
stmt->setIntParam(5, f5);
stmt->setIntParam(6, f6);
stmt->setIntParam(7, f7);
stmt->setIntParam(8, f8);
stmt->setIntParam(9, f9);
stmt->setIntParam(10, f10);
timer.start();
rv = con->beginTrans();
if (rv != OK) break;
rv = stmt->execute(rows);
if (rv != OK) break;
rv = con->commit();
if (rv != OK) break;
timer.stop();
count++;
//printf("%d\n",count);
}
printf("Total Rows Inserted %d %lld %lld %lld\n", count, timer.min(),
timer.max(), timer.avg());
stmt->free();
strcpy(statement, "SELECT * FROM t1 where f1 = ?;");
rv = stmt->prepare(statement);
if (rv != OK) {delete stmt; delete con; return -1; }
stmt->bindField(1, &id1);
stmt->bindField(2, name);
stmt->bindField(3, &f3);
stmt->bindField(4, &f4);
stmt->bindField(5, &f5);
stmt->bindField(6, &f6);
stmt->bindField(7, &f7);
stmt->bindField(8, &f8);
stmt->bindField(9, &f9);
stmt->bindField(10, &f10);
timer.reset();
count =0;
for (int i = 0 ; i < ITERATIONS ; i++)
{
timer.start();
rv = con->beginTrans();
if (rv != OK) break;
stmt->setIntParam(1, i);
stmt->execute(rows);
if (stmt->fetch() == NULL) { printf("unable to read record\n"); break; }
stmt->close();
rv = con->commit();
if (rv != OK) break;
timer.stop();
count++;
}
stmt->free();
printf("Total Rows Selected %d %lld %lld %lld\n", count, timer.min(), timer.max(), timer.avg());
strcpy(statement, "DROP TABLE t1);");
rv = stmt->prepare(statement);
if (rv != OK) {delete stmt; delete con; return -1; }
rv = stmt->execute(rows);
if (rv != OK) {delete stmt; delete con; return -1; }
stmt->free();
printf("Table dropped\n");
delete stmt;
delete con;
return 0;
}