Open Edge 10.2B Query Speed Issues

[Hoopy]

Currently in the process of testing an upgrade from 10.1B to 10.2B.

Both of the following queries take about 5 to 10 seconds in 10.1B.

However, the second takes about 2 minutes 17 seconds in 10.2B.

Testing has been carried out using MS Access.

SELECT calendar.analysis_period, customer.customer, calendar.calendar_date, despatch_date.despatch_date, sales_invoice_record.invoice_item, sales_invoice_record.invoiced_area, sales_invoice_record.invoiced_cost_quantity, sales_invoice_record.invoiced_lineal, sales_invoice_record.invoiced_pieces, sales_invoice_record.invoiced_volume, sales_invoice_record.invoiced_weight, sales_invoice_record.sales, sales_invoice_record.margin INTO tmpSalesInvoice
FROM ((sales_invoice_record INNER JOIN calendar ON sales_invoice_record.calendar_key = calendar.calendar_key) INNER JOIN customer ON sales_invoice_record.customer_key = customer.customer_key) INNER JOIN despatch_date ON sales_invoice_record.despatch_date_key = despatch_date.despatch_date_key
WHERE (((calendar.analysis_period)="201003") AND ((customer.customer)<>"SILVS001"));


SELECT calendar.analysis_period, customer.customer, calendar.calendar_date, despatch_date.despatch_date, sales_invoice_record.invoice_item, sales_invoice_record.invoiced_area, sales_invoice_record.invoiced_cost_quantity, sales_invoice_record.invoiced_lineal, sales_invoice_record.invoiced_pieces, sales_invoice_record.invoiced_volume, sales_invoice_record.invoiced_weight, sales_invoice_record.sales, sales_invoice_record.margin INTO tmpSalesInvoice
FROM ((sales_invoice_record INNER JOIN calendar ON sales_invoice_record.calendar_key = calendar.calendar_key) INNER JOIN customer ON sales_invoice_record.customer_key = customer.customer_key) INNER JOIN despatch_date ON sales_invoice_record.despatch_date_key = despatch_date.despatch_date_key
WHERE (((calendar.analysis_period)="201003") AND ((customer.customer)<>"SILVS001" And (customer.customer) Not Like "ZTIM*"));

The only difference between the two statements is that the second statement has some additional criteria...

And (customer.customer) Not Like "ZTIM*"));

Any suggestions why this would cause a differenc in 10.2B?

Regards
Paul

 

[JJames100]

Were you able to get any answers to this? Interested in knowing about.

 

[RealHeavyDude]

I am pretty sure that this additional query criteria

And (customer.customer) Not Like "ZTIM*"));

is responsible for the slowness.

Heavy Regards, RealHeavyDude.

 

[JJames100]

Reason for interest: we have a system (from vendor), based on a Progress database which is set up for replication. Their 'hyperbase' (don't know if this is a Progress term or just the name of the DB) gets refreshed with new client data around 3AM from the main Progress DB. This process used to take around 45 minutes and now takes 3 hours.
In addition to this, a query I created a couple of years ago in Access that links with the main Progress DB, used to take 5 minutes to run (has many outer joins). It now takes 30 minutes. This all started around Feb 10th I've discovered. We use vs. 10.1C. Have you heard recently of anyone having problems with queries that used to run reasonably well that now takes much longer to run?? Don't know if this is due to vendor change or possible SC change or other. Unfortunately, still waiting on vendor.

 

[cj_brandt]

Do you know how to track the database activity during these times to see what is occuring as far as reads and writes ? That is what I would look at first.

Often when a process at night suddenly starts requiring more time, it is due to a nightly backup or some other process which was started during that time.

 

[tamhas]

The obvious first question is whether you have run update statistics.

When you have done that, I would look at the differences in the query plan for the two. "not like" is, of course, the sort of criterion on it own which would be non-indexed and result in reading the whole table, but if the other selection criteria are using indexes and producing a sufficiently small result set, then the increment should be minor.

 

[JJames100]

We don't have any new processes that would affect running either at night or during day. We did get new terminal server servers (more processing power, etc). However,this problem started just before these were put into use and continued thereafter. The query now takes 30 minutes on 3 of them and 15 minutes on one which has more processing power than the others.

Why else would a query that used to take 5 minutes, now take 30 minutes (no change in query), to run occur? I was wondering if there was a change in indexing from the vendor which could account for. Unfortunately because I'm writing the queries in Access and running via odbc (no change there), the vendor will not help with this problem.

 

[tamhas]

You didn't answer the question. Have you run UPDATE STATISTICS? Search this forum for that phrase and you will find a bunch of cases where doing that made dramatic differences in the time it took for a query to run with no setup or code changes.

Then look at the query plan *as it is being executed by the SQL engine*