Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-43222

Prepared updates (with USE KEYS) are 37 slower than standard in line N1QL

    XMLWordPrintable

Details

    • Bug
    • Resolution: User Error
    • Major
    • 7.0.0
    • 6.6.0
    • query
    • Untriaged
    • 1
    • Unknown

    Description

       

      I am not positive but I think this only slows down or impacts the USE KEYS prepared statement use case.

      My tests (I can provide a complete test setup one shot script if desired)

      Across 35M documents I get can process 12203.62 items/sec 50% deletes and 50% update a TTL as follows:

      var stmt = "UPDATE `wu_src` USE KEYS \"" + key + "\" SET meta().expiration = " + Math.floor(ttl / 1000);
      N1QL(stmt);

      However across 35M documents I only process 327.39 items/sec with the same mix  50% deletes and 50% update a TTL as follows:

      var set_ttl = Math.floor(ttl / 1000 );          
      var stmt = "UPDATE `wu_src` USE KEYS \"" + key + "\" SET meta().expiration = $1";
      N1QL(stmt,[set_ttl], { isPrepared: true });

      FYI it doesn't matter if I pass {"isPrepared": true } I get the same degenerate performance.

      Why the 37X slowdown? Is this an optimizer issue?  I would have thought the the later prepared statement would have been faster.

      A bit of good news is that in 6.6.1 (Advanced Bucket Ops specifically 6.6.1-9212-1) we can do the same (avoiding N1QL entirely) customer process without N1QL as follows at 29659.66 items/sec (same 50% delete 50% TTL update mix)

      var res = couchbase.replace(src,{"id":meta.id,"expiry_date":new Date(ttl)}, doc);

      but of course this doesn't help 6.6.0 users or other UPDATE type uses of N1QL(...) in TBD use cases.  

      FYI just doing 50% deletes (no TTL updates) which have no performs issues gives me 52083.33 items/sec on my setup.

       

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          People

            jon.strabala Jon Strabala
            jon.strabala Jon Strabala
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty