oh, hm that looks kinda messed up ;)

Well, anyways I just rebooted the server and now it takes only like
90ms, so I guess that's fine

--- In firebird-support@yahoogroups.com, "Tom Frey" <tom@g...> wrote:
> Hi,
>
> I wrote this stored procedure and it takes about 2.5 seconds to
> execute. Do you see a way to make it more efficient?
>
> ALTER PROCEDURE GET_STATS (
> ACCOUNT VARCHAR(10),
> DATE_START VARCHAR(10),
> DATE_END VARCHAR(10))
> RETURNS (
> TRADES_STOCK INTEGER,
> TRADES_OPTION INTEGER,
> TRADES_FUTURE INTEGER,
> T_COMM_STOCK DECIMAL(10,2),
> T_COMM_OPTION DECIMAL(10,2),
> T_COMM_FUTURE DECIMAL(10,2),
> T_OCC_STOCK DECIMAL(10,2),
> T_OCC_OPTION DECIMAL(10,2),
> T_OCC_FUTURE DECIMAL(10,2),
> T_SEC_STOCK DECIMAL(10,2),
> T_SEC_OPTION DECIMAL(10,2),
> T_SEC_FUTURE DECIMAL(10,2),
> T_EX_OPTION DECIMAL(10,2),
> T_EX_FUTURE DECIMAL(10,2),
> MAX_VOLUME_STOCK INTEGER,
> MAX_VOLUME_OPTION INTEGER,
> MAX_VOLUME_FUTURE INTEGER,
> MAX_COMM_STOCK DECIMAL(10,2),
> MAX_COMM_OPTION DECIMAL(10,2),
> MAX_COMM_FUTURE DECIMAL(10,2),
> VOLUME_STOCK_POS INTEGER,
> VOLUME_OPTION_POS INTEGER,
> VOLUME_FUTURE_POS INTEGER,
> VOLUME_STOCK_NEG INTEGER,
> VOLUME_OPTION_NEG INTEGER,
> VOLUME_FUTURE_NEG INTEGER)
> AS
> BEGIN
> SELECT COUNT(ID), SUM(T_COMM), SUM(OCC_FEE), SUM(SEC_FEE),
> MAX(VOLUME), MIN(T_COMM) FROM ABNTRADEDATA WHERE STRLEN(SYMBOL) <6 AND
> SYMBOL NOT LIKE '/%' AND ACCOUNT=:ACCOUNT AND TRD_DATE >= :DATE_START
> AND TRD_DATE <= :DATE_END INTO :TRADES_STOCK, :T_COMM_STOCK,
> :T_OCC_STOCK, :T_SEC_STOCK, :MAX_VOLUME_STOCK, :MAX_COMM_STOCK;
> SELECT COUNT(ID), SUM(T_COMM), SUM(OCC_FEE), SUM(SEC_FEE),
> SUM(EX_FEE), MAX(VOLUME), MIN(T_COMM) FROM ABNTRADEDATA WHERE
> STRLEN(SYMBOL) >5 AND SYMBOL NOT LIKE '/%' AND ACCOUNT=:ACCOUNT AND
> TRD_DATE >= :DATE_START AND TRD_DATE <= :DATE_END INTO :TRADES_OPTION,
> :T_COMM_OPTION, :T_OCC_OPTION, :T_SEC_OPTION, :T_EX_OPTION,
> :MAX_VOLUME_OPTION, :MAX_COMM_OPTION;
> SELECT COUNT(ID), SUM(T_COMM), SUM(OCC_FEE), SUM(SEC_FEE),
> SUM(EX_FEE), MAX(VOLUME), MIN(T_COMM) FROM ABNTRADEDATA WHERE SYMBOL
> LIKE '/%' AND ACCOUNT=:ACCOUNT AND TRD_DATE >= :DATE_START AND
> TRD_DATE <= :DATE_END INTO :TRADES_FUTURE, :T_COMM_FUTURE,
> :T_OCC_FUTURE, :T_SEC_FUTURE, :T_EX_FUTURE, :MAX_VOLUME_FUTURE,
> :MAX_COMM_FUTURE;
> SELECT SUM(VOLUME) FROM ABNTRADEDATA WHERE ACCOUNT = :ACCOUNT AND
> VOLUME > 0 AND strlen(SYMBOL) <6 AND SYMBOL NOT like '/%' AND TRD_DATE
> >= :DATE_START AND TRD_DATE <= :DATE_END INTO :VOLUME_STOCK_POS;
> SELECT SUM(VOLUME) FROM ABNTRADEDATA WHERE ACCOUNT = :ACCOUNT AND
> VOLUME > 0 AND strlen(SYMBOL) >5 AND SYMBOL NOT like '/%' AND TRD_DATE
> >= :DATE_START AND TRD_DATE <= :DATE_END INTO :VOLUME_OPTION_POS;
> SELECT SUM(VOLUME) FROM ABNTRADEDATA WHERE ACCOUNT = :ACCOUNT AND
> VOLUME > 0 AND strlen(SYMBOL) <6 AND SYMBOL like '/%' AND TRD_DATE >=
> :DATE_START AND TRD_DATE <= :DATE_END INTO :VOLUME_FUTURE_POS;
> SELECT SUM(VOLUME) FROM ABNTRADEDATA WHERE ACCOUNT = :ACCOUNT AND
> VOLUME < 0 AND strlen(SYMBOL) <6 AND SYMBOL NOT like '/%' AND TRD_DATE
> >= :DATE_START AND TRD_DATE <= :DATE_END INTO :VOLUME_STOCK_NEG;
> SELECT SUM(VOLUME) FROM ABNTRADEDATA WHERE ACCOUNT = :ACCOUNT AND
> VOLUME < 0 AND strlen(SYMBOL) >5 AND SYMBOL NOT like '/%' AND TRD_DATE
> >= :DATE_START AND TRD_DATE <= :DATE_END INTO :VOLUME_OPTION_NEG;
> SELECT SUM(VOLUME) FROM ABNTRADEDATA WHERE ACCOUNT = :ACCOUNT AND
> VOLUME < 0 AND strlen(SYMBOL) <6 AND SYMBOL like '/%' AND TRD_DATE >=
> :DATE_START AND TRD_DATE <= :DATE_END INTO :VOLUME_FUTURE_NEG;
> SUSPEND;
>
> END
>
> thanks fro your help