Results 1 to 8 of 8

Thread: Several tables in one DB

  1. #1
    Junior Member
    Join Date
    Mar 2011
    Posts
    4

    Default Several tables in one DB

    If there is one table in DB, insert 20000 records takes about 0.100mls.
    Why insertion time increase, if we have for example 10 tables and trying insert data in to one of them?

  2. #2

    Default

    The insertion time should not be increased if you have 10 tables and insert into one of them only. The insertion time will be increased, if you insert into 10 tables simultaneously.

  3. #3

    Default

    Quote Originally Posted by a.todorov View Post
    The insertion time should not be increased if you have 10 tables and insert into one of them only. The insertion time will be increased, if you insert into 10 tables simultaneously.
    I made tests and results are:

    Database with 1 table insert 100 records took 1.56ms
    Database with 5 tables insert 100 records to one table took 38ms
    Database with 10 tables insert 100 records to one table took 68.4ms
    Database with 20 tables insert 100 records to one table took 116ms
    Database with 50 tables insert 100 records to one table took 269ms
    Database with 100 tables insert 100 records to one table took 534ms
    Database with 200 tables insert 100 records to one table took 1103ms
    Database with 500 tables insert 100 records to one table took 3132ms
    Database with 1000 tables insert 100 records to one table took 7888ms

    Test sequence:
    1. Generate 100 records List<>
    2. In cycle every second insert 100 records to table then Commit
    3. Results are average from 10 values.

    When inserting to table there is no action with other tables. Other tables are committed this time. Test program holds tables open - it is mean no OpenTabel member calls. Inserts to separate tables are not related - not in one transaction.

    Why count of tables in database affects insert time to separate table?

    Regards,
    Saulius

  4. #4

    Default

    Can you post your test code?

  5. #5

    Default

    Record Class:
         public class DataItemSerializable2
        {
            public int ID { get; set; }
            public string Name { get; set; }
            public int Quality { get; set; }
            public DateTime Timestamp { get; set; }
            public double Value { get; set; }
        }
    
    Insert Data to all tables one by one:

     
             for (int i = 0; i < tablesCount; i++)
            {
                foreach (DataItemSerializable2 item in dataPackage)
                {
                    AddRecordToTable("Locator" + i.ToString(), item, currentTEIndex[i]++);
                }
                CommitTable("Locator" + i.ToString());
            }
    
    AddRecordToTable Memeber:

             public void AddRecordToTable(string TableName, DataItemSerializable2 item, int nextKey)
            {
                if (!dbParamTables.ContainsKey(TableName))
                {
                    XTable<int, DataItemSerializable2> table = storage.Scheme.CreateOrOpenXTable<int, DataItemSerializable2>(new Locator(TableName.ToString()));
                    table.CacheCapacity = 0;
                    if (this.checkBoxCache.Checked)
                    {
                        table.CacheCapacity = 1;
                    }
    
                    storage.Scheme.Commit();
    
    
                    dbParamTables.Add(TableName, table);
                }
                dbParamTables[TableName][nextKey] = item;
            }
    
    Commit table method:

             public void CommitTable(string TableName)
            {
                if (dbParamTables.ContainsKey(TableName))
                    dbParamTables[TableName].Commit();
            }
    
    Main Testing Class Members:

            private static StorageEngine storage;
            private string fileName;
            private List<Row<Locator, SchemeRecord>> openTableLocators;
            private Dictionary<string, XTable<int, DataItemSerializable2>> dbParamTables;
    
            List<DataItemSerializable2> dataPackage = new List<DataItemSerializable2>();
            List<int> currentTEIndex;
    

  6. #6

    Default

    I'm not sure how to reproduce your case. All insertion times in the bellow code are correlative.

        private static StorageEngine storage;
        private string fileName = "test.stsdb";
        private List<Row<Locator, SchemeRecord>> openTableLocators = new List<Row<Locator,SchemeRecord>>();
        private Dictionary<string, XTable<int, DataItemSerializable2>> dbParamTables = new Dictionary<string,XTable<int,DataItemSerializable2>>();
        private List<DataItemSerializable2> dataPackage = new List<DataItemSerializable2>();
        private List<int> currentTEIndex = new List<int>();
    
        private void MainForm_Load(object sender, EventArgs e)
        {
            //init some records
            for (int i = 0; i < 100; i++)
            {
                DataItemSerializable2 rec = new DataItemSerializable2();
                rec.ID = i;
                rec.Name = i.ToString();
                rec.Quality = i;
                rec.Timestamp = DateTime.Now;
                rec.Value = 0.0001 * i;
    
                dataPackage.Add(rec);
            }
    
            checkBoxCache.Checked = false;
    
            foreach (var tablesCount in new int[] { 1, 5, 10, 20, 50, 100, 200, 500, 1000 })
            {
                openTableLocators.Clear();
                dbParamTables.Clear();
                currentTEIndex.Clear();
                File.Delete(fileName);
                storage = StorageEngine.FromFile(fileName);
                try
                {
                    InsertData(tablesCount);
                }
                finally
                {
                    storage.Dispose();
                }
            }
        }
    
        private void InsertData(int tablesCount)
        {
            Stopwatch sw = new Stopwatch();
    
            for (int i = 0; i < tablesCount; i++)
            {
                sw.Reset();
                sw.Start();
    
                currentTEIndex.Add(0);
    
                foreach (DataItemSerializable2 item in dataPackage)
                {
                    AddRecordToTable("Locator" + i.ToString(), item, currentTEIndex[i]++);
                }
                CommitTable("Locator" + i.ToString());
    
                sw.Stop();
                Console.WriteLine(String.Format("[test with {0} tables] Insert into Locator{1}, {2} msec", tablesCount, i, sw.ElapsedMilliseconds));
            }
        }
    
        public void AddRecordToTable(string TableName, DataItemSerializable2 item, int nextKey)
        {
            if (!dbParamTables.ContainsKey(TableName))
            {
                XTable<int, DataItemSerializable2> table = storage.Scheme.CreateOrOpenXTable<int, DataItemSerializable2>(new Locator(TableName.ToString()));
                table.CacheCapacity = 0;
                if (this.checkBoxCache.Checked)
                {
                    table.CacheCapacity = 1;
                }
    
                storage.Scheme.Commit();
                dbParamTables.Add(TableName, table);
            }
    
            dbParamTables[TableName][nextKey] = item;
        }
    
        public void CommitTable(string TableName)
        {
            if (dbParamTables.ContainsKey(TableName))
                dbParamTables[TableName].Commit();
        }
    
        public class DataItemSerializable2
        {
            public int ID { get; set; }
            public string Name { get; set; }
            public int Quality { get; set; }
            public DateTime Timestamp { get; set; }
            public double Value { get; set; }
        }
    

  7. #7

    Default

    Quote Originally Posted by a.todorov View Post
    I'm not sure how to reproduce your case. All insertion times in the bellow code are correlative.
    I think You did not catch point from our explanation, sorry. I will try explain again:

    For example we have 10000 new records
    we can insert them to 1 table and this will take 87 ms
    or we can insert them to 10 tables - 1000 to every table and this will take 234ms
    or we can insert them to 100 tables - 100 in every table and this takes 2109ms
    or 1000 tables and - 10 records which will take more than 20seconds

    Why so big difference there? We are doing commit after inserts to table, can be, that commit time is not linear related to inserted records count and if yes, then could you, please, explain why?

    Regards,
    Saulius
    Last edited by saulius_net; 10.03.2011 at 15:10.

  8. #8

    Default

    Thank you for the explanation. Finally got your question...

    can be, that commit time is not linear related to inserted records count
    Not even a linear related - the commit time of a table does not depend from the number of the inserted records in it. The commit time mostly depends from the table internal cache size (CacheCapacity property).

    The default value of the CacheCapacity property is 8192. Note, that this is not the number of the cached records, the number of the cached records is CacheCapacity x BlockCapacity (8192x1024)!

    It all boils down to setting up the appropriate CacheCapacity value. (Indeed, perhaps the default value is a little bit higher...)

    If you have many tables or just want to have a lighter commit process, set the CacheCapacity to a lower value (as in the example below - but do not set it to zero). And if you have to distribute 100k records into 1k tables, the overhead of the commit method will not overweight so much the actual insert time of the records.

        private List<DataItemSerializable2> dataPackage;
    
        private void MainForm_Load(object sender, EventArgs e)
        {
            const int RECORDS_COUNT = 10000000;
            dataPackage = new List<DataItemSerializable2>(RECORDS_COUNT);
            for (int i = 0; i < RECORDS_COUNT; i++)
            {
                DataItemSerializable2 rec = new DataItemSerializable2();
                rec.ID = i;
                rec.Name = i.ToString();
                rec.Quality = i;
                rec.Timestamp = DateTime.Now;
                rec.Value = 0.0001 * i;
    
                dataPackage.Add(rec);
            }
    
            InsertRecords(10000);
            InsertRecords(100000);
            InsertRecords(1000000);
            InsertRecords(10000000);
        }
    
        private void InsertRecords(int recordsCount)
        {
            File.Delete("test.stsdb");
            using (StorageEngine engine = StorageEngine.FromFile("test.stsdb"))
            {
                Stopwatch sw = new Stopwatch();
    
                var table = engine.Scheme.CreateOrOpenXTable<int, DataItemSerializable2>(new Locator("table"));
                table.CacheCapacity = 64; //default is 8192
                engine.Scheme.Commit();
    
                sw.Start();
                for (int i = 0; i < recordsCount; i++)
                    table[i] = dataPackage[i];
                sw.Stop();
                double speed = Math.Round(1000.0 * recordsCount / sw.ElapsedMilliseconds);
                Console.WriteLine(String.Format("Insert {0} records - {1} msec, {2} rec/sec", table.Count, sw.ElapsedMilliseconds, speed));
    
                sw.Reset();
                sw.Start();
                table.Commit();
                sw.Stop();
                Console.WriteLine(String.Format("Commit {0} records - {1} msec.", table.Count, sw.ElapsedMilliseconds));
            }
        }
    
        public class DataItemSerializable2
        {
            public int ID { get; set; }
            public string Name { get; set; }
            public int Quality { get; set; }
            public DateTime Timestamp { get; set; }
            public double Value { get; set; }
        }
    
    The results are:

    Insert 10000 records - 116 msec, 86207 rec/sec
    Commit 10000 records - 69 msec.

    Insert 100000 records - 661 msec, 151286 rec/sec
    Commit 100000 records - 86 msec.

    Insert 1000000 records - 7192 msec, 139043 rec/sec
    Commit 1000000 records - 85 msec.

    Insert 10000000 records - 72884 msec, 137204 rec/sec
    Commit 10000000 records - 94 msec.

    (the tests are maked on: Core2 Duo E6400 2.1Ghz, 2GB ram, 7200 hdd)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
2002 - 2014 STS Soft SC. All Rights reserved.
STSdb, Waterfall Tree and WTree are registered trademarks of STS Soft SC.