• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Using the for-loop to process data from datawindow become very slow after we migrate from PB7 to PB2021

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved Using the for-loop to process data from datawindow become very slow after we migrate from PB7 to PB2021

  1. Advice
0
Votes
Undo
  1. Pierce Liu
    Pierce Liu
  2. PowerBuilder
  3. Friday, 21 October 2022 07:11 AM UTC

Dear Sir,

We just migrated our system from PB7 to PB2021.
However, we found those windows which using "For-Loop" script in datawindow", the processing time become very slow.
The PB2021 version is about 50 times slower than the PB7 Version.

We use Oracle as database and the only change that we did during the mirgration is to add connection profile's DBParmso we can deal with Nchar/Nvarchar columns.

I put our connction string and for-loop script below.

      // Profile User Connection
      SQLCA.DBMS = ProfileString("LOGIN.INI","Database","DBMS","")
      SQLCA.LogPass = ls_passwd
      SQLCA.ServerName = ProfileString("LOGIN.INI","Database","ServerName","")
      SQLCA.LogId = ls_user
      SQLCA.AutoCommit = False
      SQLCA.DBParm = "StaticBind=0,NCharBind=1"  

For Loop script is like

 for i = 1 to dw_2.rowcount()
  ls_yn  = dw_2.object.w_check[i]
  if ls_yn = 'Y' then
   dw_2.object.efrb_rpt_no[i] = dw_1.object.efrc_rpt_no[dw_1.getrow()]
   dw_2.object.efrb_stn_cd[i] = dw_1.object.efrc_stn_cd[dw_1.getrow()]
   dw_2.object.efrb_sfc_cd[i] = dw_1.object.efrc_sfc_cd[dw_1.getrow()]   
   dw_2.object.efrb_status_code[i] = '2'
  else
   dw_2.object.efrb_rpt_no[i] = ''
   dw_2.object.efrb_stn_cd[i] = ''
   dw_2.object.efrb_sfc_cd[i] = ''
   dw_2.object.efrb_status_code[i] = '1' 
  end if
 next

Do you have any suggestion? Thank you very much and looking forward to your reply.

Keyboard space key not working in powerbulder ide
Notfication about notifications
Accepted Answer
mike S
mike S Accepted Answer Pending Moderation
  1. Friday, 21 October 2022 19:32 PM UTC
  2. PowerBuilder
  3. # Permalink

not an answer on why it is now slower, but what will generally speed up performance is to NOT use "object.columnname" for row by row setting..

for example,   replace:

dw_2.object.efrb_rpt_no[i] = dw_1.object.efrc_rpt_no[dw_1.getrow()]

with:

dw_2.setitem( i, 'efrb_rpt_no',  dw_1.getitemnumber( dw_1.getrow() , 'efrc_rpt_no') 

 

 

OR use dot notation to replace all values at once (not row by row, but all rows at once)

 

Comment
Load more comments
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 21 October 2022 23:16 PM UTC
Hi Chris, Mike and Roland,

Thanks !

Never too old to learn. I misinterpreted the news on the dot notation when it came out years ago.

:)
  1. Helpful
John Fauss
  1. John Fauss
  2. Saturday, 22 October 2022 00:47 AM UTC
Miguel, every day that I don't learn something is a wasted day. ;-)

When DW dot-notation was introduced (PB5??? That was a monumental release!), I was told that dot-notation incurs more overhead because of late binding... The compiler defers parsing and binding of everything past the "dw.Object." portion of the expression until execution time, whereas the GetItemXXXXX and SetItem functions can be fully compiled into psuedo-code or machine code and therefore execute faster. That may no longer be the case, but that's how I was taught.
  1. Helpful 1
Pierce Liu
  1. Pierce Liu
  2. Tuesday, 1 November 2022 07:33 AM UTC
Thank you very much, We've found problem is due to the NCharBind parameter in DB connection string .
  1. Helpful
There are no comments made yet.
Responses (5)
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Friday, 21 October 2022 07:47 AM UTC
  2. PowerBuilder
  3. # 1

Hi.

Have you tried to add a pb.ini with Accessibility=0 in datawindow section?

[Data Window]
Accessibility=0

If not, can you try and see if there is any improvement?

Add it to powerbuilder's pb.ini.

Also add a pb.ini with those two lines where you have your pbls. If it does improve speed, upon deployment you will need to deploy that file in the same file where your exe is located.

Andreas.

Comment
Pierce Liu
  1. Pierce Liu
  2. Tuesday, 1 November 2022 07:35 AM UTC
Thank you very much, We've found problem is due to the NCharBind parameter in DB connection string .
  1. Helpful
There are no comments made yet.
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Friday, 21 October 2022 08:05 AM UTC
  2. PowerBuilder
  3. # 2

Some general optimization:

  • Do not call RowCount in FOR next statement. PB evaluates it for each item. Call rowcount before the loop and store the value in a variable and use this in the FOR statement.
  • Do not call GetRow for each item. Call it outside the loop and store the value in a variable.
Comment
Load more comments
mike S
  1. mike S
  2. Friday, 21 October 2022 19:28 PM UTC
>>Do not call RowCount in FOR next statement.

i believe this was true for early versions of PB, but performance for this was fixed at some point



  1. Helpful 2
René Ullrich
  1. René Ullrich
  2. Monday, 24 October 2022 10:32 AM UTC
I've tested RowCount in a loop vs. outside a loop with PB 2019R3. There is still a small difference but only significant with a lot of rows.

With 1 million rows it is on my computer 47 ms vs. 438 ms. ;-)
  1. Helpful 1
Pierce Liu
  1. Pierce Liu
  2. Tuesday, 1 November 2022 07:34 AM UTC
Thank you very much, We've found problem is due to the NCharBind parameter in DB connection string .
  1. Helpful
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Friday, 21 October 2022 19:06 PM UTC
  2. PowerBuilder
  3. # 3

Hi Pierce,

I suggest you implement either what Rene or Miguel suggested to improve your code and also test with PB 2022.  Customers feedback to us that 2022 is faster than 2021.

The other thing is when upgrading from PB 7 to 2021/2022 you need to make some adjustments to your code, such as unicode conversion.  https://docs.appeon.com/pb/upgrading_pb_apps/UpgradingPowerBuilderApplications1UpgradingfromanyPowerBuilderversion.html

Best regards,
Armeen

Comment
Pierce Liu
  1. Pierce Liu
  2. Tuesday, 1 November 2022 07:34 AM UTC
Thank you very much, We've found problem is due to the NCharBind parameter in DB connection string .
  1. Helpful
There are no comments made yet.
Pierce Liu
Pierce Liu Accepted Answer Pending Moderation
  1. Tuesday, 1 November 2022 07:28 AM UTC
  2. PowerBuilder
  3. # 4

Thank you guys so much for your suggestions and sorry for replying late here.

We have found out the key of this problem is that we add 1 parameters ( SQLCA.DBParm =  "StaticBind=0,NCharBind=1" ) when connecting to the Oracle Database for dealing the Nchar/Nvarchar columns.(Our language is Traditionl Chinese). So when we use sql statement(insert/update) to deal with hug-amount  data  in for-loop , we will create a new connecting SQLCA1 which has no NCharBind parameter and add "Using SQLCA1;" in the end of each sql statement and disconnect SQLCA1 after loop . The processing speed returns to normal. 

Now I got a new problem . Is it possible to change Datawindow's default connection from SQLCA to SQLCA1? if yes , how?

Thank you very much again.

 

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Tuesday, 1 November 2022 07:35 AM UTC
dw_1.SetTransObject(sqlca1)

?

  1. Helpful 2
Pierce Liu
  1. Pierce Liu
  2. Tuesday, 1 November 2022 07:49 AM UTC
Yes ... Thanks a lot.
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Tuesday, 1 November 2022 18:05 PM UTC
Thanks for sharing the solution!
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.