How to improve RavenDB performance?

[Bernardo Quintero Rangel]

Hi guys I'm knew using RavenDB. basically I just started this week. 

My question is. How could I improve the performance on my RavenDB when I have:

The structure of my documents look like this:

{

    "Category": {

        "Name": "TEST CATEGORY 5",

        "id": "797a129c-4e15-4025-bdbb-2faaae9c6762",

        "CustomerId": 4

    },

    "Application": {

        "Name": "Logster.Services.Tests, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null",

        "id": "87d2e9b7-bdbe-46cb-ba22-b6f1d5c35716",

        "CustomerId": 4

    },

    "User": {

        "Name": "logsteruser 5",

        "id": "a0843520-ddea-4ef8-94bc-f039e6d50221",

        "CustomerId": 4

    },

    "Severity": "Warn",

    "Message": "{\"TestMessage\":\"Test message 1000\",\"TestJson\":{\"Type\":\"Request\",\"Method\":\"POST\",\"AbsolutePath\":\"/api/Equipment\",\"AbsoluteUri\":\"http://compairapi-qa.azurewebsites.net/api/Equipment\",\"Headers\":[{\"Key\":\"Cache-Control\",\"Value\":[\"no-cache\"]},{\"Key\":\"Connection\",\"Value\":[\"Keep-Alive\"]},{\"Key\":\"Accept\",\"Value\":[\"*/*\"]},{\"Key\":\"Accept-Encoding\",\"Value\":[\"gzip\",\"deflate\"]},{\"Key\":\"Accept-Language\",\"Value\":[\"en-US\",\"en; q=0.8\"]},{\"Key\":\"Authorization\",\"Value\":[\"bearer j7pxWhWTTeEOg7Z7YQxE836ayiJ4qw3bvVK-vb5mwj19pDmSjN6f75UH0x1R0KGQBEIqbTguF3-LtQ-9tx3x6OwMWZAf0j27KdnxVlFCpdQgwIA0oEZEqsDFnBJK9XazaFYtBprmG5Ba5Sp8KOTKVPS2_FR5NIcpM4wATLdaw9zQu91hk2Umlqu8A7QjpoR9PEbnrDC8geex4dE-VQzu4k1L1lTDGAubMVTcWyJVEBE9Jqv9sgGNbrz_lmWKikyFWjMtMF1-IckKhOP-I3J2k8FSVSfdu9WqEqVU1r0DtLPU__NIyJLWnrK5nU_BGPAvlGRH3ZyZXrUTmEr1iIsMhcSrUs-QB4vBh2xx6BhQFyNpwfappp4KI-6Xbhigy23gTkhVmeqeighASl_qBc-pMp_ct7GpYDN_YibiJ9NGCcK5iQW9l0Tsf7P-jQ_jV6ZcsvPyQGfpbIIsfVVR4tuQ3L8-Exyf7HDH8a75hveyKtlH071Uqrw63tE09a0lZAv-YNAgDWvVPCBWMkgP0yJGa2f_w3C-PJqoCksqo7rlaI3M_DN9n1Fvst7iYyrKzhQP\"]},{\"Key\":\"Cookie\",\"Value\":[\"ARRAffinity=71a987eed3e1325129fdc138ccfdcaf8f58ee2f578a23f608d9aab551841f278\"]},{\"Key\":\"Host\",\"Value\":[\"compairapi-qa.azurewebsites.net\"]},{\"Key\":\"Max-Forwards\",\"Value\":[\"10\"]},{\"Key\":\"User-Agent\",\"Value\":[\"Mozilla/5.0\",\"(Macintosh; Intel Mac OS X 10_11_0)\",\"AppleWebKit/537.36\",\"(KHTML, like Gecko)\",\"Chrome/46.0.2490.71\",\"Safari/537.36\"]},{\"Key\":\"Origin\",\"Value\":[\"chrome-extension://fhbjgbiflinjbdggehcddcbncdddomop\"]},{\"Key\":\"Postman-Token\",\"Value\":[\"9a67a773-8fe4-5a09-4d74-189d96c5ad6b\"]},{\"Key\":\"X-LiveUpgrade\",\"Value\":[\"1\"]},{\"Key\":\"X-ARR-LOG-ID\",\"Value\":[\"571b2be2-ee34-4651-88cb-19c5cdc74cc4\"]},{\"Key\":\"DISGUISED-HOST\",\"Value\":[\"compairapi-qa.azurewebsites.net\"]},{\"Key\":\"X-SITE-DEPLOYMENT-ID\",\"Value\":[\"compairapi-qa\"]},{\"Key\":\"X-Original-URL\",\"Value\":[\"/api/Equipment\"]},{\"Key\":\"X-Forwarded-For\",\"Value\":[\"202.131.107.130:16131\"]}],\"Body\":{\"workOrderId\":9,\"equipment\":[{\"oilFilter2\":\"sample string 15\",\"oilFilter1\":\"sample string 14\",\"recordStatus\":3,\"miscellaneous\":[],\"physicalLocationId\":1,\"workPerformed\":[],\"airFilter1\":\"sample string 12\",\"airFilter2\":\"sample string 13\",\"separator2\":\"sample string 17\",\"make\":\"sample string 3\",\"decomissionDate\":\"0001-01-01T00:00:00Z\",\"separator1\":\"sample string 16\",\"year\":2,\"type\":0,\"warrantyStartDate\":\"0001-01-01T00:00:00Z\",\"lineFilterElements\":\"sample string 19\",\"serialNumber\":\"sample string 5\",\"warrantyEndDate\":\"0001-01-01T00:00:00Z\",\"unitId\":\"1\",\"model\":\"sample string 4\",\"comments\":\"sample string 21\",\"id\":13,\"parts\":[],\"equipmentId\":\"35\",\"installDate\":\"2015-09-16T20:44:15Z\",\"underContract\":1,\"oilType\":\"sample string 18\",\"wosCharcoal\":\"sample string 20\"},{\"oilFilter2\":\"\",\"oilFilter1\":\"\",\"recordStatus\":1,\"miscellaneous\":[],\"physicalLocationId\":1,\"workPerformed\":[],\"airFilter1\":\"\",\"airFilter2\":\"\",\"separator2\":\"\",\"make\":\"Tesla\",\"decomissionDate\":\"0001-01-01T00:00:00Z\",\"separator1\":\"\",\"year\":2025,\"type\":0,\"warrantyStartDate\":\"0001-01-01T00:00:00Z\",\"lineFilterElements\":\"\",\"serialNumber\":\"1234\",\"warrantyEndDate\":\"0001-01-01T00:00:00Z\",\"unitId\":\"1234\",\"model\":\"Tesla Compressor\",\"comments\":\"\",\"id\":22,\"parts\":[],\"equipmentId\":\"48\",\"installDate\":\"2015-10-08T00:00:00Z\",\"underContract\":0,\"oilType\":\"\",\"wosCharcoal\":\"\"},{\"oilFilter2\":\"\",\"oilFilter1\":\"\",\"recordStatus\":2,\"miscellaneous\":[],\"physicalLocationId\":11,\"workPerformed\":[],\"airFilter1\":\"\",\"airFilter2\":\"\",\"separator2\":\"\",\"make\":\"Hp\",\"separator1\":\"\",\"type\":0,\"year\":2015,\"warrantyStartDate\":\"2015-10-19T00:00:00Z\",\"lineFilterElements\":\"\",\"serialNumber\":\"1234\",\"warrantyEndDate\":\"2015-10-19T00:00:00Z\",\"unitId\":\"1234\",\"model\":\"Hp Pivilion\",\"comments\":\"\",\"id\":0,\"parts\":[{\"id\":0,\"partId\":1,\"recordStatus\":2,\"locationId\":null,\"quantity\":34}],\"equipmentId\":23,\"installDate\":\"2015-10-19T00:00:00Z\",\"machineReading\":{\"ampsLeg3Unloaded\":null,\"recordStatus\":2,\"suctionTemp\":null,\"modulatingPressure\":null,\"targetPressure\":null,\"ampsLeg2Unloaded\":null,\"totalHours\":null,\"unloadPressure\":null,\"ambientTemp\":null,\"ampsLeg1Unloaded\":null,\"id\":0,\"voltageLeg3\":67,\"voltageLeg2\":56,\"voltageLeg1\":34,\"ampsLeg1Loaded\":null,\"injectionTemp\":null,\"suctionPressure\":null,\"sumpPressure\":null,\"ampsLeg2Loaded\":null,\"loadPressure\":null,\"ampsLeg3Loaded\":null,\"dischargePressure\":null,\"dischargeTemp\":null,\"exchangerTemp\":null,\"loadedHours\":null},\"underContract\":1,\"oilType\":\"\",\"wosCharcoal\":\"\"}]},\"Version\":\"1.0.169.1\"}}",

    "ShortMessage": "{\"TestMessage\":\"Test message 1000\",\"TestJson\":{\"Type\":\"Request\",\"Method\":\"POST\",\"AbsolutePath\":\"/api",

    "StackTrace": "   at Logster.Services.Tests.LogServiceFixture.LogMessage(String message) in test.cs:line 42\r\n 0005",

    "Exception": "System.Exception: Test Exception ---> System.Exception: Inner\r\n   --- End of inner exception stack trace ---",

    "IsResolved": false,

    "TimeStamp": "2017-01-19T20:36:33.6406297Z",

    "id": "f6aa93ef-d894-4d21-9cad-fa2c3b7ad10c",

    "CustomerId": 4

}

There are other collections for Category, Application and User, those ones are basically an id, a name  and a costumerID.

Then I have a repository in .Net that looks like this:

public class RavenRepository : IRavenRepository

    {

        private readonly IDocumentSession _session;

        private readonly IDocumentStore _store;

        public RavenRepository(IDocumentStore store)

        {

            _store = store;

            _session = _store.OpenSession();

        }

 .....

 public IQueryable<T> GetWhere<T>(Expression<Func<T, bool>> predicate)

        {

            return _session.Query<T>().Where(predicate).OfType<T>();

        }

public IQueryable<T> SearchValue<T>(Expression<Func<T, object>> predicate, string value)

        {

            return _session.Query<T>().Search(predicate, value).OfType<T>();

        }

}

I'm initializing my dcoument Store using Unity Config in my web project.

Now, In my service I have a query that look like this

 // Building the predicate for querying on RavendDB

            var predicateList = new List<Expression<Func<Log, bool>>>();

            if (categoryId != null)

                predicateList.Add(p => p.Category.Id == categoryId);

            if (applicationId != null)

                predicateList.Add(p => p.Application.Id == applicationId);

            if (userId != null)

                predicateList.Add(p => p.User.Id == userId);

            if (severityId != null)

                predicateList.Add(p => (int) p.Severity == severityId);

            if (startDate != null)

                predicateList.Add(p => p.TimeStamp >= startDate);

            if (endDate != null)

                predicateList.Add(p => p.TimeStamp <= endDate);

            // The customerId and a min date will be the only filter that is required

            var query = _ravenRepository.GetWhere<Log>(r => r.CustomerId == customerId && r.TimeStamp >= minDate);

            // Filtering the query from the filters received except for filters where they will be used as a contains

            foreach (var predicate in predicateList)

            {

                query = query.Where(predicate);

            }

            var resultDb = query.OrderByDescending(r => r.TimeStamp).Skip((pageNumber - 1) * rowsPerPage).Take(rowsPerPage)

                                .Select(log => new LogGridModel

                                                   {

                                                       Category = log.Category.Name,

                                                       Application = log.Application.Name,

                                                       Message = log.ShortMessage,

                                                       Exception = log.Exception,

                                                       IsResolved = log.IsResolved,

                                                       Severity = log.Severity,

                                                       Timestamp = log.TimeStamp,

                                                       Id = log.Id

                                                   }).AsEnumerable().ToList();

And there is my issue, It looks like my query is fast but it's not fast enough if I compare it to SQL. RavenDb spend 1000 ms and it goes lower up to 980ms, Sql spend the first time around 900ms and then goes down to 250ms. 

How I'm testing is with 5000 of documents inserted and using the Micro plan which is using 64 MB. I was wondering if I have an enterprise plan that will help my performance knowing that we will be having around 3.000.000 documents in the future.

Also, is there a way to make this query faster? By the way the index in ravenDB studio looks like this

from doc in docs.Logs

select new {

Application_id = doc.Application.id,

CustomerId = doc.CustomerId,

TimeStamp = doc.TimeStamp,

Category_id = doc.Category.id,

Severity = doc.Severity,

User_id = doc.User.id,

Exception = doc.Exception,

Message = doc.Message,

StackTrace = doc.StackTrace

}

[Johannes Rudolph]

Are you talking about RavenHQ? Certainly, the larger plans will give you much better performance. From my experience, the performance on Micro is abysmal and only suitable for very small workloads.

[Oren Eini (Ayende Rahien)]

A) Why do you have the Message property as a JSON string? It is much better if it was an actual object.

B) It seems like you are using RavenHQ, if so, you are actually running on a throttled env and shouldn't be using that to run any benchmarks.

Hibernating Rhinos Ltd  

Oren Eini l CEO l Mobile: + 972-52-548-6969

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

 

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kijana Woodard]

Perf suggestions 

- try defining a static index.

- what does performance look like locally?

Side notes...

- You might find it *much* nicer to create an extension method to take care of the predicate building. Usually in a project I have some like `WhereIf` on IRavenQueryable. That way you can do `session......WhereIf()...WhereIf()... If you run into trouble, I'll dig out an example.

- You probably don't want to store bearer tokens in a log file/database.

 

- I suppose it's obligatory for me to suggest avoiding the repository pattern: https://www.youtube.com/watch?v=BruPcBDoXZw