We are trying to upgrade our commercial application from SA9 to SA12. But there is serious performance problem. We are using query like:
SELECT name, getPrice(productId, _priceType_) FROM products ORDER BY name
priceType is provided by the application. The table is indexed by name. 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. Even for value-sensitive cursor type, where evaluating function for each row before fetch makes no sense, server performs evaluation.
Is there any way to force SA12 to behave like SA9? I know we can found workaround for this case, but there is over 100 of such queries in the application. It is significant cost to change everything.
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.
answered 19 Mar '14, 10:37
Ivan T. Bowman
Glenn Paulley on UDFs (until recently he was head of the SQL Anywhere query engine development team): http://glennpaulley.ca/conestoga/2014/02/set-level-operations-do-matter/
answered 25 Feb '14, 11:10
What is your optimization goal setting set to? i.e. what is output of
select connection_property( 'optimization_goal' );
If it is set to ALL-ROWS then the query engine may choose to materialize the entire result set before returning the first row back to the client.
Try setting the optimization_goal to FIRST-ROW.
answered 10 Feb '14, 12:56
Your overall symptoms (but not necessarily all the details) aren't too dis-similar form some of the difficulties we have been having in migrating from v10.0.1 to v16.0.0. Certainly they have revolved around unnecessary repeated execution of UDFs that doesn't happen in v10.0.1.
We have been seeing steady incremental improvements with these issues over the last year and so I'd strongly recommend trying your application on a recent version of 16.0.0.
I'm afraid I can't speak for v12 as while we encountered the same problems in v12.0.1 some time ago, we haven't been testing against any updates since v16 came out.
answered 25 Feb '14, 09:34
I've found the reason of this strange behavior.
answered 27 Feb '14, 14:42