[Info-ingres] Performance issue - I'm missing something obvious

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy X Keadell - 21 Feb 2006 15:42 GMT

Hi all,

I have a query shown below. When I give specific dates for
'start_report_month_ty' and 'end_report_month_ty', it runs in about 2
minutes.
If I ask it to look the dates up from the single row table
'date_parameters' it runs for at least an hour ( I killed it then ).

Am I missing something as to why the two qeps would be so different ?

I've shown them below too.

I think I'm not seeing the wood for the trees here and I'm suspicious of
the lookup query but can't put my finger on it.

Any help would be good

Cheers

Andy

Running on Alpha-VMS 7.3 Ingres II 2.0 axm/vms

create table alps_invoice_line

(

customer_number_id                     integer4    not null with default,

invoice_number                         char(08)    not null with default,

transaction_date                       char(08)    not null with default,

bill_to_customer                       char(11)    not null with default,

ship_to_customer                       char(11)    not null with default,

product_id                             char(8)     not null with default,

product_segment_id                     char(10)    not null with
default,

market_vendor_id                       char(03)    not null with default,

brand_id                               char(10)    not null with default,

sell_price                             float8      not null with default,

service                                float8     not null with default,

product_type                           char(03)    not null with default,

fin_stat_code                          char(03)    not null with default,

cut_discount                           float8     not null with default,

assoc_discount                         float8     not null with default,

program_discount                       float8     not null with default,

waived_art_comp                        float8     not null with default,

buried_freight                         float8     not null with default,

billed_inv_freight                     float8     not null with default,

period_id

)

\p\g

insert into alps_invoice_line

select customer_number_id = 0, invoice_number, transaction_date,

        bill_to_customer, ship_to_customer,

        product_id = product_type,

        product_segment_id = ' ',

        market_vendor_id = ' ',

        brand_Id= ' ',

        sell_price, service, product_type,

        fin_stat_code, cut_discount,

        assoc_discount, program_discount, waived_art_comp,

        buried_freight= freight_charge, billed_inv_freight = 0,

        period_id = left(transaction_date,6)

from awg_invoice_line, cusfl_main c, date_parameters

           where report_by_customer = c.rr_cust_no

              and c.sbu in ('A', 'R', 'T')

              and transaction_date between start_report_month_ty
<-----

                     and end_report_month_ty <-----

produces the qep here;
QUERY PLAN 3,1, no timeout, of main query

Cart-Prod
Heap
Pages 8166 Tups
204140
D829291 C37046
/
\
T Join(tidp)
Proj-rest
Heap
Heap
Pages 32663 Tups 816560
Pages 1 Tups 1
D829290 C20715
D1 C0
/ \ /
K Join(rr_cust_no) awg_invoice_line
date_parameters
Heap cHeap (d)
Pages 10208 Tups 816560 Pages 875539 Tups
816560Heap
D12730 C4384
Pages 4 Tups 1
/ \
Proj-rest awg_invoice_line_1
Heap I(awg_invoice_line)
Pages 44 Tups 6724 Isam(report_by_customer,
D12 C67 transaction_date)
Pages 129214 Tups 8165603
/
cusfl_main_4
I(c)
Isam(sbu)
Pages 2863 Tups 226404

********************************************************************
E_OP0008 query optimized
- trace flag indicates no execution should take place
(MON FEB 20 13:34:52 2006)

If I replace 'start_report_month_ty' and 'end_report_month_ty' with
'20060101' and '20060131' for instance I get the query plan below and

the query executes in 2 minutes

QUERY PLAN 1,1, no timeout, of main query

Cart-Prod
Heap
Pages 327 Tups
8166
D20896 C4711
/
\
T Join(tidp)
Proj-rest
Heap
Heap
Pages 327 Tups 8166
Pages 1 Tups 1
D20895 C4547
D1 C0
/ \ /
K Join(rr_cust_no) awg_invoice_line
date_parameters
Heap cHeap
Heap
Pages 103 Tups 8166 Pages 875539 Tups
816560Pages 4 Tups 1
D12730 C4384
/ \
Proj-rest awg_invoice_line_1
Heap I(awg_invoice_line)
Pages 44 Tups 6724 Isam(report_by_customer,
D12 C67 transaction_date)
Pages 129214 Tups 8165603
/
cusfl_main_4
I(c)
Isam(sbu)
Pages 2863 Tups 226404

********************************************************************
(82098 rows)

I want to get it to look up the dates and not have them hard coded, but
can't get it to do it :o(

----------------------------------------------------------------------------------------

This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit written
agreement or government initiative expressly permitting the use of e-mail
for such purpose.
----------------------------------------------------------------------------------------

Reply to this Message

Jim Gramling - 21 Feb 2006 16:03 GMT

Could you post the help "table output" for the tables in the query?
Both qeps look dreadful ... but it would help to know what the keys and
indexes are.

Reply to this Message

Andy X Keadell - 21 Feb 2006 16:22 GMT

Thanks mate,

Here's help table for the tables in the query.....

Name: awg_invoice_line
Owner: ingres_dba
Created: 24-aug-2003 11:16:27
Location: ii_database
Type: user table
Version: OI2.0
Page size: 2048
Cache priority: 0
Alter table version: 0
Alter table totwidth: 203
Row width: 203
Number of rows: 8165603
Storage structure: heap
Compression: data
Duplicate Rows: allowed
Number of pages: 1440971
Overflow data pages: 1440878
Journaling: enabled
Base table for view: yes
Optimizer statistics: yes; see avg count below, more info in the iistats
catalog

Column Information:
Key Avg
Count
Column Name Type Length Nulls Defaults Seq Per
Value
transaction_date c 8 no yes
5103.5
plant c 2 no yes
invoice_number c 8 no yes
1.7
report_by_customer c 11 no yes
81.4
bill_to_customer c 11 no yes
79.3
ship_to_customer c 11 no yes
product_id c 15 no yes
154.3
product_desc c 30 no yes
pkg_imp_flag c 1 no yes
type_item c 1 no yes
price_list_code c 3 no yes
market_type c 3 no yes
product_type c 3 no yes
fin_stat_code c 3 no yes
fin_stat_group c 3 no yes
system_vendor_code c 3 no yes
department_code c 3 no yes
brand c 1 no yes
quantity integer 4 no yes
sell_price float 8 no yes
service float 4 no yes
assoc_discount float 4 no yes
cut_discount float 4 no yes
program_discount float 4 no yes
waived_art_comp float 4 no yes
over_under_amount float 4 no yes
material_cost float 8 no yes
labor_cost float 4 no yes
fixed_burden float 4 no yes
variable_burden float 4 no yes
freight_charge float 4 no yes
commission_terr c 5 no yes
sap_invoice_number c 10 no value
sap_invoice_date c 8 no value

Secondary indexes:
Index Name Structure Keyed On
awg_invoice_line_1 isam report_by_customer,
transaction_date
awg_invoice_line_2 isam bill_to_customer,
transaction_date
awg_invoice_line_3 isam product_id, transaction_date
awg_invoice_line_4 isam invoice_number,
transaction_date

Name: cusfl_main
Owner: ingres_dba
Created: 24-aug-2003 11:01:02
Location: ii_database
Type: user table
Version: OI2.0
Page size: 2048
Cache priority: 0
Alter table version: 0
Alter table totwidth: 262
Row width: 262
Number of rows: 226404
Storage structure: isam with unique keys
Compression: none
Duplicate Rows: not allowed
Number of pages: 32562
Overflow data pages: 0
Journaling: enabled
Base table for view: no
Optimizer statistics: yes; see avg count below, more info in the iistats
catalog

Column Information:
Key Avg
Count
Column Name Type Length Nulls Defaults Seq Per
Value
c_name c 30 no yes
c_addr c 30 no yes
c_city c 15 no yes
c_state c 2 no yes
c_zip_cd c 5 no yes
10.0
c_zip4 c 4 no yes
c_area_cd c 3 no yes
c_phone_no c 7 no yes
fax_area_cd c 3 no yes
fax_no c 7 no yes
attn_ln vchar 30 no yes
terr c 5 no yes
155.7
sbu c 1 no yes
typ_bus_1 c 3 no yes
665.9
typ_bus_2 c 3 no yes
terms_cd c 1 no yes
cred_terms c 1 no yes
remit_cd c 2 no yes
acct_stat c 1 no yes
assoc_cd c 2 no yes
assoc_sub c 2 no yes
vp_no c 3 no yes
vp_cd c 1 no yes
target_acct c 1 no yes
fr_frght_cd c 1 no yes
commis_cd c 1 no yes
rr_cust_no c 11 no yes 1
unique
rfr_to_cust c 11 no yes
pp_cust_no integer 4 no yes
rpm_cust_no integer 4 no yes
c_control_no c 10 no yes
m_addr c 30 no yes
m_city c 15 no yes
m_state c 2 no yes
m_zip_cd c 5 no yes
m_zip4 c 4 no yes

Secondary indexes:
Index Name Structure Keyed On
cusfl_main_1 isam terr, rr_cust_no, typ_bus_1
cusfl_main_2 isam typ_bus_1, rr_cust_no
cusfl_main_3 isam c_zip_cd, rr_cust_no
cusfl_main_4 isam sbu, assoc_cd, rr_cust_no

Name: date_parameters
Owner: ingres_dba
Created: 14-sep-2002 08:42:04
Location: ii_database
Type: user table
Version: OI2.0
Page size: 2048
Cache priority: 0
Alter table version: 0
Alter table totwidth: 140
Row width: 140
Number of rows: 1
Storage structure: heap
Compression: none
Duplicate Rows: allowed
Number of pages: 4
Overflow data pages: 1
Journaling: enabled
Base table for view: no
Optimizer statistics: none

Column Information:
Key
Column Name Type Length Nulls Defaults Seq
start_history_date c 8 no yes
start_report_month_ty c 8 no yes
end_report_month_ty c 8 no yes
start_report_month_ly c 8 no yes
end_report_month_ly c 8 no yes
start_fiscal_ty c 8 no yes
start_fiscal_ly c 8 no yes
start_fiscal_qtr_ty c 8 no yes
start_fiscal_qtr_ly c 8 no yes
no_fiscal_months integer 2 no yes
start_sales_ty c 8 no yes
start_sales_ly c 8 no yes
start_sales_qtr_ty c 8 no yes
start_sales_qtr_ly c 8 no yes
no_sales_months integer 2 no yes
start_cost c 8 no yes
end_cost c 8 no yes
start_12_ty c 8 no yes
start_12_ly c 8 no yes

Secondary indexes: none

----------------------------------------------------------------------------------------

This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit written
agreement or government initiative expressly permitting the use of e-mail
for such purpose.
----------------------------------------------------------------------------------------

"Jim Gramling"
<jimwgramling To: info-ingres@cariboulake.com
@gmail.com> cc:
Sent by: Subject: [Info-ingres] Re: Performance issue - I'm missing something obvious
info-ingres-admi
n

02/21/2006 11:03
AM

Could you post the help "table output" for the tables in the query?
Both qeps look dreadful ... but it would help to know what the keys and
indexes are.

_______________________________________________
Info-ingres mailing list
Info-ingres@cariboulake.com
http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres

Reply to this Message

Karl & Betty Schendel - 21 Feb 2006 17:09 GMT

>Hi all,
>
[quoted text clipped - 34 lines]
> Pages 10208 Tups 816560 Pages 875539 Tups
>816560Heap

.........................................^^^^^^^^

Note how many more rows are estimated here as compared to the K-join
in the QEP with constant dates, which only estimates 6724 rows.
What is happening is that the date restriction is being applied
at the top of the tree in the date_parameter case, and at the
awg_invoice_line_1 index level in the constant case. I am presuming
that many fewer rows are being produced by virtue of the earlier
restriction. (A qe90 would confirm that.)

I don't know if better stats will convince Ingres to perform the
date restriction earlier or not. That sort of non-constant restriction
is very difficult to estimate in the general case. Make sure you
have stats on date_parameter's date columns as well as the other
columns that Peter Gale suggests.

Karl

Reply to this Message