Retrieving and updating records in bulk

When retrieving or modifying large numbers of records, the number of method calls can often dominate performance. Berkeley DB offers bulk get, put and delete interfaces which can significantly increase performance for some applications.

Bulk retrieval

To retrieve records in bulk, an application buffer must be specified to the DB->get() or DBC->get() methods. This is done in the C API by setting the data and ulen fields of the data DBT to reference an application buffer, and the flags field of that structure to DB_DBT_USERMEM. In the Berkeley DB C++ and Java APIs, the actions are similar, although there are API-specific methods to set the DBT values. Then, the DB_MULTIPLE or DB_MULTIPLE_KEY flags are specified to the DB->get() or DBC->get() methods, which cause multiple records to be returned in the specified buffer.

The difference between DB_MULTIPLE and DB_MULTIPLE_KEY is as follows: DB_MULTIPLE returns multiple data items for a single key. For example, the DB_MULTIPLE flag would be used to retrieve all of the duplicate data items for a single key in a single call. The DB_MULTIPLE_KEY flag is used to retrieve multiple key/data pairs, where each returned key may or may not have duplicate data items.

Once the DB->get() or DBC->get() method has returned, the application will walk through the buffer handling the returned records. This is implemented for the C and C++ APIs using four macros: DB_MULTIPLE_INIT, DB_MULTIPLE_NEXT, DB_MULTIPLE_KEY_NEXT, and DB_MULTIPLE_RECNO_NEXT. For the Java API, this is implemented as three iterator classes: MultipleDataEntry, MultipleKeyDataEntry, and MultipleRecnoDataEntry.

The DB_MULTIPLE_INIT macro is always called first. It initializes a local application variable and the data DBT for stepping through the set of returned records. Then, the application calls one of the remaining three macros: DB_MULTIPLE_NEXT, DB_MULTIPLE_KEY_NEXT, and DB_MULTIPLE_RECNO_NEXT.

If the DB_MULTIPLE flag was specified to the DB->get() or DBC->get() method, the application will always call the DB_MULTIPLE_NEXT macro. If the DB_MULTIPLE_KEY flag was specified to the DB->get() or DBC->get() method, and the underlying database is a Btree or Hash database, the application will always call the DB_MULTIPLE_KEY_NEXT macro. If the DB_MULTIPLE_KEY flag was specified to the DB->get() or DBC->get() method, and the underlying database is a Queue or Recno database, the application will always call the DB_MULTIPLE_RECNO_NEXT macro. The DB_MULTIPLE_NEXT, DB_MULTIPLE_KEY_NEXT, and DB_MULTIPLE_RECNO_NEXT macros are called repeatedly, until the end of the returned records is reached. The end of the returned records is detected by the application’s local pointer variable being set to NULL.

Note that if you want to use a cursor for bulk retrieval of records in a Btree database, you should open the cursor using the DB_CURSOR_BULK flag. This optimizes the cursor for bulk retrieval.

The following is an example of a routine that displays the contents of a Btree database using the bulk return interfaces.

  1. int
  2. rec_display(DB *dbp)
  3. {
  4.     DBC *dbcp;
  5.     DBT key, data;
  6.     size_t retklen, retdlen;
  7.     void *retkey, *retdata;
  8.     int ret, t_ret;
  9.     void *p;
  11.     memset(&key, 0, sizeof(key));
  12.     memset(&data, 0, sizeof(data));
  14.     /* Review the database in 5MB chunks. */
  15. #define    BUFFER_LENGTH    (5 * 1024 * 1024)
  16.     if ((data.data = malloc(BUFFER_LENGTH)) == NULL)
  17.         return (errno);
  18.     data.ulen = BUFFER_LENGTH;
  19.     data.flags = DB_DBT_USERMEM;
  21.     /* Acquire a cursor for the database. */
  22.     if ((ret = dbp->cursor(dbp, NULL, &dbcp, DB_CURSOR_BULK)) 
  23.         != 0) {
  24.             dbp->err(dbp, ret, "DB->cursor");
  25.             free(data.data);
  26.             return (ret);
  27.     }
  29.     for (;;) {
  30.         /*
  31.          * Acquire the next set of key/data pairs.  This code 
  32.          * does not handle single key/data pairs that won't fit 
  33.          * in a BUFFER_LENGTH size buffer, instead returning 
  34.          * DB_BUFFER_SMALL to our caller.
  35.          */
  36.         if ((ret = dbcp->get(dbcp,
  37.             &key, &data, DB_MULTIPLE_KEY | DB_NEXT)) != 0) {
  38.             if (ret != DB_NOTFOUND)
  39.                 dbp->err(dbp, ret, "DBcursor->get");
  40.             break;
  41.         }
  43.         for (DB_MULTIPLE_INIT(p, &data);;) {
  44.             DB_MULTIPLE_KEY_NEXT(p,
  45.                 &data, retkey, retklen, retdata, retdlen);
  46.             if (p == NULL)
  47.                 break;
  48.             printf("key: %.*s, data: %.*s\n",
  49.                 (int)retklen, (char *)retkey, (int)retdlen, 
  50.                 (char *)retdata);
  51.         }
  52.     }
  54.     if ((t_ret = dbcp->close(dbcp)) != 0) {
  55.         dbp->err(dbp, ret, "DBcursor->close");
  56.         if (ret == 0)
  57.             ret = t_ret;
  58.     }
  60.     free(data.data);
  62.     return (ret);
  63. }

Bulk updates

To put records in bulk with the btree or hash access methods, construct bulk buffers in the key and data DBT using DB_MULTIPLE_WRITE_INIT and DB_MULTIPLE_WRITE_NEXT. To put records in bulk with the recno or queue access methods, construct bulk buffers in the data DBT as before, but construct the key DBT using DB_MULTIPLE_RECNO_WRITE_INIT and DB_MULTIPLE_RECNO_WRITE_NEXT with a data size of zero;. In both cases, set the DB_MULTIPLE flag to DB->put().

Alternatively, for btree and hash access methods, construct a single bulk buffer in the key DBT using DB_MULTIPLE_WRITE_INIT and DB_MULTIPLE_KEY_WRITE_NEXT. For recno and queue access methods, construct a bulk buffer in the key DBT using DB_MULTIPLE_RECNO_WRITE_INIT and DB_MULTIPLE_RECNO_WRITE_NEXT. In both cases, set the DB_MULTIPLE_KEY flag to DB->put().

A successful bulk operation is logically equivalent to a loop through each key/data pair, performing a DB->put() for each one.

Bulk deletes

To delete all records with a specified set of keys with the btree or hash access methods, construct a bulk buffer in the key DBT using DB_MULTIPLE_WRITE_INIT and DB_MULTIPLE_WRITE_NEXT. To delete a set of records with the recno or queue access methods, construct the key DBT using DB_MULTIPLE_RECNO_WRITE_INIT and DB_MULTIPLE_RECNO_WRITE_NEXT with a data size of zero. In both cases, set the DB_MULTIPLE flag to DB->del(). This is equivalent to calling DB->del() for each key in the bulk buffer. In particular, if the database supports duplicates, all records with the matching key are deleted.

Alternatively, to delete a specific set of key/data pairs, which may be items within a set of duplicates, there are also two cases depending on whether the access method uses record numbers for keys. For btree and hash access methods, construct a single bulk buffer in the key DBT using DB_MULTIPLE_WRITE_INIT and DB_MULTIPLE_KEY_WRITE_NEXT. For recno and queue access methods, construct a bulk buffer in the key DBT using DB_MULTIPLE_RECNO_WRITE_INIT and DB_MULTIPLE_RECNO_WRITE_NEXT. In both cases, set the DB_MULTIPLE_KEY flag to DB->del().

A successful bulk operation is logically equivalent to a loop through each key/data pair, performing a DB->del() for each one.