on 2014 Feb 10 11:51 AM
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.
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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/
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I've found the reason of this strange behavior.
Product table contains computed column referencing another table. Removing this column causes function to be evaluated only during fetch.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
ROW_COUNTS has a place of honor in the 2008 article How To Make SQL Anywhere Slow, and it has always been a bad idea, in SQL Anywhere 16, in ASA 9 (see below), even in SQL Anywhere 5 (see below).
===== ASA 9 ===== ASA Database Administration Guide Database Options Alphabetical list of options ROW_COUNTS option [database] Function Specifies whether the database will always count the number of rows in a query when it is opened. Allowed values ON, OFF Scope Can be set for an individual connection or for the PUBLIC group. Takes effect immediately. Default OFF Description If this option is set to OFF, the row count is usually only an estimate. If this option is set to ON, the row count is always accurate. Warning When ROW_COUNTS is set to ON, it may take significantly longer to execute queries. In fact, it will usually cause Adaptive Server Anywhere to execute the query twice, doubling the execution time. ===== SQL Anywhere 5 ===== ROW_COUNTS Specifies whether the database will always count the number of rows in a query when it is opened. If this option is off, the row count, the row count is usually only an estimate. If this option is on, the row count is always accurate but opening queries may take significantly longer.
but why all fields are evaluated to count rows?
That's still a mystery to me, too - as you state, the bad performance has to do with computed columns, and computed columns should not be re-calculated just because they are queried, cf. this doc topic because that would make them simply superfluous...
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Are there any cursor gurus lurking about? ...this question cries out for an Alpha Plus to answer it 🙂
User | Count |
---|---|
76 | |
30 | |
10 | |
8 | |
8 | |
7 | |
7 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.