Results 1 to 6 of 6

Thread: Create index during append

  1. #1
    Junior Member
    Join Date
    Aug 2010
    Posts
    11

    Default Create index during append

    I have tweaked your sample app to increase the number of fields in each object. I notice that if I have a large number of rows it takes a while to partition/create an index on the rows; although the performance is quite good.

    If I know the index or partition I want to create at the time of the append, is there some way I can create the index and/or partition while I am appending? TIA.

  2. #2
    Junior Member
    Join Date
    Aug 2010
    Posts
    11

    Default

    reading through the 'bulk insert' thread I believe I got my answer; to create a composite key during the append operation. I'll test and post back here.

  3. #3

    Default

    There is no special conception of secondary indexes in STSdb. A secondary index of table XTable<TKey, TRecord> by field of type TProperty is just another table, of one of the following forms:

    XTable<XKey<TProperty, ulong>, TKey> index;
    
    or
    XTable<XKey<TProperty, TKey>, bool> index;
    
    (There is no principal difference between the two forms of index table - in general, the first one is more classic, the second one is more compact.)

    Simple example. If you have a table of type XTable<ulong, double>, you can update the index table while appending records to the primary one:

    const int RECORDS_COUNT = 1000000;
    Random random = new Random();
     
    using (StorageEngine stsdb = StorageEngine.FromFile("test.stsdb"))
    {
        var table = stsdb.Scheme.CreateOrOpenXTable<ulong, double>(new Locator("table"));
        var index = stsdb.Scheme.CreateOrOpenXTable<XKey<double, ulong>, bool>(new Locator("index"));
        stsdb.Scheme.Commit();
     
        ulong key = table.Count > 0 ? table.LastRow.Key + 1 : 0;
        for (int i = 0; i < RECORDS_COUNT; i++)
        {
            double value = random.NextDouble();
     
            table[key] = value;
            index[new XKey<double, ulong>(value, key)] = true;
     
            key++;
        }
     
        table.Commit();
        index.Commit();
    }
    
    We recommend the use of this method for small variation of secondary index only, otherwise the index table will be bigger and the operation will be slower. (That's why databases usually recommended to disable indexes before adding the big data, and then enable it.)

    For strongly randomized key values we recommend the update of the index table to occur after you fill the primary one, by using StressBuffer class:

    const int RECORDS_COUNT = 1000000;
    Random random = new Random();
     
    using (StorageEngine stsdb = StorageEngine.FromFile("test.stsdb"))
    {
        var table = stsdb.Scheme.CreateOrOpenXTable<ulong, double>(new Locator("table"));
        var index = stsdb.Scheme.CreateOrOpenXTable<XKey<double, ulong>, bool>(new Locator("index"));
        stsdb.Scheme.Commit();
     
        var buffer = new StressBuffer<XKey<double, ulong>, bool>(stsdb, index.KeyMap, null);
     
        ulong key = table.Count > 0 ? table.LastRow.Key + 1 : 0;
        for (int i = 0; i < RECORDS_COUNT; i++)
        {
            double value = random.NextDouble();
     
            table[key] = value;
            buffer.Add(new XKey<double, ulong>(value, key), true);
     
            key++;
        }
     
        foreach (var row in buffer)
            index[row.Key] = row.Record;
     
        table.Commit();
        index.Commit();
    }
    
    StressBuffer just helps adding records with a great diversity of keys to any XTable. The buffer instance uses a separate file in the internal file system and will be automaticly deleted after the buffer is being iterated (it's data is recycled by the STS file system's garbage collection mechanism).

  4. #4
    Junior Member
    Join Date
    Aug 2010
    Posts
    11

    Default

    excellent. thank you.

  5. #5
    Junior Member
    Join Date
    Aug 2010
    Posts
    11

    Default

    another question arises: if I delete an object, do I need to delete the reference to the object in both tables in order to delete it permanently? TIA

  6. #6

    Default

    As we saying above, there is no special conception of secondary indexes in STSdb.

    If you manually create a secondary index (ie another table) by some field (in primary one), you have to also manually update it. In this sense, if you delete a record in the "primary" table, you have to delete also the relevant record in the "secondary" table.

    More generally, there are no relations between records in the different tables.

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.