Hi Sorry if I posted this on the wrong group Testing Sybase 16(with latest ebf) after upgraded our databases from Sybase 12. The database engine struggles when you have mixture of OR with AND from example SELECT COUNT(id)
FROM TableA What changes can I try on the database engine(setting, option) to optimise the query without rewriting them? Thanks asked 31 Jan '18, 04:15 mapperclient |
Really daft question, but what OS is this, and does it have the latest patches? I found some things slowed down dramatically (way more than the advertised 25%) with the meltdown/spectre patches. Hopefully this is a red herring for you. answered 01 Feb '18, 04:56 alexlake windows 10 pro 64bit with 64GB with all the latest patches.
(02 Feb '18, 03:15)
mapperclient
|
As suggested in this post http://sqlanywhere.blogspot.nl/2011/08/new-maxbps768-set-maxquerytasks-1.html you could try to set the option
MAX_QUERY_TASKS
to 1Can you please attach the execution plan of your query? Or two plans even better - old and new.
Christian might be correct, but I would never use this option until I check the execution plan first.
@Vlad: I fully agree. It's just something you could try to see if it makes a difference.
Should "inner join C on A.id = B.id" really be coded "inner join C on C.id = B.id"?
Do any indexes exist on TableA.id, A.id, B.id, C.id, D.id, E.id, G.id and H.id?
If those indexes exist in SQL Anywhere 12 but not in 16, it might make a difference.
Do I have missed the ASE reference?
Ooops, just assumed "Sybase 12" meant the other thing :)
Should "inner join C on A.id = B.id" really be coded "inner join C on C.id = B.id"? You are correct this was an error in my transcript.
All the tables have foreign and Primary key indexes.
Testing "OR" without the "AND" took two hours to show a result, so I don't think it has anything to do with "AND" now.
Hi
I did try this but did not make a differences.
Sorry if I miss led anyone, It is Sybase Anywhere 16 and 12.
So can you correct your script in the question?
And what about the execution plans?