embeded SQL tooooo slow

[thelearner]

Hi,

I have very simple SQL embeded pgm, just read/print for record that has date >= specified date. The file has almost 800,000 records. The pgm took almost 1 min. to run. I used the same SELECT statement and run in STRSQL. It took only 2-3 sec. What's wrong with my program? Can any one help?
Thanks.

FQSYSPRT O F 132 PRINTER OFLIND(*INOF)

D MAIN PR EXTPGM('VS040')
D 5
D 1

D MAIN PI
D ZPENT 5
D ZRPTYP 1

*
D TKREC E DS EXTNAME(TKMAST)
D CISPFX S 6
D PHEADR S 20
*-----------------------------------------------
* M A I N
*-----------------------------------------------
C/EXEC SQL
C+ SET OPTION
C+ COMMIT = *NONE,
C+ CLOSQLCSR = *ENDMOD
C/END-EXEC

C/EXEC SQL
C+ DECLARE MYCURSOR SCROLL CURSOR FOR
C+ SELECT *
C+ FROM TKMAST
C+ WHERE TKOPNC * 1000000 + TKOPNY * 10000 + TKOPNM * 100 +
C+ TKOPND >= 20040308 AND
C+ TKPENT = '2704 ' AND TKETCD <> '00'
C+ FOR FETCH ONLY
C/END-EXEC

C/EXEC SQL
C+ OPEN MYCURSOR
C/END-EXEC

/FREE
DOW SQLCOD = 0;

/END-FREE
C/EXEC SQL
C+ FETCH FROM MYCURSOR
C+ INTO :TKREC
C/END-EXEC

/FREE

IF SQLCOD = 100; // end-of-data
LEAVE;
ENDIF;

CISPFX = TKBRCD + TKDVCD + %EDITC(TKFLYY:'X');
EXCEPT #DETL;

ENDDO;

EXCEPT #EOF;

*INLR = '1';
//-------------------
/END-FREE

 

[iSeriesCodePoet]

Check to make sure you aren't buiding access paths. Check the FAQs, I have a FAQ on how to speed up your SQL statments.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

Hi,

Just tried it, did not help at all. Is it possible that I missed some basic thing. I'm very very new in SQL. For this pgm what I did was create the pgm in SQLRPGLE type and compile with opt. 14 without changing any parameter.
By the way, do you think what is the approximate processing time for the file this size and the pgm just process 1 file and print?

Thanks

 

[iSeriesCodePoet]

I have seen identical run times between STRSQL and RPG SQL. Make sure you log out then back in and see if you get the same run times.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[Mercury2]

Hi Rapeek,

Do you run the SQL embedded program in the same subsystem as STRSQL runs ? It's maybe be the explanation of poor performance.
Also try of using VisualExplain that comes with OpsNav/iSeriesNav -- never used it don't ask me how it works. I heard it seems a good tool to analyze SQL performance issue.
HTH -- Philippe

 

[thelearner]

Hi,

Yes, I tried logged off and ran it. It took about the same amount of time.
And yes, Talkturkey, both pgm and STRSQL are run under same subsystem.

 

[Mercury2]

To solve this isssue Kent Milligan SQL Guru highly recommends to attend the iSerise SQL Performance Workshop - this is the best way to understand this topic. You can find parts of the course online at:

http://www-1.ibm.com/servers/enable/site/education/abstracts/sqlcode_abs.html

Just my 2 cents.

 

[arrow483]

Try taking out the except to the printer and see what is does to performance. I know that's why you are reading the file, but at least it can show you the impact of QSYSPRT.

 

[thelearner]

Thank, Talkturkey, I will take a look.
And for your suggestion, arrow483, I took out the except, even the printfile. Still slow.

Thanks

 

[Mercury2]

Hi rapeek,

Actually there is nothing wrong with your program.
When the SELECT statement runs under STRSQL it doesn't use neither a cursor nor a FETCH stm whilst you do so in a SQL embedded program to access each selected record from your file TKMAST. And I'm pretty sure that the combination cursor-fetch causes some performance degradation.
Give a try by removing the "SCROLL" option and using the PREPARE stm first, it should noticeably improve performance.
Pls, let us know.
Philippe

 

[thelearner]

Hi Talkturkey,

PREPARE? Like in dynamic SQL? I read that runtime overhead is greater than static SQL. I tried it anyway! Still took about the same amount of time, about 1 min. This is how I did it.

D DSTRING S 200 INZ('SELECT * FROM @TKL040 -
D WHERE TKOPNC * 1000000 + TKOPNY -
D * 10000 + TKOPNM * 100 + TKOPND -
D >= ? AND TKPENT = ? AND TKETCD -
D <> ?')
D FMDATE S 8S 0 INZ(20040311)
D ZPENT S 5A INZ('2704 ')
D ZETCD S 2A INZ('00')
C/EXEC SQL
C+ SET OPTION
C+ COMMIT = *NONE,
C+ CLOSQLCSR = *ENDMOD
C/END-EXEC

C/EXEC SQL
C+ PREPARE S2 FROM STRING
C/END-EXEC

C/EXEC SQL
C+ DECLARE MYCURSOR CURSOR FOR S2
C/END-EXEC
C/EXEC SQL
C+ OPEN MYCURSOR USING :FMDATE, :ZPENT, :ZETCD
C/END-EXEC

/FREE

DOW SQLCOD = 0;

/END-FREE

C/EXEC SQL
C+ FETCH MYCURSOR
C+ INTO :TKREC
C/END-EXEC

/FREE
IF SQLCOD = 100; // end-of-data
LEAVE;
ENDIF;

CISPFX = TKBRCD + TKDVCD + %EDITC(TKFLYY:'X');
EXCEPT #DETL;

ENDDO;

EXCEPT #EOF;

*INLR = '1';

Thanks

 

[Mercury2]

Hi again Rapeek,

Give it a try with varying kwd and without parm markers, i.e. :

 D DSTRING         S            200    INZ('SELECT * FROM @TKL040 -     
 D                                     WHERE TKOPNC * 1000000 + TKOPNY -
 D                                     * 10000 + TKOPNM * 100 + TKOPND -
 D                                     >= ')  
                         
 [COLOR=red][b]D STRING          S            200    VARYING[/b][/color]
                                  
 D FMDATE          S              8S 0 INZ(20040311)                    
 D ZPENT           S              5A   INZ('2704 ')                     
 D ZETCD           S              2A   INZ('00')      
                  
 C/EXEC SQL                             
 C+   SET OPTION                        
 C+       COMMIT     = *NONE,           
 C+       CLOSQLCSR  = *ENDMOD          
 C/END-EXEC                             
  
  [COLOR=red][b]/FREE  
     STRING = %TRIM(DSTRING)    + FMDATE +
              ' AND TKPENT  = ' + ZPENT  +
              ' AND TKETCD <> ' + ZETCD; 
  /END-FREE[/b][/color red]       
                                               
 C/EXEC SQL                             
 C+  PREPARE S2 FROM [COLOR=red][b]:STRING[/b][/color red]           
 C/END-EXEC                             
                                        
 C/EXEC SQL                             
 C+  DECLARE ...
   
... (same) ...

That will work but not sure to improve performance.
You could post your problem on the iSeries Guru's forum at

http://www.iseriesnetwork.com/isnforums/,

some people there are top-notch SQL Experts.
Philippe

 

[thelearner]

Hi Talkturkey,

Thank for your suggestion. I posted my question at iseries. Will let you know if they have a solution for me.
Your code for DSTRING looks nicer than mine but it didn't work. I think because it treated TKPENT and TKETCD as the numeric because when I did the debug it looked like this

TKPENT = 2704 AND TKETCD <> 00

no quote around 2704 and 00

Thanks.

 

[thelearner]

Hi Talkturkey,

I added quote around it. It worked now but no change on performance.
D @Q C CONST(X'7D')

' AND TKPENT = ' + @Q + ZPENT + @Q +
' AND TKETCD <> ' + @Q + ZETCD + @Q;

 

[Mercury2]

Hi Rapeek,

Look at iSeries they have now suggestions for you I think.