SA 12 addressed issues related with procedure inlining in the query blocks they are used. One explanation for your performance issue is that your procedures no longer qualify to be inlined. Please post graphical plans and the request level logs for both SA12 and SA11: start the server with -zr all -zo filename , run your query in dbisql to obtain the graphical plan with statistics.

A general advice: To improve the query performance, allow the predicates you have in the query in the WHERE clause to be used inside the procedure, e.g., add extra parameters and pass the constants to be used inside the procedure.

Ani

I do not know if this does apply as I'm not sure about the join conditions between the sp and the two joned tables (converv6_stat_min_8 and converv6_stat_hrs_10):

Just in case the proc parameters (or the filter you apply on them) somehow depend on the joined table and you end up using a cross join between sp and table, you may be able to turn around the dependancy by using the sp in a "lateral" call or with an APPLY operator. Basically somehing like

...
from myTable, lateral (myProc(myTable.myColumn)) as myProcCall ...

instead of

...
from myProc(<allPossibleValues>), myTable  
where myProc.something = myTable.myColumn...

I haven't been able to find a better "sample" - and hope you get the point:)

I have to see your sp definition to give a good advise here. If the procedure is not inlined, there is nothing pushed inside the procedure body from the WHERE clause. If you have a good filtering predicate the WHERE clause of the query which can restrict the result set of the procedure, then adding that predicate inside the procedure is always a good advise. The advise from Volker using "lateral" should be used with caution as his example imposes always a join nested loop between myTable JNL myProc which can be very slow for a large myTable.

Could you please qualify the columns used in the WHERE clause? I assume they are like this:

  FROM   Sp_get_conv_factor( 'VCV6_STAT_HRS_10', 
`'CONVERV6_STAT_HRS_10', '2012-10-24 14:00:00.0', 
'2012-10-24 15:59:59.0', 1, 3) AS P,
                  converv6_stat_hrs_10 AS T 
           WHERE  T.timemark BETWEEN  '2012-10-24 14:00:00.0' and '2012-10-24 15:59:59.0'
                  AND T.device_id = P.f_device_id 
                  AND T.timemark = P.f_timemark

In this case, the inferred predicate

P.timemark BETWEEN '2012-10-24 14:00:00.0' and '2012-10-24 15:59:59.0'

refers only to the procedure and it can be used in the procedure itself such that the procedure will filter rows before returning its result set:

   FROM   Sp_get_conv_factor( 'VCV6_STAT_HRS_10', 
``'CONVERV6_STAT_HRS_10', '2012-10-24 14:00:00.0', 
'2012-10-24 15:59:59.0', 1, 3,

    '2012-10-24 14:00:00.0' /*lowerbound of f_timemark */,

    '2012-10-24 15:59:59.0' /*upper bound of f_timemark */

    ) AS P,

    converv6_stat_hrs_10 AS T 
           WHERE  T.timemark BETWEEN  '2012-10-24 14:00:00.0' and '2012-10-24 15:59:59.0'
                  AND T.device_id = P.f_device_id 
                  AND T.timemark = P.f_timemark

Here's a wild guess: SQL Anywhere 10 was able to optimize the WHERE and GROUP BY clauses by pushing them down into the queries inside the stored procedure, but some change in SQL Anywhere 12 makes this optimization impossible.

Please show us the code for Sp_get_conv_factor.

You need to open a QTS issue, and/or provide the definition of the stored procedure to get help with this issue. All restrictions imposed in SA 12 for procedure inlining are correctness restrictions. Without the sp definition, we cannot even tell if the procedure was inlined in SA 10. Many other reasons such as option settings - e.g., optimization_goal='first-row' - may explain your performance problem.