Sybase 16 query using OR's and AND's very slow compared to Sybase 12

[Former Member]

Hi Sorry if I posted this on the wrong group

Testing Sybase 16(with latest ebf) after upgraded our databases from Sybase 12.
I found that query which take second in Sybase 12 are now taking hours/minutes.
I can rewrite the queries with union but this is not the answer as we have hundreds of scripts, it is a major problem.

The database engine struggles when you have mixture of OR with AND from example

SELECT COUNT(id) FROM TableA
WHERE ColumnA = 0 AND ( ( ID in (SELECT A.Id FROM A
INNER JOIN B ON A.id = B.id inner join C on A.id = B.id WHERE C.ColumnA = 0
AND B.ColumnA = 0 ) OR ID in(SELECT D.Id FROM D
INNER JOIN E ON D.id = E.id inner join C on C.id = E.id WHERE C.ColumnA = 0
AND D.ColumnA = 0 ) OR ID in(SELECT G.Id FROM G
INNER JOIN H ON H.id = G.id inner join C ON C.id = H.id WHERE C.ColumnA = 0
AND H.ColumnA = 0 )) )

What changes can I try on the database engine(setting, option) to optimise the query without rewriting them?

Thanks

[Chris26]

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 1

[Vlad]

Can 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.

[Chris26]

@Vlad: I fully agree. It's just something you could try to see if it makes a difference.

[Breck_Carter]

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.

SELECT COUNT(id) 
  FROM TableA
 WHERE ColumnA = 0 
   AND ( (    ID in ( SELECT A.Id 
                        FROM A
                             INNER JOIN B 
                                     ON A.id = B.id 
                             inner join C 
                                     on A.id = B.id -- CODING ERROR?
                       WHERE C.ColumnA = 0
                         AND B.ColumnA = 0 ) 
           OR ID in ( SELECT D.Id 
                        FROM D
                             INNER JOIN E 
                                     ON D.id = E.id 
                             inner join C 
                                     on C.id = E.id 
                       WHERE C.ColumnA = 0
                         AND D.ColumnA = 0 ) 
           OR ID in ( SELECT G.Id 
                        FROM G
                             INNER JOIN H 
                                     ON H.id = G.id 
                             inner join C 
                                     ON C.id = H.id 
                       WHERE C.ColumnA = 0
                         AND H.ColumnA = 0 )
       ) );

[VolkerBarth]

Do I have missed the ASE reference?

[Breck_Carter]

Ooops, just assumed "Sybase 12" meant the other thing 🙂

[Former Member]

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.

[Former Member]

Hi

I did try this but did not make a differences.

[Former Member]

Sorry if I miss led anyone, It is Sybase Anywhere 16 and 12.

[VolkerBarth]

So can you correct your script in the question?

And what about the execution plans?

[former_SQLA_member1694906]

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.

[Former Member]

windows 10 pro 64bit with 64GB with all the latest patches.