Skip to Content
0
Sep 19, 2016 at 12:51 PM

Permormance issue SQL Anywhere 12.0.1.3152

28 Views

Below 2 sql statements. Statement 1 is finished in 3 seconds. Statement 2 in 785 seconds.

The only difference is the line "and service_date <= gt_periods.date_till" in the where clause.


What can I do to improve the performance of the second statement?

Table cycle_results has 1555234 records. Both statements return 5 records because there i no data entered after 2016-03-31.




Statement 1 ( 3 seconds)

all sa_flush_cache();

delete gt_periods;

insert into gt_periods(comp_id, farm_id, date_from, date_till, sortno, periodtype) values (109006, 8, date('2016-03-30'), date('2016-03-01'), 1, 1);

select count(*)

from cycle_results join arrival on arrival.comp_id = cycle_results.comp_id and arrival.farm_id = cycle_results.farm_id and arrival.animal_id = cycle_results.animal_id, gt_periods

where gt_periods.periodtype = 1

and service_date >= gt_periods.date_from

and cycle_results.comp_id = gt_periods.comp_id

and cycle_results.farm_id = gt_periods.farm_id

and gt_periods.sortno = 1

and arrival.arrival_date <= cycle_results.service_date;

Statement 2 (785 seconds)

all sa_flush_cache();

delete gt_periods;

insert into gt_periods(comp_id, farm_id, date_from, date_till, sortno, periodtype) values (109006, 8, date('2016-03-30'), date('2016-03-01'), 1, 1);

select count(*)

from cycle_results join arrival on arrival.comp_id = cycle_results.comp_id and arrival.farm_id = cycle_results.farm_id and arrival.animal_id = cycle_results.animal_id, gt_periods

where gt_periods.periodtype = 1

and service_date >= gt_periods.date_from

and service_date <= gt_periods.date_till

and cycle_results.comp_id = gt_periods.comp_id

and cycle_results.farm_id = gt_periods.farm_id

and gt_periods.sortno = 1

and arrival.arrival_date <= cycle_results.service_date;