--- In firebird-support@yahoogroups.com, "liskman" <liskman@...> wrote:

>
> Hi,
>
> I use Firebird 2.0.1 in windows, and have a SQL query:
>
> SELECT * FROM table2
> WHERE id1 IN
> ( SELECT id1
> FROM talbe1
> WHERE recordtime BETWEEN '2007-06-13 00:00:00' AND '2007-06-13
> 23:59:59'
> ORDER BY id1
> ROWS 1000
> )
> ORDER BY id2;
>
> if there is no "ROWS 1000", the query execute very fast(less than
> 200 ms), but add "ROWS 1000" to query, execute time is more than 20
> sec. table1 have 3000 rows, table2 have 4000 rows.
>
> Is there a way to optimize this query ?

Your ROWS condition is in your subquery. That means that for each
record in Table2, it must perform a select on each record in talbe1
(sic), sort it by id1, and only return if the table2.id1 is in that
value. That is a heck of a lot of work.

You may be able to transform it into a simple join, but I think
because your rows limitation is different to your ordering, it may be
difficult. Note that you are not limiting it to 1000 rows if there are
multiple records in table2 for a given talbe1 record. If you meant you
wanted to limit to 1000 rows, you should move that instruction to the
last line of the command.

You could create your query as a stored procedure, and select that
stored procedure with an order by, eg.

CREATE PROCEDURE BLAH
(
STARTTIME TIMESTAMP,
ENDTIME TIMESTAMP
)
RETURNS
(
ID2 INTEGER,
A INTEGER,
B INTEGER,
C INTEGER
)
AS
DECLARE VARIABLE ID1 INTEGER;
BEGIN
FOR
SELECT id1
FROM talbe1
WHERE recordtime BETWEEN :STARTTIME AND :ENDTIME
ORDER BY id1
ROWS 1000
INTO :ID1
DO
BEGIN
FOR
SELECT ID2, A,B,C
FROM TABLE2
WHERE ID1 = :ID1
INTO :ID2, :A, :B, :C
DO
BEGIN
SUSPEND;
END
END
^

Then a query like:

SELECT *
FROM BLAH
ORDER BY ID2

would return the same dataset with I predict a much faster outcome.
You may even be able to use EXECUTE BLOCK to do it, but I have not
used it myself (we need to support FB 1.5).

Adam