Query optimization of Orders sync

What is the purpose of this article?

This article shares the queries that are currently used in orders sync. They include the optimized and the unoptimized older versions of the query too along with the steps to fetch orders details.

Current order sync queries.

Primary query.

Open

^ Above query performance ^.

Following steps were taken to optimize the main query.

1. In the queries, position indexed columns first, then non-indexed columns.

2. Remove accounts, channels, portals, external_wms_channels, shipments, party joined tables from the main query. They can be fetched separately by queries using the foreign key of shipment_id, channel_id, “brandAccountId” etc.

3. Fetch order items based on min(id) of order_items.created_at date >= previous sync end_date.

4. Currently, the shipment query is also fetching warehouse information along with shipments. Warehouse details and shipment details are to be fetched separately.

5. Remove the where condition for integration type. Instead, beforehand, query all the channel ids that have integration type equals '3'. Then in the main query use these ids to omit records where orders.channel ids equal the previously fetched channel ids.

New Flow to obtain order details for orders sync

Steps to obtains orders details

1. Fetch min (id) where created_at is greater than or equal to the input time calculated by reducting 10 min from the end_date of the last run sync.

   SELECT MIN(id) FROM order_items WHERE created_at >= '2021-07-01 00:00:00';

2. Fetch all the channels primary keys where integration type equals '3' or NULL.

   SELECT GROUP_CONCAT(id) FROM channels WHERE integrationType = '3' OR integrationType IS NULL;

3. Now using the results from Step 1 and 2, fetch all the order items details from the main query below:

   SELECT order_items.id AS orderItemId_PK, order_items.created_at AS orderItemCreatedAt, order_items.`orderItemID`, order_items.itemID, order_items.`shipment_id`, order_items.inventoryItemCode, order_items.`sku`, orders.channel_id, o.`customerOrderNumber` AS replaceOrderNumber, order_items.`lineItemOrderStatus`, order_items.`isGift`, order_items.`giftMessage`, order_items.`giftWrapCharges`, order_items.`cashOnDeliveryCharges`, order_items.`customerPrice`, order_items.`lineItemTotal`, order_items.`invoiceTotal`, order_items.`mrp`, order_items.`shippingCharges`, order_items.`promiseShipDate`, order_items.`promisePickupDate`, order_items.`promiseDeliveryDate`, order_items.`status` AS orderItemStatus, order_items.expectedDeliveryDate, orders.*, orders.`expectedShipDate`, orders.`onHold` FROM order_items LEFT JOIN order_items oi ON oi.id = order_items.`original_order_item_id` LEFT JOIN orders o ON o.id = oi.order_id LEFT JOIN orders ON order_items.order_id = orders.id WHERE order_items.id >= 19414791 AND orders.channel_id NOT IN (82,83,254,326,358,359,360,667,803,804) GROUP BY order_items.id LIMIT 1000, 1000;

4. Once all the order details result set is obtained, query for accounts, channels, portals using the order_items.channel_id obtained from the order details result set.

   SELECT ch.externalChannelID, ch.integrationType, ch.channelLabel, acc.id AS accountId, acc.account_slug as accountSlug, portals.`portal_name` as portalName FROM channels ch LEFT JOIN accounts acc ON ch.account_id = acc.id LEFT JOIN portals ON ch.portal_id = portals.id WHERE ch.id IN (380, 718, 55, 663, 298, 740, 710, 662, 476, 479);

5. From the set of shipment_ids from the main query in step 3, fetch all the shipment details.

   SELECT shipments.id AS shipmentId, shipments.`externalShipmentID`, shipments.warehouse_id as warehouseId, shipments.`trackingID`, shipments.`invoiceNumber`, shipments.external_wms_channel_id as externalWMSChannelId, shipments.isMetro AS isMetro, shipments.isSpecialplace AS isSpecial, shipments.`order_id` as orderId, shipments.`courierName`, shipments.region, shipments.`invoiceDate`, shipments.invoice_url AS invoiceUrl, shipments.weight, shipments.`dimension_length` AS `length`, shipments.`dimension_width` AS width, shipments.`dimension_height` AS height, GROUP_CONCAT( CONCAT( shipment_status_logs.status, '####', shipment_status_logs.`created_at`, '####', IF(LENGTH(shipment_status_logs.location)> 0, shipment_status_logs.location, ''), '####', IF(LENGTH(shipment_status_logs.remarks)> 0, shipment_status_logs.remarks, '')) ORDER BY `shipment_status_logs`.`id` ASC SEPARATOR '::::') AS status_log as statusLog, GROUP_CONCAT( CONCAT( shipment_status_logs.status, '####', IF(LENGTH(shipment_status_logs.remarks)> 0, shipment_status_logs.remarks, ' '), '####', IF(LENGTH(shipment_status_logs.track_payload)> 0, shipment_status_logs.track_payload, ' '), '####', IF (shipment_status_logs.`external_updated_at` = '0000-00-00 00:00:00', shipment_status_logs.`created_at`, shipment_status_logs.`external_updated_at`), '####', IF(shipment_status_logs.location IS NOT NULL AND LENGTH(shipment_status_logs.location)> 0, shipment_status_logs.location, ' '), '####', shipment_status_logs.`created_at`) SEPARATOR '::::') AS trackingLogs FROM shipments LEFT JOIN shipment_status_logs ON shipment_status_logs.shipment_id = shipments.`id` WHERE shipments.id IN (: shipmentIds) GROUP BY shipments.id;

6. Create a set of external_wms_channel_ids from shipment details and fetch external_wms_channels, party information from the database.

   SELECT wms_ch.`externalWmsChannelName`, party.`id` AS partyId, party.`partyID`, party.`refPartyID`, party.`party_name`, party.`address_line_1`, party.`address_line_2`, party.city, party.state, party.`pincode`, party.`country` FROM external_wms_channels wms_ch LEFT JOIN party ON wms_ch.billing_party_id = party.id WHERE wms_ch.id IN ( 7758056, 7758057, 7758058, 7758059, 7758060, 7758062, 7758061, 7758063, 7758064, 7758065, 7758066 );

7. Create a set of warehouse_ids from the shipment details obtained in Step 6. Fetch warehouse details using the below query.

   SELECT w.id, warehouses.id AS warehouseId, facility.`facilityLabel` AS warehouseName, warehouses.`externalWarehouseID` AS externalWarehouseId, warehouses.`address_line_1` AS whAddressLine1, warehouses.`address_line_2` AS whAddressLine2, warehouses.`city` AS whCity, warehouses.`state` AS whState, warehouses.`country` AS whCountry, warehouses.`pincode` AS whPincode, facility.`facility`, external_wms_accounts.`soapApiUrl` FROM warehouses w LEFT JOIN warehouses warehouses ON warehouses.`facility_id` = w.`facility_id` AND warehouses.`isDefault` = '1' LEFT JOIN `facility` ON facility.id = warehouses.`facility_id` LEFT JOIN external_wms_accounts ON external_wms_accounts.id = facility.`external_wms_account_id` WHERE w.id IN (: warehouseIds);

8. Once all the above queries have been executed, populate the details accordingly into a common DTO and pass the DTOs into the transformation function.