Improve performance of the SQL query (DB2)

Question

I need to get the file name with records meeting a certain criteria, but I realize that the query is very slow when executing. I have added index, but it is still very slow, how can improve the performance??? I am using DB2. Please help, thank you.

Table (FILE) (Data volume - around 100000)

• FILE_ID
• NAME
• CATEGORY

INDEX (CATEGORY, FILE_ID)

Table (RECORD) (Data volume - around 50000000)

• RECORD_ID
• DATA
• CREATE_DATE
• TYPE
• FILE_ID

INDEX(CREATE_DATE, TYPE, FILE_ID)

SELECT NAME 
FROM FILE
WHERE CATEGORY = ? AND 
      FILE_ID IN (SELECT FILE_ID FROM RECORD WHERE CREATE_DATE = ? AND TYPE = ? )

Answer 1

You might find that putting the "table file" into a temp table with the where clause speeds things up...

eg...

create a temp table then insert all records from Table File "Where Cateogry = ?" and then join that table onto Record

Answer 2

the join should solve your issue.

also sometimes you need to reorganize (REORG) tables and indexes to improve performance on slow queries.

Answer 3

this wouldn't give you duplicate result

SELECT NAME 
FROM FILE F
JOIN (
    SELECT FILE_ID FROM RECORD WHERE CREATE_DATE = ? AND TYPE = ? 
    GROUP BY FILE_ID
) Q ON F.FILE_ID = Q.FILE_ID
WHERE CATEGORY = ?

Answer 4

Try with INNER JOIN syntax:

select file.name
    from file inner join record using(file_id)
where file.category_id=? and record.create_date=? and record.type=?

with index(file_id, create_date, type) for record table.

Answer 5

Join the tables.

select f.name
from f join record r on f.file_id = r.file_id
where f.category = ?