Re: [firebird-support] Re: V1.56 query killing my V2.54 app
On 08/04/2015 19:44, andrew_s_...@yahoo.com [firebird-support] wrote: After more than a decade of Firebird I'm more than used to using the +0 to get the results desired from the engine I'm used to writing compilers, in which world it is inconceivable that a + 0 wouldn't get optimised out by constant folding, so it's not something I'd have thought of trying for myself! -- Tim Ward
Re: [firebird-support] Re: V1.56 query killing my V2.54 app
On Apr 8, 2015, at 10:05 AM, andrew_s_...@yahoo.com [firebird-support] firebird-support@yahoogroups.com wrote: Let's see when histograms come to the party, it'll make things more interesting for sure. G Histograms may be less useful in Firebird than you might think, unless there's a lot of work done in query preparation. In order to use the fact that there are lots more 0's than 1's in a two valued index effectively, Firebird has to know whether you're looking for a 1 (good) or as 0 (forget the index). Firebird queries are optimized when they are prepared, so without major changes, the histogram is useful only for literal values - not parameters. Probably a query could be partially optimized at prepare time and have a final optimization when the query is executed and all paramters are known. However, there's also the case where values become known ony during the execution of a join. Should Firebird do a row-by-row optimization of a nested loop join? I wonder if you tripped over an improvement in Firebird. In 1.5, Firebird kept only the total selectivity for compound indexes. More recent versions keep the selectivity at each column - e.g. if you have a two valued column as the first part of an index and a very selective colum next, Firebird 1.5 considered the index a good candidate, even if you only matched the first part. In later, smarter versions, it recognizes that the first part alone is not very good. Good luck, Ann
[firebird-support] Re: V1.56 query killing my V2.54 app
Hi Mark, Yes, I did a Backup/Restore and updated all the index statistics to give the engine the best chances possible for the select... Thanks. Andrew
[firebird-support] Re: V1.56 query killing my V2.54 app
Hi Set, (I assume you have optimized poorly performing queries with Fb 1.5 long ago, rather than been waiting for the upgrade). Yes. If you compare new, unoptimized queries between 1.5 and 2.5, odds are that you would conclude that 2.5 are better. I have very high expectations for that. On my initial tests everything seems faster. But I'm still testing everything. My app is rather busy and considering that this query in question should be performed in 6 seconds and went to 2 minutes... but now it's back to it's 6 seconds, thanks to you. Let's see when histograms come to the party, it'll make things more interesting for sure. G Thanks again Andrew
[firebird-support] Re: V1.56 query killing my V2.54 app
Hello Ann, Firebird 1.5 considered the index a good candidate, even if you only matched the first part. In later, smarter versions, it recognizes that the first part alone is not very good. I did change the index to only the boolean 1/0 field... then I changed back adding a part of the primary key to boost a high graularity on the index trying to make things work as I needed. After removing the compuond part and running some tests with and without the +0 on the query I got: With the +0 PLAN SORT (SORT (JOIN (B INDEX (IXColDetSmIntFlag),A INDEX (PK_msttbl Prepare time = 47ms Execute time = 2s 968ms Avg fetch time = 102,34 ms Current memory = 2.129.816 Max memory = 2.201.604 Memory buffers = 75 Reads from disk to cache = 302 Writes from cache to disk = 0 Fetches from cache = 1.555 without the +0 PLAN SORT (SORT (JOIN (A NATURAL, B INDEX (FK_dettbl Prepare time = 16ms Execute time = 1m 20s 562ms Avg fetch time = 2.778,00 ms Current memory = 2.124.564 Max memory = 2.201.604 Memory buffers = 75 Reads from disk to cache = 157.563 Writes from cache to disk = 0 Fetches from cache = 2.909.887 Apparently I made it slightly better using some of v2.5x improvements. After more than a decade of Firebird I'm more than used to using the +0 to get the results desired from the engine but still, I find it illogical that scanning a table with NATURAL doesn't have a much higher cost than any other index to the engine. Well... new engine, new lessons. Thanks. Andrew
