Help in Optimising Query

[Guest.Visitor]

Hi, I have a Query that is running for 4 mins and I need to optimise the same. The skeleton of the query is as follows Select A.fld1, B.fld2, A.fld3, c.fld4, A.fld5, D.fld6....fldn from filea A left outer join fileb B on A.fld1 = B.fld2 left outer join file3 C on A.fld3 = C.fld4 left outer join file4 D on A.fld5 = D.fld6 group by a.fld1, b.fld2, ..... Order by A.fldn Since I am using left outer join, the join order depends on the fields in the join condition. Hence the Order by takes more time. I tried creating an index on the Order by field but it does not use it. If I remove the Order by clause, then the query runs in couple of seconds. But if I include the Order by clause, it takes four mins. Is there a way out of this?? and why is it working the way it is right now?? Thanx in advance. Regards Arun Prasad R

[S.Mildenberger]

You can do the following steps to see all the messages from the Query Optimizer. It will tell you if a certain logical would help the processing. 1. Use CHQQRYA QRYTIMLMT(0) to set a limit of zero on your query run time. 2. Run the query interactively. You will receive a message indicating the estimated query run time, take option C to cancel. 3. Look in your job log for the query optimizer messages and see if their are any indicating logicals you can build to try and speed up the query. 4. Repeat the above after creating any new logicals or changing the query to see if your changes help. (I have seen a case where building the suggested logical caused my query to get slower but usually it helps.) Scott Mildenberger

[Guest.Visitor]

Arun, Is there any selection for this in either a where clause or the joins? Based on what you have written, I would guess that it should have picked up your order by key, unless this causes a join not to work. You can also play around with FORCE_JOIN_ORDER by setting it to *YES (assuming V4R4+) and seeing what happens. Is your primary file large? The size of files and the selections applied make a big difference in how the optimizer executes your query. David Morris

[Guest.Visitor]

Hi, Thanx for the suggestions. The Optimiser messages show that it builds a temporary result set. It also shows that it builds an index but it contains computed fields so building a permenant index is not possible. But the index build takes only .7 seconds and hence is not a big issue. The size of the file A = 252661 file B = 906048 file C = 146130 file D = 146130 (In fact file C and D are the same file). I am at V4R5 with latest cum PTF and database Group PTF. What I don't understand is if I remove the ORDER BY Clause it runs in seconds. Any reasons why?? Regards Arun Prasad R

[Guest.Visitor]

Arun, So this has no selection? Are all fields referenced from A in the result set? If they are not I would try adding them. David Morris

[Guest.Visitor]

David, Yes there is no selection and all the fields from file A are in the result set. Regards Arun

[Guest.Visitor]

Arun, The only things I can think of to try at this point would be to create a query options file using CRTDUPOBJ qaqqini qsys *file qtemp data(*YES). Then use SQL or a file utility like DFU to update (or add if necessary) a record that says FORCE_JOIN_ORDER *YES. Then chgarya qryoptlib(qtemp). Another would be to add optimize for 1 record to your SQL statement. Finally, although I doubt it will help, you might check for database performance PTFs, they are not always on the database group. I think if you order the PTF detail you can scan on the report for performance and database which are D and M. I am not sure but I think that is sf97450. David Morris

[Guest.Visitor]

David, Thanx for all your suggestions. I did try the FORCE_JOIN_ORDER but to no avail. I will check the other two solutions that you have suggested. Thanx Arun Prasad

[Guest.Visitor]

David, Can you please give me more information about the Database Performance PTF. I haven't heard about it. Is there any other way to find out if the Database Performance PTF is present as a part of the Database Group PTF or not. Thanx Regards Arun Prasad

[Guest.Visitor]

Arun, All PTFs are assigned to a category. If we were at IBM's support center, it would probably be easy to query PTFs by category. The only summary PTF list that I know of that lists the categories is the list you get when you order SF97450 (the 450 is the release). That PTF is just a cover letter that describes all of the available PTFs. In some columns on the right hand side of that report the PTF categories are summarized by codes. There are about 20 categories. If a PTF is not ordered by enough people, it is not put on the HIPER list. David Morris