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?
Thanks for help, Hanan Brener
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.
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 ...
... 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
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
answered 29 Oct '12, 14:22
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.
answered 25 Oct '12, 16:07
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.
answered 29 Oct '12, 14:29