Thanks for you advices. Patrick's suggestion worked for my sorted input
case. The elapse time went down to 15 minutes.

However, for random input data, it slows down again.

Any suggestions on how to figure out the cache size to fill all dirty
pages?

Another question: If I have multiple DB writers writing to different
DBs in one ENV, any cache tuning suggestion?

My test case:

I have a file with 10 Million records. Each record has 10 columns with
up to 20 bytes character. Values are very random. Assume all values are
different.

I want to write a program to find out distinct values for each column
and their count. Here is what I did:

- Open a env with cache 1.5G:

G_BDB_ENV = new DbEnv(0);
string dbHome = "G:/temp/bdb";
int ret = G_BDB_ENV->set_cachesize(1, 400*1024*1024, 1);
ret = G_BDB_ENV->open(dbHome.c_str(),
DB_INIT_CDB|DB_PRIVATE|DB_INIT_MPOOL|DB_CREATE|DB_ THREAD, 0);
- Open 10 Btree DBs, one for each column:
bdb = new Db(G_BDB_ENV, 0);
string fileName = string(RepConn::generateGUID()) + ".db";
bdb->set_pagesize(32*1024);
bdb->open(NULL, fileName.c_str(), NULL, DB_BTREE, DB_CREATE |
DB_THREAD, 0);
bdb->cursor(NULL, &cursor, DB_WRITECURSOR);
- For each value, look it up in its DB, if it exists, update the record
with its count+1. otherwise, insert a record with count =1.

Dbt key, data;
I4 row_count = 0;
memset(&key, 0, sizeof(DBT));
memset(&data, 0, sizeof(DBT));


key.set_data( value+4);
key.set_size( *(I4 *)value);

data.set_data(&row_count);
data.set_size(sizeof(I4));
data.set_ulen(sizeof(I4));
data.set_flags(DB_DBT_USERMEM);
int ret = m_cursor->get(&key, &data, DB_SET );
// 1.3 if not found insert a new record
if (ret != 0)
{
row_count = 1;
m_cursor->put(&key, &data, DB_KEYLAST);
m_distinct_count ++;
}
// 1.4 if found increase the row count
else
{
row_count++;
m_cursor->put(&key, &data, DB_CURRENT);
}

The performance was acceptable until paging started. Then it was very
slow. Easily run for 24 hours.

Did I do anything wrong here?

Thanks

David

David wrote:
Why don't you try writing in batches?
Load a number of records (maybe 10000) into a list, perform
a quicksort on it, then insert into the database. That might
partially approach the sorted case. (Haven't tried this myself yet)

Karl