This project has moved. For the latest updates, please go here.

Api.SetColumns - Error: Data buffer doesn't match column size

May 21, 2012 at 6:45 PM
Edited May 21, 2012 at 6:50 PM

I am trying to write records in ESENT db but getting "Data buffer doesn't match column size" error when Api.JetUpdate is called inside InsertRecords Method. Any idea where am I making mistake?

Also instance.Init method taking 5-6 seconds ... Any way to make this fast... ???

I want to achive 100,000/second writes ... so far I am able to achive 20,000/second INSERTS rate using following code... and if you consider Init overhead its way slower than mentioned in the performance blog.

http://blogs.msdn.com/b/laurionb/archive/2008/11/07/some-basic-esent-performance-measurements.aspx

Here is my sample code

    using System;
    using System.Text;
    using Microsoft.Isam.Esent.Interop;
    using System.Diagnostics;


namespace EsentSample
{
    class EsentTestNP
    {
        const string Directory = "ManagedEsent";

        Instance instance;
        Session session;

        JET_SESID sesid;
        JET_DBID dbid;
        JET_TABLEID tableid;

        private JET_COLUMNID columnid_productid, columnid_productname, columnid_unitprice;

        public void RunTest()
        {
            Stopwatch t1 = Stopwatch.StartNew();
            Console.WriteLine("---------- ESENT TESTING STARTED ----------");
            PerformInit();

            CreateDatabase();

            CreateTable();

            CreateIndex();

            InsertRecords();

            Console.WriteLine("---------- Populate Phase [Took {0}] ms----------", t1.ElapsedMilliseconds);
            t1 = Stopwatch.StartNew();

            ReadRecords();
            
            PerformCleanup();
            Console.WriteLine("---------- DONE ----------");
            Console.ReadLine();
        }

        private void PerformInit()
        {

            ManagedEsentPerf.ManagedEsentPerfTest.DeleteDirectory(Directory);

            Console.WriteLine("Create Instance....{0}", NowStr());

            SystemParameters.CacheSizeMin = 16 * 1024; //16384;

            instance = new Instance("ManagedEsentPerfTest");

            Console.WriteLine("Setting Instance Parameters....{0}", NowStr());

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

            Console.WriteLine("Calling Instance Init....{0}", NowStr());
            // Create the instance, database and table
            Stopwatch t1 = Stopwatch.StartNew();
            instance.Init();
            Console.WriteLine("Instance Init DONE....took {0} ms", t1.ElapsedMilliseconds);
            
            Console.WriteLine("Create Session....{0}", NowStr());
            session = new Session(instance);
            sesid = session.JetSesid;
        }

        private void CreateDatabase()
        {
//-----------------------------
            // Create the database. 
            //-----------------------------
            // To open an existing database use the JetAttachDatabase and 
            // JetOpenDatabase APIs.
            Console.WriteLine("Create Database....{0}", NowStr());
            //Api.JetCreateDatabase(sesid, "edbtest.db", null, out dbid, CreateDatabaseGrbit.OverwriteExisting);
            string database = System.IO.Path.Combine(Directory, "esentperftest.db");
            Api.JetCreateDatabase(session, database, string.Empty, out dbid, CreateDatabaseGrbit.OverwriteExisting);
        }

        private void CreateTable()
        {
            //-----------------------------
            // Create the table. 
            //-----------------------------
            // Meta-data operations are transacted and can be performed concurrently.
            // For example, one session can add a column to a table while another session is reading
            // or updating records in the same table.
            // This table has no indexes defined, so it will use the default sequential index. Indexes
            // can be defined with the JetCreateIndex API.
            Console.WriteLine("Create table....{0}", NowStr());

            Api.JetBeginTransaction(sesid);
            Api.JetCreateTable(sesid, dbid, "table", 0, 100, out tableid);

            //Create new columns
            JET_COLUMNDEF columndef1 = new JET_COLUMNDEF();
            columndef1.coltyp = JET_coltyp.Long;
            Api.JetAddColumn(sesid, tableid, "ProductID", columndef1, null, 0, out columnid_productid);

            columndef1 = new JET_COLUMNDEF();
            columndef1.coltyp = JET_coltyp.Text;
            columndef1.cp = JET_CP.ASCII;
            columndef1.cbMax = 20; //Max 20 chars
            Api.JetAddColumn(sesid, tableid, "ProductName", columndef1, null, 0, out columnid_productname);

            columndef1 = new JET_COLUMNDEF();
            columndef1.coltyp = JET_coltyp.Currency;
            columndef1.cbMax = 8;
            Api.JetAddColumn(sesid, tableid, "UnitPrice", columndef1, null, 0, out columnid_unitprice);

            Api.JetCommitTransaction(sesid, CommitTransactionGrbit.LazyFlush);
        }

        private void CreateIndex() {
            Api.JetBeginTransaction(sesid);

            string definition = "+ProductID\0\0";
            Api.JetCreateIndex(sesid, tableid, "IX_ProductID", CreateIndexGrbit.None, definition, definition.Length, 100);

            Api.JetCommitTransaction(sesid, CommitTransactionGrbit.LazyFlush);
        }

        private void InsertRecords()
        {
            //-----------------------------
            // Insert a record.
            //-----------------------------
            // This table only has one column but a table can have slightly over 64,000
            // columns defined. Unless a column is declared as fixed or variable it won't take any space
            // in the record unless set. An individual record can have several hundred columns set at one
            // time, the exact number depends on the database page size and the contents of the columns.

            
            Stopwatch stopwatch = Stopwatch.StartNew();

            int BatchSize = 100;
            bool flagInTransaction = true;

            var prodID = new Int64ColumnValue {Columnid = columnid_productid};
            var prodName = new StringColumnValue {Columnid = columnid_productname};
            var prodUnitCost = new DoubleColumnValue {Columnid = columnid_unitprice};

            //var columns = new ColumnValue[] {prodID, prodName, prodUn};
            var columns = new ColumnValue[] { prodID};
            

            int totalrows = 100000;
            Console.WriteLine("Insert [{0} rows] started....{1}", totalrows, NowStr());

            for (int i = 0; i < totalrows; i++)
            {
                //Start New Transaction
                Api.JetBeginTransaction(sesid);
                string sprodname = "Prod" + i.ToString();

                
                //Perform record insert
                Api.JetPrepareUpdate(sesid, tableid, JET_prep.Insert);
                
                //--- BULK SET (Fast)-----
                prodID.Value = i; prodName.Value = sprodname; prodUnitCost.Value = 67.99;
                Api.SetColumns(sesid, tableid, columns);
                //--- Column by Column Set (Slow???)
                //Api.SetColumn(sesid, tableid, columnid_productid, i);
                //Api.SetColumn(sesid, tableid, columnid_productname, sprodname, Encoding.ASCII);
                //Api.SetColumn(sesid, tableid, columnid_unitprice, 67.99);
                //--- 

                Api.JetUpdate(sesid, tableid);
                Api.JetCommitTransaction(sesid, CommitTransactionGrbit.LazyFlush);
            }
            Console.WriteLine("Insert [{0} rows] completed....{1}", totalrows, NowStr());

            stopwatch.Stop();
            Console.WriteLine("{0} records inserted in {1} ms", totalrows, stopwatch.ElapsedMilliseconds);
        }

        private void ReadRecords() {
            //-----------------------------
            // Retrieve a column from the record. 
            //-----------------------------
            // Here we move to the first record with JetMove. By using
            // JetMoveNext it is possible to iterate through all records in a table. Use JetMakeKey and
            // JetSeek to move to a particular record.
            Api.JetMove(sesid, tableid, JET_Move.First, MoveGrbit.None);

            //string buffer = Api.TrySeek(sesid, tableid, SeekGrbit.SeekEQ);  
            //Console.WriteLine("{0}", buffer);

        }

        private void PerformCleanup()
        {
            //-----------------------------
            // Terminate ESENT. This performs a clean shutdown.
            //-----------------------------
            Api.JetCloseTable(sesid, tableid);
            Api.JetEndSession(sesid, EndSessionGrbit.None);
            Api.JetTerm(instance);
        }

        public static string NowStr()
        {
            return DateTime.Now.ToString("MM-dd-yyyy hh:mm:ss.fff");
        }
    }
}

 Call my sample code as below

        public static void Main(string[] args) {
            EsentTestNPtest1 = new EsentTestNayan();
            test1.RunTest();
            Console.ReadLine();
        }

 

 


 

Developer
May 22, 2012 at 2:19 AM

Laurion used the C API directly. There will always be overhead of the ManagedEsent layer.

For symmetry's sake, you should probably explicitly detach the database in PerformCleanup.

Is the Init slow just when you initially create the database, or only on subsequent times? If it's slow on subsequent occasions, then you're probably term'ing dirtily. You can confirm with `esentutl -mh esentperftest.db`, and look for the State. For example 'State: Clean Shutdown'.

Updating indices can slow things down. Your code actually has TWO indices -- the implicit one mentioned in your comment in CreateTable(), and the one you explicitly create in CreateIndex(). If you use CreateIndexGrbit.IndexPrimary, then you'll replace the implicit index. Note that you can only do that on the special tableid that's returned from JetCreateTable(). That tableid is opened with exclusive access. Once you fully definie the table, you should close that tableid and open a new one explicitly. (Or you can use the JetCreateTableColumnIndex() API that does it all at once; but you still need to close the tableid that's output in the structure, otherwise you'll be frustrated when you get a table-locked type error later...)

There comes a point in your benchmark where creating all those strings will trigger more Garbage Collections. You could try something like 'sprodname = String.Format( "Prod {0}", i );' to reduce the number of strings you create. Doing "Prod" + i.ToString() will create a string out of the integer, then a new string when it does the concatenation. And in the version you pasted, you end up not using the string (but I'm sure you'll want to use the string in the future...)

For the "Data buffer doesn't match column size" error, it's probably because you defined ProductID as a JET_coltypLong, which is 32bit, but you're using Int64ColumnValue in InsertRecords(). ESE uses Visual C's definition of long (32bit), and C# says that long is 64bit. Nice and confusing, huh? :)

Hope that helps,

-martin

May 22, 2012 at 3:43 AM
Edited May 22, 2012 at 3:44 AM
Awesome... You solved my problem. Long Datatype issue is resolved now.... and my code works faster by changing SetColumn to SetColumns and removing some string concat operation to reduce Garbage collection.

FYI - Since I dont care about recovery... coz I am just creating temp database for Fast Lookup while application is running... I disabled recovery... Now my Init is very fast.

Few parameters I found which affects Performance big times

instance.Parameters.Recovery = false; //By default its True
SystemParameters.CacheSizeMin = 16 * 1024;
instance.Parameters.LogFileSize = 16*1024;
instance.Parameters.LogBuffers = 8 * 1024;

Another expensive call I found is  Api.JetTerm(instance); //I am not calling this either ... I know in real world most of people will call this but my case is different ... I use ESENT for temp lookup... I delete files once done.

Question: Whats the Int64 datatype in Esent... I see  JET_coltyp.Currency ... Can I use that to store 8-Byte Integers?

If you have any other tips to increase performance .... Most welcome!!! Every Milisecond counts :)

Again I am beginners... mopst of settings I changed by doing trial/errors ... Feel Free to comment

Thanks a lot :)

--------- Here is the Updated Code ... Which has achived real good benchmark

using System;
using System.Text;
using Microsoft.Isam.Esent.Interop;
using System.Diagnostics;


namespace EsentSample
{
    class EsentTestNP
    {
        const string Directory = "ManagedEsent";

        Instance instance;
        Session session;

        JET_SESID sesid;
        JET_DBID dbid;
        JET_TABLEID tableid;

        const int totalwritecount = 100000;
        const int totalreadcount = 1000000;

        const bool persistCache = false;

        private JET_COLUMNID columnid_productid, columnid_productname, columnid_unitprice;

        public void RunTest()
        {
            Stopwatch tFull = Stopwatch.StartNew();
            Stopwatch t1 = Stopwatch.StartNew();
            Console.WriteLine("---------- ESENT TESTING STARTED ----------");
            PerformInit();

            CreateDatabase();

            CreateTable();

            CreateIndex();

            InsertRecords();

            Console.WriteLine("---------- Populate Phase [Took {0}] ms----------", t1.ElapsedMilliseconds);
            t1 = Stopwatch.StartNew();

            ReadRecords();

            PerformCleanup();
            Console.WriteLine("---------- DONE [Took {0}] ms----------", tFull.ElapsedMilliseconds);
            Console.ReadLine();
        }

        private void PerformInit()
        {

            ManagedEsentPerf.ManagedEsentPerfTest.DeleteDirectory(Directory);

            Console.WriteLine("Create Instance....{0}", NowStr());

            SystemParameters.CacheSizeMin = 16 * 1024; //16384; // <<<<<<<<<<<< this parameter affect speed

            instance = new Instance("ManagedEsentPerfTest");

            Console.WriteLine("Setting Instance Parameters....{0}", NowStr());

            //// Circular logging, 16MB logfiles, 8MB of log buffer
            instance.Parameters.CircularLog = true;
            instance.Parameters.LogFileSize = 16 * 1024; // in KB
            instance.Parameters.LogBuffers = 8 * 1024; // in 512-byte units // <<<<<<<<<<<< dont set too large
            instance.Parameters.LogFileDirectory = Directory;
            instance.Parameters.TempDirectory = Directory;
            instance.Parameters.Recovery = false; // <<<<<<<<<<<< set to false for speed
            instance.Parameters.CreatePathIfNotExist = true;
            instance.Parameters.SystemDirectory = Directory;

            Console.WriteLine("Calling Instance Init....{0}", NowStr());
            // Create the instance, database and table
            Stopwatch t1 = Stopwatch.StartNew();
            instance.Init();
            Console.WriteLine("Instance Init DONE....took {0} ms", t1.ElapsedMilliseconds);

            Console.WriteLine("Create Session....{0}", NowStr());
            session = new Session(instance);
            sesid = session.JetSesid;
        }

        private void CreateDatabase()
        {
            //-----------------------------
            // Create the database. 
            //-----------------------------
            // To open an existing database use the JetAttachDatabase and 
            // JetOpenDatabase APIs.
            Console.WriteLine("Create Database....{0}", NowStr());
            //Api.JetCreateDatabase(sesid, "edbtest.db", null, out dbid, CreateDatabaseGrbit.OverwriteExisting);
            string database = System.IO.Path.Combine(Directory, "esentperftest.db");
            Api.JetCreateDatabase(session, database, string.Empty, out dbid, CreateDatabaseGrbit.OverwriteExisting);
        }

        private void CreateTable()
        {
            //-----------------------------
            // Create the table. 
            //-----------------------------
            // Meta-data operations are transacted and can be performed concurrently.
            // For example, one session can add a column to a table while another session is reading
            // or updating records in the same table.
            // This table has no indexes defined, so it will use the default sequential index. Indexes
            // can be defined with the JetCreateIndex API.
            Console.WriteLine("Create table....{0}", NowStr());

            Api.JetBeginTransaction(sesid);
            Api.JetCreateTable(sesid, dbid, "table", 0, 100, out tableid);

            //Create new columns
            JET_COLUMNDEF columndef1 = new JET_COLUMNDEF();
            columndef1.coltyp = JET_coltyp.Long; //Int32
            Api.JetAddColumn(sesid, tableid, "ProductID", columndef1, null, 0, out columnid_productid);

            columndef1 = new JET_COLUMNDEF();
            columndef1.coltyp = JET_coltyp.Text;
            columndef1.cp = JET_CP.ASCII;
            columndef1.cbMax = 20; //Max 20 chars
            Api.JetAddColumn(sesid, tableid, "ProductName", columndef1, null, 0, out columnid_productname);

            columndef1 = new JET_COLUMNDEF();
            columndef1.coltyp = JET_coltyp.Currency; //8Bytes Int
            columndef1.cbMax = 8;
            Api.JetAddColumn(sesid, tableid, "UnitPrice", columndef1, null, 0, out columnid_unitprice);

            Api.JetCommitTransaction(sesid, CommitTransactionGrbit.LazyFlush);
        }

        private void CreateIndex()
        {
            Api.JetBeginTransaction(sesid);

            string definition = "+ProductID\0\0";
            Api.JetCreateIndex(sesid, tableid, "IX_ProductID", CreateIndexGrbit.None, definition, definition.Length, 100);

            Api.JetCommitTransaction(sesid, CommitTransactionGrbit.LazyFlush);
        }

        private void InsertRecords()
        {
            Stopwatch stopwatch = Stopwatch.StartNew();

            var prodID = new Int32ColumnValue { Columnid = columnid_productid };
            var prodName = new StringColumnValue { Columnid = columnid_productname };
            var prodUnitCost = new DoubleColumnValue { Columnid = columnid_unitprice };

            //var columns = new ColumnValue[] {prodID, prodName, prodUn};
            var columns = new ColumnValue[] { prodID, prodName, prodUnitCost };

            int totalrows = totalwritecount;//1000000;
            Console.WriteLine("Insert [{0} rows] started....{1}", totalrows, NowStr());

            for (int i = 0; i < totalrows; i++)
            {
                //Start New Transaction
                Api.JetBeginTransaction(sesid);

                //Perform record insert
                Api.JetPrepareUpdate(sesid, tableid, JET_prep.Insert);

                //--- BULK SET (Fast)-----
                //prodID.Value = i;
                //prodName.Value = String.Format("Prod{0}", i); 
                //prodUnitCost.Value = 67.99;

                //Api.SetColumns(sesid, tableid, columns);
                //--- Column by Column Set (Slow???)

                Api.SetColumn(sesid, tableid, columnid_productid, i);
                Api.SetColumn(sesid, tableid, columnid_productname, String.Format("Prod{0}", i), Encoding.ASCII);
                Api.SetColumn(sesid, tableid, columnid_unitprice, 67.99 * i);


                //--- 

                Api.JetUpdate(sesid, tableid);
                Api.JetCommitTransaction(sesid, CommitTransactionGrbit.LazyFlush);
            }
            Console.WriteLine("Insert [{0} rows] completed....{1}", totalrows, NowStr());

            stopwatch.Stop();
            Console.WriteLine("{0} records inserted in {1} ms", totalrows, stopwatch.ElapsedMilliseconds);
        }

        private void ReadRecords()
        {
            //-----------------------------
            // Retrieve a column from the record. 
            //-----------------------------
            // Here we move to the first record with JetMove. By using
            // JetMoveNext it is possible to iterate through all records in a table. Use JetMakeKey and
            // JetSeek to move to a particular record.
            // We need to be on the primary index (which indexes the 'symbol' column).
            //Api.JetSetCurrentIndex(sesid, tableid, null); //Use Primary Index ... e.g. Clustered Index
            Api.JetSetCurrentIndex(sesid, tableid, "IX_ProductID");
            Stopwatch t1 = Stopwatch.StartNew();
            int totalrows = totalreadcount + 100;//1100100;
            int failedCnt = 0;
            int successCnt = 0;
            for (int i = 0; i < totalrows; i++)
            {
                //Api.JetBeginTransaction(sesid); //Incase u want to change something during LookupProcess ... Open Transaction
                if (SeekProductByID(sesid, tableid, i))
                {
                    successCnt++;
                }
                else
                {
                    failedCnt++;
                }
                //Api.JetCommitTransaction(sesid, CommitTransactionGrbit.LazyFlush);
            }
            Console.WriteLine("Total {0} Seeks done in {1} ms. [Success ={2}, Failed={3}], ", totalrows, t1.ElapsedMilliseconds, successCnt, failedCnt);
        }

        private bool SeekProductByID(JET_SESID sesid, JET_TABLEID tableid, int prodID)
        {
            Api.MakeKey(sesid, tableid, prodID, MakeKeyGrbit.NewKey);
            Api.JetMove(sesid, tableid, JET_Move.First, MoveGrbit.None);
            if (Api.TrySeek(sesid, tableid, SeekGrbit.SeekEQ))
            {
                string prodName = (string)Api.RetrieveColumnAsString(sesid, tableid, columnid_productname, Encoding.ASCII);
                double? prodCost = Api.RetrieveColumnAsDouble(sesid, tableid, columnid_unitprice);
                //if (prodID % 100000==0)
                //    Console.WriteLine("Read Name={0}, Cost={1}", prodName, prodCost.ToString());

                return true;
            }
            else
            {
                return false;
            }
        }


        private void PerformCleanup()
        {
            Stopwatch t1 = Stopwatch.StartNew();

            //-----------------------------
            // Terminate ESENT. This performs a clean shutdown.
            //-----------------------------
            Stopwatch t2 = Stopwatch.StartNew();
            Api.JetCloseTable(sesid, tableid);
            Console.WriteLine("CleanUP- JetCloseTable took {0} ms", t2.ElapsedMilliseconds);

            t2 = Stopwatch.StartNew();
            Api.JetEndSession(sesid, EndSessionGrbit.None);
            Console.WriteLine("CleanUP- JetEndSession took {0} ms", t2.ElapsedMilliseconds);

            if (persistCache)
            {
                t2 = Stopwatch.StartNew();
                Api.JetTerm(instance); //Most Expensive //Dont call this if you dont want to Persist Cache for Next run...
                Console.WriteLine("CleanUP- JetTerm took {0} ms", t2.ElapsedMilliseconds);
            }
            Console.WriteLine("Clean up took {0} ms", t1.ElapsedMilliseconds);
        }

        public static string NowStr()
        {
            return DateTime.Now.ToString("MM-dd-yyyy hh:mm:ss.fff");
        }
    }
}

  

Developer
May 23, 2012 at 12:39 AM

If you're just looking for pure perf, you could also try temp tables. http://msdn.microsoft.com/en-us/library/windows/desktop/gg269211(v=exchg.10).aspx

JET_coltypCurrency will work. You can also look in VistaColtyp for the 64bit integer coltyp. Sorry I don't have the source code at the moment. It might be VistaColtyp.LongLong?

I'd still recommend calling JetTerm though. You can use JetTerm2() and use the TermDirty bit (Windows7Grbits). It's still not quite as fast as exiting the process, but it just feels wrong to me not call Term. :)

Increasing the database page size should also help a bit.

-martin

May 23, 2012 at 12:49 PM

Martin thanks a lot for prompt reply ...

I will try your suggesitions... Do you have any link/samples for JetTempTable ... ?

- Nayan

Developer
May 23, 2012 at 6:51 PM

The best examples are usually in the test code. :)

EsentInteropTests\HelpersTempTableFixtureTests.cs
EsentInteropTests\OpenTemporaryTableConversionTests.cs
EsentInteropTests\TemporaryTable2Tests.cs
EsentInteropTests\TemporaryTableTests.cs
EsentInteropTests\TempTableFixture.cs
EsentInteropTests\TempTableNavigationTests.cs

-martin

May 23, 2012 at 7:15 PM

Awesome..... I will try those samples and post my benchmarks

You have been very helpful.... Thanks :)

Nayan