M
Marian Edu
Guest
you do realise that cost base optimiser isn't the holy grail, it's a fact for SQL because there is no such a thing as 'static compile-time queries' but there is no rdbms engine in the world that will perform well on any free-form user query. beside the optimiser need a lot of DBA attention, with no update statistics it's pretty much useless and hints are not really exceptions, at least when it comes of writing complex queries the developer is capable of doing a much better job that the optimiser... talking about a good one of course 
shelling out to sqlexp means you'll have to have the sql engine running, need user with rights granted, run update statistics periodically, 4gl triggers won't fire, sql-width issue might bite you from time to time then you have to find a way to catch the result from stdout or pipe it through a file in a format you can use from within 4gl and finally have that result set loaded in a temp-table - it's structure most probably need to be dynamically created based on user's query or result set output... not to mention a connection is established for each request which will give you some nice latency to enjoy. now, about Alon's query optimiser we did had some interesting discussions at the time and have to say I'm sorry things didn't worked out but there are still some things that can be done for dynamic queries... having a 4GL version of the cost base optimiser can start from 'update statistics', being able to change the query join order based on number of records that exists in each table could be a first step, then make that more complicated by using 'query plan' cache to reduce the time spent on re-engineering a query string that is 'similar' to one that was already executed. however, this is not an easy job and I fear there is really no market for something like that... vast majority in Progress world expect everything to come from PSC so guess we just have to wait a bit longer 
Marian Edu ------------------ www.acorn.ro www.akera.io
Continue reading...
shelling out to sqlexp means you'll have to have the sql engine running, need user with rights granted, run update statistics periodically, 4gl triggers won't fire, sql-width issue might bite you from time to time then you have to find a way to catch the result from stdout or pipe it through a file in a format you can use from within 4gl and finally have that result set loaded in a temp-table - it's structure most probably need to be dynamically created based on user's query or result set output... not to mention a connection is established for each request which will give you some nice latency to enjoy. now, about Alon's query optimiser we did had some interesting discussions at the time and have to say I'm sorry things didn't worked out but there are still some things that can be done for dynamic queries... having a 4GL version of the cost base optimiser can start from 'update statistics', being able to change the query join order based on number of records that exists in each table could be a first step, then make that more complicated by using 'query plan' cache to reduce the time spent on re-engineering a query string that is 'similar' to one that was already executed. however, this is not an easy job and I fear there is really no market for something like that... vast majority in Progress world expect everything to come from PSC so guess we just have to wait a bit longer Marian Edu ------------------ www.acorn.ro www.akera.ioContinue reading...