Hello,

plan for =:
PLAN JOIN (IWADISOBJE_2 INDEX (IX_IWADISOBJECT_BOLD_ID), GLOBALCONT_1
INDEX (IX_GLOBALCONTEXT_BOLD_ID))
PLAN JOIN (JOIN (DOCUMENT_1 INDEX (IX_DOCUMENT_BOLD_ID), BANNEDOBJE_1
INDEX (IX_BANNEDOBJECTS_USER_BANAON)), JOIN (BUSINESSCL_1 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1), USER__1 INDEX (IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_3 INDEX (IX_IWADISOBJECT_BOLD_ID), USER__2
INDEX (IX_USER__BOLD_ID))
PLAN JOIN (IWADISOBJE_4 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(BUSINESSCL_2 INDEX (IX_BUSINESSCLASSESROOT_BO1V1), USER__3 INDEX
(IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_5 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(CLIENT_1 INDEX (IX_CLIENT_BOLD_ID), BUSINESSCL_3 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (JOIN (DOCUMENT_2 INDEX (IX_DOCUMENT_BOLD_ID), BANNEDOBJE_2
INDEX (IX_BANNEDOBJECTS_USER_BANAON)), JOIN (BUSINESSCL_4 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1), USER__4 INDEX (IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_6 INDEX (IX_IWADISOBJECT_BOLD_ID), USER__5
INDEX (IX_USER__BOLD_ID))
PLAN JOIN (IWADISOBJE_7 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(BUSINESSCL_5 INDEX (IX_BUSINESSCLASSESROOT_BO1V1), USER__6 INDEX
(IX_USER__BOLD_ID)))
PLAN JOIN (JOIN (IWADISOBJE_8 INDEX (IX_IWADISOBJECT_BOLD_ID),
ROLEOBJECT_1 INDEX (IX_ROLEOBJECTSHOLDER_BOLD_ID)), JOIN (ROLE__1
INDEX (IX_ROLE__BOLD_ID), BUSINESSCL_6 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (JOIN (DOCUMENT_3 INDEX (IX_DOCUMENT_BOLD_ID), IWADISOBJE_9
INDEX (IX_IWADISOBJECT_ROLE_COWOBV2)), JOIN (ROLE__2 INDEX
(IX_ROLE__BOLD_ID), BUSINESSCL_7 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (WORD_1 INDEX (IDXWORD1), IWADISOBJE_1 INDEX
(IX_IWADISOBJECT_BOLD_ID))

Prepare time 4116 ms
Execution time: 11236 ms
Fetch time 30 ms

Plan for search via like / starting with (makes no difference):

PLAN JOIN (IWADISOBJE_2 INDEX (IX_IWADISOBJECT_BOLD_ID), GLOBALCONT_1
INDEX (IX_GLOBALCONTEXT_BOLD_ID))
PLAN JOIN (JOIN (DOCUMENT_1 INDEX (IX_DOCUMENT_BOLD_ID), BANNEDOBJE_1
INDEX (IX_BANNEDOBJECTS_USER_BANAON)), JOIN (BUSINESSCL_1 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1), USER__1 INDEX (IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_3 INDEX (IX_IWADISOBJECT_BOLD_ID), USER__2
INDEX (IX_USER__BOLD_ID))
PLAN JOIN (IWADISOBJE_4 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(BUSINESSCL_2 INDEX (IX_BUSINESSCLASSESROOT_BO1V1), USER__3 INDEX
(IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_5 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(CLIENT_1 INDEX (IX_CLIENT_BOLD_ID), BUSINESSCL_3 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (JOIN (DOCUMENT_2 INDEX (IX_DOCUMENT_BOLD_ID), BANNEDOBJE_2
INDEX (IX_BANNEDOBJECTS_USER_BANAON)), JOIN (BUSINESSCL_4 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1), USER__4 INDEX (IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_6 INDEX (IX_IWADISOBJECT_BOLD_ID), USER__5
INDEX (IX_USER__BOLD_ID))
PLAN JOIN (IWADISOBJE_7 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(BUSINESSCL_5 INDEX (IX_BUSINESSCLASSESROOT_BO1V1), USER__6 INDEX
(IX_USER__BOLD_ID)))
PLAN JOIN (JOIN (IWADISOBJE_8 INDEX (IX_IWADISOBJECT_BOLD_ID),
ROLEOBJECT_1 INDEX (IX_ROLEOBJECTSHOLDER_BOLD_ID)), JOIN (ROLE__1
INDEX (IX_ROLE__BOLD_ID), BUSINESSCL_6 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (JOIN (DOCUMENT_3 INDEX (IX_DOCUMENT_BOLD_ID), IWADISOBJE_9
INDEX (IX_IWADISOBJECT_ROLE_COWOBV2)), JOIN (ROLE__2 INDEX
(IX_ROLE__BOLD_ID), BUSINESSCL_7 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (IWADISOBJE_1 INDEX (IDXIWADISOBJECT1), WORD_1 INDEX
(IDXWORD3))

Prepare time 20 ms
Execution time: 685199 ms
Fetch time 3814 ms

Sure that the result set is larger with % and the fetch time is 3
seconds more but the problem is the execution time of the query with %

Again you could download a database with the two queries at
http://www.iwadis.de/upload/FirebirdUsenetPosting.rar

Any solutions where's the problem?

Thanks

Stefan

--- In firebird-support@yahoogroups.com, "Anderson Farias"
<peixedragao@...> wrote:

>
> Hi,
>
> > I although tested the SQL with STARTING and it runs 3-4 minutes

too.

> > Maybe the SQL-Statement is to complex for firebird? Why?
>
> Without knowing your data nor your query plans, I would guess the

plans were

> the same and the difference in time might be for the FETCH since

you may

> have a lot more rows for 'TEST%'.
>
> IBPlanalizer gives a lot of info (plan, reads, time for
> prepare/execute/fetch) you could post here so we can understand

better your

> issue
>
>
> Regards
> Anderson Farias
>