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
