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

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

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

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

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

Yusuf,

<< How should i write this to speed it up? >>

You can't with UPDATE/DELETE statements.

Tim Young
Elevate Software
www.elevatesoft.com

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

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

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

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