Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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:

×438
×260

question asked: 31 Jan '18, 04:15

question was seen: 1,765 times

last updated: 02 Feb '18, 03:15