mdaems wrote:Well, what I actually wanted to say was that there shouldn't be metadata queries at all when 'after scroll requerying'.
I think you misunderstood 'reopening a connection'. Because to requery a detail table you don't have to reopen the connection, just the query. So all queries using the same TZConnection should be using the same cache. And when you don't edit the queries, apart from the parameter values (I suppose you already use queries that have parameter placeholders on the fields refering to the master table), I don't see any reason why we need more metadata on a second refresh. Then you have met a bug.
Things that can point to the causes of the bug:
- temporary tables involved? This isn't necessaily a bug, but a library weakness.
- 'strange' table names. Maybe there are cache misses because the cache key is encoded different from the way it is checked.
- identifier (table or column name) quoting rules
Mark
Hello,
Yes, all the components work on one TZConnection and I don't disconnect if on AfterScroll but only perform the "Close => change parameters => Open" of the TReadOnlyQuery components.
- No temp tables. The whole startup (where the slowdown happens) is read only.
- Everything in the database is UTF8. It's MySQL 5.6 installed under an Ubuntu server if that can suggest any information.
- I don't think I am using any quoted column name. What I have is: some queries have commented out bits, because requirements changed.
Here are some of the queries.
I won't post the insert and update queries because:
1) They are not used all at program startup, where the slowdown happens. They execute fast as well.
2) They are so ever-changing I had to code a "query builder", almost an ORM, that adapts to lots of conditions and situations and crafts different SQL queries every time. Those queries are then executed in a TDataSetProvider.OnBeforeUpdateRecord().
Code: Select all
SELECT
pr.product_id AS product_id,
pr.model AS model,
pd.name AS name,
pr.image AS image,
pr.status AS status,
pr.date_added AS date_added,
pr.date_modified AS date_modified
FROM oc_product pr
LEFT JOIN oc_product_description pd ON pr.product_id = pd.product_id AND pd.language_id = :language_id
-- WHERE
-- pr.status = 1
ORDER BY
pd.name
Code: Select all
SELECT
option_id,
type,
sort_order
FROM oc_option
ORDER BY
sort_order
Code: Select all
SELECT *
FROM oc_option_description
WHERE language_id = :language_id
Code: Select all
SELECT *
FROM oc_option_value
ORDER BY sort_order
Code: Select all
SELECT *
FROM oc_option_value_description
WHERE language_id = :language_id
Code: Select all
SELECT
po.product_id AS product_id,
po.option_id AS option_id,
od.name AS name,
op.sort_order AS sort_order
FROM oc_product_option po
INNER JOIN oc_option_description od ON (po.option_id = od.option_id)
INNER JOIN oc_option op ON (po.option_id = op.option_id)
WHERE (po.product_id = :product_id) AND (od.language_id = :language_id)
ORDER BY op.sort_order
Code: Select all
SELECT
pov.product_id AS product_id,
pov.option_id AS option_id,
pov.option_value_id AS option_value_id,
ovd.name AS name,
ov.sort_order AS sort_order
FROM oc_product_option_value pov
INNER JOIN oc_option_value ov ON (ov.option_id = pov.option_id AND ov.option_value_id = pov.option_value_id)
INNER JOIN oc_option_value_description ovd ON (ov.option_value_id = ovd.option_value_id)
WHERE (ov.option_id = :option_id) AND (ovd.language_id = :language_id) AND (pov.product_id = :product_id)
ORDER BY ov.sort_order
Code: Select all
SELECT
dz.delivery_zone_id AS delivery_zone_id,
dz.geo_zone_id AS geo_zone_id,
dz.status AS status,
lc.name AS name
FROM fw_delivery_zone dz
LEFT JOIN fw_localized_country lc ON (dz.visual_country_id = lc.country_id AND lc.language_id = :language_id)
ORDER BY lc.name
Code: Select all
SELECT
pr.product_id AS product_id,
ptg.geo_zone_id AS geo_zone_id,
99 AS associated,
pr.model AS model,
pd.name AS name,
pr.image AS image,
pr.status AS status,
ptg.date_added AS date_added,
ptg.date_modified AS date_modified,
ptg.user_modified AS user_modified
FROM oc_product pr
LEFT JOIN oc_product_description pd ON pr.product_id = pd.product_id AND pd.language_id = :language_id
LEFT JOIN fw_product_to_geozone ptg ON pr.product_id = ptg.product_id AND ptg.geo_zone_id = :geo_zone_id
-- WHERE
-- pr.status = 1
ORDER BY
pd.name
Code: Select all
SELECT
po.product_option_id AS product_option_id,
po.product_id AS product_id,
po.option_id AS option_id,
od.name AS name,
op.sort_order AS sort_order
FROM oc_product_option po
INNER JOIN oc_option_description od ON (po.option_id = od.option_id)
INNER JOIN oc_option op ON (po.option_id = op.option_id)
WHERE (po.product_id = :product_id) AND (od.language_id = :language_id) AND
((po.option_id = :allowed_option_id_1) OR (po.option_id = :allowed_option_id_2))
ORDER BY op.sort_order
Code: Select all
SELECT
99 AS first_row,
pov.product_id AS product_id,
lpo.geo_zone_id AS geo_zone_id,
lpo.quantity AS quantity,
lpo.subtract AS subtract,
lpo.price AS price,
lpo.price_prefix AS price_prefix,
lpo.points AS points,
lpo.points_prefix AS points_prefix,
lpo.weight AS weight,
lpo.weight_prefix AS weight_prefix,
lpo.date_added AS date_added,
lpo.date_modified AS date_modified,
lpo.user_modified AS user_modified,
pov.product_option_value_id AS product_option_value_id,
pov.option_id AS option_id,
pov.option_value_id AS option_value_id,
ovd.name AS name,
ov.sort_order AS sort_order
FROM oc_product_option_value pov
INNER JOIN oc_option_value ov ON (ov.option_id = pov.option_id AND ov.option_value_id = pov.option_value_id)
INNER JOIN oc_option_value_description ovd ON (ov.option_value_id = ovd.option_value_id)
LEFT JOIN fw_local_product_option_value lpo ON (lpo.option_id = pov.option_id AND lpo.option_value_id = pov.option_value_id) AND (lpo.product_id = :product_id) AND (lpo.geo_zone_id = :geo_zone_id)
WHERE
(pov.product_id = :product_id) AND
(ov.option_id = :option_id) AND
(ovd.language_id = :language_id)
ORDER BY ov.sort_order
), I'd like to add some considerations about it because I believe they do affect ZEOS performance compared to other libraries.
