IDS Forum

Re: Access Rows

Posted By: Art Kagel
Date: Tuesday, 7 October 2008, at 7:10 a.m.

In Response To: Access Rows (ABDUL QADIR)

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.

Messages In This Thread

• Access Rows
  ABDUL QADIR -- Tuesday, 7 October 2008, at 5:44 a.m.
  • Re: Access Rows
    Keith Simmons -- Tuesday, 7 October 2008, at 6:56 a.m.
  • Re: Access Rows
    Art Kagel -- Tuesday, 7 October 2008, at 7:10 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.