New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
adding a condition kills select execution [CORE68] #392
Comments
Modified by: Alice F. Bird (firebirds)description: SFID: 1440232# Problem: Testing: Result: 1.1 - Grouped select WITHOUT the 1.2 - Grouped select WITH the 2.1 - Ungrouped select WITHOUT the 2.2 - Ungrouped select WITH the => SFID: 1440232# Problem: Testing: Result: 1.1 - Grouped select WITHOUT the 1.2 - Grouped select WITH the 2.1 - Ungrouped select WITHOUT the 2.2 - Ungrouped select WITH the |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @pcisarWorkflow: jira [ 10092 ] => Firebird [ 14254 ] |
Outdated / not reproducible. |
Submitted by: centros (centros)
SFID: 1440232#
Submitted By: centros
Problem:
Adding the SKLPOHDETAIL.EIDSKLADKARTA = 3 condition
into a select
dramatically increases the select execution time, about
200 to 300 times.
Testing:
All selects were tested with some indexes set Inactive
in first try and also with the same indexes
set Active in second try. Results are under selects
marked I -> for indexes Inactive, A -> for Active.
These indexes are: SKLPOH.Recordactive,
SKLPOH.Stornovano and SKLPOHDETAIL.Recordactive
This was made only to find out, whether that indexes
have some influence to the problem tested.
Tests were made on database sent to Dmitry last week.
Of course database was backuped and restored.
Refer to Field-test item #1432475 - INDEXES WITH LOW
SELECTIVITY KILL THE SELECT EXECUTION.
Result:
Active indexes with bad selectivity are included in a
plan, but it has no real bad influence
on select execution. The problem depends only on a
fact, whether the SKLPOHDETAIL.EIDSKLADKARTA = 3
condition is included in the select statement or not.
Problem with indexes is not in a select itself,
but in an execution of the SALDO procedure, which is
listed below, in another test case.
1.1 - Grouped select WITHOUT the
SKLPOHDETAIL.EIDSKLADKARTA = 3 condition
select SKLPOHDETAIL.EIDSKLADKARTA,SKLPOH.SKLPOHTYP,
sum(SKLPOHDETAIL.SKLPOHDETAILMNOZSTVI),sum(SKLPOHDETAIL.SKLPOHDETAILCELKEMCENANAKUP)
from SKLPOH
left outer join SKLPOHDETAIL on SKLPOHDETAIL.IDSKLPOH =
SKLPOH.IDSKLPOH
where SKLPOH.RECORDACTIVE = '1' and
SKLPOH.SKLPOHDATZADAL <= '1.3.2005' and
SKLPOH.SKLPOHSTORNOVANO = '0'
and SKLPOHDETAIL.RECORDACTIVE = '1'
group by SKLPOHDETAIL.EIDSKLADKARTA, SKLPOH.SKLPOHTYP
order by SKLPOHDETAIL.EIDSKLADKARTA, SKLPOH.SKLPOHTYP
I ->
PLAN SORT (JOIN (SKLPOH INDEX (IDX_SKLPOH_DATZADAL),
SKLPOHDETAIL INDEX (IDX_SKLPOHDETAIL_IDSKLPOH)))
250 ms, 29.558 buffer reads
A ->
PLAN SORT (JOIN (SKLPOH INDEX (IDX_SKLPOH_STORNOVANO,
IDX_SKLPOH_RA, IDX_SKLPOH_DATZADAL), SKLPOHDETAIL INDEX
(IDX_SKLPOHDETAIL_IDSKLPOH)))
328 ms, 29.483 buffer reads
1.2 - Grouped select WITH the
SKLPOHDETAIL.EIDSKLADKARTA = 3 condition
select SKLPOHDETAIL.EIDSKLADKARTA,SKLPOH.SKLPOHTYP,
sum(SKLPOHDETAIL.SKLPOHDETAILMNOZSTVI),sum(SKLPOHDETAIL.SKLPOHDETAILCELKEMCENANAKUP)
from SKLPOH
left outer join SKLPOHDETAIL on SKLPOHDETAIL.IDSKLPOH =
SKLPOH.IDSKLPOH
where SKLPOH.RECORDACTIVE = '1' and
SKLPOH.SKLPOHDATZADAL <= '1.3.2005' and
SKLPOH.SKLPOHSTORNOVANO = '0'
and SKLPOHDETAIL.RECORDACTIVE = '1' and
SKLPOHDETAIL.EIDSKLADKARTA = 3
group by SKLPOHDETAIL.EIDSKLADKARTA, SKLPOH.SKLPOHTYP
order by SKLPOHDETAIL.EIDSKLADKARTA, SKLPOH.SKLPOHTYP
I ->
PLAN SORT (JOIN (SKLPOH INDEX (IDX_SKLPOH_DATZADAL),
SKLPOHDETAIL INDEX (IDX_SKLPOHDETAIL_IDSKLPOH,
IDX_SKLPOHDETAIL_EIDSKLADKARTA)))
55.015 ms, 228.372 buffer reads
A ->
PLAN SORT (JOIN (SKLPOH INDEX (IDX_SKLPOH_STORNOVANO,
IDX_SKLPOH_RA, IDX_SKLPOH_DATZADAL), SKLPOHDETAIL INDEX
(IDX_SKLPOHDETAIL_IDSKLPOH,
IDX_SKLPOHDETAIL_EIDSKLADKARTA)))
54.969 ms, 228.297 buffer reads
2.1 - Ungrouped select WITHOUT the
SKLPOHDETAIL.EIDSKLADKARTA = 3 condition
select SKLPOHDETAIL.EIDSKLADKARTA,SKLPOH.SKLPOHTYP,
SKLPOHDETAIL.SKLPOHDETAILMNOZSTVI,SKLPOHDETAIL.SKLPOHDETAILCELKEMCENANAKUP
from SKLPOH
left outer join SKLPOHDETAIL on SKLPOHDETAIL.IDSKLPOH =
SKLPOH.IDSKLPOH
where SKLPOH.RECORDACTIVE = '1' and
SKLPOH.SKLPOHDATZADAL <= '1.3.2005' and
SKLPOH.SKLPOHSTORNOVANO = '0'
and SKLPOHDETAIL.RECORDACTIVE = '1'
I ->
PLAN JOIN (SKLPOH INDEX (IDX_SKLPOH_DATZADAL),
SKLPOHDETAIL INDEX (IDX_SKLPOHDETAIL_IDSKLPOH))
125 ms, 11.750 buffer reads
A ->
PLAN JOIN (SKLPOH INDEX (IDX_SKLPOH_STORNOVANO,
IDX_SKLPOH_RA, IDX_SKLPOH_DATZADAL), SKLPOHDETAIL INDEX
(IDX_SKLPOHDETAIL_IDSKLPOH))
235 ms, 11.791 buffer reads
2.2 - Ungrouped select WITH the
SKLPOHDETAIL.EIDSKLADKARTA = 3 condition
select SKLPOHDETAIL.EIDSKLADKARTA,SKLPOH.SKLPOHTYP,
SKLPOHDETAIL.SKLPOHDETAILMNOZSTVI,SKLPOHDETAIL.SKLPOHDETAILCELKEMCENANAKUP
from SKLPOH
left outer join SKLPOHDETAIL on SKLPOHDETAIL.IDSKLPOH =
SKLPOH.IDSKLPOH
where SKLPOH.RECORDACTIVE = '1' and
SKLPOH.SKLPOHDATZADAL <= '1.3.2005' and
SKLPOH.SKLPOHSTORNOVANO = '0'
and SKLPOHDETAIL.RECORDACTIVE = '1' and
SKLPOHDETAIL.EIDSKLADKARTA = 3
I ->
PLAN JOIN (SKLPOH INDEX (IDX_SKLPOH_DATZADAL),
SKLPOHDETAIL INDEX (IDX_SKLPOHDETAIL_IDSKLPOH,
IDX_SKLPOHDETAIL_EIDSKLADKARTA))
40.390 ms, 229.273 buffer reads
A ->
PLAN JOIN (SKLPOH INDEX (IDX_SKLPOH_STORNOVANO,
IDX_SKLPOH_RA, IDX_SKLPOH_DATZADAL), SKLPOHDETAIL INDEX
(IDX_SKLPOHDETAIL_IDSKLPOH,
IDX_SKLPOHDETAIL_EIDSKLADKARTA))
40.344 ms, 228.297 buffer reads
The text was updated successfully, but these errors were encountered: