Description:
When using a FUNCTION to complete part of a WHERE clause there is a huge performance problem. Scenario: We have an inventory database. Inventory items can be "contained" with in another inventory item (i.e. kitting). There are times that we need to find what the highest level inventory item is from some child point. Example: Inventory item A1 contains B1 which contains C1 whcih contains D1. A1's container (ContInvKey) will be zero (highest level). A row/col view would look like this:
A1, 0 (highest level)
B1, A1
C1, B1
D1, C1
"Inventory" Database table:
CREATE TABLE Inventory
(
InventoryKey int unsigned default 0 not null,
ContInvKey int unsigned default 0 not null,
InventoryName VARCHAR(128) ,
primary key(InventoryKey),
)
TYPE = InnoDB;
CREATE INDEX Inventory_IDX1 ON Inventory(ContInvKey);
We wrote this SQL function:
CREATE FUNCTION InventoryGetHighestInvKey( pInventoryKey INT UNSIGNED ) RETURNS INT UNSIGNED
MAIN: BEGIN
DECLARE mInventoryKey INT UNSIGNED DEFAULT 0;
DECLARE mLastInventoryKey INT UNSIGNED DEFAULT 0;
SET mLastInventoryKey = pInventoryKey;
SEARCH: LOOP
-- Get the Parent/Containing Inventory Key.
SET mInventoryKey = InventoryGetParentInvKey( mLastInventoryKey );
IF mInventoryKey < 1 THEN
-- This inventory key is not contained.
-- That is, it's the HIGHEST most item in the hiarchy.
LEAVE SEARCH;
END IF;
SET mLastInventoryKey = mInventoryKey;
END LOOP SEARCH;
RETURN mLastInventoryKey;
END MAIN;
and then there is this function:
CREATE FUNCTION InventoryGetParentInvKey( pInventoryKey INT UNSIGNED ) RETURNS INT UNSIGNED
MAIN: BEGIN
DECLARE mInventoryKey INT UNSIGNED DEFAULT 0;
IF pInventoryKey > 0 THEN
-- Get the Parent/Containing Inventory Key.
SELECT ContInvKey
INTO mInventoryKey
FROM Inventory
WHERE InventoryKey = pInventoryKey;
END IF;
RETURN mInventoryKey;
END MAIN;
How to repeat:
If you execute the FUNCTION like this:
SELECT InventoryGetHighestInvKey( 123456 );
it will return a key within milliseconds (database has ~1M records).
This statement is also instantaneous:
SELECT * FROM Inventory WHERE InventoryKey = 9234733;
However, execute a query like this:
SELECT * FROM Inventory WHERE InventoryKey = InventoryGetHighestInvKey( 123456 );
and the query takes minutes to run!
We have investigated other tables and they behave the same when we use a function to complete a WHERE clause. We use functions heavily to get primary keys by name (name being a varchar) and they all do the same thing. Example:
SELECT * FROM OrderLineItems WHERE InventoryKey = InventoryGetKeyByName( "Shovel" );
Suggested fix:
I don't know. When watching the process via the SQL Administrator Server Connections, it appears that it keeps RE-Executing the functions over and over and over again for each row. If that's true, then that would explain the problem.
Description: When using a FUNCTION to complete part of a WHERE clause there is a huge performance problem. Scenario: We have an inventory database. Inventory items can be "contained" with in another inventory item (i.e. kitting). There are times that we need to find what the highest level inventory item is from some child point. Example: Inventory item A1 contains B1 which contains C1 whcih contains D1. A1's container (ContInvKey) will be zero (highest level). A row/col view would look like this: A1, 0 (highest level) B1, A1 C1, B1 D1, C1 "Inventory" Database table: CREATE TABLE Inventory ( InventoryKey int unsigned default 0 not null, ContInvKey int unsigned default 0 not null, InventoryName VARCHAR(128) , primary key(InventoryKey), ) TYPE = InnoDB; CREATE INDEX Inventory_IDX1 ON Inventory(ContInvKey); We wrote this SQL function: CREATE FUNCTION InventoryGetHighestInvKey( pInventoryKey INT UNSIGNED ) RETURNS INT UNSIGNED MAIN: BEGIN DECLARE mInventoryKey INT UNSIGNED DEFAULT 0; DECLARE mLastInventoryKey INT UNSIGNED DEFAULT 0; SET mLastInventoryKey = pInventoryKey; SEARCH: LOOP -- Get the Parent/Containing Inventory Key. SET mInventoryKey = InventoryGetParentInvKey( mLastInventoryKey ); IF mInventoryKey < 1 THEN -- This inventory key is not contained. -- That is, it's the HIGHEST most item in the hiarchy. LEAVE SEARCH; END IF; SET mLastInventoryKey = mInventoryKey; END LOOP SEARCH; RETURN mLastInventoryKey; END MAIN; and then there is this function: CREATE FUNCTION InventoryGetParentInvKey( pInventoryKey INT UNSIGNED ) RETURNS INT UNSIGNED MAIN: BEGIN DECLARE mInventoryKey INT UNSIGNED DEFAULT 0; IF pInventoryKey > 0 THEN -- Get the Parent/Containing Inventory Key. SELECT ContInvKey INTO mInventoryKey FROM Inventory WHERE InventoryKey = pInventoryKey; END IF; RETURN mInventoryKey; END MAIN; How to repeat: If you execute the FUNCTION like this: SELECT InventoryGetHighestInvKey( 123456 ); it will return a key within milliseconds (database has ~1M records). This statement is also instantaneous: SELECT * FROM Inventory WHERE InventoryKey = 9234733; However, execute a query like this: SELECT * FROM Inventory WHERE InventoryKey = InventoryGetHighestInvKey( 123456 ); and the query takes minutes to run! We have investigated other tables and they behave the same when we use a function to complete a WHERE clause. We use functions heavily to get primary keys by name (name being a varchar) and they all do the same thing. Example: SELECT * FROM OrderLineItems WHERE InventoryKey = InventoryGetKeyByName( "Shovel" ); Suggested fix: I don't know. When watching the process via the SQL Administrator Server Connections, it appears that it keeps RE-Executing the functions over and over and over again for each row. If that's true, then that would explain the problem.