1. Sivaprakash BKR
  2. PowerBuilder
  3. Tuesday, 14 May 2024 08:12 AM UTC

Hello,

Using PB 2022 R3 build 3289 With PostgreSQL 15 and SQLite 3

1.  Connecting either PostgreSQL or SQLite based on the option chosen at application open

2.  While taking report, one NVO will be called, with required parameters, which will build required SQL query, set it to a local DS, retrieve it, process it (if required) and send the final output as a blob, using GetFullState

3.  Different dw for PostgreSQL and SQLite, as computed values are returned as characters instead of decimal (or numeric).  Because of this, we first convert the required character values into decimal and then use that columns for places.  

4.  For this particular report, 17 columns are retrieved, and 7 columns are displayed.  And we convert 4 columns from character to decimal for SQLite dw alone.  For Postgres dw, this is straight forward and no conversion.

5.  After receiving the blob from NVO, we use SetFullState(lb_ret) to the required datawindow in the report window.  

6.  Processing Time

Process PostgreSQL SQLite
Generate SQL 0 Sec 0 Sec
SetSQLSelect 0 sec 0 sec
Retrieve 0-1 sec 3-4 sec
(Same query took 0-1 sec, if run against the database)
GetFullState 0 sec 0 sec
SetFullState 0-1 sec 4 sec
PostProcess 0-1 sec 3-4 sec
( to set formats, fonts, text using dw_modify)
( around 60-70 commands run for both postgreSQL and SQLite )
     
Total Time 1-2 sec 10-12 sec 
[ Sometimes goes upto 15 sec ]
     
Converted columns NIL 4 columns [ from character to decimal ]

 

 

 

If we run the generated query against the database [ either from PowerBuilder or some 3rd party GUI tools], the time taken is 0-1 sec. only [ against both PostgreSQL and SQLite].   We created a new datawindow with the generated query and run it, it also took just 0-1 sec only [ for SQLite].   So natural conclusion, for the delay in retrieval of data from SQLite will be due to the conversion of character values into decimal values.  

Next is the time taken for SetFullState command.  It's around 3-4 sec.  Couldn't understand why it's taking so much time.    We tried running other reports against SQLite, with the same process procedure, where the SetFullState took just 0-1 sec [ with 2 converted columns ].  

Also the Postprocess procedure takes another 3-4 sec for SQLite database, whereas the same process took almost nil time.   dw_modify commands to set

openingcredit1.format = "#,##,##,##,##0.00;-#,##,##,##,##0.00;#"
openingcredit_bf.format = "#,##,##,##,##0.00;-#,##,##,##,##0.00;#"
pageno.expression = '1'
currentdate_summary.Visible = 0
rep_companyname.text="N.N. GANESH ENTERPRISES"
rep_title.Font.Height = -12
rep_fromdate.text='Between 01-04-2023 And 31-03-2024'
pagetotalco_t.Text="* Page Total - C/o *"
....
....

I expect the time taken for the same report should be same for both databases, but it's not.  Can anyone guide me to identify the issue for the delay? Or suggestion to improve the performance of this report.

Happiness Always
BKR Sivaprakash

 

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 14 May 2024 17:57 PM UTC
  2. PowerBuilder
  3. # 1

Hi BKR ;

  Thought: If your DB Schema is using larger Char / VarChar text columns, did you ask the DBMS to drop trailing spaces? If not, that would bloat the DWO's buffers  with spaces.

Regards .. Chris

Comment
  1. Sivaprakash BKR
  2. Wednesday, 15 May 2024 06:06 AM UTC
Thanks Chris,

Tried with those settings. No improvement. Instead of returning the blob, tried returning the JSON from the NVO and then importing the JSON in the report datawindow. It's 2 sec faster.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 15 May 2024 15:53 PM UTC
  2. PowerBuilder
  3. # 2

Hi BKR ;

  Wow .. the Blob "should" be faster. That is very weird! As still a guess, it sounds like a strange issue possibly related to the SQLite result set that in turn drives the data content in the DWO buffers.

PS: Watch out though for DWO's using computed columns and expressions. They can be a real CPU killer

Regards .. Chris

 

Comment
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.