Hello all,

I have some quiries with joins of different tables and at the end order by clause. These quiries take very longer time from 7 Min to more.
The Records in the datatbase are more than half million
When ever I remove the order by clause then the quiries pull out with in seconds.

Here is sample sql
---------------------

select * from a,b where a.col1=b.col1 and a.col2='123'
order by 1 desc

The database I am using is Informix.

Do I need to increase the buffer size etc or there is need to do some performance tuning etc.

Any help/tip will be highly appriciated.

Thanks in advance

A few tuning tips...

Don't use 'select * ...'
Create one or more temporary dbspaces (preferably on the fastest disks)
Create an index on the order by column.
Use PDQ and add sort threads. and PDQ-mem

What version of Informix are U using?
Can you give the explain output?

Hi Roelwe,
Thank very much for your for response

Please see the sqexplain.out.

---------------------->sqexplain.out<-------------------------------------

QUERY:
------
SELECT
device_requests.trace_audit_no,
device_requests.trans_date,
device_requests.trans_time,
device_requests.device_type,
dt.device_type_desc,
device_requests.device_id,
device_requests.request_no,
device_requests.card_acceptor_code,
m.merchant_name,
device_requests.comp_code
,fcc.comp_desc,
device_requests.trans_local_dtime,
device_requests.trans_status,
device_requests.amount,
device_requests.currency_code,
cc.currency_desc,
device_requests.contra_account,
device_requests.description,
device_requests.consumer_entry,
device_requests.business_date,
device_requests.commission_amount,
device_requests.retrieval_ref_no,
device_requests.auth_code,
fac.auth_desc,
device_requests.discounted_price,
products.product_name,
device_requests.clerk_id,
device_requests.description,
device_requests.shift_no,
device_requests.iso_reconcil_date
FROM
device_requests,
merchants m,
currency_codes cc,
fc_auth_codes fac,
device_types dt,
fc_comp_codes fcc ,
products ,
trans_charges t
WHERE
device_requests.card_acceptor_code=m.merchant_id
AND device_requests.currency_code=cc.currency_code
AND device_requests.device_type=dt.device_type
AND device_requests.auth_code=fac.fc_auth_code
AND device_requests.comp_code=fcc.fc_comp_code
AND t.trace_audit_no=device_requests.trace_audit_no
And device_requests.consumer_entry=products.product_id
AND t.receiver_code=='2090701001 '
And t.level >0
order by 1 ASC

Estimated Cost: 412
Estimated # of Rows Returned: 43
Temporary Files Required For: Order By

1) root.t: INDEX PATH

Filters: root.t.level > 0

(1) Index Keys: receiver_code
Lower Index Filter: root.t.receiver_code = '2090701001 '

2) root.device_requests: INDEX PATH

(1) Index Keys: trace_audit_no
Lower Index Filter: root.device_requests.trace_audit_no = root.t.trace_audit_no
NESTED LOOP JOIN

3) root.m: INDEX PATH

(1) Index Keys: merchant_id
Lower Index Filter: root.m.merchant_id = root.device_requests.card_acceptor_code
NESTED LOOP JOIN

4) root.products: INDEX PATH

(1) Index Keys: product_id
Lower Index Filter: root.products.product_id = root.device_requests.consumer_entry
NESTED LOOP JOIN

5) root.fac: INDEX PATH

(1) Index Keys: fc_auth_code
Lower Index Filter: root.fac.fc_auth_code = root.device_requests.auth_code
NESTED LOOP JOIN

6) root.fcc: INDEX PATH

(1) Index Keys: fc_comp_code
Lower Index Filter: root.fcc.fc_comp_code = root.device_requests.comp_code
NESTED LOOP JOIN

7) root.cc: INDEX PATH

(1) Index Keys: currency_code
Lower Index Filter: root.cc.currency_code = root.device_requests.currency_code
NESTED LOOP JOIN

8) root.dt: INDEX PATH

(1) Index Keys: device_type
Lower Index Filter: root.dt.device_type = root.device_requests.device_type
NESTED LOOP JOIN


QUERY:
------
SELECT
device_requests.trace_audit_no,
device_requests.trans_date,
device_requests.trans_time,
device_requests.device_type,
dt.device_type_desc,
device_requests.device_id,
device_requests.request_no,
device_requests.card_acceptor_code,
m.merchant_name,
device_requests.comp_code
,fcc.comp_desc,
device_requests.trans_local_dtime,
device_requests.trans_status,
device_requests.amount,
device_requests.currency_code,
cc.currency_desc,
device_requests.contra_account,
device_requests.description,
device_requests.consumer_entry,
device_requests.business_date,
device_requests.commission_amount,
device_requests.retrieval_ref_no,
device_requests.auth_code,
fac.auth_desc,
device_requests.discounted_price,
products.product_name,
device_requests.clerk_id,
device_requests.description,
device_requests.shift_no,
device_requests.iso_reconcil_date
FROM
device_requests,
merchants m,
currency_codes cc,
fc_auth_codes fac,
device_types dt,
fc_comp_codes fcc ,
products ,
trans_charges t
WHERE
device_requests.card_acceptor_code=m.merchant_id
AND device_requests.currency_code=cc.currency_code
AND device_requests.device_type=dt.device_type
AND device_requests.auth_code=fac.fc_auth_code
AND device_requests.comp_code=fcc.fc_comp_code
AND t.trace_audit_no=device_requests.trace_audit_no
And device_requests.consumer_entry=products.product_id
AND t.receiver_code=='2090701001 '
And t.level >0
order by 1 ASC

Estimated Cost: 412
Estimated # of Rows Returned: 43
Temporary Files Required For: Order By

1) root.t: INDEX PATH

Filters: root.t.level > 0

(1) Index Keys: receiver_code
Lower Index Filter: root.t.receiver_code = '2090701001 '

2) root.device_requests: INDEX PATH

(1) Index Keys: trace_audit_no
Lower Index Filter: root.device_requests.trace_audit_no = root.t.trace_audit_no
NESTED LOOP JOIN

3) root.m: INDEX PATH

(1) Index Keys: merchant_id
Lower Index Filter: root.m.merchant_id = root.device_requests.card_acceptor_code
NESTED LOOP JOIN

4) root.products: INDEX PATH

(1) Index Keys: product_id
Lower Index Filter: root.products.product_id = root.device_requests.consumer_entry
NESTED LOOP JOIN

5) root.fac: INDEX PATH

(1) Index Keys: fc_auth_code
Lower Index Filter: root.fac.fc_auth_code = root.device_requests.auth_code
NESTED LOOP JOIN

6) root.fcc: INDEX PATH

(1) Index Keys: fc_comp_code
Lower Index Filter: root.fcc.fc_comp_code = root.device_requests.comp_code
NESTED LOOP JOIN

7) root.cc: INDEX PATH

(1) Index Keys: currency_code
Lower Index Filter: root.cc.currency_code = root.device_requests.currency_code
NESTED LOOP JOIN

8) root.dt: INDEX PATH

(1) Index Keys: device_type
Lower Index Filter: root.dt.device_type = root.device_requests.device_type
NESTED LOOP JOIN

---------------------->end<-------------------------------------

Following is the detail of Plateforms.

OS-> UNIX sco 5.6
DB-> Informix 7.3

A weird thing is that the optimizer does not use the index for it's sort.
Did you 'Update Statistics' correctly? If not, lots of scripts are available at http://www.iiug.org

Another stange behaviour is that it uses temp files...
What is the setting of your DBSPACETEMP in the $ONCONFIG file? Is it blank?

What are the following settings in your $ONCONFIG?
OPTCOMPIND
OPT_GOAL

For 7.3, it's better to set the OPT_GOAL = -1 and the OPTCOMPIND = 0.
I know the optimizer has changed from 7.x to 9.x.

You could always try to set Optimizer directives, to make it use the index on device_request for the sort.

An easy way of speeding up the qry is putting in in a temp table:
select blablabla
from blablabla
where blablabla
into temp temp_table1 with no log;

select .......
from temp_table1
order by 1 ASC;

Thank you very much for your response.

Temp table is not solving the issue because it first enter more than half million of records in temp table then there is need to re-select order by which is again taking long time.

More Over I need this fuctionality on each user basis seprately(Session wise) and I will have to create temp table for every user seprately.

Here is the detail of settings which I am using in Informix.

Command Value
-------------- -----------
DBSPACETEMP tempdbs
OPTCOMPIND 2
OPT_GOAL -1


Please let me know if any other info is required.

Thaks again for your support

Your explain output states the following:
Estimated # of Rows Returned: 43

That is why he will not choose the index to do the order by.
If you say millions of rows are returned by the qry, you statistics are not correct.

Use UPDATE STATISTICS scripts. You can download several scripts from iiug.org. If you cannot find one I could send you one.

Temp tables are session based. No problem about that.

Yuo might want to check onstat -d to see if tempdbs is one of the dbspaces. Either it is not large enough, or IDS cannot find it.

You could also try to set the OPTCOMPIND to 0.
That means he will try to use indexes as much as possible.

Hello Roelwe,


Please send me the scripts at email address below,
I will be highly obliged for this favour.

Thank you,

Istikhar

I faced One strange thing during the investigation of the problem which is

When the number of columns in select statement were more the quiry took longer time, But when the number of columns were too few the quiry surprisingly took less time.

Can you please let me know why this is happening and what is the solution?


Thanks in advance

Don't you think that's normal?
The more columns, the more data returned, the slower it is.

Hello

I have a problem with Parallel Database Query. By enabling PDQ and using the value PDQPRIORITY = 4 , The system serve the first few queries very fastly then with the passage of time it does not respond for the next queries. Then to make the system available again i need to restart the database.

I am accessing the system through web in the form of crystal web reports and there are large number of concurrent users.
In this case the enabling of PDQ is feasible?

Here is the output by using the command onstat -g mgm which shows how the system goes to nonfunctional state. The free memory shown as 128 also goes to -128, -256 and so on reducing to handle new requests.
------------------------------------------------------------------------------------
Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 01:01:49 -- 167936 Kbytes

Memory Grant Manager (MGM)
--------------------------

MAX_PDQPRIORITY: 100
DS_MAX_QUERIES: 600
DS_MAX_SCANS: 1000
DS_TOTAL_MEMORY: 153600 KB

Queries: Active Ready Maximum
30 1 600

Memory: Total Free Quantum
(KB) 153600 128 256

Scans: Total Free Quantum
1000 987 1

Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit)
Gate 1 Gate 2 Gate 3 Gate 4 Gate 5
(Queue Length) 1 0 0 0 0

Active Queries:
---------------
Session Query Priority Thread Memory Scans Gate
21 1775aec4 4 175a7e14 736/736 0/1 -
8 17eca028 4 175f3cb0 0/0 0/1 -
23 17f0b8b0 4 1760a320 0/736 0/0 -
24 17e1910c 4 17635224 0/736 0/1 -
24 1813c028 4 17635224 0/736 0/0 -
8 17f3b804 4 175f3cb0 0/736 0/1 -
8 17f8fa14 4 175f3cb0 0/736 0/1 -
24 1801c028 4 17635224 0/736 0/0 -
8 17ff8250 4 175f3cb0 0/736 0/1 -
24 182ac028 4 17635224 0/736 0/0 -
24 182b8c20 4 17635224 0/736 0/1 -
23 17f17044 4 1760a320 0/736 0/0 -
23 17f75b6c 4 1760a320 0/736 0/1 -
24 18668028 4 17635224 0/736 0/0 -
24 188b8444 4 17635224 0/736 0/1 -
23 189c6028 4 1760a320 0/736 0/0 -
8 18bead94 4 175f3cb0 0/736 0/1 -
24 199a8028 4 17635224 0/736 0/0 -
23 1888cfa8 4 1760a320 0/736 0/0 -
23 181a8028 4 1760a320 0/736 0/0 -
23 181d6028 4 1760a320 0/736 0/0 -
23 18212028 4 1760a320 0/736 0/0 -
23 1830c028 4 1760a320 0/736 0/0 -
8 18318250 4 175f3cb0 0/736 0/1 -
24 1838a028 4 17635224 0/736 0/0 -
8 18354a8c 4 175f3cb0 0/736 0/1 -
24 183e6028 4 17635224 0/736 0/0 -
8 183c2d88 0 175f3cb0 0/16 0/1 -
24 18438028 0 17635224 0/16 0/0 -
23 1830e798 0 1760a320 0/16 0/0 -

Ready Queries:
--------------
Session Query Priority Thread Memory Scans Gate
47 183c910c 4 1997f148 0/736 0/1 1

Free Resource Average # Minimum #
-------------- --------------- ---------
Memory 9230.0 +- 6856.1 16
Scans 992.6 +- 4.5 987

Queries Average # Maximum # Total #
-------------- --------------- --------- -------
Active 14.5 +- 9.7 30 57
Ready 1.0 +- 0.0 1 1

Resource/Lock Cycle Prevention count: 0
------------------------------------------------------------------------------------
If more info to solve the issue is required then Please let me know
Thanks in advance

You can use command onstat -g ses <you session> to analyze you session thread status , what status is ?
In your onstat -g mgm you session is stucked by memory gate , I think your database doesn't process you query because you SET PDQPRIORITY and it(db server) proceses each other PDQ queries , in this case you can test by don't SET PDQPRIORITY in your query , it can help your query by unblock from eache other PDQ queries

If you use PDQ with a lot of sessions, you need A LOT of memory.
PDQ divides the memory into blocks: DS_TOTAL_MEMORY/DS_MAX_QUERIES= 256 (quantum)
If the amount of free memory is below the quantum, the next query asking for memory, comes in a wait queue.

SO, Either you allocate more memory, either you don't set your PDQ_PRIORITY to 4, but lower, either you increment the DS_MAX_QUERIES so the quamtum is lower.

Analyse everything in your onstat -g mgm. Check the administration guide from informix for more tips. Chapter 15.
and the Performance guide, chapter 9.

http://www.ibm.com/software/data/informix/pubs

then click documentation on Informix Products.