Slow query response on graph-like queries.

[IanC]

I wrote a simple test app to test a graph-like query. The writes run at around 500k / sec, but the reads are only about 16k / sec.

Code to write:

    const int run = 1000000;

    IStorageEngine db = STSdb.FromFile("test.stsdb4");
    var col = db.OpenXTable<long, long[]>("graph");

    Random R = new Random(0);

    int max = run;
    long next;

    for (long i = 0; i < run; i++)
    {
        long[] vl = new long[2];
        vl[0] = 1;

        // prevent a reference to self
        do
        {
            next = R.Next(max);
        } while (next == i);

        vl[1] = next;
        col[i] = vl;
    }

    db.Commit();
    db.Dispose();

Code to read:

const int run = 1000000;
IStorageEngine db = STSdb.FromFile("test.stsdb4");

if (db.Exists("graph"))
{
    var col = db.OpenXTable<long, long[]>("graph");
    long[] vl = new long[2];
    long count = 0, trail = 1;

    try
    {
        do
        {
            vl = col[trail];

            if (vl[0] == 1)
            {
                trail = vl[1];
            }
        } while (++count < run);
    }
    catch
    {
    }
}

db.Dispose();

Anything wrong with this code?

[k.dimitrov]

Greetings Ian.

In the following post, section 1, I have described why initial queries are slow and an easy workaround: Slow read performance and other evaluating questions.

[IanC]

Greetings Christian

I added the workaround (foreach read through the table before Commit()) and the speed actually dropped to under 12 k / sec.

foreach (var item in table) { }
 
db.Commit();

Are there other solutions?

[k.dimitrov]

Greetings Christian

I added the workaround (foreach read through the table before Commit()) and the speed actually dropped to under 12 k / sec.

foreach (var item in table) { }
 
db.Commit();

Are there other solutions?

If you are planning on doing mostly random reads/queries with STSdb, I would suggest you to lower the Wtree depth. There is an article in the developer’s guide that explains how this can be achieved: Memory Usage. Keep in mind that lowering the depth of the tree will lower your random insert speed.

There is also a very good explanation on the Wtree depth and optimal settings here: http://stssoft.com/forum/threads/486-I-have-some-questions-about-STSDB?p=1328&viewfull=1#post1328