Help about index on the column of string

[Peter Wang]

I create a table named t1 ,then create an index on a column f2 typed string of t1.Coded with db api,the resultset of   is null,but with the tools  ,the resultset is right. Please give some help,thanks.

The primary code as following:

TableDef tabDef;
tabDef.addField("f1", typeInt, 0, NULL, true );
tabDef.addField("f2", typeString, 32,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 ); 

rv = dbMgr->createTable("t1", tabDef);

HashIndexInitInfo *idxInfo = new HashIndexInitInfo();
strcpy(idxInfo->tableName, "t1");
idxInfo->list.append("f2");

idxInfo->isUnique = true;
idxInfo->isPrimary = true;
idxInfo->indType = hashIndex;
 
.....

Condition p1;
int val1 = 0;
int val2 = 0;
char val3[32];
val3[0]='\0';
p1.setTerm("f2", OpEquals, val3);
table->setCondition(&p1);
 
rv =conn.startTransaction();
if (rv != OK) exit(1);
val1 = i%100;

val1= (1000000+i)%9999;
val2=100000+i%12332; 
sprintf(val3, "%.10d%.10d%.10d\0",i,val1,val2);

table->execute();
tuple = (char*)table->fetch() ;
if (tuple == NULL) {
printf("Query condition is:%s,length is:%d\n",val3,strlen(val3));
}

[Bijaya kumar Sahu]

Hi,
  I should work if you are inserted proper values . Post your whole code here with record insert code.

[Prabakaran Thirumalai]

Could you please let us know for which application you are planning to use CSQL. DBAPI provides direct access to CSQL storage engine. You  can alternatively use standard APIs like ODBC or JDBC and try out whether CSQL meets your performance requirement, as your learning curve will be minimal to use that. What is the performance gain you are looking with CSQL?. CSQL also provides another proprietary C++ interface named SQLAPI which is simpler to learn and use than DBAPI.
Thanks for using CSQL.
Regards
Prabakaran

[Peter Wang]

I'm just learning CSQL,the test code is changed from examples in CSQL package.I have resolved the problem.

val3[0]='\0'; 
p1.setTerm("f2", OpEquals, val3); 
table->setCondition(&p1); 

changed to :

val3[0]='\0'; 
p1.setTerm("f2", OpEquals, &val3); 
table->setCondition(&p1); 

The result is right. Thank you !

I'm not good at  hash algorithm, the source code is :

//validate the bucket size
    if (bucketSize < 100 || bucketSize > 200000)
    {
        printError(ErrBadRange, "Index Bucket size %d not in range 100-200000",
                                 bucketSize);
        return ErrBadRange;
    }

Why? Can you explain? How to set the value of bucketSize according to records?

Thks.

[Prabakaran Thirumalai]

CSQL uses simple modulus hashing algorithm(key modulus BUCKET_SIZE) for indexes on numeric data types like short ,int, etc.
The best bucket size would be the anticipated total number of records in that table. If the number of records of the table is larger than bucket size of index, search will compute the hash bucket and then traverse the bucket link list to find the pointer to the actual record. You can find more information about hashing in wikipedia.
http://en.wikipedia.org/wiki/Hash_table

[Peter Wang]

I test the hash index,create table t1,hash index is on a string column ,the length  is 31 bytes,Every operation is just one row,The result is :
                                      min     max       avg
6000000 rows inserted 5648 1685937 7631
6000000 rows selected 3338 3625887 5370

Why is the max-time so long?