Can't imagine what to do here. My query is:
SELECT
ACCOUNT.ID,
ACCOUNT.NAME,
SUPPLIERACCOUNT.NAME,
HEADER.CURRENCY,
HEADER.DDATE,
HEADER.RECIPIENTACCOUNTNUMBER,
HEADER.REQUESTID,
HEADER.PRINCIPALAMOUNT,
LINEITEM.HEADERID,
LINEITEM.DATE
FROM
ACCOUNT
JOIN HEADER ON ACCOUNT.ID = HEADER.ISSUERACCOUNTNUMBER
JOIN ACCOUNT AS SUPPLIERACCOUNT ON HEADER.RECIPIENTACCOUNTNUMBER = SUPPLIERACCOUNT.ID
JOIN LINEITEM ON HEADER.HEADERID = LINEITEM.HEADERID
WHERE ACCOUNT.BPID = 7222222
AND LINEITEM.LTYPE = 'P'
AND HEADER.POSTINGDATE >= ACCOUNT.CREATIONDATE
and ACCOUNT.ID IN (1111111,2222222 ...)
and HEADER.RECIPIENTACCOUNTNUMBER IN (3333333,6666666,9999999 ...)
AND HEADER.POSTINGDATE >= '2018-11-17'
AND HEADER.POSTINGDATE <= '2019-10-17'
ORDER BY LINEITEM.DATE, ACCOUNT.ID, LINEITEM.HEADERID
Without LINEITEM.DATE and LINEITEM.HEADERID in ORDER my query runs 2 sec, with them about 1 min. The result is about 200k rows. These columns in ORDER have indexes in tables.
Can someone, please, give me a hint, what I'm missing? Thank you.
Edit. Sybase version ASE 16.0. With full ORDER and set statistics time, io on I have this result:
[2019-10-20 22:05:17] [01000][3613] Parse and Compile Time 11.
[2019-10-20 22:05:17] Adaptive Server cpu time: 1100 ms.
[2019-10-20 22:05:17] [01000][3613] Parse and Compile Time 2.
[2019-10-20 22:05:17] Adaptive Server cpu time: 200 ms.
[2019-10-20 22:05:18] [01000][3615] Table: Worktable1 scan count 1, logical reads: (regular=18136 apf=0 total=18136), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[2019-10-20 22:05:18] [01000][3615] Table: ACCOUNT scan count 214, logical reads: (regular=428 apf=0 total=428), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[2019-10-20 22:05:18] [01000][3615] Table: HEADER scan count 214, logical reads: (regular=14934 apf=0 total=14934), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[2019-10-20 22:05:18] [01000][3615] Table: LINEITEM scan count 199411, logical reads: (regular=420968 apf=0 total=420968), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[2019-10-20 22:05:18] [01000][3615] Table: ACCOUNT (SUPPLIERACCOUNT) scan count 501, logical reads: (regular=1002 apf=0 total=1002), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[2019-10-20 22:05:18] [01000][3631] Total actual I/O cost for this command: 874664.
[2019-10-20 22:05:18] [01000][3614] Total writes for this command: 0
[2019-10-20 22:05:18] [01000][3612] Execution Time 566.
[2019-10-20 22:05:18] Adaptive Server cpu time: 56513 ms. Adaptive Server elapsed time: 56790 ms.
[2019-10-20 22:05:18] 500 rows retrieved starting from 1 in 58 s 825 ms (execution: 58 s 473 ms, fetching: 352 ms)
And with ORDER BY ACCOUNT.ID only i have:
[2019-10-20 22:15:22] [01000][3613] Parse and Compile Time 11.
[2019-10-20 22:15:22] Adaptive Server cpu time: 1100 ms.
[2019-10-20 22:15:22] [01000][3613] Parse and Compile Time 2.
[2019-10-20 22:15:22] Adaptive Server cpu time: 200 ms.
[2019-10-20 22:15:22] [01000][3615] Table: Worktable3 scan count 1, logical reads: (regular=2 apf=0 total=2), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[2019-10-20 22:15:22] [01000][3615] Table: Worktable1 scan count 1, logical reads: (regular=2 apf=0 total=2), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[2019-10-20 22:15:22] [01000][3615] Table: ACCOUNT scan count 1, logical reads: (regular=205 apf=0 total=205), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[2019-10-20 22:15:22] [01000][3615] Table: HEADER scan count 2, logical reads: (regular=33 apf=0 total=33), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[2019-10-20 22:15:22] [01000][3615] Table: LINEITEM scan count 501, logical reads: (regular=1142 apf=0 total=1142), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[2019-10-20 22:15:22] [01000][3615] Table: ACCOUNT (SUPPLIERACCOUNT) scan count 501, logical reads: (regular=1002 apf=0 total=1002), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
[2019-10-20 22:15:22] [01000][3631] Total actual I/O cost for this command: 4764.
[2019-10-20 22:15:22] [01000][3614] Total writes for this command: 0
[2019-10-20 22:15:22] [01000][3612] Execution Time 1.
[2019-10-20 22:15:22] Adaptive Server cpu time: 105 ms. Adaptive Server elapsed time: 106 ms.
[2019-10-20 22:15:22] 500 rows retrieved starting from 1 in 2 s 163 ms (execution: 1 s 936 ms, fetching: 227 ms)
With only:
order by account.id
Sybase can clearly choose a query plan where it goes through the account table in id order and joins the rest of the rows and does the filtering. This means that it can start returning results very quickly. It is not clear if the 2 seconds is the time to the first result to to the entire result set.
With the more complex order by, Sybase has to generate the entire result set and then order it. It cannot use indexes when the keys are mixed between tables. Unless you are running on a wristwatch, it should not take a minute to sort 200k rows. There are some caveats:
order by starts returning results after 2 seconds but the entire result set takes much longer, then the comparison is not valid.