SELECT   a.name_id,  a.name_bez,  b.norm_id,  b.norm_bez,
b.norm_land_d,
        c.ngruppe_id,  c.ngruppe_wksttyp_id,  d.wksttyp_bez_d

FROM   name a,   norm  b,   ngruppe c,   wksttyp d

WHERE
((a.name_norm_id = b.norm_id)          AND
 (a.name_ngruppe_id = c.ngruppe_id)    AND
 (c.ngruppe_wksttyp_id = d.wksttyp_id))

AND (UPPER (a.name_bez)  LIKE UPPER ('%42%'))
AND (UPPER (b.norm_bez)  LIKE UPPER ('%'))
AND (UPPER (b.norm_land_d) LIKE UPPER ('%'))
AND (UPPER (d.wksttyp_bez_d) LIKE UPPER ('%'))

ORDER BY
 a.name_bez

I used this sql statement within DBISAM query without any problems.
Now I used it in a EDB Query with params instead of the %.
Nothing happend the application was waiting and waiting.

To check it I created a test view with the sql statement and opened the
view in EDBManager. Same result the hourglas is showing nothing
happened. I have to break the program.

In the log file is no entry.
What could be the reason?

EDB 2.02 B3

Karlheinz

KHJ wrote:

>
> To check it I created a test view with the sql statement and opened
> the view in EDBManager. Same result the hourglas is showing nothing
> happened. I have to break the program.
>
> In the log file is no entry.
> What could be the reason?
>
> EDB 2.02 B3
>
> Karlheinz

Problem was that the query / view needs 6 minutes to find 148 rows from
6606 in the main table.

I am really surprised about the low speed.
Some hints for me to optimize the query?

Karlheinz

KHJ wrote:

> I am really surprised about the low speed.
> Some hints for me to optimize the query?
>
> Karlheinz

With this SQL statement it is much, much, much faster (< 1 sec to
execute instead of approx 6 minutes before)

SELECT   a.name_id,  a.name_bez,  b.norm_id,  b.norm_bez,
b.norm_land_d,
        c.ngruppe_id,  c.ngruppe_wksttyp_id,  d.wksttyp_bez_d

FROM   name a

INNER JOIN norm  b on a.name_norm_id = b.norm_id
INNER JOIN ngruppe c on a.name_ngruppe_id = c.ngruppe_id
INNER JOIN wksttyp d on c.ngruppe_wksttyp_id = d.wksttyp_id   

WHERE (UPPER (a.name_bez)  LIKE UPPER ('%42%'))
AND (UPPER (b.norm_bez)  LIKE UPPER ('%'))
AND (UPPER (b.norm_land_d) LIKE UPPER ('%'))
AND (UPPER (d.wksttyp_bez_d) LIKE UPPER ('%'))

ORDER BY
 a.name_bez

Hey Karlheinz,

this kind of query are'nt optimized any more.
Use the inner join syntax instead.

Ralf

KHJ schrieb:
> SELECT   a.name_id,  a.name_bez,  b.norm_id,  b.norm_bez,
> b.norm_land_d,
>          c.ngruppe_id,  c.ngruppe_wksttyp_id,  d.wksttyp_bez_d
>
> FROM   name a,   norm  b,   ngruppe c,   wksttyp d
>
> WHERE
>  ((a.name_norm_id = b.norm_id)          AND
>   (a.name_ngruppe_id = c.ngruppe_id)    AND
>   (c.ngruppe_wksttyp_id = d.wksttyp_id))
>
> AND (UPPER (a.name_bez)  LIKE UPPER ('%42%'))
> AND (UPPER (b.norm_bez)  LIKE UPPER ('%'))
> AND (UPPER (b.norm_land_d) LIKE UPPER ('%'))
> AND (UPPER (d.wksttyp_bez_d) LIKE UPPER ('%'))
>
> Karlheinz