SA12 - Stored Procedure in from clause select statement problem

[former_SQLA_member1694890]

We have moving our product from SA10 to SA12 (Linux 12.0.1 ebf 3519; Windows 12.0.1 ebf 3718). The select used inside 'from' clause stored procedure(sp) which used tables as parameters. Example -

SELECT service_id, 
       Sum(octets_io)  AS OCTETS_IO, 
FROM  (SELECT service_id, 
              Sum(octets_in * f_octets_in + octets_out * f_octets_out) AS OCTETS_IO
       FROM   Sp_get_conv_factor('VCV6_STAT_MIN_17', 'CONVERV6_STAT_MIN_8', '2012-10-24 13:05:00.0', '2012-10-24 13:59:59.0', 1, 3), 
              converv6_stat_min_8 
       WHERE  timemark BETWEEN '2012-10-24 13:05:00.0' AND '2012-10-24 13:59:59.0' 
              AND device_id = f_device_id 
              AND timemark = f_timemark 
        GROUP  BY service_id 
       UNION ALL 
       SELECT service_id, 
              Sum(octets_in * f_octets_in + octets_out * f_octets_out)AS OCTETS_IO, 
       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), 
              converv6_stat_hrs_10 
       WHERE  timemark BETWEEN  '2012-10-24 14:00:00.0' and '2012-10-24 15:59:59.0'
              AND device_id = f_device_id 
              AND timemark = f_timemark 
              GROUP  BY service_id)VT 
GROUP  BY service_id 
HAVING octets_io <> 0 
ORDER  BY octets_io DESC

After running OK, this statement begin to run like in loop, test prints from SP show that select statement call to the same SP (Sp_get_conv_factor) all time (we see this with prints on start/finish SP). After running statement 'DROP STATISTICS ...' on tables involved in SP , select statement come back to work properly. We have tested with last SA12 ebf for Linux(3759) the same problem appears. In SA10 we didn't see problem with these select statements. What is the reason for this 'strange' behavioral in SA12(this database instance is not upgraded from SA10 but created as SA12)? We need change somethings in statistics gathering properties?

Here's the code of the SP "Sp_get_conv_factor"

Thanks for help, Hanan Brener

[Former Member]

How complex is the stored procedure definition?

[former_SQLA_member1694890]

I have upload sp_get_conv_factor code

[VolkerBarth]

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:)

[Former Member]

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

[justin_willey]

@Nica_SAP - this reads like a rather important bit of advice - are you able to expand a bit on what the optimizer can and can't do? Thanks

[former_SQLA_member1694890]

Thanks for answers. We have tested option work 'lateral' but get the same result (query statement run SP in loop). 'DROP STATISTICS ...' on tables inside SP solve problem but not always, sometimes in our test we encountered with the same problem or opposite after using 'DROP STATISTICS ...' few times we have remove it from code and statement continue to work properly (with different tables as argument in SP). Question what is possibility to limit new optimization features in specific cases. I want emphasize this problem arised after moving from SA10 to SA12

[Former Member]

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

[Breck_Carter]

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.

[former_SQLA_member1694890]

How can I add file with SP to my question?

[Former Member]

Edit the question and click on the paper-clip-like thingy at the top of the editor box.

[Former Member]

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.

[Breck_Carter]

Here's the code of the SP "Sp_get_conv_factor"

[VolkerBarth]

Given the rather complex SP definition and the fact that only procedures whose body consists of one single SELECT statement are inlined, I doubt it would have ever been inlined. - To cite from the v12.0.1 docs:

A procedure is never inlined if it uses default arguments, or if it contains anything other than a single SELECT statement in the body.