Extremely slow response times from server, fine from localhost

Hey,

So I seem to be having an issue where queries to Appwrite are working fine from localhost, however they are very slow on times from the server. Not every time, but it also doesn't appear to be a cold start sort of thing either.

These may be queries to fetch a user session, or fetch data from a database collection generally, as these are the 2 main things happening when I load the page.

In terms of database stuff, I have optimised the requests already with:

• useful indexes on the collections for what I am querying
• useful limit queries (often a limit of 1)
• Where possible, only requesting certain attributes from a document to minimize data being sent

Now, on the particular project my website is linked to for testing on the server, I have a total of 7 documents and 2 users, so there should be no reason for large delays.

Querying locally I only ever see decent response times. However on the server, I am seeing requests take many seconds. The most recent example from my server logs, being a query to my "Polls" collection, which took 27,054 ms to complete. This is a request which happens on page load, and it is seriously hampering my webs app performance.

The query I am running for that specific request, is the following (using .net SDK):

var now = DateTime.UtcNow;

var result = await _serverAppwriteClient.Databases.ListDocuments<Poll>(new()
{
    DatabaseId = AppwriteIds.Database.Id,
    CollectionId = AppwriteIds.Database.Collections.Polls,
    Queries = [
        Query.Limit(1),
        Query.LessThan(nameof(Poll.StartTime), now),
        Query.GreaterThanEqual(nameof(Poll.EndTime), now),
        Query.OrderDesc(nameof(Poll.StartTime)),
        Query.OrderDesc("$createdAt")
    ]
});

Currently, that collection has 1 document, which does not match the filtering (EndTime is before now). I have 3 indexes on the collection, one for just StartTime, one for just EndTime and another for StartTime and EndTime combined.

I don't really know how to investigate much further as I can't see the issue locally at all.

Project ID is 676a24200030ba9ffc59 - Not sure if you are able to see anything relating to slow requests there?

My server is an Azure app service application, and its hosted in their UKSOUTH region, so distance from server shouldn't be an issue either.

Can see the sporadic slow loading times here: https://wrevo-dev.azurewebsites.net/ - Load the homepage, it may load in a decent amount of time, it may take ages. The worst I've had is around 3 minutes, other times its all done under a second. There are many things loading here of course, but the thing to look for is the time to load that initial request of the root document - that is the thing being held up predominantly by the requests server is making in an attempt to pre-render.