Permormance issue SQL Anywhere 12.0.1.3152

[former_member274096]

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;

with_datetill 3seconds_saplan.txt.zip

without_datetill785seconds_saplan.txt.zip

[former_member194571]

Hi Eric,

To seriously analyze your issue, the information you provided is of limited use.

1. The plans don't contain any actual values, just optimizer estimates. These in fact look pretty similar. If the actual run time is that different, something is likely to be wrong with the estimates.

2. To get an idea what might be a reasonable access path, table definition and index definitions on your large table are required. Supplement is optimizer statistics (output from DBHist utility).

Also your request looks inconsistent.

1. The date_till in your example is < date_from, so the result should be empty

2. Your description says that the statement including the "and service_date <= gt_periods.date_till" clause runs longer than the one w/out, but the names of the attached plans indicate the opposite.

The general surprise factor would be much smaller if the statement w/ the extra clause is faster or at least not slower than the one w/out. Whether or not it provides an actual restriction, it may tilt index usage etc.

Finally, if you're actually restricting with only one row in the join, you'll be way better off by specifying the restrictions as parameters or literals. The join approach hides a lot of valuable information from the optimizer.

- Volker

[former_member207653]

I would donwload the latest patch available for SQL Anywhere 12.0.1 and test again (your build number is the GA version). If the performance does not improve, you might attach new query plans with statistics.

Thanks