A
Alon Blich
Guest
Hello All, In my humble opion, a query optimizer is the holy grail of database applications in terms of performance improvement and maybe as a challenge 
I think, in database applications no other way of improving performance comes close e.g. 32bit and 64bit optimization, multi-threading, transaction per seond etc. but maybe it's just me I read a book about query optimization many years ago that started this way, more or less - In database applications what takes the most amount of time by far are database operations and what takes the most amount of time out of that by far are read operations, and the way to improve that is a query optimizer. For example, let's say we had a report with the following query - for each Order, each OrderLine of Order, each Item of OrderLine. If you had a filter for itemname = "test" and use the same join order - for each Order, each OrderLine of Order, each Item of OrderLine where Item.itemname = "test". then you would run over all the records in the query which is the worst case possible. If the tables had millions of records that could take hours (although this join order would be ideal for filtering by ordernum = " "). But if you changed the join order (which you can with inner joins) to - for each Item where item.itemname = "test", each OrderLine of Item, each Order of OrderLine. then you would only run over the records you requested which is the best possible or ideal case. In this case a few tens of records that will take a few milliseconds to fetch. That's the difference of a program running hours or milliseconds. You cannot have a static, fixed way of running dynamic queries (execution plan) for every condition, or filter. Which in most cases means changing the join order and indexes. I did write a 4gl query optimizer that is 90% complete but I eventually stopped and published the code on the oehive.org (included in the standard libraries project) because ultimately there is no market for it and the interest that I got. I also suggested using b-tree indexes key distributions instead of statistics along time ago which in theory would not require collecting statistics and could porbably be accurate enough for measuring the query's progress. BTW hopefully, I will be presenting an open source framework project at the north amercian conference, if my proposal is accepted, that has a 4gl reporting frameworking and query optimization that can change the join order and indexes according to the filter.
Continue reading...
I think, in database applications no other way of improving performance comes close e.g. 32bit and 64bit optimization, multi-threading, transaction per seond etc. but maybe it's just me I read a book about query optimization many years ago that started this way, more or less - In database applications what takes the most amount of time by far are database operations and what takes the most amount of time out of that by far are read operations, and the way to improve that is a query optimizer. For example, let's say we had a report with the following query - for each Order, each OrderLine of Order, each Item of OrderLine. If you had a filter for itemname = "test" and use the same join order - for each Order, each OrderLine of Order, each Item of OrderLine where Item.itemname = "test". then you would run over all the records in the query which is the worst case possible. If the tables had millions of records that could take hours (although this join order would be ideal for filtering by ordernum = " "). But if you changed the join order (which you can with inner joins) to - for each Item where item.itemname = "test", each OrderLine of Item, each Order of OrderLine. then you would only run over the records you requested which is the best possible or ideal case. In this case a few tens of records that will take a few milliseconds to fetch. That's the difference of a program running hours or milliseconds. You cannot have a static, fixed way of running dynamic queries (execution plan) for every condition, or filter. Which in most cases means changing the join order and indexes. I did write a 4gl query optimizer that is 90% complete but I eventually stopped and published the code on the oehive.org (included in the standard libraries project) because ultimately there is no market for it and the interest that I got. I also suggested using b-tree indexes key distributions instead of statistics along time ago which in theory would not require collecting statistics and could porbably be accurate enough for measuring the query's progress. BTW hopefully, I will be presenting an open source framework project at the north amercian conference, if my proposal is accepted, that has a 4gl reporting frameworking and query optimization that can change the join order and indexes according to the filter.Continue reading...