You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I have a Sql that is very slowly in Firebird 2.x and fast in Firebird 1.5, firebird 2.1 also sugest another Plan different then Firebird 1.5.
The file BD 1.5.zip in attachment is the BD in Firebird 1.5
The file BD 2.1.zip in attachment is the BD in Firebird 2.1.2.
The file SQL.txt is the SQL that I made in both version, and the performances are very different, in firebird 1.5 is very faster, why ?
The text was updated successfully, but these errors were encountered:
I have to admit this is a bad example, because the SQL query is wrong. When you combine LEFT JOIN with the WHERE clause against the right table, it effectively converts your query into an inner join but with a pre-defined join order (A->M, which is a bad choice in this case). If you really intended to use an inner join, then write that explicitly and you'll get better performance on both v1.5 and v2.x. Otherwise, the query should be written as:
From Artigo A
Left Outer Join Movimentos_Produtos M
On (A.Codigo_Arm = M.Armazem and A.Referencia = M.Prod_Ref and M.Data_Doc <= :D)
Regardless the above, I agree that the usage of the index MOVIMENTOS_PRODUTOS_IDX04 looks like a bad idea. The v1.5 optimizer uses a heuristical approach and rejects that index because its selectivity is much worse than the one for MOVIMENTOS_PRODUTOS_IDX01. The v2.x optimizer uses a cost based approach instead and considers it being an useful addition allowing to reduce the effective stream cardinality (number of rows to read from M). But with your data, this additional predicate doesn't help much, reducing the cardinality by about 10% only, while actually costing more.
Submitted by: Pedro Rodrigues (pemisaro)
Attachments:
BD 1.5.zip
BD 2.1.zip
Sql.txt
Votes: 4
I have a Sql that is very slowly in Firebird 2.x and fast in Firebird 1.5, firebird 2.1 also sugest another Plan different then Firebird 1.5.
The file BD 1.5.zip in attachment is the BD in Firebird 1.5
The file BD 2.1.zip in attachment is the BD in Firebird 2.1.2.
The file SQL.txt is the SQL that I made in both version, and the performances are very different, in firebird 1.5 is very faster, why ?
The text was updated successfully, but these errors were encountered: