How to speed up Raven DB query performance?

user_12343064 0
Viewed 138

Could anyone help me speed this query up? I'm using RavenDB 3.5.8 and the following query is taking quite some time (this is exceptionally true on first load):

var query = session
                .Query<Card>()
                .Include(p => p.AuthorId)
                .Include(p => p.CompanyId)
                .Where(x => !x.Id.StartsWith("Archived"))
                .Where(x => !x.IsDeleted);

do
{
    var popStash = stashedResults != null && stashedResults.Count > 0;
    if (popStash)
    {
        stashSkip = stashedResults.Count;
    }

    results = query
        .Statistics(out stats)
        .OrderByDescending(x => x.ModifiedAt)
        .Skip(this.ServerPage * this.ServerPageSize)
        .Take(this.ServerPageSize)
        .Select(MapCard)
        .Where(x => x.IsAuthorized)
        .Skip(clientPage * command.PageSize)
        .Take(command.PageSize - stashSkip)
        .ToList();

    if (popStash)
    {
        results = results.Union(stashedResults).ToList();
        stashedResults.Clear();
    }

    if (results.Count < command.PageSize)
    {
        ++this.ServerPage;
        clientPage = 0;

        if (results.Count > 0)
        {
            stashedResults = results;
        }
    }
    

} while (results.Count < command.PageSize &&
    stats.TotalResults >= (this.ServerPage * this.ServerPageSize) + this.ServerPageSize);

I know it's far from perfect, but note that the double Skip and Take is necessary due to the fact that RavenDB doesn't support authentication by default, so I have to Map each card and add the IsAuthorized field to it with the following code snippet:

IsAuthorized = session.Advanced.IsOperationAllowedOnDocument("Authorization/Users/" + user.Id, "Cards/View", card.Id).IsAllowed;

Basically what happens is that first the query loads a bunch of cards (actually ServerPageSize defines the number of cards there) without considering security and later when security is applied command.PageSize card is loaded. (The webpage dynamically loads the cards - first 50 cards are loaded and after these are viewed another 50 is being grabbed and so on..).

Can someone please gve me hints how to boost the performance of my code?

ravendb c#
1 Answers

You should be able to use the streaming support in order to avoid multiple queries.

You are also querying on a negative, it is far better to query on something positive and then negate.

Note that this means that you are running an enormously expensive SELECT N+1, for each result, you'll get a query.

Why aren't you using the authorization bundle query filtering feature?

See the documentation here:

https://ravendb.net/docs/article-page/3.5/Csharp/server/bundles/authorization#operations

You are missing the call to SecureFor

You can use:

session.SecureFor("Authorization/Users/" + user.Id, "Cards/View");

Now RavenDB will do the filtering server side.

user_63660
Related