Dec 31, 2010 at 12:10 AM

I have been trying out Esent via the managed interface on Windows XP with .Net 3.5. The performance I am seeing is not what I would have expected. Everything seems to work; it's just slow.

I have a table with 2 columns: key:currency, value:longbinary. The key column is indexed primary key, but not auto-increment. I am inserting 100,000 records with incrementing key values from 1-100,000 and the value set to 1,000 bytes, all zeros. I get about 2500 inserts per second. If I randomly seek to all the records (100,000 seeks), I get about 500 seeks per second.

Would it be possible to post the code that was used to get the performance results listed in the documentation to get a baseline for comparison?

Dec 31, 2010 at 1:45 AM

Have you seen this StackOverflow question dealing with performance?

500 seeks per second is really slow, suggesting that the database isn't cached. By default, the cache size is managed automatically and will grow too slowly to give good performance results instantly. By setting a minimum cache size you should get much better performance. You can see that in the code below that sets cache size min.

Here is an extract of a simple perf test.

// --------------------------------------------------------------------------------------------------------------------
// <copyright file="ManagedEsentPerfTest.cs" company="Microsoft">
//   Copyright (c) Microsoft Corporation.
// </copyright>
// <summary>
//   Simple performance test for the ManagedEsent interop layer.
// </summary>
// --------------------------------------------------------------------------------------------------------------------

namespace ManagedEsentPerf
    using System;
    using System.Diagnostics;
    using System.IO;
    using System.Linq;
    using Microsoft.Isam.Esent.Interop;

    /// <summary>
    /// Basic performance tests for ManagedEsent
    /// </summary>
    public class ManagedEsentPerfTest
        /// <summary>
        /// Directory for the database, logfiles and temporary database.
        /// </summary>
        private const string Directory = "ManagedEsent";

        /// <summary>
        /// Size of binary data to insert.
        /// </summary>
        private const int DataSize = 32;

        /// <summary>
        /// Previous value of the cache min size.
        /// </summary>
        private int cacheSizeMinSaved = 0;

        private JET_COLUMNID columnidData;
        private JET_COLUMNID columnidKey;

        /// <summary>
        /// Buffer used to insert records
        /// </summary>
        private byte[] data;

        /// <summary>
        /// Buffer used to retrieve records.
        /// </summary>
        private byte[] dataBuf;

        private Instance instance;
        private long nextKey = 0;

        private Random random;
        private Session session;
        private Table table;

        public static void RunTest()
            var test = new ManagedEsentPerfTest();

        /// <summary>
        /// Setup for a test -- this creates the database
        /// </summary>
        private void Setup()
            this.random = new Random();
   = new byte[DataSize];

            this.dataBuf = new byte[DataSize];

            JET_DBID dbid;

            this.cacheSizeMinSaved = SystemParameters.CacheSizeMin;
            SystemParameters.CacheSizeMin = 16384;

            this.instance = new Instance("ManagedEsentPerfTest");

            // Circular logging, 16MB logfiles, 8MB of log buffer
            this.instance.Parameters.CircularLog = true;
            this.instance.Parameters.LogFileSize = 16 * 1024; // in KB
            this.instance.Parameters.LogBuffers = 16 * 1024; // in 512-byte units
            this.instance.Parameters.LogFileDirectory = Directory;
            this.instance.Parameters.TempDirectory = Directory;
            this.instance.Parameters.SystemDirectory = Directory;
            this.instance.Parameters.CreatePathIfNotExist = true;

            // Create the instance, database and table
            this.session = new Session(this.instance);
            string database = Path.Combine(Directory, "esentperftest.db");
            Api.JetCreateDatabase(this.session, database, string.Empty, out dbid, CreateDatabaseGrbit.None);

            // Create the table
            using (var trx = new Transaction(this.session))
                JET_TABLEID tableid;
                var columndef = new JET_COLUMNDEF();

                Api.JetCreateTable(this.session, dbid, "table", 0, 100, out tableid);
                columndef.coltyp = JET_coltyp.Currency;
                Api.JetAddColumn(this.session, tableid, "Key", columndef, null, 0, out this.columnidKey);
                columndef.coltyp = JET_coltyp.Binary;
                Api.JetAddColumn(this.session, tableid, "Data", columndef, null, 0, out this.columnidData);
                Api.JetCreateIndex(this.session, tableid, "primary", CreateIndexGrbit.IndexPrimary, "+key\0\0", 6, 100);
                Api.JetCloseTable(this.session, tableid);

            this.table = new Table(this.session, dbid, "table", OpenTableGrbit.None);

        /// <summary>
        /// Cleanup after the test
        /// </summary>
        private void Teardown()
            SystemParameters.CacheSizeMin = this.cacheSizeMinSaved;

        /// <summary>
        /// Test inserting and retrieving records.
        /// </summary>
        private void BasicPerfTest()

        private void InsertReadSeek()
            const int NumRecords = 1000000;

            // Randomly seek to all records in the table
            long[] keys = (from x in Enumerable.Range(0, NumRecords) select (long) x).ToArray();

            Util.TimeAction("Insert records", () => this.InsertRecords(NumRecords));
            Util.TimeAction("Read one record", () => this.RepeatedlyRetrieveOneRecord(NumRecords));
            Util.TimeAction("Read all records", this.RetrieveAllRecords);
            Util.TimeAction("Seek to all records", () => this.SeekToAllRecords(keys));

        private void InsertRecord()
            long key = this.nextKey++;
            Api.JetPrepareUpdate(this.session, this.table, JET_prep.Insert);
            Api.SetColumn(this.session, this.table, this.columnidKey, key);
            Api.SetColumn(this.session, this.table, this.columnidData,;
            Api.JetUpdate(this.session, this.table);

        private void InsertRecords(int numRecords)
            for (int i = 0; i < numRecords; ++i)
                Api.JetCommitTransaction(this.session, CommitTransactionGrbit.LazyFlush);

        private void RetrieveRecord()
            BitConverter.ToInt64(Api.RetrieveColumn(this.session, this.table, this.columnidKey), 0);
            Api.RetrieveColumn(this.session, this.table, this.columnidData);

            ////int actualSize;
            ////Api.RetrieveColumnAsInt64(this.session, this.table, this.columnidKey);
            ////    this.session,
            ////    this.table,
            ////    this.columnidData,
            ////    this.dataBuf,
            ////    this.dataBuf.Length,
            ////    out actualSize,
            ////    RetrieveColumnGrbit.None,
            ////    null);

        private void RetrieveAllRecords()
            Api.MoveBeforeFirst(this.session, this.table);
            while (Api.TryMoveNext(this.session, this.table))
                Api.JetCommitTransaction(this.session, CommitTransactionGrbit.LazyFlush);

        private void RepeatedlyRetrieveOneRecord(int numRetrieves)
            Api.JetMove(this.session, this.table, JET_Move.First, MoveGrbit.None);
            for (int i = 0; i < numRetrieves; ++i)
                Api.JetCommitTransaction(this.session, CommitTransactionGrbit.None);

        private void SeekToAllRecords(long[] keys)
            foreach (long key in keys)
                Api.MakeKey(this.session, this.table, key, MakeKeyGrbit.NewKey);
                Api.JetSeek(this.session, this.table, SeekGrbit.SeekEQ);
                long actual = (long)Api.RetrieveColumnAsInt64(this.session, this.table, this.columnidKey);
                Debug.Assert(actual == key, "landed on wrong record");
                Api.JetCommitTransaction(this.session, CommitTransactionGrbit.None);

Jan 1, 2011 at 8:05 PM

Thank you for the code. The main difference between your code and the code I had written is that I was using LongBinary with data size of 1000 bytes. It appears that this has a pretty significant impact on performance.

Here are some numbers that I got on a Vista x64 system (2.66GHz*4, 6GB RAM) using Binary and LongBinary with different data sizes. The most striking number is the seek to all records for LongBinary(1000) where it took 1 hour to complete. This equates to ~267 records/second. There is even a pretty significant  change when going from 32 to 256 bytes.

I don't know anything about Esent, but what is the reason for this poor performance? I wouldn't consider 1000 bytes to be large, but I could be wrong.

Thank you for your assistance.

------ Binary(32) -------
Insert records: 00:00:10.2307263 (97744.7710628326 records/second)
Read one record: 00:00:01.5739620 (635339.35380905 records/second)
Read all records: 00:00:02.0603314 (485358.81169408 records/second)
Seek to all records: 00:00:03.5729066 (279884.170495809 records/second)

------ LongBinary(1000) -------
Insert records: 00:01:54.7596813 (8713.86177333345 records/second)
Read one record: 00:00:02.5612831 (390429.312558225 records/second)
Read all records: 00:00:24.5874133 (40671.2161136528 records/second)
Seek to all records: 01:02:26.0555512 (266.947456152823 records/second)

------ LongBinary(256) -------
Insert records: 00:00:31.1506657 (32102.0426860412 records/second)
Read one record: 00:00:01.7356263 (576160.893620937 records/second)
Read all records: 00:00:07.8018985 (128173.930998974 records/second)
Seek to all records: 00:03:26.6863583 (4838.24867894051 records/second)

------ LongBinary(32) -------
Insert records: 00:00:11.4143275 (87609.1911678546 records/second)
Read one record: 00:00:01.6562408 (603776.938715675 records/second)
Read all records: 00:00:02.1447044 (466264.721609188 records/second)
Seek to all records: 00:00:03.6208912 (276175.102969125 records/second)

------ Binary(256) -------
Insert records: 00:00:30.1100588 (33211.4927653346 records/second)
Read one record: 00:00:02.0563684 (486294.187364482 records/second)
Read all records: 00:00:08.4960491 (117701.767990018 records/second)
Seek to all records: 00:03:47.0655565 (4404.01448556994 records/second)

Jan 1, 2011 at 8:23 PM

It looks like you will have around a 1GB database with 1,000 byte columns. If the database isn't cached it will take 1-2 disk reads to retrieve a disk record, which would give a few hundred seeks/second on a single, non-SSD disk. If you have a big database cache your seek speed will increase as more of the database is cached.

Try using SetColumnGrbit.IntrinsicLV and SetColumnGrbit.SeparateLV. Those control whether LongBinary columns are stored inline with the record or in a separate b-tree. Using separate LVs should increase seek speed, but decrease retrieval speed. (by default long-values are stored separately once they are bigger than 1024 bytes).