Hi!

I have a problem with a sql query and I don't know what can I do to optimize it. Using dbsys utility, locally, in a 32gb RAM computer, the query takes 90 seconds to execute.

The query plan says everyting is "optimized". However, using "JOINOPTIMIZECOSTS" it just takes 4 seconds.
Using "NOJOINOPTIMIZE" takes 2 seconds.

Here is the generated plan:

================================================================================
SQL statement (Executed with 4.45 Build 3)
================================================================================

select histoven.FACTURA,histoven.ARTICULO,histoven.DESCR,familias.NOMBRE

from movim inner join histoven on movim.FACTURA = histoven.FACTURA and
movim.SERIE = histoven.SERIE

inner join familias on familias.CODIGO = histoven.FAMILIA

where movim.MOV = 'VENTA'

Tables Involved
---------------

movim (movim) table opened shared, has 30831 rows
histoven (histoven) table opened shared, has 81103 rows
familias (familias) table opened shared, has 34 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

WHERE Clause Execution
----------------------

Optimizer will attempt to move any joins in the WHERE clause into the JOIN
clause
Use the NOWHEREJOINS clause at the end of the SQL statement to force the
optimizer to leave the joins in the WHERE clause so that they are evaluated
after the JOIN clause expressions

The expression:

movim.MOV = 'VENTA'

is OPTIMIZED, covers 1742 rows or index keys, costs 39020 bytes, and will be
applied to the movim table (movim) before any joins

Join Ordering
-------------

The driver table is the movim table (movim)



The movim table (movim) is joined to the histoven table (histoven) with the
INNER JOIN expression:



movim.FACTURA = histoven.FACTURA and movim.SERIE = histoven.SERIE



The histoven table (histoven) is joined to the familias table (familias) with
the INNER JOIN expression:



histoven.FAMILIA = familias.CODIGO

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

Optimized Join Ordering
-----------------------

The driver table is the familias table (familias)



The familias table (familias) is joined to the histoven table (histoven) with
the INNER JOIN expression:



familias.CODIGO = histoven.FAMILIA



The histoven table (histoven) is joined to the movim table (movim) with the
INNER JOIN expression:



histoven.FACTURA = movim.FACTURA and histoven.SERIE = movim.SERIE

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

familias.CODIGO = histoven.FAMILIA

is OPTIMIZED

The expression:

histoven.FACTURA = movim.FACTURA and histoven.SERIE = movim.SERIE

is OPTIMIZED

================================================================================
>>>>> 58396 rows affected in 90,515 seconds
================================================================================

I have tried to add different 'order by' with no success. The three tables involved has indexes in MOV, FACTURA, FAMILIA, etc. Attached is a screenshot with the tables indexes.

Is this normal, or is it posible to optimize it with some different index type?

Thanks!

Luis,

<< I have a problem with a sql query and I don't know what can I do to optimize it. Using dbsys utility, locally, in a 32gb RAM computer, the query takes 90 seconds to execute.

The query plan says everyting is "optimized". However, using "JOINOPTIMIZECOSTS" it just takes 4 seconds.

Using "NOJOINOPTIMIZE" takes 2 seconds. >>

If you want to email me your database tables, I can tell you exactly why things are executing like they are.

However, my question for now is: is 2 secs good enough for you, or are you wanting to do better ?  Also, is this a query that is performing *worse* in 4.45 than prior versions ?

Tim Young
Elevate Software
www.elevatesoft.com

Hi, Tim.

<< is 2 secs good enough for you, or are you wanting to do better ? >>

Even with 10 seconds I will be happy.

<< Also, is this a query that is performing *worse* in 4.45 than prior versions ? >>

It's difficult to know. Each customer needs different custom queries for their data, for custom statistics and reports.


I will send you the involved tables by email. Thanks, Tim.

Luis C.


Tim Young [Elevate Software] wrote:

Luis,

<< I have a problem with a sql query and I don't know what can I do to optimize it. Using dbsys utility, locally, in a 32gb RAM computer, the query takes 90 seconds to execute.

The query plan says everyting is "optimized". However, using "JOINOPTIMIZECOSTS" it just takes 4 seconds.

Using "NOJOINOPTIMIZE" takes 2 seconds. >>

If you want to email me your database tables, I can tell you exactly why things are executing like they are.

However, my question for now is: is 2 secs good enough for you, or are you wanting to do better ?  Also, is this a query that is performing *worse* in 4.45 than prior versions ?

Tim Young
Elevate Software
www.elevatesoft.com

Hi, Tim.

Did you receive my email? The attached .zip file is about 17mb, so I don't know if you received it. Please, let me know.

Thanks!

Luis C.

Luis Conception Gonzalez wrote:

Hi, Tim.

<< is 2 secs good enough for you, or are you wanting to do better ? >>

Even with 10 seconds I will be happy.

<< Also, is this a query that is performing *worse* in 4.45 than prior versions ? >>

It's difficult to know. Each customer needs different custom queries for their data, for custom statistics and reports.


I will send you the involved tables by email. Thanks, Tim.

Luis C.


Tim Young [Elevate Software] wrote:

Luis,

<< I have a problem with a sql query and I don't know what can I do to optimize it. Using dbsys utility, locally, in a 32gb RAM computer, the query takes 90 seconds to execute.

The query plan says everyting is "optimized". However, using "JOINOPTIMIZECOSTS" it just takes 4 seconds.

Using "NOJOINOPTIMIZE" takes 2 seconds. >>

If you want to email me your database tables, I can tell you exactly why things are executing like they are.

However, my question for now is: is 2 secs good enough for you, or are you wanting to do better ?  Also, is this a query that is performing *worse* in 4.45 than prior versions ?

Tim Young
Elevate Software
www.elevatesoft.com

Luis,

<< Even with 10 seconds I will be happy. >>

In that case, you should use the JOINOPTIMIZECOSTS or NOJOINOPTIMIZE clause with the query.  Those clauses are there for these types of situations where the optimizer cannot make a reliable decision without actually executing the entire query.

Tim Young
Elevate Software
www.elevatesoft.com

Luis,

<< Did you receive my email? The attached .zip file is about 17mb, so I don't know if you received it. Please, let me know. >>

Yes, I received it, but I'll wait until you respond to my later reply before looking into this further.

Tim Young
Elevate Software
www.elevatesoft.com

Hi, Tim.

The problem is that these queries are created at runtime by third-party components, like query designers or report designers. In this case, we are using Report Builder, and it is not posible to add JOINOPTIMIZECOSTS and similar exclusive DBISAM sentences. Also, most reports are made by end-users.

We don't know if a particular query will need JOINOPTIMIZECOSTS because the query is created at runtime by end-users, and adding an option like "try to improve the query..." seems a Little bit tricky.

We are looking into de possibility of migrating from DBISAM to ElevateDB if the second one is faster that the first. Customers are becoming more and more important, with bigger databases, etc., but changing a huge application will take a lot of time.



Tim Young [Elevate Software] wrote:

Luis,

<< Did you receive my email? The attached .zip file is about 17mb, so I don't know if you received it. Please, let me know. >>

Yes, I received it, but I'll wait until you respond to my later reply before looking into this further.

Tim Young
Elevate Software
www.elevatesoft.com

Luis,

<< The problem is that these queries are created at runtime by third-party components, like query designers or report designers. In this case, we are using Report Builder, and it is not posible to add JOINOPTIMIZECOSTS
and similar exclusive DBISAM sentences. Also, most reports are made by end-users. >>

Unfortunately, there's no way to get around some of these issues.  These keywords were added specifically to provide hints to the query optimizer, which isn't an uncommon thing when you start getting into large databases where even small changes to the way that the query optimizer works can yield very large benefits.  There is only so much a query optimizer can derive about how joins will perform because it is working with incomplete information. There's no way to do a projection on what the total cost of a series of nested loop joins will cost without actually executing them.  For example, the JOINOPTIMIZECOSTS keyword only results in the query optimizer performing a small sample of the cost of a join, and it can also be imperfect.

Tim Young
Elevate Software
www.elevatesoft.com

Hi, Tim.

Thanks for your looking into it.


I have been reading about different kind of indexes, case-insensitive indexes, using upper(), etc.
In this case, the field movim.MOV will ALWAYS contains uppercase values. Do you think using a case-insensitive index will improve queries/filters speed?

And most important…

Will it affect if there are two indexes for the same field, one case-ins and one normal?

Thanks!


Tim Young [Elevate Software] wrote:

Luis,

<< The problem is that these queries are created at runtime by third-party components, like query designers or report designers. In this case, we are using Report Builder, and it is not posible to add JOINOPTIMIZECOSTS
and similar exclusive DBISAM sentences. Also, most reports are made by end-users. >>

Unfortunately, there's no way to get around some of these issues.  These keywords were added specifically to provide hints to the query optimizer, which isn't an uncommon thing when you start getting into large databases where even small changes to the way that the query optimizer works can yield very large benefits.  There is only so much a query optimizer can derive about how joins will perform because it is working with incomplete information. There's no way to do a projection on what the total cost of a series of nested loop joins will cost without actually executing them.  For example, the JOINOPTIMIZECOSTS keyword only results in the query optimizer performing a small sample of the cost of a join, and it can also be imperfect.

Tim Young
Elevate Software
www.elevatesoft.com

Luis,

<< I have been reading about different kind of indexes, case-insensitive indexes, using upper(), etc.
In this case, the field movim.MOV will ALWAYS contains uppercase values. Do you think using a case-insensitive index will improve queries/filters speed? >>

Well, the catch with DBISAM is that you'll also need to use an UPPER() around both sides of the operator for a given condition if you want to ensure that DBISAM's query optimizer detects that it is a case-insensitive condition, and that can be problematic.  EDB is a lot easier to use in this respect:

https://www.elevatesoft.com/articles?action=view&category=edb&article=collations_comparisons

<< Will it affect if there are two indexes for the same field, one case-ins and one normal? >>

See above.  If you don't use UPPER() around both sides of the operator, then DBISAM will resort to using a case-sensitive index for the condition:

https://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=rsdelphiwin32&version=10T&topic=Optimizations

Tim Young
Elevate Software
www.elevatesoft.com