TomBascom
Curmudgeon
It could take quite a while to run in that environment.
As I guessed everything is stuffed into the schema area. This is very, very bad for performance. For one thing it means that the SQL-92 no-index table scan cannot be used. Which is probably why the optimizer wasn't much help.
It does not, however, explain why you didn't see the same problem with version 9. All I can think of in that regards is that something else must be different. Perhaps less data or a different physical disk layout. (You'd be astonished at how many "disk upgrades" are really tremendous steps backwards in performance terms...)
You can change to type 2 areas and fix all of that without having any impact at all on the application (other than dramatically improving performance) but you will need to get beyond the idea that your vendor has control over such things.
It isn't worth discussing any more though unless you're going to be willing to make some changes.
As I guessed everything is stuffed into the schema area. This is very, very bad for performance. For one thing it means that the SQL-92 no-index table scan cannot be used. Which is probably why the optimizer wasn't much help.
It does not, however, explain why you didn't see the same problem with version 9. All I can think of in that regards is that something else must be different. Perhaps less data or a different physical disk layout. (You'd be astonished at how many "disk upgrades" are really tremendous steps backwards in performance terms...)
You can change to type 2 areas and fix all of that without having any impact at all on the application (other than dramatically improving performance) but you will need to get beyond the idea that your vendor has control over such things.
It isn't worth discussing any more though unless you're going to be willing to make some changes.