| Subject | Strange Performance |
|---|---|
| Author | HKlemt |
| Post date | 2003-11-25T14:20:21Z |
below you find two select statements used in a 200MB Database
first takes 11 minutes, second takes 4 seconds.
all i have done is to enter a where clause on the field
SSTRUKX.IDVISIB (which is indexed) and the value in the
where clause is the default value (-1). When i take a
value which is not used, it comes in less than a second.
all changes to add the clause in the top join conditions
where also unsuccessful.
the size of the tables can be found in the ibexpert
performance output, the result is the same, but not all
record have the default value and the one with another
value should not be in the result list.
I already heard about problems in mixing where/join
Style, but since there is such a big time between
this, it could not be the reason.
the problem occurs on current fb15rc win releases,
but also on older releases. CPU is 100% for firebird
when doing this query, but the reads and write
in that process are almost the same, so it cannot
be a typical problem, such as for example writing
a tempfile.
Is anybody interested in getting the database for
finding the reason? i could upload to our server
(please send a private mail to hk@...)
Select
VKUNDE.ID ID,
VKUNDE.HOSTNR VKUNDE_HOSTNR,
VKUNDEX.HOSTNR VKUNDE_ID_VKUNDE,
SADRES.MATCHCODE SADRES_MATCHCODE,
SADRES.ADRESSZEILE1 SADRES_ADRESSZEILE1,
SADRES.ADRESSZEILE2 SADRES_ADRESSZEILE2,
SADRES.POSTLEITZAHLSTRASSE SADRES_POSTLEITZAHLSTRASSE,
SADRES.ORTSTRASSE SADRES_ORTSTRASSE,
SCLAND.BEZ SCLAND_BEZ,
VKUNDE.ID_SWAERG VKUNDE_ID_SWAERG,
VKUNDE.ID_SGEBIE VKUNDE_ID_SGEBIE,
VKUNDE.ID_VKDART VKUNDE_ID_VKDART,
VKUNDE.TOURNR VKUNDE_TOURNR,
VKUNDE.LAUFNR VKUNDE_LAUFNR,
VKUNDE.UMSATZSTEUERID VKUNDE_UMSATZSTEUERID,
VKUNDE.ID_VRECST VKUNDE_ID_VRECST,
VKUNDE.ID_SFDAGL VKUNDE_ID_SFDAGL,
VKUNDE.ID_VLFBED VKUNDE_ID_VLFBED,
SSTATUX.BEZ SSTATUX_BEZ,
SWAERG.BEZ SWAERG_BEZ,
SGEBIE.GEBIET SGEBIE_GEBIET,
VKDART.KUNDENART VKDART_KUNDENART,
VRECST.RECHNUNGSSTELLUNGSKZ VRECST_RECHNUNGSSTELLUNGSKZ,
SFDAGL.FORDERUNGSAUSGLEICHSKZ SFDAGL_FORDERUNGSAUSGLEICHSKZ,
VLFBED.BEZ VLFBED_BEZ,
SSTRUKX.ID SSTRUKX_ID,
SADRES.ADRESSZEILE3 SADRES_ADRESSZEILE3,
sgebie3.gebiet SGEBIE3_GEBIET,
sgebie2.gebiet SGEBIE2_GEBIET,
SSTRUKX.ID_SSTATU SSTRUKX_ID_SSTATU
from SSTRUK00 SSTRUKX
join VKUNDE00 VKUNDE on (VKUNDE.ID=SSTRUKX.IDTABELLE)
join SWAERG00 SWAERG on (SWAERG.ID=VKUNDE.ID_SWAERG)
join SGEBIE00 SGEBIE on (SGEBIE.ID=VKUNDE.ID_SGEBIE)
join VKDART00 VKDART on (VKDART.ID=VKUNDE.ID_VKDART)
join VRECST00 VRECST on (VRECST.ID=VKUNDE.ID_VRECST)
join SFDAGL00 SFDAGL on (SFDAGL.ID=VKUNDE.ID_SFDAGL)
join VLFBED00 VLFBED on (VLFBED.ID=VKUNDE.ID_VLFBED)
join SADRES00 SADRES on (SSTRUKX.ID_SSTRUK=SADRES.ID)
join SCLAND00 SCLAND on (SCLAND.ID=SADRES.ID_SCLAND)
join VKUNDE00 VKUNDEX on (VKUNDEX.ID=VKUNDE.ID_VKUNDE)
join sstruk00 sstruk1 on (sstruk1.id=vkunde.ID_SGEBIE)
left join sstruk00 sstruk2 on (sstruk2.id=sstruk1.id_sstruk)
left join sgebie00 sgebie2 on (sgebie2.id=sstruk2.id)
left join sstruk00 sstruk3 on (sstruk3.id=sstruk2.id_sstruk)
left join sgebie00 sgebie3 on (sgebie3.id=sstruk3.id)
left join sstruk00 sstruk4 on (sstruk4.id=sstruk3.id_sstruk)
left join sstruk00 sstruk5 on (sstruk5.id=sstruk4.id_sstruk)
join SSTATU00 SSTATUX on (SSTATUX.ID=SSTRUKX.ID_SSTATU)
where ((SSTRUKX.IDVISIB=-1)) --here is the problem
order by 2 ASC
Query Time
------------------------------------------------
Prepare : 20,00 ms
Execute : 656.244,00 ms
Avg fetch time: 19.301,29 ms
Memory
------------------------------------------------
Current: 3.220.284
Max : 3.350.324
Buffers: 10.000
Operations
------------------------------------------------
Read : 3.090
Writes : 13
Fetches: 9.972.126
Enchanced Info:
+--------------------------+-----------+-----------+---------+-------
--+---------+
| Table Name | Index | Non-Index | Updates |
Deletes | Inserts |
| | reads | reads |
| | |
+--------------------------+-----------+-----------+---------+-------
--+---------+
| SCLAND00| 39409 | 0 | 0 |
0 | 0 |
| SGEBIE00| 59723 | 0 | 0 |
0 | 0 |
| SSTATU00| 12798 | 0 | 0 |
0 | 0 |
| SSTRUK00| 158462 | 0 | 0 |
0 | 0 |
| SWAERG00| 34127 | 0 | 0 |
0 | 0 |
| VKDART00| 34127 | 0 | 0 |
0 | 0 |
| VKUNDE00| 46929 | 0 | 0 |
0 | 0 |
| SFDAGL00| 12798 | 0 | 0 |
0 | 0 |
| VLFBED00| 12798 | 0 | 0 |
0 | 0 |
| VRECST00| 12798 | 0 | 0 |
0 | 0 |
| SADRES00| 0 | 39409 | 0 |
0 | 0 |
+--------------------------+-----------+-----------+---------+-------
--+---------+
Plan
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SADRES
NATURAL,SCLAND INDEX (RDB$PRIMARY15),SSTRUKX INDEX
(SSTRUK2,SSTRUK00_IDX3),VKUNDE INDEX (RDB$PRIMARY85),SSTRUK1 INDEX
(RDB$PRIMARY54),SGEBIE INDEX (RDB$PRIMARY19),SWAERG INDEX
(RDB$PRIMARY67),VKDART INDEX (RDB$PRIMARY81),VRECST INDEX
(RDB$PRIMARY106),SFDAGL INDEX (RDB$PRIMARY115),VLFBED INDEX
(RDB$PRIMARY118),VKUNDEX INDEX (RDB$PRIMARY85)),SSTRUK2 INDEX
(RDB$PRIMARY54)),SGEBIE2 INDEX (RDB$PRIMARY19)),SSTRUK3 INDEX
(RDB$PRIMARY54)),SGEBIE3 INDEX (RDB$PRIMARY19)),SSTRUK4 INDEX
(RDB$PRIMARY54)),SSTRUK5 INDEX (RDB$PRIMARY54)),SSTATUX INDEX
(RDB$PRIMARY51)))
Adapted Plan
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SADRES
NATURAL,SCLAND INDEX (INTEG_30),SSTRUKX INDEX
(SSTRUK2,SSTRUK00_IDX3),VKUNDE INDEX (INTEG_170),SSTRUK1 INDEX
(INTEG_108),SGEBIE INDEX (INTEG_38),SWAERG INDEX (INTEG_134),VKDART
INDEX (INTEG_162),VRECST INDEX (INTEG_212),SFDAGL INDEX
(INTEG_229),VLFBED INDEX (INTEG_235),VKUNDEX INDEX
(INTEG_170)),SSTRUK2 INDEX (INTEG_108)),SGEBIE2 INDEX
(INTEG_38)),SSTRUK3 INDEX (INTEG_108)),SGEBIE3 INDEX
(INTEG_38)),SSTRUK4 INDEX (INTEG_108)),SSTRUK5 INDEX
(INTEG_108)),SSTATUX INDEX (INTEG_102)))
Select
VKUNDE.ID ID,
VKUNDE.HOSTNR VKUNDE_HOSTNR,
VKUNDEX.HOSTNR VKUNDE_ID_VKUNDE,
SADRES.MATCHCODE SADRES_MATCHCODE,
SADRES.ADRESSZEILE1 SADRES_ADRESSZEILE1,
SADRES.ADRESSZEILE2 SADRES_ADRESSZEILE2,
SADRES.POSTLEITZAHLSTRASSE SADRES_POSTLEITZAHLSTRASSE,
SADRES.ORTSTRASSE SADRES_ORTSTRASSE,
SCLAND.BEZ SCLAND_BEZ,
VKUNDE.ID_SWAERG VKUNDE_ID_SWAERG,
VKUNDE.ID_SGEBIE VKUNDE_ID_SGEBIE,
VKUNDE.ID_VKDART VKUNDE_ID_VKDART,
VKUNDE.TOURNR VKUNDE_TOURNR,
VKUNDE.LAUFNR VKUNDE_LAUFNR,
VKUNDE.UMSATZSTEUERID VKUNDE_UMSATZSTEUERID,
VKUNDE.ID_VRECST VKUNDE_ID_VRECST,
VKUNDE.ID_SFDAGL VKUNDE_ID_SFDAGL,
VKUNDE.ID_VLFBED VKUNDE_ID_VLFBED,
SSTATUX.BEZ SSTATUX_BEZ,
SWAERG.BEZ SWAERG_BEZ,
SGEBIE.GEBIET SGEBIE_GEBIET,
VKDART.KUNDENART VKDART_KUNDENART,
VRECST.RECHNUNGSSTELLUNGSKZ VRECST_RECHNUNGSSTELLUNGSKZ,
SFDAGL.FORDERUNGSAUSGLEICHSKZ SFDAGL_FORDERUNGSAUSGLEICHSKZ,
VLFBED.BEZ VLFBED_BEZ,
SSTRUKX.ID SSTRUKX_ID,
SADRES.ADRESSZEILE3 SADRES_ADRESSZEILE3,
sgebie3.gebiet SGEBIE3_GEBIET,
sgebie2.gebiet SGEBIE2_GEBIET,
SSTRUKX.ID_SSTATU SSTRUKX_ID_SSTATU
from SSTRUK00 SSTRUKX
join VKUNDE00 VKUNDE on (VKUNDE.ID=SSTRUKX.IDTABELLE)
join SWAERG00 SWAERG on (SWAERG.ID=VKUNDE.ID_SWAERG)
join SGEBIE00 SGEBIE on (SGEBIE.ID=VKUNDE.ID_SGEBIE)
join VKDART00 VKDART on (VKDART.ID=VKUNDE.ID_VKDART)
join VRECST00 VRECST on (VRECST.ID=VKUNDE.ID_VRECST)
join SFDAGL00 SFDAGL on (SFDAGL.ID=VKUNDE.ID_SFDAGL)
join VLFBED00 VLFBED on (VLFBED.ID=VKUNDE.ID_VLFBED)
join SADRES00 SADRES on (SSTRUKX.ID_SSTRUK=SADRES.ID)
join SCLAND00 SCLAND on (SCLAND.ID=SADRES.ID_SCLAND)
join VKUNDE00 VKUNDEX on (VKUNDEX.ID=VKUNDE.ID_VKUNDE)
join sstruk00 sstruk1 on (sstruk1.id=vkunde.ID_SGEBIE)
left join sstruk00 sstruk2 on (sstruk2.id=sstruk1.id_sstruk)
left join sgebie00 sgebie2 on (sgebie2.id=sstruk2.id)
left join sstruk00 sstruk3 on (sstruk3.id=sstruk2.id_sstruk)
left join sgebie00 sgebie3 on (sgebie3.id=sstruk3.id)
left join sstruk00 sstruk4 on (sstruk4.id=sstruk3.id_sstruk)
left join sstruk00 sstruk5 on (sstruk5.id=sstruk4.id_sstruk)
join SSTATU00 SSTATUX on (SSTATUX.ID=SSTRUKX.ID_SSTATU)
order by 2 ASC
Query Time
------------------------------------------------
Prepare : 20,00 ms
Execute : 4.006,00 ms
Avg fetch time: 117,82 ms
Memory
------------------------------------------------
Current: 3.027.360
Max : 3.386.168
Buffers: 10.000
Operations
------------------------------------------------
Read : 78
Writes : 0
Fetches: 2.105.082
Enchanced Info:
+--------------------------+-----------+-----------+---------+-------
--+---------+
| Table Name | Index | Non-Index | Updates |
Deletes | Inserts |
| | reads | reads |
| | |
+--------------------------+-----------+-----------+---------+-------
--+---------+
| SCLAND00| 39409 | 0 | 0 |
0 | 0 |
| SGEBIE00| 62230 | 0 | 0 |
0 | 0 |
| SSTATU00| 12929 | 0 | 0 |
0 | 0 |
| SSTRUK00| 163476 | 0 | 0 |
0 | 0 |
| SWAERG00| 36372 | 0 | 0 |
0 | 0 |
| VKDART00| 36372 | 0 | 0 |
0 | 0 |
| VKUNDE00| 49305 | 0 | 0 |
0 | 0 |
| SFDAGL00| 12929 | 0 | 0 |
0 | 0 |
| VLFBED00| 12929 | 0 | 0 |
0 | 0 |
| VRECST00| 12929 | 0 | 0 |
0 | 0 |
| SADRES00| 0 | 39409 | 0 |
0 | 0 |
+--------------------------+-----------+-----------+---------+-------
--+---------+
Plan
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SADRES
NATURAL,SCLAND INDEX (RDB$PRIMARY15),SSTRUKX INDEX (SSTRUK2),VKUNDE
INDEX (RDB$PRIMARY85),SSTRUK1 INDEX (RDB$PRIMARY54),SGEBIE INDEX
(RDB$PRIMARY19),SWAERG INDEX (RDB$PRIMARY67),VKDART INDEX
(RDB$PRIMARY81),VRECST INDEX (RDB$PRIMARY106),SFDAGL INDEX
(RDB$PRIMARY115),VLFBED INDEX (RDB$PRIMARY118),VKUNDEX INDEX
(RDB$PRIMARY85)),SSTRUK2 INDEX (RDB$PRIMARY54)),SGEBIE2 INDEX
(RDB$PRIMARY19)),SSTRUK3 INDEX (RDB$PRIMARY54)),SGEBIE3 INDEX
(RDB$PRIMARY19)),SSTRUK4 INDEX (RDB$PRIMARY54)),SSTRUK5 INDEX
(RDB$PRIMARY54)),SSTATUX INDEX (RDB$PRIMARY51)))
Adapted Plan
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SADRES
NATURAL,SCLAND INDEX (INTEG_30),SSTRUKX INDEX (SSTRUK2),VKUNDE INDEX
(INTEG_170),SSTRUK1 INDEX (INTEG_108),SGEBIE INDEX (INTEG_38),SWAERG
INDEX (INTEG_134),VKDART INDEX (INTEG_162),VRECST INDEX
(INTEG_212),SFDAGL INDEX (INTEG_229),VLFBED INDEX
(INTEG_235),VKUNDEX INDEX (INTEG_170)),SSTRUK2 INDEX
(INTEG_108)),SGEBIE2 INDEX (INTEG_38)),SSTRUK3 INDEX
(INTEG_108)),SGEBIE3 INDEX (INTEG_38)),SSTRUK4 INDEX
(INTEG_108)),SSTRUK5 INDEX (INTEG_108)),SSTATUX INDEX (INTEG_102)))
first takes 11 minutes, second takes 4 seconds.
all i have done is to enter a where clause on the field
SSTRUKX.IDVISIB (which is indexed) and the value in the
where clause is the default value (-1). When i take a
value which is not used, it comes in less than a second.
all changes to add the clause in the top join conditions
where also unsuccessful.
the size of the tables can be found in the ibexpert
performance output, the result is the same, but not all
record have the default value and the one with another
value should not be in the result list.
I already heard about problems in mixing where/join
Style, but since there is such a big time between
this, it could not be the reason.
the problem occurs on current fb15rc win releases,
but also on older releases. CPU is 100% for firebird
when doing this query, but the reads and write
in that process are almost the same, so it cannot
be a typical problem, such as for example writing
a tempfile.
Is anybody interested in getting the database for
finding the reason? i could upload to our server
(please send a private mail to hk@...)
Select
VKUNDE.ID ID,
VKUNDE.HOSTNR VKUNDE_HOSTNR,
VKUNDEX.HOSTNR VKUNDE_ID_VKUNDE,
SADRES.MATCHCODE SADRES_MATCHCODE,
SADRES.ADRESSZEILE1 SADRES_ADRESSZEILE1,
SADRES.ADRESSZEILE2 SADRES_ADRESSZEILE2,
SADRES.POSTLEITZAHLSTRASSE SADRES_POSTLEITZAHLSTRASSE,
SADRES.ORTSTRASSE SADRES_ORTSTRASSE,
SCLAND.BEZ SCLAND_BEZ,
VKUNDE.ID_SWAERG VKUNDE_ID_SWAERG,
VKUNDE.ID_SGEBIE VKUNDE_ID_SGEBIE,
VKUNDE.ID_VKDART VKUNDE_ID_VKDART,
VKUNDE.TOURNR VKUNDE_TOURNR,
VKUNDE.LAUFNR VKUNDE_LAUFNR,
VKUNDE.UMSATZSTEUERID VKUNDE_UMSATZSTEUERID,
VKUNDE.ID_VRECST VKUNDE_ID_VRECST,
VKUNDE.ID_SFDAGL VKUNDE_ID_SFDAGL,
VKUNDE.ID_VLFBED VKUNDE_ID_VLFBED,
SSTATUX.BEZ SSTATUX_BEZ,
SWAERG.BEZ SWAERG_BEZ,
SGEBIE.GEBIET SGEBIE_GEBIET,
VKDART.KUNDENART VKDART_KUNDENART,
VRECST.RECHNUNGSSTELLUNGSKZ VRECST_RECHNUNGSSTELLUNGSKZ,
SFDAGL.FORDERUNGSAUSGLEICHSKZ SFDAGL_FORDERUNGSAUSGLEICHSKZ,
VLFBED.BEZ VLFBED_BEZ,
SSTRUKX.ID SSTRUKX_ID,
SADRES.ADRESSZEILE3 SADRES_ADRESSZEILE3,
sgebie3.gebiet SGEBIE3_GEBIET,
sgebie2.gebiet SGEBIE2_GEBIET,
SSTRUKX.ID_SSTATU SSTRUKX_ID_SSTATU
from SSTRUK00 SSTRUKX
join VKUNDE00 VKUNDE on (VKUNDE.ID=SSTRUKX.IDTABELLE)
join SWAERG00 SWAERG on (SWAERG.ID=VKUNDE.ID_SWAERG)
join SGEBIE00 SGEBIE on (SGEBIE.ID=VKUNDE.ID_SGEBIE)
join VKDART00 VKDART on (VKDART.ID=VKUNDE.ID_VKDART)
join VRECST00 VRECST on (VRECST.ID=VKUNDE.ID_VRECST)
join SFDAGL00 SFDAGL on (SFDAGL.ID=VKUNDE.ID_SFDAGL)
join VLFBED00 VLFBED on (VLFBED.ID=VKUNDE.ID_VLFBED)
join SADRES00 SADRES on (SSTRUKX.ID_SSTRUK=SADRES.ID)
join SCLAND00 SCLAND on (SCLAND.ID=SADRES.ID_SCLAND)
join VKUNDE00 VKUNDEX on (VKUNDEX.ID=VKUNDE.ID_VKUNDE)
join sstruk00 sstruk1 on (sstruk1.id=vkunde.ID_SGEBIE)
left join sstruk00 sstruk2 on (sstruk2.id=sstruk1.id_sstruk)
left join sgebie00 sgebie2 on (sgebie2.id=sstruk2.id)
left join sstruk00 sstruk3 on (sstruk3.id=sstruk2.id_sstruk)
left join sgebie00 sgebie3 on (sgebie3.id=sstruk3.id)
left join sstruk00 sstruk4 on (sstruk4.id=sstruk3.id_sstruk)
left join sstruk00 sstruk5 on (sstruk5.id=sstruk4.id_sstruk)
join SSTATU00 SSTATUX on (SSTATUX.ID=SSTRUKX.ID_SSTATU)
where ((SSTRUKX.IDVISIB=-1)) --here is the problem
order by 2 ASC
Query Time
------------------------------------------------
Prepare : 20,00 ms
Execute : 656.244,00 ms
Avg fetch time: 19.301,29 ms
Memory
------------------------------------------------
Current: 3.220.284
Max : 3.350.324
Buffers: 10.000
Operations
------------------------------------------------
Read : 3.090
Writes : 13
Fetches: 9.972.126
Enchanced Info:
+--------------------------+-----------+-----------+---------+-------
--+---------+
| Table Name | Index | Non-Index | Updates |
Deletes | Inserts |
| | reads | reads |
| | |
+--------------------------+-----------+-----------+---------+-------
--+---------+
| SCLAND00| 39409 | 0 | 0 |
0 | 0 |
| SGEBIE00| 59723 | 0 | 0 |
0 | 0 |
| SSTATU00| 12798 | 0 | 0 |
0 | 0 |
| SSTRUK00| 158462 | 0 | 0 |
0 | 0 |
| SWAERG00| 34127 | 0 | 0 |
0 | 0 |
| VKDART00| 34127 | 0 | 0 |
0 | 0 |
| VKUNDE00| 46929 | 0 | 0 |
0 | 0 |
| SFDAGL00| 12798 | 0 | 0 |
0 | 0 |
| VLFBED00| 12798 | 0 | 0 |
0 | 0 |
| VRECST00| 12798 | 0 | 0 |
0 | 0 |
| SADRES00| 0 | 39409 | 0 |
0 | 0 |
+--------------------------+-----------+-----------+---------+-------
--+---------+
Plan
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SADRES
NATURAL,SCLAND INDEX (RDB$PRIMARY15),SSTRUKX INDEX
(SSTRUK2,SSTRUK00_IDX3),VKUNDE INDEX (RDB$PRIMARY85),SSTRUK1 INDEX
(RDB$PRIMARY54),SGEBIE INDEX (RDB$PRIMARY19),SWAERG INDEX
(RDB$PRIMARY67),VKDART INDEX (RDB$PRIMARY81),VRECST INDEX
(RDB$PRIMARY106),SFDAGL INDEX (RDB$PRIMARY115),VLFBED INDEX
(RDB$PRIMARY118),VKUNDEX INDEX (RDB$PRIMARY85)),SSTRUK2 INDEX
(RDB$PRIMARY54)),SGEBIE2 INDEX (RDB$PRIMARY19)),SSTRUK3 INDEX
(RDB$PRIMARY54)),SGEBIE3 INDEX (RDB$PRIMARY19)),SSTRUK4 INDEX
(RDB$PRIMARY54)),SSTRUK5 INDEX (RDB$PRIMARY54)),SSTATUX INDEX
(RDB$PRIMARY51)))
Adapted Plan
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SADRES
NATURAL,SCLAND INDEX (INTEG_30),SSTRUKX INDEX
(SSTRUK2,SSTRUK00_IDX3),VKUNDE INDEX (INTEG_170),SSTRUK1 INDEX
(INTEG_108),SGEBIE INDEX (INTEG_38),SWAERG INDEX (INTEG_134),VKDART
INDEX (INTEG_162),VRECST INDEX (INTEG_212),SFDAGL INDEX
(INTEG_229),VLFBED INDEX (INTEG_235),VKUNDEX INDEX
(INTEG_170)),SSTRUK2 INDEX (INTEG_108)),SGEBIE2 INDEX
(INTEG_38)),SSTRUK3 INDEX (INTEG_108)),SGEBIE3 INDEX
(INTEG_38)),SSTRUK4 INDEX (INTEG_108)),SSTRUK5 INDEX
(INTEG_108)),SSTATUX INDEX (INTEG_102)))
Select
VKUNDE.ID ID,
VKUNDE.HOSTNR VKUNDE_HOSTNR,
VKUNDEX.HOSTNR VKUNDE_ID_VKUNDE,
SADRES.MATCHCODE SADRES_MATCHCODE,
SADRES.ADRESSZEILE1 SADRES_ADRESSZEILE1,
SADRES.ADRESSZEILE2 SADRES_ADRESSZEILE2,
SADRES.POSTLEITZAHLSTRASSE SADRES_POSTLEITZAHLSTRASSE,
SADRES.ORTSTRASSE SADRES_ORTSTRASSE,
SCLAND.BEZ SCLAND_BEZ,
VKUNDE.ID_SWAERG VKUNDE_ID_SWAERG,
VKUNDE.ID_SGEBIE VKUNDE_ID_SGEBIE,
VKUNDE.ID_VKDART VKUNDE_ID_VKDART,
VKUNDE.TOURNR VKUNDE_TOURNR,
VKUNDE.LAUFNR VKUNDE_LAUFNR,
VKUNDE.UMSATZSTEUERID VKUNDE_UMSATZSTEUERID,
VKUNDE.ID_VRECST VKUNDE_ID_VRECST,
VKUNDE.ID_SFDAGL VKUNDE_ID_SFDAGL,
VKUNDE.ID_VLFBED VKUNDE_ID_VLFBED,
SSTATUX.BEZ SSTATUX_BEZ,
SWAERG.BEZ SWAERG_BEZ,
SGEBIE.GEBIET SGEBIE_GEBIET,
VKDART.KUNDENART VKDART_KUNDENART,
VRECST.RECHNUNGSSTELLUNGSKZ VRECST_RECHNUNGSSTELLUNGSKZ,
SFDAGL.FORDERUNGSAUSGLEICHSKZ SFDAGL_FORDERUNGSAUSGLEICHSKZ,
VLFBED.BEZ VLFBED_BEZ,
SSTRUKX.ID SSTRUKX_ID,
SADRES.ADRESSZEILE3 SADRES_ADRESSZEILE3,
sgebie3.gebiet SGEBIE3_GEBIET,
sgebie2.gebiet SGEBIE2_GEBIET,
SSTRUKX.ID_SSTATU SSTRUKX_ID_SSTATU
from SSTRUK00 SSTRUKX
join VKUNDE00 VKUNDE on (VKUNDE.ID=SSTRUKX.IDTABELLE)
join SWAERG00 SWAERG on (SWAERG.ID=VKUNDE.ID_SWAERG)
join SGEBIE00 SGEBIE on (SGEBIE.ID=VKUNDE.ID_SGEBIE)
join VKDART00 VKDART on (VKDART.ID=VKUNDE.ID_VKDART)
join VRECST00 VRECST on (VRECST.ID=VKUNDE.ID_VRECST)
join SFDAGL00 SFDAGL on (SFDAGL.ID=VKUNDE.ID_SFDAGL)
join VLFBED00 VLFBED on (VLFBED.ID=VKUNDE.ID_VLFBED)
join SADRES00 SADRES on (SSTRUKX.ID_SSTRUK=SADRES.ID)
join SCLAND00 SCLAND on (SCLAND.ID=SADRES.ID_SCLAND)
join VKUNDE00 VKUNDEX on (VKUNDEX.ID=VKUNDE.ID_VKUNDE)
join sstruk00 sstruk1 on (sstruk1.id=vkunde.ID_SGEBIE)
left join sstruk00 sstruk2 on (sstruk2.id=sstruk1.id_sstruk)
left join sgebie00 sgebie2 on (sgebie2.id=sstruk2.id)
left join sstruk00 sstruk3 on (sstruk3.id=sstruk2.id_sstruk)
left join sgebie00 sgebie3 on (sgebie3.id=sstruk3.id)
left join sstruk00 sstruk4 on (sstruk4.id=sstruk3.id_sstruk)
left join sstruk00 sstruk5 on (sstruk5.id=sstruk4.id_sstruk)
join SSTATU00 SSTATUX on (SSTATUX.ID=SSTRUKX.ID_SSTATU)
order by 2 ASC
Query Time
------------------------------------------------
Prepare : 20,00 ms
Execute : 4.006,00 ms
Avg fetch time: 117,82 ms
Memory
------------------------------------------------
Current: 3.027.360
Max : 3.386.168
Buffers: 10.000
Operations
------------------------------------------------
Read : 78
Writes : 0
Fetches: 2.105.082
Enchanced Info:
+--------------------------+-----------+-----------+---------+-------
--+---------+
| Table Name | Index | Non-Index | Updates |
Deletes | Inserts |
| | reads | reads |
| | |
+--------------------------+-----------+-----------+---------+-------
--+---------+
| SCLAND00| 39409 | 0 | 0 |
0 | 0 |
| SGEBIE00| 62230 | 0 | 0 |
0 | 0 |
| SSTATU00| 12929 | 0 | 0 |
0 | 0 |
| SSTRUK00| 163476 | 0 | 0 |
0 | 0 |
| SWAERG00| 36372 | 0 | 0 |
0 | 0 |
| VKDART00| 36372 | 0 | 0 |
0 | 0 |
| VKUNDE00| 49305 | 0 | 0 |
0 | 0 |
| SFDAGL00| 12929 | 0 | 0 |
0 | 0 |
| VLFBED00| 12929 | 0 | 0 |
0 | 0 |
| VRECST00| 12929 | 0 | 0 |
0 | 0 |
| SADRES00| 0 | 39409 | 0 |
0 | 0 |
+--------------------------+-----------+-----------+---------+-------
--+---------+
Plan
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SADRES
NATURAL,SCLAND INDEX (RDB$PRIMARY15),SSTRUKX INDEX (SSTRUK2),VKUNDE
INDEX (RDB$PRIMARY85),SSTRUK1 INDEX (RDB$PRIMARY54),SGEBIE INDEX
(RDB$PRIMARY19),SWAERG INDEX (RDB$PRIMARY67),VKDART INDEX
(RDB$PRIMARY81),VRECST INDEX (RDB$PRIMARY106),SFDAGL INDEX
(RDB$PRIMARY115),VLFBED INDEX (RDB$PRIMARY118),VKUNDEX INDEX
(RDB$PRIMARY85)),SSTRUK2 INDEX (RDB$PRIMARY54)),SGEBIE2 INDEX
(RDB$PRIMARY19)),SSTRUK3 INDEX (RDB$PRIMARY54)),SGEBIE3 INDEX
(RDB$PRIMARY19)),SSTRUK4 INDEX (RDB$PRIMARY54)),SSTRUK5 INDEX
(RDB$PRIMARY54)),SSTATUX INDEX (RDB$PRIMARY51)))
Adapted Plan
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SADRES
NATURAL,SCLAND INDEX (INTEG_30),SSTRUKX INDEX (SSTRUK2),VKUNDE INDEX
(INTEG_170),SSTRUK1 INDEX (INTEG_108),SGEBIE INDEX (INTEG_38),SWAERG
INDEX (INTEG_134),VKDART INDEX (INTEG_162),VRECST INDEX
(INTEG_212),SFDAGL INDEX (INTEG_229),VLFBED INDEX
(INTEG_235),VKUNDEX INDEX (INTEG_170)),SSTRUK2 INDEX
(INTEG_108)),SGEBIE2 INDEX (INTEG_38)),SSTRUK3 INDEX
(INTEG_108)),SGEBIE3 INDEX (INTEG_38)),SSTRUK4 INDEX
(INTEG_108)),SSTRUK5 INDEX (INTEG_108)),SSTATUX INDEX (INTEG_102)))