Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
One more problem with a SQL in a view or query |
Sun, Nov 9 2008 1:35 PM | Permanent Link |
"KHJ" | 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 |
Sun, Nov 9 2008 3:12 PM | Permanent Link |
"KHJ" | 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 |
Sun, Nov 9 2008 3:49 PM | Permanent Link |
"KHJ" | 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 |
Sun, Nov 9 2008 3:57 PM | Permanent Link |
Ralf Graap | 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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |