Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Can this query be optimized? |
Tue, Mar 10 2009 5:03 AM | Permanent Link |
Uli Becker | Hi,
I am using this query: select * from untersuchungen where patientenid in (select patientenid from informa.patienten where name like 'becker%' and vorname like 'f%') There are indexes on PatientenID both in "untersuchungen" and in "patienten". Also indexes on "name" and "vorname". Here the execution plan: The following filter condition was applied to the untersuchungen table: "patientenid" IN ( SELECT ALL "patientenid" AS "patientenid" FROM "informa"."patienten" WHERE "Name" = 'becker' [Partial length match on 6 char(s)] AND "Vorname" = 'f' [Partial length match on 1 char(s)] [Row scan (): 0 rows, 0 bytes estimated cost] ORDER BY "patientenid") [Row scan (Untersuchungen): 74862 rows, 33538176 bytes estimated cost] ================================================================================ 3 row(s) returned in 1,453 secs ================================================================================ When executing the subquery alone it is done in 0 seconds (3 records returned). Executing the main query with the values of the subquery like this: select * from untersuchungen where patientenid in (8179,17157,34435) is done in 0 seconds as well. Did I miss anything to optimize the query? Regards Uli |
Tue, Mar 10 2009 5:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Uli
What about rewriting it as a join? Roy Lambert |
Tue, Mar 10 2009 7:16 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Uli,
<< Did I miss anything to optimize the query? >> It doesn't appear so. Can you send me the database catalog ? Thanks, -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Mar 10 2009 7:54 AM | Permanent Link |
Uli Becker | Roy,
> What about rewriting it as a join? Forgot to mention that I tried that. It even takes more time. Uli Becker |
Tue, Mar 10 2009 7:58 AM | Permanent Link |
Uli Becker | Tim,
> It doesn't appear so. Can you send me the database catalog ? I sent the two catalogs by mail. Regards Uli |
Tue, Mar 10 2009 3:14 PM | Permanent Link |
Uli Becker | Roy,
> What about rewriting it as a join? Your are right. I tried a join, but it was the wrong one. This code works very fast: select * from untersuchungen u join informa.patienten p on u.patientenID = p.PatientenID where p.name like 'Becker%' and p.vorname like 'F%'; Thank you. Uli |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |