Horror perfomance and correct use of indexes

Oct 2, 2014 at 12:10 AM
I already posted a message under issues as it is obvious that queries do not use indexes in case a where clause has more than one property. Now testing with path based queries like

Where(x=>x.Customer.Address.City.Name=="Miami") results in horror perfomance, i.e. 45 seconds search with 100k customers.

Makes sense as we no indexes all traveresed objects have to be scanned. So I tried to set an index on Customer.Address and got a IComparable exception. Implemented such so no exception but even a query like Customer.Address==someAddressToCompare results in the same horror performance.

So how can this be solved to run as expected ?
Coordinator
Oct 2, 2014 at 7:03 AM
Linq to NDatabase is definitely not designed to be performant, for such please use SODA query. Linq aims to be simple and easy, but works only for simple cases.

Besides of that I'm happy to take any pull requests with fixes, if you would like to use linq with high performance and you know how to do that I would appreciate your help.

Thanks,
Jacek
Oct 2, 2014 at 1:06 PM
Thanks Jacek,

I will test with SODA if it performs well. I can do the LINQ stuff, I will definitely also do a better LINQPad provider which is dynamic and does not need a custom context (did so for another OODBMS). This all depends of course on the fact that I get the speed issue resolved first.

Can you please contact me privately ? I need the product for a commercial solution, we need to talk about license etc.

Joe
Oct 2, 2014 at 1:35 PM
Ok update, unfortunately SODA makes it FAR WORSE. It does not use indexes at all, for code below I get 23 SECONDS!! with SODA and 475 ms with LINQ.

Even though the LINQ is much faster it is still to slow. An index lookup query should be in the single digit MSec range.

I will be able to fix this by altering (or possible rewriting) the LINQ provider, but this is not a trivial task. One possible solution would be to use the db as in memory for the time being, but it looks like there is no way to open an existing DB and to sync an in memory DB with the disk. If there is how can this be used ?
        string lDBURL =@"C:\databases\DBTest.db";
        using (IOdb odb = OdbFactory.Open(lDBURL))
        {
            IQuery query = odb.Query<Party>();
            StopWatch.Reset();
            StopWatch.Start();
            query.Descend("name").Constrain("Foo88888").Equal();
            IObjectSet<Party> result = query.Execute<Party>();
            int lCount = result.Count;
            StopWatch.Stop();
            StopWatch.ElapsedTimeInMilliSecondsToConsole("Time to count via SODA Nr Objects:" + lCount.ToString());
            //
            StopWatch.Reset();
            StopWatch.Start();
            odb.AsQueryable<Party>().Where(x => x.Name == "Foo88888").Count();
            StopWatch.Stop();
            StopWatch.ElapsedTimeInMilliSecondsToConsole("Time to count via LINQ Nr Objects:" + lCount.ToString());
        }