SQL Query needs optimization

Question

Question

Virat Sharma · Jan 24, 2023

Hi All,

I am having an SQL query (mentioned below). When I am running this query it is taking 90 min to retrieve all records (approx 1 million records).

Fetching data from 5 tables using inner join in cache. Relative cost displaying 10 million

I wan to confirm How this query can be optimized

Select Books.nam,Books.print,Books.Relativename,Books.Firstpage,Books.Lastpage,Books.trn,Books.lastissue,Books.firstissue,Books.person,Books.author,Books.price,Books.cd,Books.ab,Books.pf,Books.ju,Books.er,Books.qw,Books.qt,Books.mn
Records.qw,Records.er,Records.ty,Records.ui,Records.op,Records.as
OrderBooks.mn,OrderBooks.bv
OrderRecords.sd,OrderRecords.fg,OrderRecords.hj
Orders.lastdate
From SQLUser.Books Books
INNER JOIN SQLUser.Records Records ON Books.id=Recordsid
INNER JOIN SQLUser.OrderedBooks OrderBooks ON Books.id=OrderBooks.id
INNER JOIN SQLUser.OrderedRecords OrderRecords ON Books.id=OrderRecords 
INNER JOIN SQLUser.Orders Orders ON Books.id=Orders.id
where ((Records.qw=OrderBooks.qw) and (Records.er is null))

Thanks in advance!!

Discussion (12)3

Log in or sign up to continue

score 0 · Answer 1 · 2023-01-24T16:28:01-05:00

David Hockenbroch · Jan 24, 2023

Have you tuned your tables?

0 0

score 0 · Answer 2 · 2023-01-25T01:45:26-05:00

Did you copy/paste the query correctly here?

Select Books.nam,Books.print,Books.Relativename,Books.Firstpage,Books.Lastpage,Books.trn,Books.lastissue,
Books.firstissue,Books.person,Books.author,Books.price,Books.cd,Books.ab,Books.pf,Books.ju,Books.er,
Books.qw,Books.qt,Books.mn,
Records.qw,Records.er,Records.ty,Records.ui,Records.op,Records."as",
OrderBooks.mn,OrderBooks.bv,
OrderRecords.sd,OrderRecords.fg,OrderRecords.hj,
Orders.lastdate

From SQLUser.Books Books

INNER JOIN SQLUser.Records Records ON Books.id=Recordsid
INNER JOIN SQLUser.OrderedBooks OrderBooks ON Books.id=OrderBooks.id
INNER JOIN SQLUser.OrderedRecords OrderRecords ON Books.id=OrderRecords
INNER JOIN SQLUser.Orders Orders ON Books.id=Orders.id

where ((Records.qw=OrderBooks.qw) and (Records.er is null))

In addition to tuning tables, I would first look at the query plan and, accordingly, the indexes involved in it.

score 0 · Answer 3 · 2023-01-26T04:59:02-05:00

please consider these as typo while writing the query here. I have typed the correct one below

Records.asa

Books.id=Records.id

Books.id=OrderRecords.id

score 0 · Answer 4 · 2023-01-25T04:49:54-05:00

90 min. to 1M records is very low.
It seems that the query is scanning at one of your big tables (Records maybe).
Do you have indices on: Records.qw and on Records.er
Consider to run the query in %parallel mode, after you optimize it.

score 0 · Answer 5 · 2023-01-26T13:58:25-05:00

indices is qw (in both class) but not on er.

Not sure of %parallel mode. How does this work? is this something similar to running the job in background.

score 1 · Answer 6 · 2023-01-25T07:30:42-05:00

Will you please get the query plan for this without running it in %parallel first, so that we can see what it does internally.

From there we can determine full scans on tables, etc.

Also, as mentioned, which fields are indexed and the types of indices.

score 0 · Answer 7 · 2023-01-26T07:41:59-05:00

Stefan is correct. Getting the query plan does 2 things

It provides us your exact SQL statement
It will tell us what plan is being used and if we have any table scans.

My suspicion is you are missing an index on one of your tables that are included with the INNER JOIN. I imagine the number of rows for these tables are on the order of you have

n number of books
n *10 number of orders(as a guess)

and there is a missing index on one of the Order tables but again the results of Show Plan will provide all of the information that is needed.

score 0 · Answer 8 · 2023-01-26T13:55:07-05:00

Query Plan:

Relative cost = 21418093

Read master map SQLUser.RecordsMASTER, looping on id and ~{330}*10(qw).

For each row:

Read master map SQLUser.Orders.Orders, using the given idkey value.
Read master map SQLUser.OrderedBooksMASTER, using the given idkey value.
Read master map SQLUser.BooksMASTER, using the given idkey value.
Read master map SQLUser.OrderedRecordsMASTER, using the given idkey value.
Output the row.

Index is on property qw and not on er

score 0 · Answer 9 · 2023-01-26T13:55:20-05:00

Query Plan:

Relative cost = 21418093

Read master map SQLUser.RecordsMASTER, looping on id and ~{330}*10(qw).

For each row:

Read master map SQLUser.Orders.Orders, using the given idkey value.
Read master map SQLUser.OrderedBooksMASTER, using the given idkey value.
Read master map SQLUser.BooksMASTER, using the given idkey value.
Read master map SQLUser.OrderedRecordsMASTER, using the given idkey value.
Output the row.

Index is on property qw and not on er

score 0 · Answer 10 · 2023-01-26T14:00:16-05:00

Query Plan:

Relative cost = 21418093

Read master map SQLUser.RecordsMASTER, looping on id and ~{330}*10(qw).

For each row:

Read master map SQLUser.Orders.Orders, using the given idkey value.
Read master map SQLUser.OrderedBooksMASTER, using the given idkey value.
Read master map SQLUser.BooksMASTER, using the given idkey value.
Read master map SQLUser.OrderedRecordsMASTER, using the given idkey value.
Output the row.

Index is on property qw and not on er

score 1 · Answer 11 · 2023-01-26T16:12:25-05:00

the query plan tells us that it is doing a table scan on every single row in SQLUser.Records.

if er was indexed in Records it would at least use the index.,

score 0 · Answer 12 · 2023-01-25T07:38:59-05:00

As a starting point, you can try thr following to force the IS NULL filter to be applied first - assuming there is an index on it.
From %FIRSTTABLE Records SQLUser.Books Books