Access Rows

Hi all, I m fetching rows from four tables with the use of "DISTINCT" but database server takes approx. 4 min to fetch that data, previously it take above 10 minutes. I optimize the query by using "SET EXPLAIN ON" now it takes around 4 minutes. Only one table have lacs of records and rest are having thousand of records. thanks.

2008/10/7 ABDUL QADIR <qadirspn@rediff.com>: > Hi all, > > I m fetching rows from four tables with the use > of "DISTINCT" but database server takes approx. > 4 min to fetch that data, previously it take > above 10 minutes. I optimize the query by > using "SET EXPLAIN ON" now it takes around 4 minutes. > Only one table have lacs of records and rest are > having thousand of records. > > thanks. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Yes, and .... What is the question, you have analysed a long running query, optimised it and reduced the run time, what is the issue?? Keith

The DISTINCT clause is forcing a sort with duplicate removal which is relatively expensive. One thing you can do would be to improve sort performance by enabling parallel sorting and tuning the sort memory by: - PDQPRIORITY set high enough to involve more than one CPU VP if you have them - PSORT_NPROCS set to 2x NUMCPUVPS - PSORT_DBTEMP set to a list of from 3 to 6 filesystems with enough free space to hold sort-work files. Ideally these should all reside on separate physical drives, but do what you can - increase DS_NONPDQ_QUERY_MEM to at least 50 MB (the default of 128K is WAY to low) - note that this is a relatively new ONCONFIG parameter, if you don't have it, don't worry about it - this is why you ANLWAYS have to post your IDS version and platform information (I think I've posted that admonition 20 times in the last two months). You MAY have to set or increase DS_TOTAL_MEMORY to allow this as the defaults for that are very low also (128K * DS_MAX_QUERIES if that's set or 256K * NUMCPUVPS if DS_MAX_QUERIES is also not set). Also make sure that you have 3 or more temp dbspaces configured and listed in DPSPACETEMP so that the resulting temp table can be written and read as efficiently as possible. Art On Tue, Oct 7, 2008 at 5:44 AM, ABDUL QADIR <qadirspn@rediff.com> wrote: > Hi all, > > I m fetching rows from four tables with the use > of "DISTINCT" but database server takes approx. > 4 min to fetch that data, previously it take > above 10 minutes. I optimize the query by > using "SET EXPLAIN ON" now it takes around 4 minutes. > Only one table have lacs of records and rest are > having thousand of records. > > thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.