SA12 performance when ROW_COUNTS option turned on

[Former Member]

We are trying to upgrade our commercial application from SA9 to SA12. In application query results are often shown in listboxes. To properly set scrollbars we use ROW_COUNTS option. This works efficiently for all ASA version including ASA 9. In SA12 we have serious performance problem. Provide we use query like:

SELECT name, getPrice(productId, priceType) FROM products WHERE status > 'INACTIVE' ORDER BY name

priceType is provided by the application. The table is indexed by name and status. There is over 100k products. Function is evaluated in ~1ms, uses few other tables.

In SA9 query was performed in <1s, the function was called only for fetched rows. User can see first screen of product list in 1s.

In SA12 the function is called for each row in the table, and next during fetch again. So the query is executed in 2 minutes. User has to wait 2 minutes to view product list. This is independent of Optimization_goal option.

As a matter of fact exact query doesn't matter. Seems, that if WHERE isn't very simple, to get rowcount server executes the query. It is Ok, and it was similarly done in ASA9. But SA12 evaluates all fields from select list.

Is there any way to force SA12 to behave like SA9?
If no, we have to simulate old behavior manually.

[VolkerBarth]

Just to clarify: When ROW_COUNTS is set to the default "Off", then performance between ASA 9 and SA 12 is comparable (and good enough)?

[Former Member]

I don't know. It requires some tests.

[Former Member]

Even when ROW_COUNTS is off, FETCH ABSOLUTE is very slow in SA12. Probably also all columns for intermediate (skipped) rows are evaluated. I will perform more tests.

[Former Member]

Finally I've verified FETCH behavior. ROW_COUNTS setting doesn't matter in this case. DMRF too. Both FETCH ABSOLUTE and FETCH RELATIVE evaluates all intermediate rows.
So in SA12 FETCH RELATIVE 10000 triggers 10000 calls of functions used in select list.
In ASA9 function was evaluated only once.
We are using FETCH ABSOLUTE to set cursor position according to user scrollbar adjusting.

[Former Member]

In version 10.0, expression evaluation in SQLA changed to be generally more efficient. A consequence is that in some cases expressions are evaluated before they are needed -- this reduces the overhead needed to track which expressions are currently valid. For the case of absolute and relative fetches, the changes that generally improve performance cause a degradation as they lead to evaluations of potentially expensive UDF or subquery expressions that are not retrieved by the client. In the case that the Row_counts option is on, an absolute fetch is used to count the rows in the cursor unless the query optimizer can determine a good estimate of the number of rows. For example, "SELECT * FROM T" can usually estimate the number of rows returned based on the number of rows in T. Various features of the query (predicates in the WHERE clause, use of computed columns or joins) may affect whether the estimate is considered sufficiently accurate to avoid this fetch.

In order to improve the performance in these cases, a server change has been made to avoid evaluating select expressions for rows skipped by a relative or absolute fetch (including those for the purpose of the Row_counts option). The change is expected to be available in a future SP with build number 4089 or higher (version 12) or 1858 or higher (version 16). The associated release notes may reference engineering case # 760144.

Please see also this same answer posted to a related question.

[VolkerBarth]

Ivan is back as a forum contributor - a great answer on its own:)