[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
[firebird-support] V1.56 query killing my V2.54 app
Hi all, I'm trying to upgrade an app to v2.54 from v1.56 but there are some queries that aren't planning the way they should. I have this query: Select Distinct a.col01Int, a.col02SmInt, a.col03SmInt, a.col04VarCh45 from dettbl b Inner Join msttbl a on (a.col01Int = i.col01Int and a.col02SmInt = i.col02SmInt and a.col03SmInt = i.col03SmInt) where b.ColDetSmIntFlag = 1 Order by a.col04VarCh45 on v1.56 I get this plan: PLAN SORT (SORT (JOIN (B INDEX (IXColDetSmIntFlag),A INDEX (PK_msttbl and on v2.54 I get PLAN SORT (SORT (JOIN (A NATURAL, B INDEX (FK_dettbl that is killing my app. The tables in question have 450K+ and 800K+ rows. The DDL for the tables on both versions of Firebird are: CREATE TABLE msttbl ( col01IntDM_INTEGNNULL NOT NULL /* DM_INTEGNNULL = INTEGER NOT NULL CHECK (value 0) */, col02SmInt DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT NULL CHECK (value 0) */, col03SMInt DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT NULL CHECK (value 0) */, col04VarCh45DM_NOMESNNULL /* DM_NOMESNNULL = VARCHAR(45) NOT NULL */ ); ALTER TABLE msttbl ADD CONSTRAINT PK_msttbl PRIMARY KEY (col01Int, col02Int, col03Int); CREATE INDEX IXmsttbl_col04VarCh45 ON msttbl (col04VarCh45); CREATE TABLE dettbl ( col01IntDM_INTEGNNULL NOT NULL /* DM_INTEGNNULL = INTEGER NOT NULL CHECK (value 0) */, col02SmInt DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT NULL CHECK (value 0) */, col03SMInt DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT NULL CHECK (value 0) */, col04IntDM_INTEGNNULL NOT NULL /* DM_INTEGNNULL = INTEGER NOT NULL CHECK (value 0) */, ColDetSmIntFlag DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT NULL CHECK (value 0) */ ); ALTER TABLE dettbl ADD CONSTRAINT PK_dettbl PRIMARY KEY col01Int, col02Int, col03Int, col04Int); ALTER TABLE dettbl ADD CONSTRAINT FK_dettbl FOREIGN KEY (col01Int, col02Int, col03Int) REFERENCES msttbl (col01Int, col02Int, col03Int); CREATE INDEX IXColDetSmIntFlag ON dettbl (ColDetSmIntFlag, col04Int); I've been changing the query and trying to change indexes to no success. Is there a way to force FB 2.54 to chose the right indexes? TIA Andrew
Re: [firebird-support] V1.56 query killing my V2.54 app
Hello Set, Actually, the problem isn't with FK_dettbl, but with the A Natural. In a Master detail relationship with 450K+ rows in the master and 800K+ rows in the detail, that natural is a killer. Thanks Again
Re: [firebird-support] V1.56 query killing my V2.54 app
Ok, used the +0 and worked. On v1.56 I was used with setting up a high granularity data column (col04Int - part of the primary key) with a True/false (0/1) type of column (ColDetSmIntFlag) to boost the selectivity of the index IXColDetSmIntFlag. I kept the index with that configuration for a just in case. (The stat of the index is 0,01407...) Set, don't get me wrong, I am very gratefull for your help and for Firebird, but saying that a Natural on a big table seems better than an index doesn't compute for me, and I've been using Firebird since Interbase and Oracle since v6 (as DBA BTW). At a least case scenario it should use the PK when there is a declared join using the PK. For me, the new optimizer is wierd and highly illogical. Thanks Andrew
