Niql query time difference

Couchbase Server
1

Hi all, I know the answer might be obvious, but when querying with N1QL the metrics state the query takes 2ms however i am often seeing 60ms+ times when running the below code in .NET

var sw = new Stopwatch();
sw.Reset();
sw.Start();
var query = bucket.Query(@“SELECT * FROM default WHERE sportName = ‘AFL’”);
sw.Stop();
var a = sw.ElapsedMilliseconds;

I am running single node on localhost so I dont think its network slow down or anything… is there anything obvious which can account for the delta?? or is this normal?

On a side note, a very similar query using Linq2couchbase takes a significant amount of time more:
var sw = new Stopwatch();
sw.Reset();
sw.Start();
var query = db.Query().Where(p => p.SportName == “AFL”);
var result = !(query.Rows.IsNullOrEmpty());
sw.Stop();
var a = sw.ElapsedMilliseconds;

Should these not be essentially the same query with similar execution times?

2

How long do the queries take in cbq shell or Query Workbench?

3

Hi @calhamd

The metrics in the resultset only includes only the server query processing time.
It doesn’t include the time taken by SDK to submit and the result transport. Hence, there will be a difference.

If the resultset is quite large, the difference can be higher has well.

4

In addition to what @keshav_m noted, the .NET SDK intentionally pipelines requests to be efficient in resource usage at the app and cluster. If you have a high concurrency, network transport time and queuing for that pipeline may account for where some of the time is going.

The documentation has some details on client settings that you may want to adjust. In most cases the defaults are good for deployment in interactive web applications. You may need to adjust them if you are trying to simulate large concurrency with a single process/client. In that case, for instance, you may need to increase the pool size. Either @jmorris or @MikeGoldsmith may be able to advise further.

5

@calhamd -

The initial query on a newly opened bucket will take much longer than subsequent queries because of the cost to create and initialize the connections. After that they are cached, so you should see latency go down considerably. I suggest you do a warm up query or two and then take the measurement.

As @keshav_m mentioned, there is considerable amount of overhead on the client due to network latency, deserialization, etc. This is a convenience you get with the SDK in that you do not have to map the results to objects, its done for you.

With Linq2Couchbase there is an additional step that has to take place to convert the IQueryable object into a N1QL statement, hence it will be slightly slower. The benefit you get here is that you have a familiar API (easy transition from Linq2SQL or Entity Framework) and strongly typed queries along with the object mapping of the result.

-Jeff

6

Thanks for all the responses.
@jmorris; I yes subsequent queries are MUCH faster and related to the metrics (ie less sdk/network latency) in the below example,
I see that the first call:
a1 = 55ms with metrics of 13ms = 32 ms delta
The second call takes
a2 = 13ms with metrics of 12ms = 1 ms delta
This is all on primary index, so once I’ve properly indexed my data it should be quite fast

        sw.Reset();
        sw.Start();
        var query2 = bucket.Query<dynamic>("SELECT * FROM default WHERE type = 'Sport'");
        sw.Stop();
        var a1 = sw.ElapsedMilliseconds;

        sw.Reset();
        sw.Start();
        var query3 = bucket.Query<dynamic>("SELECT type FROM default WHERE playerName = '1'");
        sw.Stop();
        var a2 = sw.ElapsedMilliseconds;

Not sure I will continue with Linq2couchbase as it increases to a 200ms for the below query which is a fair bit more overhead… I’m obviously doing something wrong…
var query = (from sportlist in db.Query() select sportlist.SportName).ToList();

Same thing again though; second query is MUCH faster… 17ms. so seems that the majority of the lag is due to the initial caching and I suppose initialising the connections? Does that sound right to you all based on the above?

Last thing, do you all just pass around your Ibucketcontext object to the functions that need it if it werent present within that function? I haven’t found a method to get a current open context on a bucket…

FYI:
Below is a comparison Niql and linq2couchbase… they seem equivalent… what components could the slowdown be? I think linq2cb needs to construct an object for the result mapping could that be it?
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “default”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Fetch”,
“as”: “Extent1”,
“keyspace”: “default”,
“namespace”: “default”
},
{
“#operator”: “Filter”,
“condition”: “((Extent1.type) = "Sport")”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “result”,
“expr”: “(Extent1.sportName)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
}

{
“requestID”: “0fa134eb-0c07-4724-afa0-c4aa5ca7e608”,
“signature”: “json”,
“results”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “default”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Fetch”,
“keyspace”: “default”,
“namespace”: “default”
},
{
“#operator”: “Filter”,
“condition”: “((default.type) = "Sport")”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(default.sportName)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
}
]

7

Yes, it does sound like the difference here is the initial request. With connection building and JIT’d runtimes like .NET, it usually takes quite a few requests before you reach a steady state that is representative of production.

8

Hi @calhamd

I agree the timings point towards initial setup for both Bucket queries and Linq2Couchbase queries and the output query plans do look the same except the second query includes an alias for the document type which I wouldn’t expect to add any latency.

9

The BucketContext (it’s a lightweight object) should be scoped to the HTTP request - you can reuse it multiple times within the request - generally you would make it property or field on your controller and create it in the constructor. In a desktop app it can be scoped to the application. The Cluster and Bucket objects should be scoped to the application in either case - see IBucket lifetime in .NET SDK 2.0 and https://github.com/couchbaselabs/Linq2Couchbase/blob/master/docs/bucket-context.md

-Jeff