Post Function taking longer time

Hi,

I have a post function define as below.

Post Function :

            Func<QueryRow, bool> filterFunction = null;
            filterFunction = (row) =>
            {
                try
                {
                    if ((string.IsNullOrEmpty(assetNumber) || (row.Document.Properties["assetNumber"] != null && row.Document.Properties["assetNumber"].ToString().ToLower().Contains(assetNumber)))
                        && (string.IsNullOrEmpty(serialNumber) || (row.Document.Properties["serialNumber"] != null && row.Document.Properties["serialNumber"].ToString().ToLower().Contains(serialNumber)))
                        && (string.IsNullOrEmpty(barCode) || (row.Document.Properties["assetBarcode"] != null && row.Document.Properties["assetBarcode"].ToString().ToLower().Contains(barCode)))
                        && (string.IsNullOrEmpty(chipId) || (row.Document.Properties["chipIds"] != null && row.Document.Properties["chipIds"].ToString().ToLower().Contains(chipId)))
                        && !assets.Contains(row.DocumentId)
                       )
                    {
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    return false;
                }

                return false;
            }; 

Running function on database :

                var assetsDocsView = database.GetView(viewName);
                var query = assetsDocsView.CreateQuery();

                query.PostFilter = filterFunction ;
                query.Descending = true;

                query.Limit = 10;

                var rows = query.Run(); 

My problem is that, When i don’t pass any value for all four permeates(i.e assetNumber,serialNumber,barCode,chipId) for the post function, I got max 10 records with in 5 seconds. But when i only pass exact match value for “assetNumber” permeate, It takes around more then a minute to return only one record which is the exact match. My problem why is it taking so long to return that one record. is there any thing else i can do to optimise it. its searching on over 16000 documents.

Please help me on this. Why its taking this much of time and how i can optimise it ?

Environment details -

Couchbase Lite - 1.3.1
Xamarin - 4.2.2.6
Xamarin Android - 7.0.2.42
Xamarin Forms - 10.3.1.7

Thanks.

But when i only pass exact match value for “assetNumber” permeate, It takes around more then a minute

The only difference is the amount of work being done in your postFilter function. When your variables have non-null values, the function does a bunch of string operations. This implies the postFilter is being called a huge number of times, for it to take so much time. (Probably 16000 times, i.e. once per document.)

What does your view’s map function look like? It looks like you’ve set things up so the view itself does almost no filtering and returns all or most of the rows in the database, leaving it to the postFilter to select the rows you want. That’s going to give you very poor performance … it’s the equivalent of running a SQL query without creating an index.

View’s Map function :

            MapDelegate eventAssetsViewFilterMapDelegate = (doc, emit) =>
            {
                try
                {
                    if (doc["_id"].ToString().StartsWith(Constants.AssetDocumentID) &&
                       (doc["status"] == null || doc["status"].ToString().ToLower().Equals("active") ||
                       doc["status"].ToString().ToLower().Equals("a")))
                    {
                        //emit(doc["createdDate"], doc);
                        emit(doc["_id"], new
                        {
                            _id = doc["_id"],
                            assetNumber = doc["assetNumber"],
                            chipIds = doc["chipIds"],
                            assetBarcode = doc["assetBarcode"],
                            serialNumber = doc["serialNumber"],
                            modelAndPart = doc["modelAndPart"]
                        });
                    }
                }
                catch (Exception e)
                {
                    Debug.WriteLine(e.Message);
                }
            };

            var eventAssetsView = database.GetView(Constants.EventAssetsView);
            eventAssetsView.SetMap(eventAssetsViewFilterMapDelegate, "2");

Is something wrong with it ?

It doesn’t look wrong, but it’s not doing much for you other than eliminating some documents. Emitting the document ID as the key is usually not the right thing to do. The key should be the thing you’re searching for in the query.

Do you have any experience with SQL/relational databases? Because this is very much the same task as optimizing a query there — you want to create indexes on the right columns to help the query engine find results efficiently without scanning the whole table.

In your case it looks like you have four different things you’re searching for independently — assetNumber, serialNumber, assetBarcode, chipIds. That would call for four views/indexes, one for each.

You’re doing a case-insensitive match; if this is really necessary, then you’d emit the lowercased property value as the key in each view, then query for key = the lowercased desired value.

Yes, But i am searching on four fields and how can i emit key for four fields ?

So how can i create indexes for these for fields to fast up my result ?


Case-insensitive match you mean by following :
row.Document.Properties[“serialNumber”].ToString().ToLower().Contains(serialNumber)

yes we need ir, we are finding string contains or not. so that’s why.

How i can do this. I didn’t understand it. SO please can you explain it.

Create four views. In each view’s map function, emit one of those fields as the key.
If you need case-insensitive matching, lowercase the key before emitting it, then query for the lowercased value you’re looking for.

Have you read the documentation on views and queries? If so, you might re-read it.

how four different views would work for me ?, Because i need only one view since my search depended on these four fields. of example i can search for a record which need to have exact “assetNumber” and “serialNumber”. so in these case how four different views would work for me ?.

so that’s why i am looking for the way in which only have one view but have key indexes on those four fields.

i am doing now these way. to emit on multiple key ( multiple indexes ). Would it work ?
i guess its not since search time still the same.

MapDelegate eventAssetsViewFilterMapDelegate = (doc, emit) =>
{

                        object[] key = new object[] { doc["assetNumber"].ToString().ToLower(), doc["chipIds"].ToString().ToLower(), doc["assetBarcode"].ToString().ToLower(), doc["serialNumber"].ToString().ToLower(), doc["modelAndPart"].ToString().ToLower() };

                        emit(key, new
                        {
                            _id = doc["_id"],
                            assetNumber = doc["assetNumber"],
                            chipIds = doc["chipIds"],
                            assetBarcode = doc["assetBarcode"],
                            serialNumber = doc["serialNumber"],
                            modelAndPart = doc["modelAndPart"]
                        });

};

If you want to search for equality of two or more fields, emit a key consisting of an array of those fields. (The key can have more fields than you’re searching for; so for example you could emit all four fields and then use that view to search on the first field, the first two, the first three, or all four.)

i guess its not since search time still the same.

As long as you don’t set the startKey and/or endKey, the query is going to iterate over every row in the index, i.e. every document. That’s slow. You should set the startKey and endKey to the key or key range you want to search for.

Again, please (re)read the documentation on views/queries. It goes into all of this.