Hi Zlatko, In your commands, I did't find table "stocks" used in view "qry_stocks_sum". In any case, try to use SQLite default types, like REAL and TEXT in place of FLOAT and VARCHAR. The type "VARCHAR(0)" is not indicated for field type and length, please use some thing like "TEXT(40)". Finally, indexes help queries performances.
[]'s, Marco Antonio Abreu IT Quality Systems [EMAIL PROTECTED] http://www.itquality.com.br Zlatko Matic wrote: > 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] > ----------------------------------------------------------------------------- > > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
