C# performance query

SQL++
,
1

Hello,

The query takes a long time (600ms) to run and to return 1433 items, I create indexes but it does not solve the problem, can you help me please ?

couchbase
couchbase.jpg2060×1462 378 KB

2

Your query results size is 5.7MB. You should try with cbq shell with pretty=false. Check the EXPLAIN and see if query using index1 only.

3

thanks for your answer.

the query use ‘index1’

[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “index1”,
“index_id”: “c85bc941a64fc793”,
“keyspace”: “bucket”,
“namespace”: “default”,
“spans”: [
{
“Exact”: true,
“Range”: {
“High”: [
"“widget”"
],
“Inclusion”: 3,
“Low”: [
"“widget”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Fetch”,
“keyspace”: “bucket”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “((bucket.type) = “widget”)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT * FROM bucket where type = ‘widget’”
}
]

4

Plan looks good. The result size is too big and transportation might be taking time. Try pretty=false and that will speed up.

5

It’s on localhost and after that I will use the query on C#

6

pretty=false is Query parameter from 4.5.1 If C# supports you can send it. It removes all spaces and reduces document size. Also you can set this Query Service wide.

7

I tested with pretty=false with c#

pretty
pretty.png466×720 6.87 KB

8

How much you are getting through cbq shell.
In shell
\set -pretty false;
Run the query capture time and results size

Compare those with C#

9

Hello ,

result with pretty=false;

“status”: “success”,
“metrics”: {
“elapsedTime”: “2.440916981s”,
“executionTime”: “2.440867239s”,
“resultCount”: 1433,
“resultSize”: 5723203
}

10

From what we’ve seen, pretty=false improves both latency and throughput.
How did you exactly set the pretty to false?
Here are the things you can do to.

  1. Use prepared statement. Set adhoc to false
  2. Set pretty=false
  3. Increase pipeline-batch to 2048.
    info on how to increase it is at at: N1QL 200x slower than views for simple queries
11

my query on cbq :
cbq> \set -pretty false;
cbq> select * from bucket_name where type = ‘widget’ ;

result: “status”: “success”,
“metrics”: {“elapsedTime”: “697.502235ms”,“executionTime”: “697.419731ms”,“resultCount”: 1433,“resultSize”: 3873089}
}

C#
var request = new QueryRequest(query)
.AdHoc(false)
.Pretty(false);

                    nresult = _bucket.Query<dynamic>(request).Rows;

=========N1QL============
ElapsedMilliseconds 1433:
1475
ElapsedMilliseconds 1433:
1096
ElapsedMilliseconds 1433:
1773
ElapsedMilliseconds 1433:
2591
ElapsedMilliseconds 1433:
1256
ElapsedMilliseconds 1433:
1169
ElapsedMilliseconds 1433:
2142
ElapsedMilliseconds 1433:
1598
ElapsedMilliseconds 1433:
1007
ElapsedMilliseconds 1433:
1060
========fin======

for pipeline-batch how can I increase it with c# sdk ?

Thanks :slight_smile:

12

You can set them at query service level where query service running.

curl -u Administrator:pass http://localhost:8093/admin/settings -XPOST -d '{"pretty":false,"pipeline-batch":1024,"pipeline-cap":1024}'
13

@mbenaissa

For the query in C#, you should check the version of the CouchbaseNetClient you are using. There was a bug related to pretty=false that was fixed in 2.4.7.

https://issues.couchbase.com/browse/NCBC-1447

Also, I’m not certain but using “dynamic” instead of a strong type might be affecting performance as well. Depends on how many properties are on each document.

Brant

14

Hello,

Thanks Brant for your answer.

I’am using 2.4.8 and I still have performance problems.

Thanks

15

@mbenaissa

Did you try using a strongly typed POCO instead of dynamic? With large amounts of data I wouldn’t be surprised if dynamic is a bottleneck.

16

Hello Brant,

I upgraded the version to 2.5.0 and I used the GetDocumentsAsync instead of Get and now I have a better performance (30 ms)

Thanks for your help :slight_smile: