Query execution time difference between SQLA12 and SQLA17

[former_SQLA_member1694912]

We are doing some regression testing in SQLA17. I am executing the same query in both versions, in SQLA12 the execution time is 7 seconds but in SQLA17 is 13 seconds (almost the double of the time). I already ran the sa_flush_cache procedure and updated the statistics of the tables involved in the query. The database in SQLA17 is the same database that was upgraded from version 12 to 17 and both are running in the same server.

It is assumed that version 17 has enhancements, should not degrade response time.

Any idea why this? What else could I do to improve the time in 17?

Thanks

[Vlad]

Can you please upload the execution plan?

[former_SQLA_member1694912]

How can I upload it? It's so big to put in the comments.

[VolkerBarth]

In my humble experience, some queries run (sometimes considerably) slwoer with v16/v17 compared to v12 when intra-query parallelism comes into play. On those cases, I have usually tested with disabling that (i.e. via setting option max_query_tasks to 1).

For example, see that question...

To disable intra-query parallelism for your test query, you can easily do that via

SET TEMPORARY OPTION max_query_tasks = 1;

[former_SQLA_member1694912]

thanks for the comment, it's good to know but in my case the query is not using intra-query parallelism.