Re: Slow while adding rows in Table using TableAddLine

Hello,

Using Windev 22, HFSQL Classic

I add rows to a Table using TableAddLine after executing a query using HExecuteSQLQuery. Query Execution is fast, but adding the resultant rows is slow.

1. I delete all rows in the Table, using TableDeleteAll (takes few milliseconds)
2. Construct and Execute the query using HExecuteSQLQuery (tables few milliseconds)
3. Add rows using HReadFirst(sdQuery), For each sdQuery and TableAddLine (takes 8-10 seconds)

If I rerun the same query, from the same window by clicking a button, without closing the window, the time taken doubles.
1. I delete all rows in the Table, using TableDeleteAll (takes few milliseconds)
2. Construct and Execute the query using HExecuteSQLQuery (tables few milliseconds)
3. Add rows using HReadFirst(sdQuery), For each sdQuery and TableAddLine (takes around 18-25 seconds)

Table got 20 columns.

1. Would like to know why TableAddLine takes double the time while running for the second time?
2. Is there any way to speed up Table Row addtiion for the first time as well as second time?

Happiness Always
BKR Sivaprakash

Hello Sivaprakash

Check the table content option is "By Programming" and that none of the table columns have a link

I also fill memory tables using TableAddLine() and it is very quick but I use a different method

In my code I would create a view of the data with HCreateView() and declare integer variables for nTableRow is int = 1 and nViewCount is int = hnbrec(Viewname) and then use For nTableRow = 1 to nViewCount followed by TableAddLine()


Another option you could try if the table columns match the query fields is FileToMemoryTable()


Regards
Al



Edited 1 time(s). Last edit at 08/25/2022 04:40PM by Al.

Thanks AI.

1. Yes the content option is set to Programming and all the table columns have NO link.
2. Our code to add columns. Adding around 8655 rows to this table.

bQueryResult = HExecuteSQLQuery(sdQuery, a360_DataBaseConnection, hQueryWithoutCorrection, ls_select)
IF bQueryResult THEN
	HReadFirst(sdQuery)
	IF HOut(sdQuery) = False THEN
		FOR EACH sdQuery 
			nRow = TableAddLine(Table_Ledger)
			Table_Ledger[nRow].COL_VoucherDate = sdQuery.voucherdate
                        /* similary set value for another 20 columns */
		END
	END
END	
TableSort(Table_Ledger, "table_ledger.COl_accountname", "table_ledger.COL_VoucherDate", "Table_Ledger.COL_DayBookOrder", "Table_Ledger.COL_VoucherSeries", "table_ledger.COL_VoucherVersion", "table_ledger.COL_VoucherNumber", "table_ledger.COL_SequenceNumber" )

3. Performance report by Windev [ attached as a file: Table Add Line Performance.png ]
It shows 4s 873 ms for TableAddline(), &
..Value 4 s 581 ms
HReadFirst() 4 s 275 ms

What does it mean? Is that taking 4 s 275ms for reading the first record, another 4s for TableAddtion, and another 4s for .. Value? I don't get it. What does ..Value refers to? Any Help?

Happiness Always
BKR Sivaprakash

Update: Running the second time same query takes more time. That image is attached next in Table Add Line Performance II.png.
Update: double clicking .. value gave me this details [ image : Table Add Line Performance III.png ] Could not understand what does Internal Runtime Engine Process means...



Edited 2 time(s). Last edit at 08/26/2022 09:10AM by sivakrith.

Greeting

Try to create a query through the query wizard, then link your table to the query and see if the situation improves.

At the end of the process, set HFreeQuery to free memory

[infosonline.net]

Hello Sivaprakash,

That is a very inefficient way to add lines to the table because you are executing the tableaddline() function 20 times for each row.
Use a single tableaddline for each row by adding the sdquery fields in the same sequence as the table columns with each sdquery value separated by a comma.

FOR EACH sdQuery
TableAddline(Table_Ledger,sdQuery.voucherdate,sdQuery.AccountName,sdQuery.DayBookOrder, and so on naming each field in the query separated by a comma)
END

//You can break the line with ... for better readability
TableAddline(Table_Ledger,sdQuery.voucherdate,sdQuery.AccountName,sdQuery.DayBookOrder,...
and so on naming each field in the query separated by a comma)

You should also try the FileToMemoryTable() function.

Regards
Al



Edited 1 time(s). Last edit at 08/26/2022 09:37AM by Al.

Hello Sivaprakash,

perhaps you can try "SQLTable".

Christoph

Hello,
Thanks All for your reply.

AI: I don't think the code executes TableAddLine 20 times. It executes TableAddLine 1 time and set 20 columns value. Here is the full code.

		FOR EACH sdQuery 
			nRow = TableAddLine(Table_Ledger)
			IF Combo_AltLanguage = "Y" THEN
				Table_Ledger[nRow].COL_AccountName = sdQuery.accountnametamil
				Table_Ledger[nRow].COL_AccountNarration = sdQuery.accountnarrationtamil
			ELSE
				Table_Ledger[nRow].COL_AccountName = sdQuery.accountname
				Table_Ledger[nRow].COL_AccountNarration = sdQuery.accountnarration
			END
			Table_Ledger[nRow].COL_VoucherDate = sdQuery.voucherdate
			Table_Ledger[nRow].COL_DebitCredit = sdQuery.debitcredit = "D" ? "Dr." ELSE "Cr."
			Table_Ledger[nRow].COL_VoucherNumber = sdQuery.vouchernumber
			Table_Ledger[nRow].COL_VouNo = sdQuery.vouno
			Table_Ledger[nRow].COL_CompanyCode = sdQuery.companycode
			Table_Ledger[nRow].COL_BranchCode = sdQuery.branchcode
			Table_Ledger[nRow].COL_AccountPeriodID = sdQuery.accountperiodid
			Table_Ledger[nRow].COL_VoucherSeries = sdQuery.voucherseries
			Table_Ledger[nRow].COL_VoucherVersion = sdQuery.voucherversion
			Table_Ledger[nRow].COL_SequenceNumber = sdQuery.sequencenumber
			Table_Ledger[nRow].COL_AccountNameFK = sdQuery.accountnamefk
			Table_Ledger[nRow].COL_TransactionDetailPK = sdQuery.transactiondetailpk
			Table_Ledger[nRow].COL_TransactionHeaderPK = sdQuery.headerpk
			Table_Ledger[nRow].COL_DayBookOrder = sdQuery.daybookorder
			Table_Ledger[nRow].COL_AccountGroupFK = sdQuery.accountgroupfk
			Table_Ledger[nRow].COL_TotalWeight = sdQuery.weight
			
			IF go_variables.getDebitCredit() = "D" THEN
				Table_Ledger[nRow].COL_DebitAmount = sdQuery.debitamount
				Table_Ledger[nRow].COL_CreditAmount = sdQuery.creditamount
			ELSE
				Table_Ledger[nRow].COL_DebitAmount = sdQuery.creditamount
				Table_Ledger[nRow].COL_CreditAmount = sdQuery.debitamount
			END
                 END

Since values have to be assigned on conditions, adding all column's values in a single row may not be feasible.


infos: Creating a query wizard may not be feasible, because we execute multiple queries and add the resulting rows to the table.


Christoph: Can you elaborate on what you mean by SQLTable? Do you mean query linked to a table, as suggested by infos?

Happiness Always
BKR Sivaprakash

Hello,

Another issue.

TableAddLine returns 1 when we execute the query a second time, without leaving the window. It was working with a delay earlier. What we changed is to put some info statements in between to check the processing. Now we see that TableAddLine always returns one. And the execution never completes [ waited for 4 minutes ]

Happiness Always
BKR Sivaprakash

Greeting

Why can't he?
In the query wizard, you can edit multiple queries and even a union of queries. I would suggest that you do all the filters and conditions in the query itself.
I use this method in my applications and have never had a problem loading data.

[infosonline.net]

Hello Sivaprakash

It is the multiple TableAddLine() commands per row that are killing your performance. They are the source of the very large ..Value numbers in your profile screen shots.

The answer is to create some memory variables and work out your assignment value before putting the data into the table with a single TableAddLine() function per row

If you have absolutely have to change or add values within the loop through the SDQuery, do not use the For Each method. I have written before that I believe that function to be broken in that respect. Use a For x = 1 to HNBrec(SDQuery) as I outlined in my first post.

nTableRow is int
nTableCount is int = hnbrec(sdQuery)
sAccountName is string
aAccNarration is string
sDebitCredit is string
cCreditAmt is currency
cDebitAmt is currency

For nTablerow = 1 to nTableCount
			IF Combo_AltLanguage = "Y" THEN
				sAccountName = sdQuery.accountnametamil
				aAccNarration = sdQuery.accountnarrationtamil
			ELSE
				sAccountName = sdQuery.accountname
				aAccNarration = sdQuery.accountnarration
			End //IF Combo_AltLanguage = "Y" THEN
			
			If sdQuery.debitcredit = "D" 
				sDebitCredit = "Dr."
				cDebitAmt = sdQuery.debitamount
				cCreditAmt = sdQuery.creditamount
			else
				sDebitCredit = "Cr."
				cDebitAmt = sdQuery.creditamount
				cCreditAmt = sdQuery.debitamount
			END  //If sdQuery.debitcredit = "D" 
			
			
			TableAddline(Table_Ledger,sAccountName,aAccNarration,sdQuery.voucherdate,sDebitCredit,sdQuery.vouchernumber,...
			sdQuery.vouno,sdQuery.companycode,branchcode,sdQuery.accountperiodid,sdQuery.voucherseries,sdQuery.voucherversion,...
			sdQuery.sequencenumber,accountnamefk,sdQuery.transactiondetailpk,sdQuery.headerpk,sdQuery.daybookorder,sdQuery.accountgroupfk,...
			sdQuery.weight,cCreditAmt,cDebitAmt)
			Hreadnext(sdQuery)
END  //For nTablerow = 1 to nTableCount

Edited for typos (:

Regards
Al



Edited 6 time(s). Last edit at 08/26/2022 11:21AM by Al.

Hello Sivaprakash

The SQLTable and FileToMemory Table are existing commands to write the entire file to a memory table in one go.
They are in the Help

They would be difficult to use in this instance without a complex query to work out the values you need in the query process

Regards
Al



Edited 1 time(s). Last edit at 08/26/2022 11:14AM by Al.

Why are you complicating it?

[files.fm]

[infosonline.net]

Thanks all,

After incorporating the following changes,

1. Wrote a single query, combining multiple queries using UNION ALL
2. Converted into a single TableAddLine command, as suggested by AI.

the report takes 10 seconds ( 6 sec for query execution and 4 seconds for TableAddLine).. This performance is from second time onwards and it takes around 15 minutes if we run the report for the first time [ after restart ].

Time taken is same (6 sec) when we run the query in WDSQL and also in HExecuteSQLQuery.

Not sure whether retrieving rows directly into a table will reduce this time further.

Happiness Always
BKR Sivaprakash