Data loading in N1ql

[muhammad_ibrahim]

Why couchbase sometimes load page normally but sometimes becomes too slow…I think it save page in cache first time if i am not wrong. Is there any way to make the page load consistent.

[raju]

@muhammad_ibrahim I am sorry that you are experiencing some slowness. Are you talking about the Admin UI? Can you clarify what exactly are you doing, Couchbase Server version you are using, what browser etc. There is not enough information here to respond back to your question

[muhammad_ibrahim]

HI i am talking about my application . I am using PHP SDK 2.6.0 COUCHBASE 6.0. Sometimes my dashboard loads quickly but Sometimes it becomes slow. When i refresh my app first time it loads slow second time becomes little faster. i want to know how to prevent this thing.

[avsej]

The library is using persistent connections, so second time you might just take ready connection from that list. Default idle time is 60 seconds. Is it local machine? how often the dashboard is getting refreshed?

[muhammad_ibrahim]

yeah its running on local machine . it was working fine before uploading the data since i uplaoded the few thousands of record it become too slow like dead.Its basically a POS application so may be it will get refersh after every 10 mints

[avsej]

if it is N1QL requests, does it behave the same in Query Bench on the Admin UI? I mean could we ensure it is something with SDK behaviour, and not the Query service.

[muhammad_ibrahim]

i added indexes for every query indexes are covering the query but dont know why it becomes slow in few thousands of records even it support to scan millions of records.

[muhammad_ibrahim]

its not the sdk issue its taking same time in ui too

[muhammad_ibrahim]

i noticed that its because of unnest of array … when i search by removing the unnested part it works fine but i really needed to scan the query in the same way.

[muhammad_ibrahim]

MY QUERY

SELECT inv.id as invoice_id,ins.due_time as inv_time ,inv.cus_id, customer.first_name,customer.last_name, inv.total_bill,ins.due_date as inv_date ,ins.id as sales_id FROM stitchit_data_bucket inv USE INDEX (adv_ALL_invoice_services_type_store_id_due_date_cus_id_id_total_bill) UNNEST inv.invoice_services as ins INNER JOIN stitchit_initialization_hq customer ON customer.customer_id = inv.cus_id AND customer.type=‘customer’ WHERE inv.type=‘invoice’ AND inv.due_date BETWEEN ‘2020-03-29’ AND ‘2020-04-04’ AND inv.store_id = ‘3221’ AND ins.working_status = ‘rack’ AND ins.express = ‘express’ ORDER BY ins.due_time ASC

MY INDEXS

CREATE INDEX adv_type_customer_id_last_name_first_name ON stitchit_initialization_hq ( type , customer_id , last_name , first_name )

CREATE INDEX adv_ALL_invoice_services_type_store_id_due_date_cus_id_id_total_bill ON stitchit_data_bucket ((all ( invoice_services )), type , store_id , due_date , cus_id , id , total_bill )

index scan result

IndexScan3

• as - inv
• covers
  • cover ( ins )
  • cover (( inv . type ))
  • cover (( inv . store_id ))
  • cover (( inv . due_date ))
  • cover (( inv . cus_id ))
  • cover (( inv . id ))
  • cover (( inv . total_bill ))
  • cover ((meta( inv ). id ))
• filter_covers
  • cover ((( inv . invoice_services ) < {})) - true
  • cover (( <= ( inv . invoice_services ))) - true
  • cover (is_array(( inv . invoice_services ))) - true
• index - adv_ALL_invoice_services_type_store_id_due_date_cus_id_id_total_bill
• index_id - c680c74c65a986e8
• keyspace - stitchit_data_bucket
• namespace - default
• spans
  • range
    • inclusion - 0
    • low - null
• using - gsi

IndexScan3

• as - customer
• covers
  • cover (( customer . type ))
  • cover (( customer . customer_id ))
  • cover (( customer . last_name ))
  • cover (( customer . first_name ))
  • cover ((meta( customer ). id ))
• index - adv_type_customer_id_last_name_first_name
• index_id - c3f2dfa31cf1e2bf
• keyspace - stitchit_initialization_hq
• namespace - default
• nested_loop - true
• spans
  • exact - true
  • range
    • high - “customer”
    • inclusion - 3
    • low - “customer”
    • high - cover (( inv . cus_id ))
    • inclusion - 3
    • low - cover (( inv . cus_id ))
• using - gsi

MY doc

{
“canada_exempt”: “”,
“completed_notification”: “sent”,
“corp_emp”: “0”,
“corp_po”: “0”,
“corp_req”: “0”,
“cus_id”: “006020000008”,
“customer_signaure”: “”,
“date”: “2020-04-26”,
“defect_reason”: “”,
“description”: “”,
“discount”: “0”,
“discount_id”: “0”,
“discount_method”: “”,
“due_date”: “2020-04-26”,
“due_time”: “14:00”,
“employee_id”: “7019”,
“exempt_1”: “”,
“exempt_2”: “”,
“id”: “00602000000009”,
“invoice_payment”: [
{
“account_no”: “”,
“auth_code”: “”,
“card_balance”: “”,
“entry_mode”: “”,
“gift_card_ref”: “”,
“host_response”: “”,
“payment_type”: “Cash”,
“terminal_id”: “Manual”,
“trans_amount”: 11.5,
“trans_date”: “2020-04-26”,
“trans_id”: 1582576119,
“trans_ref”: “”,
“trans_status”: “000”,
“trans_time”: “14:00”,
“trans_total_amount”: 11.5,
“trans_type”: “Sale”
},
{
“account_no”: “”,
“auth_code”: “”,
“card_balance”: “”,
“entry_mode”: “”,
“gift_card_ref”: “”,
“host_response”: “”,
“payment_type”: “Cash”,
“terminal_id”: “Manual”,
“trans_amount”: 11.5,
“trans_date”: “2020-04-26”,
“trans_id”: 1582576513,
“trans_ref”: “”,
“trans_status”: “000”,
“trans_time”: “14:00”,
“trans_total_amount”: 11.5,
“trans_type”: “Sale”
}
],
“invoice_process”: [
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“status”: “IN”,
“time”: “14:00”,
“total_bill”: “10.00”
},
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“status”: “pending”,
“time”: “14:00”,
“total_bill”: 10
},
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“status”: “ready”,
“time”: “14:00”,
“total_bill”: 10
},
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“status”: “EDIT”,
“time”: “14:00”,
“total_bill”: “20.00”
},
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221200000000100”,
“item_name”: “Pants”,
“status”: “pending”,
“time”: “14:00”,
“total_bill”: “20”
},
{
“branch_id”: “60”,
“date”: “2020-04-26”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“status”: “ready”,
“time”: “14:00”,
“total_bill”: 20
},
{
“date”: “2020-04-26”,
“time”: “14:00”,
“branch_id”: “60”,
“employee_id”: “7019”,
“invoice_id”: “00602000000009”,
“item_id”: “3221190019”,
“item_name”: “Pants”,
“total_bill”: “20.00”,
“status”: “OUT”
}
],
“invoice_services”: [
{
“check_redo”: “no”,
“check_status”: “OUT”,
“completed_date”: “2020-02-25”,
“completed_time”: “01:36:30”,
“created_emp_id”: “7019”,
“date”: “2020-04-26”,
“description”: “”,
“discount”: 0,
“discount_id”: “0”,
“discount_method”: “”,
“discount_name”: “”,
“due_date”: “2020-04-26”,
“due_time”: “14:00”,
“express”: “no”,
“express_amt”: 0,
“express_notification”: “waiting”,
“id”: “0060200000000900”,
“inv_id”: “00602000000009”,
“item_list”: [
{
“item_detail_charges”: 0,
“item_id”: “3221190649”,
“item_name”: "Waist Clip (4 piece) ",
“item_qty”: 1,
“price”: 10,
“sales_item_id”: “006020000000090000”,
“tag_3”: “”
}
],
“qty”: 2,
“service_id”: “3221190019”,
“service_name”: “Pants”,
“start_date”: “2020-02-25”,
“start_time”: “01:36:05”,
“store_id”: “60”,
“tag_1”: “”,
“tag_2”: “”,
“tax”: 0,
“total_bill”: 20,
“upcharge_charges”: 0,
“upcharge_charges_per”: 0,
“working_employee”: “7019”,
“working_status”: “ready”
}
],
“invoice_transaction”: [
{
“branch_id”: “60”,
“credit_note”: 0,
“date”: “2020-04-26”,
“description”: “SALES”,
“invoice_id”: “00602000000009”,
“method”: “Cash”,
“proceed”: 11.5,
“sales”: 11.5,
“time”: “14:00”
},
{
“branch_id”: “60”,
“credit_note”: 0,
“date”: “2020-04-26”,
“description”: “EDIT”,
“invoice_id”: “00602000000009”,
“method”: “”,
“proceed”: 11.5,
“sales”: 23,
“time”: “14:00”
},
{
“branch_id”: “60”,
“credit_note”: 0,
“date”: “2020-04-26”,
“description”: “CHECKOUT”,
“invoice_id”: “00602000000009”,
“method”: “Cash”,
“proceed”: 11.5,
“sales”: 0,
“time”: “14:00”
}
],
“notification_status”: “sent”,
“payment_date”: “2020-02-25”,
“pickup_name”: “”,
“price_list_id”: “3221190011”,
“province_exempt”: “”,
“referal_or_corp”: “Walk in”,
“referal_or_corp_id”: “0”,
“status”: “OUT”,
“store_id”: “60”,
“tax_amount”: 14.975,
“tax_method”: “%”,
“tax_name”: “GST”,
“time_entrance”: “14:00”,
“total_bill”: 23,
“total_change”: 0,
“total_express”: 0,
“total_paid”: 23,
“total_qty”: 2,
“total_tendered”: 11.5,
“type”: “invoice”

[muhammad_ibrahim]

please help its really urgent thanks

[vsr1]

Try this. No use of creating Array index because you don’t have predicate.

CREATE INDEX cust_ix1 ON stitchit_initialization_hq(customer_id, last_name, first_name) WHERE type ="customer";
CREATE INDEX inv_ix1 ON stitchit_initialization_hq(store_id, due_date) WHERE type ="invoice";

SELECT d.*, customer.first_name, customer.last_name
FROM (SELECT inv.id AS invoice_id, inv.cus_id, inv.total_bill,
             ins.due_time AS inv_time, ins.due_date AS inv_date ,ins.id AS sales_id
      FROM stitchit_data_bucket AS inv USE INDEX (inv_ix1)
      UNNEST inv.invoice_services AS ins
      WHERE inv.type = "invoice" AND inv.due_date BETWEEN "2020-03-29" AND "2020-04-04" AND inv.store_id = "3221"
            AND ins.working_status = "rack" AND ins.express = "express" ) AS d
INNER JOIN stitchit_initialization_hq AS customer ON customer.customer_id = d.cus_id AND customer.type = "customer"
ORDER BY d.inv_time ASC;

OR

CREATE INDEX cust_ix1 ON stitchit_initialization_hq(customer_id, last_name, first_name) WHERE type ="customer";
CREATE INDEX inv_ix2 ON stitchit_initialization_hq(store_id,
             DISTINCT ARRAY [ins.working_status, ins.express] FOR ins IN invoice_services END, due_date) WHERE type ="invoice";

SELECT d.invoice_id, d.cus_id, d.total_bill, d.first_name, d.last_name, ins.*
FROM (SELECT inv.id AS invoice_id, inv.cus_id, inv.total_bill,
             customer.first_name, customer.last_name,
             ARRAY {"inv_time":ins.due_time, "inv_date": ins.due_date, "sales_id": ins.id}
               FOR ins IN inv.invoice_services WHEN  ins.working_status = "rack" AND ins.express = "express" END AS invoice_services
      FROM stitchit_data_bucket AS inv USE INDEX (inv_ix2)
      INNER JOIN stitchit_initialization_hq AS customer ON customer.customer_id = inv.cus_id AND customer.type = "customer"
      WHERE inv.type = "invoice" AND inv.due_date BETWEEN "2020-03-29" AND "2020-04-04" AND inv.store_id = "3221"
            AND ANY ins IN inv.invoice_services SATISFIES [ins.working_status,ins.express] = ["rack","express"] END) AS d
      )
UNNEST d.invoice_services AS ins
ORDER BY ins.inv_time;

[muhammad_ibrahim]

Thank you so much you are a life saver I am chsooing the first solution its simple and understandable for me

[muhammad_ibrahim]

one more question if query scans multiple indexes to scan the data so should i delete those indexes which i am not supposed to scan with specific query i mean if i created a index for employee and its coming dont know why for any other query which is not suppose to use employee index so should i delete the that index or make employee index only for type employee. i am saying this in terms of memory saving and faster performance.

[vsr1]

If you are sure u can drop. or use index hint

[muhammad_ibrahim]

Ok thanks i am sorry but can you please check why search with join is taking time . if i search with out join its working very fast. but with join its not thanks.

CREATE INDEX def_first_name_last_name_customer ON stitchit_initialization_hq
((distinct (array (distinct (suffixes(lower(f)))) for f in
[first_name,last_name]
end))) WHERE (type = ‘customer’)

CREATE INDEX def_invoice_date_cus_id ON stitchit_data_bucket(date,cus_id) WHERE type = ‘invoice’

SELECT inv.*,customer.first_name,customer.last_name
FROM stitchit_data_bucket inv
LEFT JOIN stitchit_initialization_hq customer
ON customer.customer_id = inv.cus_id
AND customer.type=‘customer’
WHERE inv.type=‘invoice’ AND ANY f IN
[customer.first_name,customer.last_name]
SATISFIES (ANY v IN SUFFIXES(LOWER(f)) SATISFIES v LIKE ‘spantik’ END) END AND inv.date BETWEEN ‘2020-01-01’ AND ‘2020-05-01’
LIMIT 10

[muhammad_ibrahim]

@vsr1

SELECT d.*, customer.first_name, customer.last_name FROM (SELECT inv.id AS invoice_id, inv.cus_id, inv.total_bill, ins.due_time AS inv_time, ins.due_date AS inv_date ,ins.id AS sales_id FROM stitchit_data_bucket AS inv USE INDEX (inv_ix1) UNNEST inv.invoice_services AS ins WHERE inv.type = “invoice” AND inv.due_date BETWEEN “2020-03-29” AND “2020-04-04” AND inv.store_id = “3221” AND ins.working_status = “rack” AND ins.express = “express” ) AS d INNER JOIN stitchit_initialization_hq AS customer ON customer.customer_id = d.cus_id AND customer.type = “customer” ORDER BY d.inv_time ASC;

This query worked

SELECT d.*, customer.first_name, customer.last_name
FROM (SELECT inv.id AS invoice_id, inv.cus_id, inv.total_bill
FROM stitchit_data_bucket AS inv USE INDEX (def_invoice_date_cus_id)
WHERE inv.type = “invoice” AND inv.due_date BETWEEN “2020-03-29” AND “2020-04-04” AND inv.store_id = “3221”) AS d
INNER JOIN stitchit_initialization_hq AS customer ON customer.customer_id = d.cus_id AND customer.type = “customer”
AND ANY f IN
[customer.first_name,customer.last_name]
SATISFIES (ANY v IN SUFFIXES(LOWER(f)) SATISFIES v LIKE ‘spantik’ END)
END LIMIT 10;

Thanks