Hello.
I have terrible performance when executing the following query, which
inserts rows from table "products" to table "bom_products":
INSERT INTO bom_products (
plant,
product,
product_description,
product_base_qty_units,
product_base_qty)
SELECT DISTINCT
products.plant,
products.product,
products.product_description,
products.product_base_qty_units,
products.product_base_qty
FROM
products
ORDER BY
products.plant,
products.product;
The query fires trigger "bom_products_tr_after_row_insert" (see below)
that should populate table "bom_components" with corresponding rows
for every row in table "bom_products" (bom_products and bom_components
are one-to-many).
CREATE TABLE [products] (
[products_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[plant] VARCHAR(0) NULL,
[product] VARCHAR(0) NULL,
[product_description] VARCHAR(0) NULL,
[product_base_qty_units] VARCHAR(0) NULL,
[product_base_qty] FLOAT NULL
)
CREATE TABLE [bills_of_materials] (
[bills_of_materials_id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[plant] VARCHAR(0) NULL,
[product] VARCHAR(0) NULL,
[component] VARCHAR(0) NULL,
[component_description] VARCHAR(0) NULL,
[component_brutto_qty] FLOAT NULL,
[component_brutto_qty_units] VARCHAR(0) NULL,
[product_base_qty] FLOAT NULL
)
CREATE TABLE [bom_products] (
[bom_products_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[plant] VARCHAR(0) NULL,
[product] VARCHAR(0) NULL,
[product_description] VARCHAR(0) NULL,
[product_base_qty_units] VARCHAR(0) NULL,
[product_base_qty] FLOAT NULL,
[product_target_qty] FLOAT NULL
)
CREATE TRIGGER [bom_products_tr_after_row_insert]
AFTER INSERT ON [bom_products]
FOR EACH ROW
BEGIN
INSERT INTO bom_components(
plant,
product,
component,
component_description,
component_brutto_qty,
component_brutto_qty_units,
product_base_qty,
product_target_qty,
component_stock_unrestricted,
component_stock_restricted,
component_stock_qlty_insp,
component_stock_blocked,
component_stock_in_transfer,
component_stock_returns,
component_stock_total)
SELECT DISTINCT
qry_bom_components_input.plant,
qry_bom_components_input.product,
qry_bom_components_input.component,
qry_bom_components_input.component_description,
qry_bom_components_input.component_brutto_qty,
qry_bom_components_input.component_brutto_qty_units,
qry_bom_components_input.product_base_qty,
NEW.product_target_qty,
qry_bom_components_input.component_stock_unrestricted,
qry_bom_components_input.component_stock_restricted,
qry_bom_components_input.component_stock_qlty_insp,
qry_bom_components_input.component_stock_blocked,
qry_bom_components_input.component_stock_in_transfer,
qry_bom_components_input.component_stock_returns,
qry_bom_components_input.component_stock_total
FROM
qry_bom_components_input
WHERE qry_bom_components_input.product=NEW.product
ORDER BY
qry_bom_components_input.plant,
qry_bom_components_input.product,
qry_bom_components_input.component;
END
CREATE VIEW qry_bom_components_input
AS
SELECT DISTINCT bills_of_materials.plant AS plant,
bills_of_materials.product AS product,
bills_of_materials.component AS component,
bills_of_materials.component_description AS component_description,
bills_of_materials.component_brutto_qty AS component_brutto_qty,
bills_of_materials.component_brutto_qty_units AS
component_brutto_qty_units,
bills_of_materials.product_base_qty AS product_base_qty,
bills_of_materials.product_base_qty AS product_target_qty,
qry_stocks_sum.material_stock_unrestricted AS
component_stock_unrestricted,
qry_stocks_sum.material_stock_restricted AS
component_stock_restricted, qry_stocks_sum.material_stock_qlty_insp AS
component_stock_qlty_insp, qry_stocks_sum.material_stock_blocked AS
component_stock_blocked, qry_stocks_sum.material_stock_in_transfer AS
component_stock_in_transfer, qry_stocks_sum.material_stock_returns AS
component_stock_returns, qry_stocks_sum.material_stock_total AS
component_stock_total
FROM bills_of_materials bills_of_materials LEFT JOIN qry_stocks_sum
qry_stocks_sum ON bills_of_materials.component =
qry_stocks_sum.material AND bills_of_materials.plant =
qry_stocks_sum.plant
ORDER BY bills_of_materials.plant, bills_of_materials.product,
bills_of_materials.component,
bills_of_materials.component_description,
bills_of_materials.component_brutto_qty,
bills_of_materials.component_brutto_qty_units,
bills_of_materials.product_base_qty,
qry_stocks_sum.material_stock_unrestricted,
qry_stocks_sum.material_stock_restricted,
qry_stocks_sum.material_stock_qlty_insp,
qry_stocks_sum.material_stock_blocked,
qry_stocks_sum.material_stock_in_transfer,
qry_stocks_sum.material_stock_returns,
qry_stocks_sum.material_stock_total,
bills_of_materials.product_base_qty
CREATE VIEW [qry_stocks_sum] AS
SELECT stocks.material AS material,
stocks.material_description AS material_description,
stocks.material_type AS material_type,
stocks.plant AS plant,
sum(stocks.material_stock_unrestricted) AS
material_stock_unrestricted,
sum(stocks.material_stock_restricted) AS material_stock_restricted,
sum(stocks.material_stock_qlty_insp) AS material_stock_qlty_insp,
sum(stocks.material_stock_blocked) AS material_stock_blocked,
sum(stocks.material_stock_returns) AS material_stock_returns,
sum(stocks.material_stock_in_transfer) AS material_stock_in_transfer,
sum(stocks.material_stock_total) AS material_stock_total,
stocks.material_stock_units AS material_stock_units,
sum(stocks.material_value_unrestricted) AS
material_value_unrestricted,
sum(stocks.material_value_restricted) AS material_value_restricted,
sum(stocks.material_value_qlty_insp) AS material_value_qlty_insp,
sum(stocks.material_value_blocked) AS material_value_blocked,
sum(stocks.material_value_in_transfer) AS material_value_in_transfer,
sum(stocks.material_value_returns) AS material_value_returns,
sum(stocks.material_value_total) AS material_value_total,
stocks.material_value_units AS material_value_units
FROM stocks
GROUP BY stocks.material, stocks.material_description,
stocks.material_type,
stocks.plant, stocks.material_stock_units, stocks.material_value_units
ORDER BY stocks.material, stocks.material_description,
stocks.material_type,
stocks.plant
CREATE TABLE [bom_components] (
[bom_components_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[plant] VARCHAR(0) NULL,
[product] VARCHAR(0) NULL CONSTRAINT bom_components_bom_components_id
REFERENCES bom_products(product) ON DELETE CASCADE ON UPDATE CASCADE,
[component] VARCHAR(0) NULL,
[component_description] VARCHAR(0) NULL,
[component_brutto_qty] FLOAT NULL,
[component_brutto_qty_units] VARCHAR(0) NULL,
[product_base_qty] FLOAT NULL,
[product_target_qty] FLOAT NULL,
[component_required_qty] FLOAT NULL,
[component_stock_unrestricted] FLOAT NULL,
[component_stock_restricted] FLOAT NULL,
[component_stock_in_transfer] FLOAT NULL,
[component_stock_qlty_insp] FLOAT NULL,
[component_stock_blocked] FLOAT NULL,
[component_stock_returns] FLOAT NULL,
[component_stock_total] FLOAT NULL
)
I tested all queries without triggers and they work OK. But, when I
put it in trigger it is terrible slow (take many ours to finish)!
What could be the reason for that?
Please note that it is ported from PostgreSQL where it has been
executed in matter of seconds...
Thanks in advance,
Zlatko
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
