Order by is running slower after migrating to ASE1...

former_member783467 · ‎01-03-2022

Hi community,

First of all, happy new year to all of you.

We have noticed an unusual behaviour after migrating ASE from 15.7 to ASE16.

SELECT * FROM table1 where sop=1144000

If we run the statement above , it is executed in 0.056 seconds.

SELECT * FROM table1 where sop=1144000 order by usuario_actividad desc

If we run the statement above with an orderby desc, it is excecuted in 29.706 seconds.

SELECT * FROM table1 where sop=1144000 order by usuario_actividad asc

If we run the statement above with an orderby asc, it is executed in 19.642 seconds.

The table1 has 32'848.731 rows, we have a clustered index by sop and usuario_actividad is it's primary key.

The derived stats of the table and indexes are correct.

If we replicate the same situation under an ASE15.7 or 15.5 instance, the execution time for the order by is less than 1 second.

Has anyone of you faced something like this?

Kind regards,

Cristian Lopez,

Mark_A_Parsons · ‎01-04-2022

A quick look through the optdiags doesn't show any glaring issues:

ASE 15.5 has 2.2 million rows vs ASE 16 with 32.8 million rows, but ASE 16 stats have been bumped up with what looks like plenty of extra histogram steps
sop=0 represents 29% of the rows in ASE 15.5 and 43% of the rows in ASE 16; likely not an issue since it looks like REMOVE_SKEW_FROM_DENSITY has already been applied to the sop column; not that this should impact the queries in question

First guess would be a bad cached statement but from the query plans it doesn't look like you're using statement cache.

I'd be curious what the query plan looks like if you were to disable the backward scan of the pk_trz_usuario_actividad index (sp_configure 'allow backward scans',0) and then re-run the order by/desc query.

'fraid I'm not coming up with anything else at the moment; the few clients I've worked with that have gone to ASE 16 haven't seen this behavior (or they haven't mentioned it to me); then again they're all several releases behind you (16.0 SP03 PL10) so can't tell if this might be a regression issue with the newer release(s).

If you've got a support contract and you haven't already done it, I'd go ahead and open a ticket. [If support comes back with a confirmed issue and/or solution, and you've got the time, could you update this thread with the results?]

Regards,

Mark