Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 11 total |
Why is this soooo slow |
Thu, Mar 14 2019 1:50 PM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Hi have following query:
select a_pos, kgf, colli, paletten, vstellplatz from positionen where a_pos in (select a_pos from possammler where vmaster='T19031224' or vmaster='47693') order by kgf >> needs 2,859 seconds and the same query with a join: select a_pos, kgf, colli, paletten, vstellplatz from possammler inner join positionen on possammler.a_pos=positionen.a_pos where possammler.vmaster='T19031224' or possammler.vmaster='47693' order by kgf >> needs only 0,25 seconds In old advantagedatabase the first was faster ... and we have done a lot of queries like the first one. Should we rewrite them all or is there something we're missing? Yusuf Zorlu MicrotronX |
Thu, Mar 14 2019 2:05 PM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | update positionen set issammlerpos=1
where a_pos in (select a_pos from possammler where vmaster='T19031224') How should i write this to speed it up? Yusuf Zorlu MicrotronX |
Fri, Mar 15 2019 3:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Yusuf
The first thing you need to do is check out if the subselect is optimised. Cut it out and paste into a new sql window in EDBManager, make sure you've checked Request Execution Plan and run the query. See what the execution plan tells you. Having said that JOIN will generally be faster (at least in my experience). Roy Lambert |
Fri, Mar 15 2019 8:22 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Hi Roy,
new queries are written with join but we have a lot in filters, customer side scripts writtein with "... in (subselect)" and these were wit advantagedatabase not as slow as they are with elevatedb. Yusuf Zorlu MicrotronX |
Wed, Mar 20 2019 3:45 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Yusuf,
<< Should we rewrite them all or is there something we're missing? >> Yes, you should rewrite them. IN sub-query expressions do *not* limit the number of rows visited in the outer query at all. The IN can only be satisfied by looping through every row in the outer query and checking to see if it exists in the sub-query, and it cannot be reversed or manipulated in any way, unlike inner joins. Tim Young Elevate Software www.elevatesoft.com |
Wed, Mar 20 2019 3:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Yusuf,
<< How should i write this to speed it up? >> You can't with UPDATE/DELETE statements. Tim Young Elevate Software www.elevatesoft.com |
Fri, Mar 22 2019 6:14 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Tim Young [Elevate Software] wrote:
<< Yes, you should rewrite them. IN sub-query expressions do *not* limit the number of rows visited in the outer query at all. The IN can only be satisfied by looping through every row in the outer query and checking to see if it exists in the sub-query, and it cannot be reversed or manipulated in any way, unlike inner joins. No chance that the "optimizer" makes a "inner join" for those? Yusuf Zorlu MicrotronX |
Mon, Mar 25 2019 11:36 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Yusuf,
<< No chance that the "optimizer" makes a "inner join" for those? >> Yes, I understand what's involved. It's a tricky change, however, with a lot of edge cases, so I just haven't had time to tackle it without risking destabilizing other aspects of the optimizer. Tim Young Elevate Software www.elevatesoft.com |
Tue, Mar 26 2019 2:44 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Tim Young [Elevate Software] wrote:
<< Yes, I understand what's involved. It's a tricky change, however, with a lot of edge cases, so I just haven't had time to tackle it without risking destabilizing other aspects of the optimizer. I think this "optimizing" is the reason why advantagedatabase is looking "faster" in runtime because we have a lot of where joins and it's optimized to "inner joins" ... as far as i can say... This optimizing becomes more important if you know that our sql and query engine is open for customer side scripts / changes since 18 years. We have merged now over 60 customers from ads to edb but it's a mass of work, looking into all queries ... all reports ... all customer side scripts and change where joins into inner joins. We have round about 180 customers in front of us which should be merged to version 2019 (only working with edb) ... so a lot of work has to be done on our side ... Do you think it is really impossible to optimize "where joins" to "inner joins" ... in a acceptable timeframe (2 to 6 months)? Yusuf Zorlu MicrotronX |
Mon, Apr 1 2019 2:33 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Yusuf,
<< Do you think it is really impossible to optimize "where joins" to "inner joins" ... in a acceptable timeframe (2 to 6 months)? >> I don't make estimates about feature implementations anymore. My estimates can vary wildly, depending upon what happens with current development tasks and, more importantly, what kind of support issues arise with customers. I lost 3-4 months of development time last year working closely with EDB customers on support issues, and such changes can completely blow any estimates out of the water. All I can say is that I will see what I can do, given the time that I have available. When a feature is completed, it will get released and you will get a notification via email. Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |