I run a financial data simulation that resets this stored procedure many times and quickly with different parameters every time.
Speed is very important here.
What does this procedure do:
Find the value of the variable STDthat is at the level Xof the input value STDon either side of STD. (This gives the boundaries of the range around the input values STD, Xin length).
Compares a list of variables in this range that match a set of conditions.
Processes this list with a different set of conditions to create the final list, which is the opening price, order type, and closing price.
The following is a compressed (rar) dump table for a single non-transient table.
Here is the SHOW CREATE TABLEinfo table :
| b50d1 | CREATE TABLE `b50d1` (
`pTime` int(10) unsigned NOT NULL,
`Slope` float(8,4) unsigned NOT NULL DEFAULT '0.0000',
`STD` float(8,4) unsigned NOT NULL DEFAULT '0.0000',
`Slope_Pos` int(1) unsigned NOT NULL DEFAULT '2',
`STD_Pos` int(1) unsigned NOT NULL DEFAULT '2',
PRIMARY KEY (`pTime`),
UNIQUE KEY `ID1` (`pTime`,`STD`,`STD_Pos`) USING BTREE,
UNIQUE KEY `ID2` (`pTime`,`Slope`,`Slope_Pos`),
KEY `STD` (`STD`) USING BTREE,
KEY `Slope` (`Slope`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=439340 PACK_KEYS=1
|
Here is a small example table if you do not want to load a dump:
mysql> select * from b50d1 limit 10;
+------------+---------+--------+-----------+---------+
| pTime | Slope | STD | Slope_Pos | STD_Pos |
+------------+---------+--------+-----------+---------+
| 1107309300 | 1.6326 | 1.3924 | 0 | 1 |
| 1107314400 | 6.8959 | 0.2243 | 1 | 1 |
| 1107323100 | 18.1991 | 1.4685 | 1 | 0 |
| 1107335400 | 2.5014 | 0.4736 | 0 | 0 |
| 1107362100 | 4.2878 | 0.8558 | 0 | 1 |
| 1107363300 | 6.9629 | 1.4130 | 0 | 0 |
| 1107363900 | 8.1032 | 0.2860 | 0 | 0 |
| 1107367500 | 16.6244 | 0.6159 | 0 | 0 |
| 1107368400 | 19.3792 | 1.1875 | 0 | 0 |
| 1107369300 | 21.9457 | 0.9426 | 0 | 0 |
+------------+---------+--------+-----------+---------+
And here is my code:
Parameters:
t1 varchar (15), inp1 float, inp2 int, inp3 float, inp4 int, inp9 int, inp10 int
Procedure:
BEGIN
DROP TABLE IF EXISTS MainList;
DROP TABLE IF EXISTS SearchListA;
DROP TABLE IF EXISTS List1;
DROP TABLE IF EXISTS List2;
CREATE TABLE MainList(
`pTime` int unsigned NOT NULL,
`STD` double unsigned NOT NULL,
`STD_Pos` int unsigned NOT NULL,
PRIMARY KEY (`pTime` ASC),
INDEX (`STD` ASC) USING BTREE,
INDEX `ID1` (`pTime` ASC, `STD` ASC) USING BTREE,
INDEX `ID2` (`pTime` ASC, `STD` ASC, `STD_Pos` ASC) USING BTREE
) ENGINE = MEMORY;
CREATE TABLE SearchListA(
`pTime` int unsigned NOT NULL ,
`STD` double unsigned NOT NULL,
`STD_Pos` int unsigned NOT NULL,
`SearchEnd` int unsigned NOT NULL,
PRIMARY KEY (`pTime` ASC),
INDEX (`STD` ASC),
INDEX `ID1` (`pTime`,`STD` ASC) USING BTREE,
INDEX `ID2` (`pTime` ASC, `STD` ASC, `STD_Pos` ASC) USING BTREE
) ENGINE = MEMORY;
CREATE TABLE List1(
`pTime` int unsigned NOT NULL,
`STD` double unsigned NOT NULL DEFAULT 0,
`STD_Pos` int unsigned NOT NULL DEFAULT 2,
PRIMARY KEY (`pTime` ASC),
INDEX (`STD`,`STD_Pos` ASC) USING BTREE
) ENGINE = MEMORY;
CREATE TABLE List2(
`pTime` int unsigned NOT NULL,
`Slope` double unsigned NOT NULL DEFAULT 0,
`Slope_Pos` int unsigned NOT NULL DEFAULT 2,
PRIMARY KEY (`pTime` ASC),
INDEX `ID1` (`Slope`,`Slope_Pos` ASC) USING BTREE
) ENGINE = MEMORY;
SET @s1 = CONCAT('INSERT INTO List1(pTime,STD,STD_Pos) SELECT t1.pTime, t1.STD, t1.STD_Pos FROM ',t1,' AS t1 USE INDEX (STD) WHERE t1.STD < ', ABS(inp1),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by STD DESC limit ', inp2);
PREPARE stmt FROM @s1;
EXECUTE stmt;
SET @lim = inp2+(inp2-(SELECT count(*) FROM List1));
SET @s2 = CONCAT('INSERT INTO List1(pTime,STD,STD_Pos) SELECT t1.pTime, t1.STD, t1.STD_Pos FROM ',t1,' AS t1 USE INDEX (STD) WHERE t1.STD >=', ABS(inp1),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by STD ASC limit ?');
PREPARE stmt FROM @s2;
EXECUTE stmt USING @lim;
SET @s3 = CONCAT('INSERT INTO List2(pTime,Slope,Slope_Pos) SELECT t1.pTime, t1.Slope, t1.Slope_Pos FROM ',t1,' AS t1 USE INDEX (Slope) WHERE t1.Slope < ',ABS(inp3),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by Slope DESC limit ', inp4);
PREPARE stmt FROM @s3;
EXECUTE stmt;
SET @lim = inp4+(inp4-(SELECT count(*) FROM List2));
SET @s4 = CONCAT('INSERT INTO List2(pTime,Slope,Slope_Pos) SELECT t1.pTime, t1.Slope, t1.Slope_Pos FROM ',t1,' AS t1 USE INDEX (Slope) WHERE t1.Slope >=',ABS(inp3),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by Slope ASC limit ?');
PREPARE stmt FROM @s4;
EXECUTE stmt USING @lim;
SET @minSL1 = (SELECT MIN(Slope) FROM List2);
SET @maxSL1 = (SELECT MAX(Slope) FROM List2);
SET @minSD1 = (SELECT MIN(STD) FROM List1);
SET @maxSD1 = (SELECT MAX(STD) FROM List1);
SET @s = CONCAT('INSERT INTO MainList(pTime,STD,STD_Pos) SELECT t1.pTime, t1.STD, t1.STD_Pos FROM ',t1,' AS t1 ',
' WHERE t1.Slope >= ', @minSL1 ,
' AND t1.Slope <= ', @maxSL1 ,
' AND t1.STD >= ', @minSD1 ,
' AND t1.STD <= ', @maxSD1,
' AND ((t1.Slope_Pos <> t1.STD_Pos) AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' ) ORDER BY t1.pTime'
);
PREPARE stmt FROM @s;
EXECUTE stmt;
INSERT INTO SearchListA (pTime,STD,STD_Pos,SearchEnd)
SELECT sql_no_cache M1.pTime,M1.STD,M1.STD_Pos,M2.pTime
FROM MainList as M1
JOIN MainList as M2
ON(M2.pTime = (
SELECT M3.pTime FROM MainList as M3 WHERE M3.pTime>M1.pTime ORDER BY M3.pTime ASC limit 1)
);
SET @q = CONCAT('
SELECT
m.pTime as OpenTime,
CASE WHEN m.STD_Pos = 0 THEN 1 ELSE -1 END As Type,
min( big.pTime ) as CloseTime
FROM
SearchListA m
JOIN ',t1,' as big ON (
big.pTime > m.pTime
AND big.pTime <= LEAST(m.SearchEnd,m.pTime+172800)
AND m.STD < big.STD AND m.STD_Pos <> big.STD_Pos
)
GROUP BY m.pTime
');
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
Sample function call:
CALL `data`.`JustMain`( "b50d1",1.5,5000,6.43,5000,1121126400,1278892800)
I can run this procedure in a second, but faster is better!
If anyone needs more information, I will include it.