slow query after upgrade to IDS V9.21 2

[Davidhunnam]

The following query runs VERY slowly after upgarding to IDS version 9.21.
Anyone any ideas how to tune it for faster performance?

SELECT dmy_proc(a.xact_date) DATE, timec_proc(a.xact_time) TIME, a.bid [1,10] CARD, a.employee[1,5] EMPID, a.last_name[1,20], b.first_name[1,10], a.reader_desc, ' current history' source
FROM badge_history a, badge b
WHERE (a.bid=b.bid)
AND (a.reader_desc="093.03.01 IBM Meeting Reader&quot
AND (today -3 <=mdy_proc(xact_date))
ORDER BY 1,2 ASC

 

[schu]

After an upgrade you should rebuild your distributions.
We had the same problem and IBM/informix told us to
drop and recreate the distributions, after that all was fine and dandy.

use
update statistics drop distributions
then perform your
update statistics low/medium/high
etc.

please check manual

 

[vpshriyan]

Hi David,

First, you have to ascertain does these two tables head an index on joined columns (bid column).Check it using SQL for example:
&quot;info indexes for badge_history&quot;
&quot;info indexes for badge&quot;
If not, enforce a primary/foreign key relationship or create unique/duplicate indices.

Your SQL statement also uses stored procedures mdy_proc & timec_proc. You may issue a SQL: e.g.
&quot;set optimization high; update statistics for procedure mdy_proc&quot;
&quot;set optimization high; update statistics for procedure timec_proc&quot;

Create an unique/duplicate index on badge_history as the case may be: e.g.
&quot;create index bh_xact_date on badge_history (xact_date)&quot;

Monitor the results by setting the statistics low: e.g.
&quot;update statistics low for table badge_history&quot;
&quot;update statistics low for table badge&quot;

Benchmark the results using:
&quot;set optimization low; <your SQL>&quot;
&quot;set optimization high; <your SQL>&quot;

Set the explain and analyze the optimizer plan: e.g.
&quot;set explain on; <your SQL>&quot;
Explain file: ./sqexplain.out in UNIX and %INFORMIXDIR%\sqexpln\%USERNAME%.out in Windows.

Regards,
Shriyan

 

[Davidhunnam]

I have had two useful posts in response to my query - thanks, guys!
However, we clearly need more information, as no-one here has heard of &quot;distributions&quot;.
Can anyone tell me what MANUALS I should have, and where can I get them? (online or URL hopefully!).
Then I can do some serious reading and try out these excellent suggestions!

 

[schu]

For the distributions try the SQL reference manual.

Also the administration guides are useful to.

The 7.x manuals can be found at

http://www-3.ibm.com/software/data/informix/pubs/library/

Most of the stuff is the same on 7.3x and 9.2x so you should not have many problems in terms of SQL syntax and commands.

 

[Davidhunnam]

Problem now resolved.
Eventual cause was an inefficient SQL statement
(Using a &quot;LIKE&quot; rather than an &quot;=&quot
But, thanks for the assistance - we learned a lot on the way!