I am curious as to how much overhead building a dynamic query in a trigger adds to the process. The example:
Have a list of subcontractors, each of which gets unique pricing. There is a total of roughly 100,000 items available and some 100 subcontractors. The 2 design choices would be 100 tables (one for each sub) at 100,000 rows or 1 table with 10,000,000 rows.
Choice 1: table has item number (indexed) and price
Choice 2: table has subcontractor id, item number, and price; index on (subcontractorid, item number).
Table of orders would have a trigger to insert line item cost: ----------------------------------- Trigger Choice 1: Select into thetable lookupprice from subcontractors where subcontractorid = NEW.subcontractorid;
thequery := ''Select price from '' || thetable.lookupprice || '' where itemnumber = '' || NEW.itemnumber;
FOR therow IN EXECUTE thequery LOOP NEW.itemcost := therow.price; END LOOP; RETURN NEW; ----------------------------------- Trigger Choice 2: Select into thetable lookupprice from subcontractors where subcontractorid = NEW.subcontractorid;
Select into therow price from mastertable where subcontractorid = NEW.subcontractorid and itemnumber = NEW.itemnumber;
NEW.itemcost := therow.price; RETURN NEW; -----------------------------------
Doing a select from the command line, the mastertable method (with id and partno index) is faster than looking up a single item in a named table (with partno index). At what point would Trigger Choice 2 fall behind performance with Trigger Choice 1 (if ever)? Is there a way to analyze the performance of dynamic queries? If I had only 10 subcontractors or if I had 1000 subcontractors, at what point is the overhead of building/executing a dynamic query negated by the amount of time to look up both the subid and part number in one massive table?
Thanks,
Sven
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster