Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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

asked 31 Jan '18, 04:15

mapperclient's gravatar image

mapperclient
11112
accept rate: 0%

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

(31 Jan '18, 06:29) Christian Ha...
Replies hidden
1

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.

(31 Jan '18, 07:16) Vlad
Replies hidden

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

(31 Jan '18, 07:30) Christian Ha...

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 )
       ) );
(31 Jan '18, 12:06) Breck Carter
Replies hidden

Do I have missed the ASE reference?

(31 Jan '18, 14:09) Volker Barth
Replies hidden

Ooops, just assumed "Sybase 12" meant the other thing :)

(31 Jan '18, 16:06) Breck Carter

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.

(01 Feb '18, 03:35) mapperclient

Hi

I did try this but did not make a differences.

(01 Feb '18, 03:37) mapperclient

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

(01 Feb '18, 03:44) mapperclient
1

So can you correct your script in the question?

And what about the execution plans?

(01 Feb '18, 05:07) Volker Barth
showing 4 of 10 show all flat view

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.

permanent link

answered 01 Feb '18, 04:56

alexlake's gravatar image

alexlake
1317917
accept rate: 11%

edited 01 Feb '18, 04:56

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

(02 Feb '18, 03:15) mapperclient
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×439
×261

question asked: 31 Jan '18, 04:15

question was seen: 1,921 times

last updated: 02 Feb '18, 03:15