ISQL Performace Optimization 
Author Message
 ISQL Performace Optimization

I have a rather general question about query optimization and
performance benchmarking with Informix ISQL/4GL 4.1 running
with the online engine.

In Paul Mahler's book, INFORMIX 4GL, he briefly mentions an
indexing scheme called multilevel indexing where one can use
indexes on indexes to gain a little on performance with
respect to lookup. He frames his discussion around B-Trees
which is straightforward on the theory, yet vague on how one
can implement this in ISQL.  I am sitting here with the
reference manual and see no  way syntactically to do this.
Yet, Mahler's reference almost implies that this is implicit
in building any indexing scheme with informix.  Is this
possible in ISQL?

Secondly, my development team has been spending a good deal
of effort lately looking for bottlenecks in our code that,
when optimized, can yield a substantial speedup in time.
Thus far we've been able to squeeze about 12% out of our
payroll accounting package but are looking for more.  We've
gone through and looked for poorly formed joins where the
filter was performed after the join itself; or reworked poorly
designed queries to avoid multiltable joins that were only
necessary a small amount of the time where independent table
selects were applicable; and of course, a great deal of work
has gone into rethinking our indexing strategy.

We've used the SET EXPLAIN option to study the query plan
for most of the processor intensive joins butr have found it
a bit vague in determining  from where some extra time
savings could be found.

Can someone point me in the direction of some literature
discussing Query optimization / database optimization and
tuning with respect to informix products?  Or, perhaps
provide some useful feedback on some known methods of
optimal indexing strategies (multilevel indexing perhaps).

--------------------------------->
Jeff Dirks
Software Engineer/Developer
Fourgen Software Technologies, Inc
Seattle, WA
uunet!4gen!jeffd    OR

<--------------------------------



Fri, 14 Oct 1994 12:50:11 GMT
 ISQL Performace Optimization


Quote:
> I have a rather general question about query optimization and
> performance benchmarking with Informix ISQL/4GL 4.1 running
> with the online engine.

> We've used the SET EXPLAIN option to study the query plan
> for most of the processor intensive joins butr have found it
> a bit vague in determining  from where some extra time
> savings could be found.

We've found 'set explain' to be useful only in the most obvious
of cases, to confirm what indices are being used for simple
queries, etc.  What we would really like is the capability to
INSTRUCT the optimizer of when we 'know' better.

Beyond that, a good knowledge of the schema,
a knowledge of what is being done [after all, if you make a
million reads, it just HAS to take a while ]
and a desire to experiment with real data seem to be the most
useful.  In addition, the INFORMIX Guide to SQL Reference,
Version 4.1.0, has a VERY useful chapter on OPTIMIZATION, although
this is NOT a magic bullet.

An important observation is that multiple table joins
frequently do NOT operate as one might expect, and that by
a simple redesign of the query, some dramatic optimization
might be achieved [see above reference for an example].

Standard disclaimers - and I'm still learning ...

Ihor Kinal
att!cbnewsh!ijk



Sun, 16 Oct 1994 03:01:28 GMT
 ISQL Performace Optimization

I am trying to find what stops the application from running:

OnLine4 Application stops, UNIX continues running happily,
CPU-usage 25%, MEM-usage 50%, cache hit rate R:93.88% W:92.23%

from tbstat when in troubles :
____________________________________________________________

Users
address  flags   pid     user     tty      wait     tout locks nreads   nwrites
300d5e   B-----D 722     informix console  0        0    0     10       44
300dc6   ------D 0       informix console  0        0    0     0        0
300e2e   ------F 730     informix          0        0    0     0        0
300e96   ------- 7433    herbst   ttyc7d2  0        0    1     38       28
300efe   ------- 4859    informix -        0        0    1     24       14
300f66   ------- 4860    informix -        0        0    1     8        4
300fce   ------- 4863    informix -        0        0    1     8        1
301036   S-B---- 15748   herbst   ttyc7d2  30009a   0    1     844      564
30109e   ------- 10077   simhofer ttyc6d3  0        0    1     31       46
301106   S-T-X-- 28497   informix -        b03f88   0    4     314      44
30116e   S------ 28981   informix console  30009a   0    0     0        0
3011d6   S-B---- 10722   simhofer ttyc6d3  30009a   0    1     1849     737
30123e   ------- 26557   kraucher ttyc6d15 0        0    1     45       28
3012a6   ------- 21451   pehn     ttyc6d11 0        0    1     54       59
301376   ------- 14259   karrer   ttyc6d0  0        0    1     45       49
3013de   S-B---- 14292   karrer   ttyc6d0  30009a   0    1     1503     742
301446   ------- 17425   veit     ttyc6d6  0        0    1     57       60
3014ae   S---R-- 27311   bobek    ttyc6d4  30009a   0    1     66       35
301516   S---R-- 27405   bobek    ttyc6d4  30009a   0    1     160      108
30157e   ------- 19183   anton    ttyc6d8  0        0    1     59       55
3015e6   S-T-X-- 28523   anton    ttyc6d8  30009a   0    5     171      49
30171e   S-B---- 25930   veit     ttyc6d6  30009a   0    1     145      54
301786   S------ 29122   informix ttyc6d7  30009a   0    0     0        0
3017ee   S-B---- 26583   kraucher ttyc6d15 30009a   0    1     1071     488
301856   S-B---- 27102   pehn     ttyc6d11 30009a   0    1     26       21
3019f6   S------ 687     eibl     ttyc6d9  30009a   0    0     0        0
301a5e   ------- 23038   informix -        0        0    1     2        2
301ac6   ------- 23029   informix -        0        0    1     4        3
301b2e   ------- 23032   informix -        0        0    1     15       12
301b96   ------- 23035   informix -        0        0    1     1        1
 30 active, 60 total

Latches with lock or user set
name     address  lock wait user    
tblsps   30009a   8000 1    301106  
pt[0]    b03f88   0    1    0      
pt[33]   b05d70   8000 0    301106  

Locks
address  wtlist   owner    lklist   type     tblsnum  rowid    size    
3329f0   0        3015e6   332b50   HDR+X    10007f5  0        0      
332a10   0        300efe   0            S    1000002  202      0      
332a30   0        300f66   0            S    1000002  202      0      
332a50   0        30109e   0            S    1000002  202      0      
332a70   0        300fce   0        HDR+S    1000002  202      0      
332ab0   0        300e96   0            S    1000002  202      0      
332b30   0        3011d6   0            S    1000002  202      0      
332b50   0        3015e6   332fd0   HDR+X    1000001  7f500    0      
332b90   0        3013de   0            S    1000002  202      0      
332c10   0        3015e6   0            S    1000002  202      0      
332c30   0        301376   0            S    1000002  202      0      
332c90   0        3012a6   0            S    1000002  202      0      
332cd0   0        301856   0            S    1000002  202      0      
332df0   0        301b2e   0            S    1000002  202      0      
332e30   0        301ac6   0            S    1000002  202      0      
332f50   0        301036   0            S    1000002  202      0      
332f70   0        30157e   0            S    1000002  202      0      
332f90   0        3015e6   332c10   HDR+X    10007f4  0        0      
332fb0   0        301446   0            S    1000002  202      0      
332fd0   0        3015e6   332f90   HDR+IX   1000001  0        0      
332ff0   0        301106   333310       IX   1000001  0        0      
333090   0        301a5e   0            S    1000002  202      0      
3330f0   0        3017ee   0            S    1000002  202      0      
333190   0        301516   0            S    1000002  202      0      
3331b0   0        301b96   0            S    1000002  202      0      
333210   0        30171e   0            S    1000002  202      0      
333310   0        301106   338db0   HDR+X    10007fe  0        0      
3333b0   0        301106   332ff0   HDR+X    1000001  7fe00    0      
333470   0        30123e   0            S    1000002  202      0      
3334d0   0        3014ae   0            S    1000002  202      0      
338db0   0        301106   0            S    1000002  202      0      
 31 active, 256000 total, 16384 hash buckets
____________________________________________________________

* everyone is waiting for 30009a(tblsps) - latch is held by user 301106
Users
address  flags   pid     user     tty      wait     tout locks nreads   nwrites
301106   S-T-X-- 28497   informix -        b03f88   0    4     314      44
            ^
            |
301106(informix) seems busy.

Q: could this (daemon ???) process block everything ????

Please reply by e-mail - i'll post a summary if there is enough interest

Thanx for any pointers from OnLine-Gurus (GonLine Urus?)

Erwin

--
+--/~~~~\---------------------------------------------------------------+

|  \_  _/      and this is me ->  in-real-life Erwin Eder               |
+----||-----------------------------------------------------------------+



Tue, 08 Nov 1994 06:45:33 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. ISQL vs ISQL/W (server 6.5)

2. Help: ISQL/W 6.5 vs ISQL/W 7.0 and different results

3. ISQL/w and isql dos

4. Float data shows differently in ISQL (cmd line) and ISQL/w

5. Using isql (not ISQL/W)

6. Help with ISQL/w vs ISQL dos

7. ISQL/w vs isql dos

8. ISQL for Windows; ISQL features

9. Access/SQL performaces

10. Performace problems accessing SQL Server from a Windows 95 client

11. Performace on MS SQL Server


 
Powered by phpBB® Forum Software