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

[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