Оптимизация запросов SQL в 1 -м номинальных базах данных, которые требуют фильтров строк, которые выбирают с использован ⇐ MySql

[Anonymous]

Настройка < /strong>
Я работаю в базе данных MySQL, которая все еще работает 5.6 (поэтому я не могу использовать языковые конструкции MySQL 8). Окончательный SQL встроит в JasperReport (но это более поздняя проблема) < /p>
Таблицы выглядят так, что < /p>
Действует таблица - есть также таблицы Act_detail, которые имеют (act_id, name, value) < /p>
|fieldname|type|extra|
|----|----|---|
|act_id| bigint(20)| AI PK |
|created| datetime | |
|arch_short_name| varchar(100) ||
|name| varchar(255) ||
|description| varchar(255) ||
|active| bit(1) |
|activity_start_time| datetime |
|activity_end_time| datetime |
|status| varchar(255) |
< /code>
Таблицы объектов - Существует также таблица entity_detail, которая имеет (entity_id, name, value) < /p>
|fieldname|type|extra|
|----|----|---|
|entity_id|bigint(20)|AI PK |
|created| datetime |
|created_id |bigint(20) |
|arch_short_name| varchar(100) |
|name| varchar(100)|
< /code>
Таблица: Участие (это отражает сущность, участвующую в акте) < /p>
|fieldname|type|extra|
|----|----|---|
|participation_id| bigint(20)| AI PK |
|arch_short_name| varchar(100) |
|name| varchar(255) |
|entity_id |bigint(20) |
|act_id| bigint(20) |
|activity_start_time| datetime |
|activity_end_time| datetime|
< /code>
Таблица: Act_relationships (это отражает связь между 2 актами) < /p>
|fieldname|type|extra|
|----|----|---|
|act_relationship_id |bigint(20)| AI PK |
|arch_short_name| varchar(100) |
|name |varchar(255) |
|description| varchar(255) |
|source_id |bigint(20) |
|target_id |bigint(20) |
< /code>
Данные в основном то, что я бы назвал «практичной» 1 -й номинальной формой.
Объекты данных < /p>
- product stored in entity
- "stock location" stored in entity
- delivery stored as act
- "delivery items" stored as act
< /code>
Акционерный местоположение хранит продукты
доставка содержит много предметов доставки и доставляется в 1 местоположение запаса
Предмет доставки содержит 1 продукт < /p>
Я пытаюсь продукт продукта, который показывает самый последний предмет доставки для каждого поставки, который можно поставлено на каждый поставки. Элементы.
select
e.entity_id as pid,
e.name as product,
pt.name as productType,
l.name as uom,
sl.name as stockLocation,
round(if(d2.value,d2.value,"0"),2) as currentStock,
round(max(d3.value),2) as idealStock,
round(max(ppud.value),2) as unitcost,
round(max(ppfd.value),2) as fixedcost,
if(round(max(ppud.value),2),round(max(ppud.value),2),round(max(ppfd.value),2)) as cost
from
entities e inner join products p on e.entity_id = p.product_id
left join entity_links r on e.entity_id = r.source_id and r.arch_short_name = "entityLink.productType"
left join entities pt on r.target_id = pt.entity_id
left outer join entity_details d on d.entity_id = e.entity_id and d.name = "sellingUnits"
left outer join lookups l on l.code = d.value and l.arch_short_name = "lookup.uom"
left join entity_links s on s.source_id = e.entity_id and s.arch_short_name = "entityLink.productStockLocation"
left join entity_link_details d2 on s.id = d2.id and d2.name = "quantity"
left outer join entity_link_details d3 on d3.id = s.id and d3.name = "idealQty"
left join entities sl on sl.entity_id = s.target_id
left outer join product_prices ppu on ppu.product_id = p.product_id and ppu.arch_short_name = "productPrice.unitPrice" and ppu.end_time is null
left outer join product_price_details ppud on ppud.product_price_id = ppu.product_price_id and ppud.name = "cost"
left outer join product_prices ppf on ppf.product_id = p.product_id and ppf.arch_short_name = "productPrice.fixedPrice" and ppf.end_time is null
left outer join product_price_details ppfd on ppfd.product_price_id = ppf.product_price_id and ppfd.name = "cost"
where e.name like "%" and
e.active = 1 and
pt.entity_id not in (114,121,132,528343,833974) and
pt.name like "%" and
sl.name like "%" and
(
(e.arch_short_name = "product.medication") or
(e.arch_short_name = "product.merchandise")
) and
round(if(d2.value,d2.value,"0"),2) > 0.00
group by sl.entity_id, e.entity_id
order by sl.name, e.name

< /code>
Это работает, но он не включает в себя материал доставки. < /p>
На производственном сервере он возвращает около 10000 строк и довольно быстро. Query Times Out. У меня также есть проблемы, выбирающие самую последнюю позицию строки доставки для каждого элемента и местоположения.
Поэтому я работал над созданием SQL, который возвращает элементы доставки < /p>
Я придумал < /p>

SELECT
MAX(delivery_item.activity_start_time) as deliveryDate,
stockLocation.entity_id as location,
prod.entity_id as product
FROM acts delivery_item
LEFT JOIN participations prod_deliv_part on prod_deliv_part.act_id = delivery_item.act_id
AND prod_deliv_part.arch_short_name = "participation.stock"
LEFT JOIN entities prod on prod.entity_id = prod_deliv_part.entity_id
LEFT JOIN act_relationships deliv_item_r ON deliv_item_r.target_id = delivery_item.act_id AND deliv_item_r.arch_short_name = 'actRelationship.supplierDeliveryItem'
LEFT JOIN acts delivery ON delivery.act_id = deliv_item_r.source_Id
LEFT JOIN participations delivery_location_p ON delivery_location_p.act_id = delivery.act_id AND delivery_location_p.arch_short_name = 'participation.stockLocation'
LEFT JOIN entities stockLocation on stockLocation.entity_id = delivery_location_p.entity_id
WHERE delivery_item.arch_short_name = "act.supplierDeliveryItem" and delivery.status = "POSTED"
GROUP BY location,product

< /code>
Это работает, создавая таблицу, подобную < /p>



date < /th>
< /th>
< /br /> productid < /br /br /br /br />
productid < /br /br /tr>

productId < /br /br /br /tr. /> < /thead>


2025-06-11 00:00:00 < /td>
68 < /td>
22196 < /td>
< /tr>
2023-23. 00:00:00
68
22112



.... и так на
locally около 642 ROW /> На производстве это время ... производство представляет собой базу данных 600 ГБ. Таблица ACTS имеет 85 миллионов записей. Он большой. < /Li>
< /ul>
Так что это первая проблема. После того, как я решу, что мне нужно связать данные из этого выбора обратно с моим исходным выбором, и я пытался гнездировать приведенный выше выбор и псевдоним, но я просто не могу его разобрать. < /P>
Это была моя идея; Любая помощь будет оценена. LEFT JOIN (SELECT
MAX(delivery_item.activity_start_time) as deliveryDate,
stockLocation.entity_id as location,
prod.entity_id as product
FROM acts delivery_item
LEFT JOIN participations prod_deliv_part on prod_deliv_part.act_id = delivery_item.act_id
AND prod_deliv_part.arch_short_name = "participation.stock"
LEFT JOIN entities prod on prod.entity_id = prod_deliv_part.entity_id
LEFT JOIN act_relationships deliv_item_r ON deliv_item_r.target_id = delivery_item.act_id AND deliv_item_r.arch_short_name = 'actRelationship.supplierDeliveryItem'
LEFT JOIN acts delivery ON delivery.act_id = deliv_item_r.source_Id
LEFT JOIN participations delivery_location_p ON delivery_location_p.act_id = delivery.act_id AND delivery_location_p.arch_short_name = 'participation.stockLocation'
LEFT JOIN entities stockLocation on stockLocation.entity_id = delivery_location_p.entity_id
WHERE delivery_item.arch_short_name = "act.supplierDeliveryItem"
GROUP BY location,product) deliveries on deliveries.deliveryDate = delivery_item.activity_start_time and deliveries.location = stockLocation.entity_Id and deliveries.product = prod.entity_id


Подробнее здесь: https://stackoverflow.com/questions/796 ... rs-that-se