CIS functional compensation performance considerations apply.

[kimon_moschandreou]

Hello,

In several places in IQ documentation we see this note

Note: CIS functional compensation performance considerations apply.

Recently we had the following incident on IQ 15.4, a complex query using derived tables had isnumeric() in one of the columns of a derived table. The select command returned around 60000 rows in less than a minute but insert using the select lasted almost 3 hours. When isnumeric() was removed insert lasted less than 5 minutes. We suspect the above note is related to this behavior, it seems that isnumeric is parsed by SQL anywhere engine and not IQ engine and this causes a delay. In general this is a known issue but until now we never had such an extreme case (5 min compared to 3 hours for the same command).

Is there somewhere a list of the commands, functions etc that may have similar problems? Maybe a document describing what is processed by SQL Anywhere and what from IQ. I suppose there is no solution or workaround for this, so it would be important to know how to avoid it

Thank you in advance, any feedback could be valuable.

Kimon Moschandreou

[saroj_bagai · Accepted solution]

Following statements are usually handled by SA

- Prepared SQL statements

- commands being run with no commits

- Creating table with 'IN SYSTEM' clause and inserting data into that table.
-  SA functions

-  Query with User Defined Functions

[markmumy · Accepted solution]

And don't forget to add the functions that we haven't/won't implement in the IQ engine.  ISNUMERIC() is one of them.  Still... 

Also, any time you have a stored procedure call within a SELECT statement:

     select * from sp_iqwho()

You can search the manuals for the string "CIS functional compensation performance considerations apply".  This will give you a list of which functions are handled in the SA engine and not ported to IQ.  Avoid those for queries where performance is important as they usually will slow down your operations.

Note, though, that there are more SA functions that are not in our manuals.  All of these would have the same restriction.  The HASH function, for instance, is in the SA manual but not in the IQ manual.  While it will functionally work in IQ, it is an SA function and thus subject to the same performance compensations.

Mark

[kimon_moschandreou · Accepted solution]

Hi,

Thank you for the answer, what do you mean by this

- commands being run with no commits

I am afraid I don't understand it.

Kimon