|
Dave, I really like SQL dspf pgms. Initial loading does take a bit longer than a page-at-a-time sfl pgm with native I/O. But I've pared that down by using the optimize clause, and making it double the size of the sfl. I also look at the debug info to see if there are any indexes that need to be created. Also, in Ops Nav under Database there is SQL Performance Monitor which is helpful. (V5R1 atleast.) I often don't allow copy data (unless required to because of joins) because I want changes made to the files noticable right away in the sfl without having to close/open the cursor. I don't usually use parameter markers because it makes debugging more difficult, but maybe I should try it on sfls which I can't get to speed up otherwise. One thing I did early on that was a performance killer: In the loadsfl subroutine, I had a call to another procedure which did a select on another file. By getting rid of that call by doing a join instead removed the performance hit. This is definately something that you get better at with practice! Phil > -----Original Message----- > From: rpg400-l-admin@midrange.com [mailto:rpg400-l-admin@midrange.com]On > Behalf Of Smith, Mike > Sent: Wednesday, May 22, 2002 2:13 PM > To: 'rpg400-l@midrange.com' > Subject: RE: SQLRPGLE - Load-Up question... > > > No the 'for read only' doesn't really do anything other than > 'Document' the > fact that I'm not updating this file. > > By default it is 'Read only' > > -----Original Message----- > From: dmosley@dancik.com [mailto:dmosley@dancik.com] > Sent: Wednesday, May 22, 2002 2:11 PM > To: rpg400-l@midrange.com > Subject: RE: SQLRPGLE - Load-Up question... > > > > Thanks Mike, as soon as I sent my last reply to you I figured > out what you > were talking about. I doesn't seem to take much away initially, but after > the first one, watch out... > > ? Does the "FOR READ ONLY" do anything??? > > thanks > dav > > David L. Mosley, Jr. > Systems Analyst > 2000 CentreGreen Way > Suite 250 > Cary, NC 27513 > > > > "Smith, Mike" > <Mike_Smith@RGCReso To: > "'rpg400-l@midrange.com'" <rpg400-l@midrange.com> > urces.com> cc: > Sent by: Subject: RE: SQLRPGLE - > Load-Up question... > rpg400-l-admin@midr > ange.com > > > 05/22/02 01:56 PM > Please respond to > rpg400-l > > > > > > > Here are some snippets, hopefully I have clipped enough to make sense to > you. > > IF #POSTO > *BLANKS > EVAL #SELCT = %REPLACE(#WHERE: > SQLARY(1): > %SCAN('#':SQLARY(1)): > 1) > ELSE > EVAL #SELCT = %REPLACE(' ': > SQLARY(1): > %SCAN('#':SQLARY(1)): > 1) > ENDIF > EVAL #SELCT2 = %REPLACE(#SORTFLD: > #SELCT: > %SCAN('@':#SELCT)) > EVAL #SELCT2 = %trim(#SELCT2) + > ' FOR READ ONLY' + > ' OPTIMIZE FOR 12 ROWS' > > C/EXEC SQL > C+ PREPARE #SEL FROM :#SELCT2 > C/END-EXEC > > C/EXEC SQL > + Declare CTLCURS SCROLL Cursor For #SEL > C/END-EXEC > > *OPEN THE CURSOR > C/EXEC SQL > + Open CTLCURS > C/END-EXEC > > ************************************************ > C $NEXT BEGSR > C/EXEC SQL > + Fetch NEXT FROM CTLCURS Into :QABADDS > C/END-EXEC > C ENDSR > > > > SQLARY is the generic select statement that is modified as necessary based > on the particular needs > > > > -----Original Message----- > From: dmosley@dancik.com [mailto:dmosley@dancik.com] > Sent: Wednesday, May 22, 2002 1:43 PM > To: rpg400-l@midrange.com > Subject: RE: SQLRPGLE - Load-Up question... > > > > Where exactly are you talking about me adding the 'optimize' status. I'm > not using a SELECT statement, I'm using a PREPARE and FETCH combo... > > David L. Mosley, Jr. > Systems Analyst > 2000 CentreGreen Way > Suite 250 > Cary, NC 27513 > > > > "Smith, Mike" > <Mike_Smith@RGCReso To: > "'rpg400-l@midrange.com'" <rpg400-l@midrange.com> > urces.com> cc: > Sent by: Subject: RE: SQLRPGLE - > Load-Up question... > rpg400-l-admin@midr > ange.com > > > 05/22/02 01:20 PM > Please respond to > rpg400-l > > > > > > > What Buck says makes a lot of sense and should be heeded, however, I would > still consider placing this on your select. It won't make much difference > if the file is small, but it will make a Huge difference when the file is > large. > > Just append the 'optimize' statement to the end of your sql statement. I > use 'X' as the subfile page size. > > > > -----Original Message----- > From: dmosley@dancik.com [mailto:dmosley@dancik.com] > Sent: Wednesday, May 22, 2002 1:17 PM > To: rpg400-l@midrange.com > Subject: RE: SQLRPGLE - Load-Up question... > > > > No, I'm not too familiar with using the "Optimize for x rows". I don't > think that the delay begins within the first SQL command. (I think) > > I do compile them with the parameter "Allow copy of data" ALWCPYDTA(*YES), > rather than *OPTIMIZE. I had read a while back that this would help, by > retrieving 4% of the results. (something like that). It > definitely cuts the > time down. > > David L. Mosley, Jr. > Systems Analyst > 2000 CentreGreen Way > Suite 250 > Cary, NC 27513 > > > > "Smith, Mike" > <Mike_Smith@RGCReso To: > "'rpg400-l@midrange.com'" <rpg400-l@midrange.com> > urces.com> cc: > Sent by: Subject: RE: SQLRPGLE - > Load-Up question... > rpg400-l-admin@midr > ange.com > > > 05/22/02 12:58 PM > Please respond to > rpg400-l > > > > > > > David > Do you have an 'Optimize for x rows' on your select statement? > > -----Original Message----- > From: dmosley@dancik.com [mailto:dmosley@dancik.com] > Sent: Wednesday, May 22, 2002 12:36 PM > To: rpg400-l@midrange.com > Subject: SQLRPGLE - Load-Up question... > > > > I've created several SQLRPGLE service programs that are used for display > files into subfiles. No, problem. However, the very first time of entering > the program, there is a delay of several seconds more than a normal RPGLE > program would have. I'm guessing that it is the SQL-stuff loading up. (Not > sure of the proper terminology, don't kill me). > > Is there a way to shrink this time down. After the first entry, > is ok. SQL > activation groups are all still active, and the program flies right > into action. It's just that first time, that sucks. Also, these same > service programs are used for loading up HTML, and the load-up > time on that > is even longer. It's hard to sell the idea of SQLRPGLE objects, when right > off the bat, the > user experiences a delay. First impressions are hard to get past.. > > thanks > dav > > > David L. Mosley, Jr. > Systems Analyst > 2000 CentreGreen Way > Suite 250 > Cary, NC 27513 > > _______________________________________________ > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@midrange.com To subscribe, > unsubscribe, or > change list options, > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l > or email: RPG400-L-request@midrange.com > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > _______________________________________________ > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@midrange.com To subscribe, > unsubscribe, or > change list options, > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l > or email: RPG400-L-request@midrange.com > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > > > > > _______________________________________________ > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@midrange.com To subscribe, > unsubscribe, or > change list options, > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l > or email: RPG400-L-request@midrange.com > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > _______________________________________________ > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@midrange.com To subscribe, > unsubscribe, or > change list options, > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l > or email: RPG400-L-request@midrange.com > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > > > > > _______________________________________________ > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@midrange.com To subscribe, > unsubscribe, or > change list options, > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l > or email: RPG400-L-request@midrange.com > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > _______________________________________________ > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@midrange.com To subscribe, > unsubscribe, or > change list options, > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l > or email: RPG400-L-request@midrange.com > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > > > > > _______________________________________________ > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@midrange.com To subscribe, > unsubscribe, or > change list options, > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l > or email: RPG400-L-request@midrange.com > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > _______________________________________________ > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@midrange.com > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l > or email: RPG400-L-request@midrange.com > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > >
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
