Speed of Indexes between RDS 6.04 and RDS 4.20

I am using RDS 6.04 to debug a report because we have the 6.04 Debugger. The report will eventually run on RDS 4.20. When this report is compiled and run under 6.04 the report finishes in four-five minutes versus when compiled and run under 4.20 it takes upto one hour. Using the same database (dbexport/dbimport). I have used the SQL Explain function and the results are interesting between the two versions. Is there something I should know about the indexing/optimisation RDS 4.20? I need to make the RDS 4.20 version of the program upto the speed of the RDS 6.04 program fast. Note before: The payptd table has an index on the (employee_number, payment_code) in both databases (of course). This part of the sqlexplain.out is part of a loop, so I didn't both with the whole thing. This is part of the sqlexplain.out for the RDS 4.20 version of the program ======================================================================== QUERY: ------ select employee.department, employee.employee_number,sum(dec_value) dec_value from employee, payptd where employee.employee_number = payptd.employee_number and employee.current_shift between 100 and 999 and payptd.payment_code in ("000","886+","867","892","PDO","860","861","864","892-") group by employee.department, employee.employee_number Estimated Cost: 298 Estimated # of Rows Returned: 1 Temporary Files Required For: Group By 1) mario.employee: INDEX PATH (1) Index Keys: current_shift employee_number Lower Index Filter: mario.employee.current_shift >= 100 Upper Index Filter: mario.employee.current_shift <= 999 2) informix.payptd: INDEX PATH Filters: informix.payptd.payment_code IN ('000' , '886+' , '867' , '892' , 'PDO' , '860' , '861' , '864' , '892-' ) (1) Index Keys: employee_number payment_code Lower Index Filter: informix.payptd.employee_number = mario.employee.employee_number QUERY: ------ select payment_code, dec_value, trans_date from payptd wherepayptd.employee_number = 406281 and payptd.payment_code in ("000","886+","867","892","PDO","860","861","864","892-") Estimated Cost: 54 Estimated # of Rows Returned: 49 1) informix.payptd: INDEX PATH (1) Index Keys: employee_number payment_code Lower Index Filter: (informix.payptd.employee_number = 406281 AND informix.payptd.payment_code = '000' ) (2) Index Keys: employee_number payment_code Lower Index Filter: (informix.payptd.employee_number = 406281 AND informix.payptd.payment_code = '886+' ) (3) Index Keys: employee_number payment_code Lower Index Filter: (informix.payptd.employee_number = 406281 AND informix.payptd.payment_code = '867' ) (4) Index Keys: employee_number payment_code Lower Index Filter: (informix.payptd.employee_number = 406281 AND informix.payptd.payment_code = '892' ) (5) Index Keys: employee_number payment_code Lower Index Filter: (informix.payptd.employee_number = 406281 AND informix.payptd.payment_code = 'PDO' ) (6) Index Keys: employee_number payment_code Lower Index Filter: (informix.payptd.employee_number = 406281 AND informix.payptd.payment_code = '860' ) (7) Index Keys: employee_number payment_code Lower Index Filter: (informix.payptd.employee_number = 406281 AND informix.payptd.payment_code = '861' ) (8) Index Keys: employee_number payment_code Lower Index Filter: (informix.payptd.employee_number = 406281 AND informix.payptd.payment_code = '864' ) (9) Index Keys: employee_number payment_code Lower Index Filter: (informix.payptd.employee_number = 406281 AND informix.payptd.payment_code = '892-' ) QUERY: ------ insert into printfile values ( ?, ?, ?, ?, ?, ?, ?) Estimated Cost: 2 Estimated # of Rows Returned: 10 ======================================================================== This is part of the sqlexplain.out for the RDS 6.04 version of the program ======================================================================== QUERY: ------ select employee.department, employee.employee_number,sum(d ec_value) dec_value from employee, payptd where employee.employee_number = pa yptd.employee_number and employee.current_shift between 100 and 999 and pay ptd.payment_code in ("000","886+","867","892","PDO","860","861","864","892-") gr oup by employee.department, employee.employee_number Estimated Cost: 8 Estimated # of Rows Returned: 1 Temporary Files Required For: Group By 1) mario.employee: INDEX PATH (1) Index Keys: current_shift employee_number Lower Index Filter: mario.employee.current_shift >= 100 Upper Index Filter: mario.employee.current_shift <= 999 2) informix.payptd: INDEX PATH Filters: informix.payptd.payment_code IN ('000' , '886+' , '867' , '892' , ' PDO' , '860' , '861' , '864' , '892-' ) (1) Index Keys: employee_number payment_code Lower Index Filter: informix.payptd.employee_number = mario.employee.emp loyee_number QUERY: ------ select payment_code, dec_value, trans_date from payptd wherepayptd.employee_ number = 484869 and payptd.payment_code in ("000","886+","867","892","PDO"," 860","861","864","892-") Estimated Cost: 3 Estimated # of Rows Returned: 5 1) informix.payptd: SEQUENTIAL SCAN Filters: (informix.payptd.employee_number = 484869 AND informix.payptd.payme nt_code IN ('000' , '886+' , '867' , '892' , 'PDO' , '860' , '861' , '864' , '89 2-' )) QUERY: ------ insert into printfile values ( ?, ?, ?, ?, ?, ?, ?) Estimated Cost: 2 Estimated # of Rows Returned: 10 QUERY: ------ insert into printfile values ( ?, ?, ?, ?, ?, ?, ?) Estimated Cost: 2 Estimated # of Rows Returned: 10 QUERY: ------ insert into printfile values ( ?, ?, ?, ?, ?, ?, ?) Estimated Cost: 2 Estimated # of Rows Returned: 10 QUERY: ------ insert into printfile values ( ?, ?, ?, ?, ?, ?, ?) Estimated Cost: 2 Estimated # of Rows Returned: 10 ======================================================================== Please help. Reguards Brett Schubert

Brett Schubert wrote: > > I am using RDS 6.04 to debug a report because we have the 6.04 > Debugger. The report will eventually run on RDS 4.20. > > When this report is compiled and run under 6.04 the report finishes > in four-five minutes versus when compiled and run under 4.20 it takes > upto one hour. Using the same database (dbexport/dbimport). This is primarily a database performance problem, and not all that closely related to which version of RDS you are using. Presumably the 4.20 RDS code is running against a 5.10 OnLine engine, and the 6.04 RDS is running against a 7.x engine. > I have used the SQL Explain function and the results are interesting > between the two versions. Is there something I should know about the > indexing/optimisation RDS 4.20? I need to make the RDS 4.20 version > of the program upto the speed of the RDS 6.04 program fast. > > Note before: The payptd table has an index on the (employee_number, > payment_code) in both databases (of course). > This part of the sqlexplain.out is part of a loop, so I didn't > both with the whole thing. Thanks for not sending everything. UPDATE STATISTICS? > This is part of the sqlexplain.out for the RDS 4.20 version of the program > ======================================================================== > > QUERY: > ------ > select employee.department, employee.employee_number,> sum(dec_value) dec_value from employee, payptd where > employee.employee_number = payptd.employee_number and > employee.current_shift between 100 and 999 and payptd.payment_code in > ("000","886+","867","892","PDO","860","861","864","892-") group by > employee.department, employee.employee_number > > Estimated Cost: 298 > Estimated # of Rows Returned: 1 > Temporary Files Required For: Group By > > 1) mario.employee: INDEX PATH > > (1) Index Keys: current_shift employee_number > Lower Index Filter: mario.employee.current_shift >= 100 > Upper Index Filter: mario.employee.current_shift <= 999 > > 2) informix.payptd: INDEX PATH > > Filters: informix.payptd.payment_code IN ('000' , '886+' , '867' , '892' > , 'PDO' , '860' , '861' , '864' , '892-' ) > > (1) Index Keys: employee_number payment_code > Lower Index Filter: informix.payptd.employee_number = > mario.employee.employee_number > > QUERY: > ------ > select payment_code, dec_value, trans_date from payptd where> payptd.employee_number = 406281 and payptd.payment_code in > ("000","886+","867","892","PDO","860","861","864","892-") > > Estimated Cost: 54 > Estimated # of Rows Returned: 49 > > 1) informix.payptd: INDEX PATH > > (1) Index Keys: employee_number payment_code > Lower Index Filter: (informix.payptd.employee_number = 406281 AND > informix.payptd.payment_code = '000' ) > > (2) Index Keys: employee_number payment_code > Lower Index Filter: (informix.payptd.employee_number = 406281 AND > informix.payptd.payment_code = '886+' ) > > (3) Index Keys: employee_number payment_code > Lower Index Filter: (informix.payptd.employee_number = 406281 AND > informix.payptd.payment_code = '867' ) > > (4) Index Keys: employee_number payment_code > Lower Index Filter: (informix.payptd.employee_number = 406281 AND > informix.payptd.payment_code = '892' ) > > (5) Index Keys: employee_number payment_code > Lower Index Filter: (informix.payptd.employee_number = 406281 AND > informix.payptd.payment_code = 'PDO' ) > > (6) Index Keys: employee_number payment_code > Lower Index Filter: (informix.payptd.employee_number = 406281 AND > informix.payptd.payment_code = '860' ) > > (7) Index Keys: employee_number payment_code > Lower Index Filter: (informix.payptd.employee_number = 406281 AND > informix.payptd.payment_code = '861' ) > > (8) Index Keys: employee_number payment_code > Lower Index Filter: (informix.payptd.employee_number = 406281 AND > informix.payptd.payment_code = '864' ) > > (9) Index Keys: employee_number payment_code > Lower Index Filter: (informix.payptd.employee_number = 406281 AND > informix.payptd.payment_code = '892-' ) > > QUERY: > ------ > insert into printfile values ( ?, ?, ?, ?, ?, ?, ?)> > Estimated Cost: 2 > Estimated # of Rows Returned: 10 > > ======================================================================== > > This is part of the sqlexplain.out for the RDS 6.04 version of the program > ======================================================================== > > QUERY: > ------ > select employee.department, employee.employee_number,> sum(d > ec_value) dec_value from employee, payptd where employee.employee_number > = pa > yptd.employee_number and employee.current_shift between 100 and 999 and > pay > ptd.payment_code in > ("000","886+","867","892","PDO","860","861","864","892-") gr > oup by employee.department, employee.employee_number > > Estimated Cost: 8 > Estimated # of Rows Returned: 1 > Temporary Files Required For: Group By > > 1) mario.employee: INDEX PATH > > (1) Index Keys: current_shift employee_number > Lower Index Filter: mario.employee.current_shift >= 100 > Upper Index Filter: mario.employee.current_shift <= 999 > > 2) informix.payptd: INDEX PATH > > Filters: informix.payptd.payment_code IN ('000' , '886+' , '867' , '892' > , ' > PDO' , '860' , '861' , '864' , '892-' ) > > (1) Index Keys: employee_number payment_code > Lower Index Filter: informix.payptd.employee_number = > mario.employee.emp > loyee_number > > QUERY: > ------ > select payment_code, dec_value, trans_date from payptd where> payptd.employee_ > number = 484869 and payptd.payment_code in > ("000","886+","867","892","PDO"," > 860","861","864","892-") > > Estimated Cost: 3 > Estimated # of Rows Returned: 5 > > 1) informix.payptd: SEQUENTIAL SCAN > > Filters: (informix.payptd.employee_number = 484869 AND > informix.payptd.payme > nt_code IN ('000' , '886+' , '867' , '892' , 'PDO' , '860' , '861' , '864' , > '89 > 2-' )) > > QUERY: > ------ > insert into printfile values ( ?, ?, ?, ?, ?, ?, ?)> > Estimated Cost: 2 > Estimated # of Rows Returned: 10 > > QUERY: > ------ > insert into printfile values ( ?, ?, ?, ?, ?, ?, ?)> > Estimated Cost: 2 > Estimated # of Rows Returned: 10 > > QUERY: > ------ > insert into printfile values ( ?, ?, ?, ?, ?, ?, ?)> > Estimated Cost: 2 > Estimated # of Rows Returned: 10 > > QUERY: > ------ > insert into printfile values ( ?, ?, ?, ?, ?, ?, ?)> > Estimated Cost: 2 > Es