Calling Developers!
We are reenergizing our code contribution process! Learn More

Download query optimization on Zed

ilyafil2012
ilyafil2012 Spryker Solution Partner Posts: 2 🧑🏻‍🚀 - Cadet

I'm trying to modify the sales table on zed by adding a country name column to it. It seems to work relatively well on the main table itself, but trying to download a csv leads to a request of >60s, which seems to trigger the default client connection of 60s, thus dropping the connection. At least that's my hypothesis.

The download query command is as follows, the last 2 joins and the withColumn is what has been added and what has been causing us issues. Is there something i could improvide with the query, or do we need to use a different solution ?

protected function getDownloadQuery(): ModelCriteria 
{       
$salesOrderQuery = $this->queryBuilder->buildQuery();       
$salesOrderQuery->orderBy(SpySalesOrderTableMap::COL_ID_SALES_ORDER, \Spryker\Zed\PropelOrm\Business\Runtime\ActiveQuery\Criteria::DESC);

$salesOrderQuery->addJoin(SpySalesOrderTableMap::COL_FK_SALES_ORDER_ADDRESS_SHIPPING, SpySalesOrderAddressTableMap::COL_ID_SALES_ORDER_ADDRESS, Criteria::LEFT_JOIN);       
$salesOrderQuery->addJoin(SpySalesOrderAddressTableMap::COL_FK_COUNTRY, SpyCountryTableMap::COL_ID_COUNTRY, Criteria::LEFT_JOIN);       
$salesOrderQuery->withColumn(SpyCountryTableMap::COL_NAME, static::SHIPPING_COUNTRY);
return $salesOrderQuery;   
}

Tagged:

Answers

  • Alberto Reyer
    Alberto Reyer Lead Spryker Solution Architect / Technical Director Posts: 690 🪐 - Explorer

    Enable your propel log locally, get the query from the log and run it with EXPLAIN in front of the query in your DB, this will give you a fairly lengthy output.
    In this output I would look for full table scanes (hinting that an index is missing or not used during the join), as well as for big data sets.

    In addition check that the query results is not becomming bigger then you expected due to the join. Sometimes it might happen that instead of just adding a new column to the result instead the result set becomes a multiple of your orders times addresses times countries.

    Regarding:

    > which seems to trigger the default client connection of 60s, thus dropping the connection

    Thats probably not a default timeout for the connection but the PHP process time max_execution_time ( https://www.php.net/manual/en/info.configuration.php#ini.max-execution-time ) you are hitting.
    You can increase this in the action with set_ini('max_execution_time', <number>)

    Another option would be to add a limit to the query and sort by modified_at to download only the most recent orders.

  • ilyafil2012
    ilyafil2012 Spryker Solution Partner Posts: 2 🧑🏻‍🚀 - Cadet

    It does seem to do a different type of query for the three tables in question, but i'm not sure how much that impacts it. I do see that the other table columns are added via subqueries in the query handler interface, so i guess i have to do that instead.

    id

    select_type

    table

    type

    possible_keys

    key

    key_len

    ref

    rows

    Extra

    1

    PRIMARY

    spy_sales_order

    index

    PRIMARY

    4

    1

    1

    PRIMARY

    spy_sales_order_address

    eq_ref

    PRIMARY

    PRIMARY

    4

    eu-docker.spy_sales_order.fk_sales_order_address_shipping

    1

    Using where

    1

    PRIMARY

    spy_country

    eq_ref

    PRIMARY

    PRIMARY

    4

    eu-docker.spy_sales_order_address.fk_country

    1

    Using where

    7

    DEPENDENT SUBQUERY

    discounts

    eq_ref

    PRIMARY

    PRIMARY

    4

    eu-docker.spy_sales_order.id_sales_order

    1

    Using index

    7

    DEPENDENT SUBQUERY

    spy_sales_discount

    ALL

    index-spy_sales_discount-fk_sales_order

    1

    Using where; Using join buffer (flat, BNL join)

    6

    DEPENDENT SUBQUERY

    pt

    eq_ref

    PRIMARY

    PRIMARY

    4

    eu-docker.spy_sales_order.id_sales_order

    1

    Using index

    6

    DEPENDENT SUBQUERY

    spy_sales_payment

    ALL

    index-spy_sales_payment-fk_sales_order,index-spy_sales_payment-fk_sales_payment_method_type

    1

    Using where; Using join buffer (flat, BNL join)

    6

    DEPENDENT SUBQUERY

    spy_sales_payment_method_type

    eq_ref

    PRIMARY

    PRIMARY

    4

    eu-docker.spy_sales_payment.fk_sales_payment_method_type

    1

    5

    DEPENDENT SUBQUERY

    sccg

    eq_ref

    PRIMARY,spy_sales_order-customer_reference

    PRIMARY

    4

    eu-docker.spy_sales_order.id_sales_order

    1

    Using where

    5

    DEPENDENT SUBQUERY

    spy_customer

    eq_ref

    PRIMARY,spy_customer-customer_reference

    spy_customer-customer_reference

    767

    eu-docker.sccg.customer_reference

    1

    Using index

    5

    DEPENDENT SUBQUERY

    spy_customer_group_to_customer

    ref

    fk_customer_group-fk_customer,index-spy_customer_group_to_customer-fk_customer

    index-spy_customer_group_to_customer-fk_customer

    4

    eu-docker.spy_customer.id_customer

    1

    5

    DEPENDENT SUBQUERY

    spy_customer_group

    eq_ref

    PRIMARY

    PRIMARY

    4

    eu-docker.spy_customer_group_to_customer.fk_customer_group

    1

    4

    DEPENDENT SUBQUERY

    spy_sales_order_item

    ref

    index-spy_sales_order_item-fk_sales_order

    index-spy_sales_order_item-fk_sales_order

    4

    eu-docker.spy_sales_order.id_sales_order

    1

    Using index

    4

    DEPENDENT SUBQUERY

    sso

    eq_ref

    PRIMARY

    PRIMARY

    4

    eu-docker.spy_sales_order.id_sales_order

    1

    Using where; Using index

    3

    DEPENDENT SUBQUERY

    sso

    eq_ref

    PRIMARY

    PRIMARY

    4

    eu-docker.spy_sales_order.id_sales_order

    1

    Using index; Using temporary; Using filesort

    3

    DEPENDENT SUBQUERY

    spy_sales_order_item

    ALL

    index-spy_sales_order_item-fk_sales_order,index-spy_sales_order_item-fk_oms_order_item_state

    1

    Using where; Using join buffer (flat, BNL join)

    3

    DEPENDENT SUBQUERY

    spy_oms_order_item_state

    eq_ref

    PRIMARY

    PRIMARY

    4

    eu-docker.spy_sales_order_item.fk_oms_order_item_state

    1

    2

    DEPENDENT SUBQUERY

    sso

    eq_ref

    PRIMARY

    PRIMARY

    4

    eu-docker.spy_sales_order.id_sales_order

    1

    Using index; Using temporary; Using filesort

    2

    DEPENDENT SUBQUERY

    spy_sales_order_totals

    ALL

    index-spy_sales_order_totals-fk_sales_order

    1

    Using where; Using join buffer (flat, BNL join)

  • Oleksandr Peresypkin
    Oleksandr Peresypkin Lead Demo Engineering Sprykee Posts: 22 🧑🏻‍🚀 - Cadet

    Checking a standard demo shop, I can see that adding the following indexes could help avoid some full table scans:

    Table spy_sales_order_item composite index fk_sales_order, fk_oms_order_item_state

    Table spy_sales_order_totalscomposite covering index fk_sales_order, created_at, id_sales_order_totals, grand_total.

    If you provided the final query, it would be helpful.