0

I'm struggling with optimising my query. I've tried to solve the issue with various indexes by none of them seems to be helpful.

There are 3 tables: transfers(id, amount, type, timestamp, file_id), files(id, path), file_to_report(id, report_id, file_id).

transfers and files have many to one relation. files and file_to_report have many to many relation.

1 report ≈ 20k files ≈ 200k transfers

I have to select all transfers of particular type for particular report_id, sorted by timestamp.

SELECT t.id, t.amount, t.timestamp
FROM transfers t
INNER JOIN file_to_report ftr ON t.file_id = ftr.file_id
WHERE t.type = 'debit'
  AND ftr.report_id = 1
ORDER BY t.timestamp, t.id
OFFSET 5000 ROWS
FETCH NEXT 1000 ROWS ONLY;

Currently this query takes ~20s. With every next page it needs more and more time. Without ORDER BY it takes 0.05s.

I've tried to create the next indexes to avoid sorting / make filtering by file_to_report faster:

CREATE INDEX i1 ON transfer (timestamp, id);
CREATE INDEX i2 ON transfer (timestamp, id, file_id);
CREATE INDEX i3 ON transfer (file_id, timestamp, id);
CREATE INDEX i4 ON transfer (type, timestamp, id);
CREATE INDEX i5 ON transfer (type);
CREATE INDEX i6 ON transfer (type, file_id, timestamp, id);

CREATE index i7 ON file_to_report (file_id);
CREATE index i8 ON file_to_report (file_id, report_id);

However, none of these indexes makes the query faster. Please, help me to understand what is wrong.

2
  • 5
    Can you add the actual queryplans by using pastetheplan.com
    – Peter
    Aug 23 at 8:44
  • Try adding an included column to i4: CREATE INDEX i4 ON transfer (type, timestamp, id) INCLUDE(file_id);
    – Dan Guzman
    Aug 23 at 18:35

1 Answer 1

1

almost

You'd want to adjust this index to include amount:

CREATE INDEX i6 ON transfer (type, file_id, timestamp, id) INCLUDE (amount);

And change this index to switch the column order:

CREATE index i8 ON file_to_report (report_id, file_id);

local test

Using a similar query against tables I can access:

SELECT
    u.Id,
    u.CreationDate,
    u.Reputation
FROM dbo.Users AS u
JOIN dbo.Posts AS p
  ON u.Id = p.OwnerUserId
WHERE u.DownVotes = 0
AND   p.PostTypeId = 1
ORDER BY
    u.CreationDate,
    u.Id
OFFSET 5000 ROW
FETCH NEXT 1000 ROW ONLY;

With the following indexes:

CREATE INDEX u
ON dbo.Users
(DownVotes, Id, CreationDate)
INCLUDE(Reputation);

CREATE INDEX p
ON dbo.Posts
(PostTypeId, OwnerUserId);

I get the following query plan, completing in 169ms:

NUTS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.