cancel
Showing results for 
Search instead for 
Did you mean: 

SA12 UDF in select list evaluation time

Former Member
3,442

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.

Accepted Solutions (0)

Answers (5)

Answers (5)

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.

Breck_Carter
Participant

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/

Former Member
0 Kudos

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.

VolkerBarth
Contributor
0 Kudos

Well, and that has not happened with SA9?

Breck_Carter
Participant
0 Kudos

Oops, I just accidentally deleted ODL_Sybase's comment when trying to delete my own! Sorry!

ODL_Sybase's comment talked about how the symptom has returned, and it required ROW_COUNTS to be on to see the bad behavior.

Breck_Carter
Participant
0 Kudos

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.
Breck_Carter
Participant
0 Kudos

The question "How do I [satisfy some requirement] without turning ROW_COUNTS on?" has been asked in the past... and it could be asked again if you don't want to search.

Former Member
0 Kudos

Documentation say about "row_counts":
When row_counts is set to On, it may take significantly longer to execute queries. In fact, it will usually cause SQL Anywhere to execute the query twice, doubling the execution time.
It is Ok, but why all fields are evaluated to count rows?

Former Member
0 Kudos

Yes, we probably can manually execute "select count(*)" or "select count(distinct ...)" when GROUP BY used.
There is little effort to expand aliases used in WHERE clause, but still much less than rewriting queries totally.

Former Member
0 Kudos

By the way: cannot find this question. Can you post a link?

VolkerBarth
Contributor
0 Kudos

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...

Breck_Carter
Participant
0 Kudos

Please post this as a new question; this thread is becoming unmanageable 🙂

justin_willey
Participant
0 Kudos

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.

MarkCulp
Participant
0 Kudos

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.

Breck_Carter
Participant
0 Kudos

The OP did say "This is independent of Optimization_goal option."

MarkCulp
Participant
0 Kudos

Ok, missed that sentence. Ignore my answer.... it was a long shot guess 😉

Breck_Carter
Participant

Are there any cursor gurus lurking about? ...this question cries out for an Alpha Plus to answer it 🙂

VolkerBarth
Contributor
0 Kudos

That's an attempt to "prefetch" those query processing gurus, isn't it?

Can't tell whether it has been successful by now:(