What is wrong with this query? It is very, very slow!!! It is clasical
master-detail situation. Table "Dokument" has 3000 records and table
"Pozicije" has 8000 records. There is an index on
"dokument;stevilka;poslovalnica;leto".
ver4.25 and D6
please, advise


select sum(p.kolicina * (1 - p.rabat/100)*p.pc) as mpvrednost,
      sum(if(p.ddv = 20  then p.kolicina * (1 -
p.rabat/100)*p.pc*p.ddv/(p.ddv+100) else cast(0 as Float)) ) as
vrednostddv20,
      sum(if(p.ddv = 8.5 then p.kolicina * (1 -
p.rabat/100)*p.pc*p.ddv/(p.ddv+100) else cast(0 as Float)) ) as
vrednostddv85,
      sum(if(p.ddv = 20  then p.kolicina * (1 - p.rabat/100)*p.pc/(1 +
p.ddv/100) else cast(0 as Float)) ) as osnova20,
      sum(if(p.ddv = 8.5 then p.kolicina * (1 - p.rabat/100)*p.pc/(1 +
p.ddv/100) else cast(0 as Float)) ) as osnova85,
      sum(p.kolicina * (((1 - p.rabat/100)*p.pc/(1 + p.ddv/100))-p.cena))
as marza,
      sum(p.kolicina * p.cena) as nabavna,
      sum(p.kolicina) as komadi,
      sum(p.kolicina*(p.rabat/100)*p.pc) as rabati,
      cast(d.datum as char(20)) as opis, d.datum

from pozicije p, dokumenti d where p.dokument = d.dokument and p.stevilka =
d.stevilka and p.poslovalnica = d.poslovalnica and p.leto = d.leto and
d.dokument = 'MP'

group by d.datum
order by d.datum

Andrej


There is an index on
"dokument;stevilka;poslovalnica;leto".

from pozicije p, dokumenti d where p.dokument = d.dokument and p.stevilka =
d.stevilka and p.poslovalnica = d.poslovalnica and p.leto = d.leto and
d.dokument = 'MP'

group by d.datum
order by d.datum

I presume you mean there's an index on each field in the where clause on both tables. You'll also need one on datum

As a tip - learn to use the query plans that DBSys produces they will help you tremendously in spotting the reasons for slow queries. Even if you do nothing more than post the query plan when asking for help it will be useful. If nothing else we'll see what you mean by slow

Roy Lambert

Andrej,

<< What is wrong with this query? It is very, very slow!!! It is clasical
master-detail situation. Table "Dokument" has 3000 records and table
"Pozicije" has 8000 records. There is an index on
"dokument;stevilka;poslovalnica;leto". >>

As Roy indicated, it would be very helpful if you were to post the query
plan.  If you need help on how to generate one, just let me know.

--
Tim Young
Elevate Software
www.elevatesoft.com

This is "plan". I have an index for each keyfield and still very slow. I have to mantion,
taht very similar query in ver2.12 is very quick.

================================================================================
SQL statement (Executed with 4.25 Build 6)
================================================================================

select sum(p.kolicina * (1 - p.rabat/100)*p.pc) as mpvrednost,
      sum(if(p.ddv = 20  then p.kolicina * (1 - p.rabat/100)*p.pc*p.ddv/(p.ddv+100)
else cast(0 as Float)) )as vrednostddv20,
      sum(if(p.ddv = 8.5 then p.kolicina * (1 - p.rabat/100)*p.pc*p.ddv/(p.ddv+100)
else cast(0 as Float)) ) as vrednostddv85,
      sum(if(p.ddv = 20  then p.kolicina * (1 - p.rabat/100)*p.pc/(1 + p.ddv/100)
else cast(0 as Float)) ) as osnova20,
      sum(if(p.ddv = 8.5 then p.kolicina * (1 - p.rabat/100)*p.pc/(1 + p.ddv/100)
else cast(0 as Float)) ) as osnova85,
      sum(p.kolicina * (((1 - p.rabat/100)*p.pc/(1 + p.ddv/100))-p.cena)) as marza,
      sum(p.kolicina * p.cena) as nabavna,
      sum(p.kolicina) as komadi,
      sum(p.kolicina*(p.rabat/100)*p.pc) as rabati,


      cast(d.datum as char(20)) as opis, d.datum

 

from pozicije p, dokumenti d where p.dokument = d.dokument and p.stevilka =
d.stevilka and p.poslovalnica = d.poslovalnica and p.leto = d.leto and
p.poslovalnica = d.poslovalnica and d.dokument = 'MP'
 

 group by d.datum
 order by d.datum

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

pozicije (p) table opened shared, has 8190 rows
dokumenti (d) table opened shared, has 3218 rows

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

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

datum

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

datum ASC

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

The expression:

d.dokument = 'MP'

is OPTIMIZED, covers 3131 rows or index keys, costs 35067 bytes, and will be
applied to the dokumenti table (d) before any joins

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

The driver table is the pozicije table (p)

The pozicije table (p) is joined to the dokumenti table (d) with the INNER JOIN
expression:

p.dokument = d.dokument AND p.stevilka = d.stevilka AND p.poslovalnica =
d.poslovalnica AND p.leto = d.leto AND p.poslovalnica = d.poslovalnica

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 dokumenti table (d)

The dokumenti table (d) is joined to the pozicije table (p) with the INNER JOIN
expression:

d.dokument = p.dokument AND d.stevilka = p.stevilka AND d.poslovalnica =
p.poslovalnica AND d.leto = p.leto AND d.poslovalnica = p.poslovalnica

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:

d.dokument = p.dokument AND d.stevilka = p.stevilka AND d.poslovalnica =
p.poslovalnica AND d.leto = p.leto AND d.poslovalnica = p.poslovalnica

has been rewritten and is OPTIMIZED

================================================================================
>>>>> 32 rows affected in 87,547 seconds
================================================================================




"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Andrej,

<< What is wrong with this query? It is very, very slow!!! It is clasical
master-detail situation. Table "Dokument" has 3000 records and table
"Pozicije" has 8000 records. There is an index on
"dokument;stevilka;poslovalnica;leto". >>

As Roy indicated, it would be very helpful if you were to post the query
plan.  If you need help on how to generate one, just let me know.

--
Tim Young
Elevate Software
www.elevatesoft.com

Andrej


Looking at that my guess would be you need to create an index on datum. Be interesting to see what Tim says.

Roy Lambert

Andrej,

<< This is "plan". I have an index for each keyfield and still very slow. I
have to mantion, taht very similar query in ver2.12 is very quick. >>

Something isn't right.  The tables list indicates that there are 3218 rows
in the dokumenti table, yet the optimized index expression indicates that
there are 3131 rows.   Did you try repairing the tables to make sure that
they aren't corrupted ?   If the repair indicates that the tables are okay,
could you please email the tables so that I can see what is going on ?

--
Tim Young
Elevate Software
www.elevatesoft.com

I've repaired tables before but no errors...
I am sending tables to you.
Not all record of table "dokumenti" are involved in a query. They are "filtered" by
dokument = "MP".


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Andrej,

<< This is "plan". I have an index for each keyfield and still very slow. I
have to mantion, taht very similar query in ver2.12 is very quick. >>

Something isn't right.  The tables list indicates that there are 3218 rows
in the dokumenti table, yet the optimized index expression indicates that
there are 3131 rows.   Did you try repairing the tables to make sure that
they aren't corrupted ?   If the repair indicates that the tables are okay,
could you please email the tables so that I can see what is going on ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Andrej,

<< I've repaired tables before but no errors... I am sending tables to you.
Not all record of table "dokumenti" are involved in a query. They are
"filtered" by dokument = "MP". >>

I understand.  The part I was concerned about was that I thought the row
count for the filter expression (3131) was higher than the reported row
count (3218) for the table.   However, I was just mis-reading the numbers.

I've looked this over pretty thoroughly, and I'm afraid that the performance
is what it is.  DBISAM is not especially good when you have a lot of join
operations, and you've got 7224 (number of rows joined) x 4 (number of join
conditions in the join) = 28896 joins.   However, I'm seeing a total query
time of around 15 seconds here, not anywhere near 87 seconds.

--
Tim Young
Elevate Software
www.elevatesoft.com

Well, I can not do much eather. I could join fields into one field, but sould I mention
how much work that is??
15 seconds? Why is that? Faster computer??


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Andrej,

<< I've repaired tables before but no errors... I am sending tables to you.
Not all record of table "dokumenti" are involved in a query. They are
"filtered" by dokument = "MP". >>

I understand.  The part I was concerned about was that I thought the row
count for the filter expression (3131) was higher than the reported row
count (3218) for the table.   However, I was just mis-reading the numbers.

I've looked this over pretty thoroughly, and I'm afraid that the performance
is what it is.  DBISAM is not especially good when you have a lot of join
operations, and you've got 7224 (number of rows joined) x 4 (number of join
conditions in the join) = 28896 joins.   However, I'm seeing a total query
time of around 15 seconds here, not anywhere near 87 seconds.

--
Tim Young
Elevate Software
www.elevatesoft.com

Andrej,

<< Well, I can not do much eather. I could join fields into one field, but
sould I mention how much work that is?? >>

Probably more trouble than it is worth for the performance gain.

<< 15 seconds? Why is that? Faster computer?? >>

I assume so - I'm using a Pentium D 2.8 GHz machine with 2 Gigs of RAM.

--
Tim Young
Elevate Software
www.elevatesoft.com