--- In firebird-support@yahoogroups.com, "ericsibert1971"
<courrier@e...> wrote:

> Hi,
>
> I have a table with a lot of names in a colomn. Giving the beginning
> of a word, I want to be able to go to the corresponding area in the
> table and to have an overview of the area. More precisely, I'm
> looking for FISHE. I want to retrieve the first record in
> alphabetical after FISHE, inclunding FISHE itself if it exists. I
> also want the ten next and ten previous records in alphabetical
> order. Therefore, I tried :
> - for the first and ten next :
> SELECT FIRST 11
> ID_NAME
> FROM NAMES
> WHERE NAME>='FISHE'
> ORDER BY NAME;
>
> -for the ten previous :
> SELECT FIRST 10
> ID_NAME
> FROM NAMES
> WHERE NAME<'FISHE'
> ORDER BY NAME DESC;
>
> But this is very slow. 1m45s each on my server (FB1.5.2 /Linux).
>
> In opposite :
> SELECT FIRST 11
> ID_NAME
> FROM NAMES
> WHERE NAME LIKE 'FISHE%'
> ORDER BY NAME;
>
> only need 1s (but don't give the right result if less than 11 names
> are starting with FISHE).
>
> Any idea to solve my problem (the global one, not just improving my
> requests)?
>
> Eric

Eric,

You haven't mentioned whether you have any index(es) on Name that
could be used in these queries. If you have both an ascending index
and a descending index on the Name field.

Assuming BOTH these indexes are available, it should run in a few ms

SELECT FIRST 11
ID_NAME
FROM NAMES
WHERE NAME>='FISHE'
ORDER BY NAME;

SELECT FIRST 10
ID_NAME
FROM NAMES
WHERE NAME<'FISHE'
ORDER BY NAME DESC;

You may be able to use a union to collect the results together, but I
don't know if it will like the two different order by statements, so
maybe the following stored procedure would help.

Firstly, the index

CREATE DESCENDING INDEX IX_NAME_D ON NAMES (NAME);
CREATE INDEX IX_NAME ON NAMES (NAME);

(I cant remember the exact create index syntax so look it up yourself)

Secondly, the SP

CREATE PROCEDURE SP_GETAREA
(
WORDOFINTEREST VARCHAR(50)
)
RETURNS
(
ID_NAME INTEGER,
NAME VARCHAR(50)
)
AS
BEGIN
FOR SELECT FIRST 11
ID_NAME
FROM NAMES
WHERE NAME>=:WORDOFINTEREST
ORDER BY NAME
INTO :ID_NAME, :NAME DO
BEGIN
SUSPEND;
END

SELECT FIRST 10
ID_NAME
FROM NAMES
WHERE NAME<:WORDOFINTEREST
ORDER BY NAME DESC
INTO :ID_NAME, :NAME DO
BEGIN
SUSPEND;
END

END
^

You may need to sort the SP output.

select * from sp_getarea('FISHE') order by NAME;

Hope that helps.
Adam