Slow performance of reading row by row in Grid

[Former Member]

Hi,

I am appreciate if anyone can assist on the following issue that i encountered.

I have the following details:

Form1 + Matrix + 1 Command Button

Form2 + Grid (with datatable)

Assuming Grid data in Form2 contained 500 records, and matrix in Form1 is empty. After i selected two records from Grid in Form2, these two records will be copied to grid in Form1.

After copied, this meant that Form1 's matrix contained of 2 records, and Form2 's grid contained of 498 records. When i triggered the command button in Form1, i will need to remove the 2 records that previously added from datatable in Form2 's grid.

But this will cause the system performs very slow, please refer to below for the sample code.

For iRow = oMatrix.RowCount - 1 To 1 Step -1

oEditTxt1 = oMatrix.Columns.Item("1").Cells.Item(iRow).Specific

If Trim(oEditTxt1.Value) <> "" Then

For jRow = oGrid.Rows.Count - 1 To 0 Step -1

If oGrid.DataTable.Columns.Item(4).Cells.Item(jRow).Value = oEditTxt1.Value Then

oGrid.DataTable.Rows.Remove(jRow)

Exit For

End If

Next jRow

End If

Next iRow

Kindly advise is there any solution or alternative ways i can do to improve the speed?

Thanks in advance.

Regards,

cks

[Former Member · Accepted solution]

Thanks Varma!

Yes, as per my code attached, i am using the same statement to remove the record from the Grid. But as mentioned earlier, this will need to go thru all the records of 500 for two times (2 records from Form1) before to issue this command.

If i have 10 records in Form1, then i will have to do looping of 500 records x 10 = 5000 records to find the record in Form2 to delete.

This is very time consuming... and not effective. I hope you are understand the whole situation.

Thanks again for your help

Regards,

cks

[Former Member · Accepted solution]

Hi,

I also had the nearly the same problem:

I have one form (unlike you with two) with two grids. The user should select one or more rows in the left grid and press a button "Move to right".

In fact the user can move items from one pallet (left grid) to another pallet (right grid).

Because removing and adding rows in a DataTable was very slow but loading data from DB into grid comparatively fast here I've done this:

1. When the form is initialized two strings with a unique temporary tablenames (UserSignat./Date, Milliseconds used in the name) are created (TempTableNames start with "#")

2. When the user selects the pallets for left and right grid the pallet-data is copied from the "real" table into the TempTables:

SELECT Code, Col0, Col1...ColN, 0 AS UpdFlag
INTO #TEMPTABLE_LEFT_001_20081007155
WHERE U_PalletNo='P12345678'
ORDER BY Col1,Col2
--FOR BROWSE

-This is done for the other (right) TempTable (Pallet) too

-0 AS UpdFlag is loaded into the grid as a marker for moved items (set to 1 then)

-SQL comment --FOR BROWSE is to prevent B1-SDK from error messages with this query

-Code Column (unique) is needed for moving action

3. The Grid/DataTable is loaded from the TempTables

4. Everytime the user moves selected items from left to right (Button Move is pressed):

a) collect the Codes of selected rows and make a comma seperated string of 'codes'

b)

INSERT INTO #TEMPTABLE_RIGHT_001_20081007155
(Code, Col0, Col1...ColN, 1 AS UpdFlag)
(SELECT
  Code, 'RightPalletNo', Col1...ColN, 1 AS UpdFlag
FROM
  #TEMPTABLE_LEFT_001_20081007155
WHERE
  Code IN ( CommaSeparatedStringFromAbove)

DELETE FROM #TEMPTABLE_LEFT_001_20081007155
WHERE
  Code IN ( CommaSeparatedStringFromAbove)

5. Reload the grids from TempTables

6. When the user presses the Update-Button the in the real live DB-Table only the "moved" rows are updated with the new pallet

The codes of moved rows can be found in the #TEMPTABLE_RIGHT_001_20081007155 WHERE UpdFlag=1

7. Delete the TempTables in every case of form closing (although they should be deleted automatically when the user logs off B1)

I cannot write all further details - this is what I do here in principle and I'm happy with the performance.

[YatseaLi · Accepted solution]

Hello Roland,

You really know SQL and SDK well (--For Browse). : )

I will suggest that DON'T use this. The query in DI is designed for Data manipulation only, not for Data Definition. Just to protect the data and avoiding accidence.

The solution in this case is also working with xml.

oDataTable.GetAsXml()

'Manipulate with xml

oDataTable.LoadFromXml()...

The bottle neck for the performance problem here is

Too many RPC (Remote Process Call) calls in the big loop. UI is an inter-process COM server. Every UI function call is a RPC, consuming more 3~10 times' time than in process function call (xml function call).

So the solution:

1.Working with xml: Use in-process xml funciton call intead of UI call (RPC)

2.Cache: Cahced some UI object, etc collections. instead retrieve again over again

Kind Regards

-Yatsea

[Former Member · Accepted solution]

release your recordset objects which might make the values to load faster

dim oUserTable as sapbobscom.usertables

Catch ex As Exception

' 'Throw ex

' Finally

' System.Runtime.InteropServices.Marshal.ReleaseComObject(oUserTable<-write ur object)

' oUserTable = Nothing

' GC.WaitForPendingFinalizers()

' GC.Collect()

' End Try

Rgds

Micheal

[Former Member · Accepted solution]

LoadFromXML has been working great for us for a long time, but we have hit an out of memory error in one case where the XML representation of the data is very large. Is there any way around this?

[Former Member]

Thanks all ~

XML method is also quite useful... thanks yatsea... as for the temp table, what i can foresee is the concurent user to access, if you use the select into the temp table, then what will happen if another users to run the same process, will he/she encounters the records being mixed/corrupted?

I have found another solution which can reduce the waiting time for the data transfer from one grid to another (no checking)

Which meant in the item event, i capture the click event for the row that i selected. And this row of grid data i copied to another hidden grid on the same form. When i confirm to copy the selected records, then i will use the hidden grid to perform the data transfer. This will save a lot of times, ie if i only select 10 out of 500 records, then instead of looping 500 times, i will only process 10 records from this hidden grid.

Any comments?

Regards,

cks

[Former Member]

Hi again...

what i can foresee is the concurent user to access, if you use the select into the temp table

Each user gets his own #TEMPTABLE (and it's deleted on form-close). The Temptable-Name is generated by the user-ID and DateTimeMilliseconds of the "Opening-the-form-time". Here the only crash would happen if the same user is logged on twice and opens the form at the same millisecond...

But what is still missing is a B1-Like-Message "Another user has modified the table" when two users are trying to modify the same data (pallets) in parallel and the data of the user-actions are written back to (or more exactly: UPDATEed in) the original main-table.

One solution could be a lock-flag in the main-table which only allows one user at a time loading the same data for manipulation (in case of pallet repacking this should really be done only by one user at a time)

But I will have a look at the XML solutions too. Maybe I change my proceeding...

[Former Member]

Thanks Yatsea... i will give it a try...

[YatseaLi]

Hello,

The solution: Working with XML and Cache. XML funciton call instead of UI function call.

'Cache the collections
Dim oMxCells =  oMatrix.Columns.Item("1").Cells
Dim xmlDoc = new XmlDocument()
Dim rowNodeList As Xml.XmlNodeList
Dim oGridRows = oGrid.DataTable.Rows
Dim oEditValue As String = ""

For iRow = oMatrix.RowCount - 1 To 1 Step -1
'oEditTxt1 = oMatrix.Columns.Item("1").Cells.Item(iRow).Specific
oEditTxt1 = oMxCells.Item(iRow).Specific
oEditValue = oEditTxt1.Value

If Trim(oEditValue) "" Then
       xmlDoc.LoadXml(oGrid.DataTable.GetAsXml())
       'Please specify the target xpath here
        rowNodeList = doc.SelectNodes("//Row")
        Dim jRow As Integer
        'loop with xml instead of UI function
        For jRow = rows.Count - 1 To 0
            If rows.Item(jRow).InnerText.Equals(oEditValue) Then
                oGridRows.Remove(jRow)
                Exit For
            End If
        Next jRow
End If
Next iRow

Kind Regards

-Yatsea

[Former Member]

Hello Yatsea,

I tried using GetasXML on my recordset but keep getting a RPC exception hresult....

Any suggestions?

Thanks,

Akhil

[Former Member]

hi,

when u press the command button

use this statement by catching the rownumbers in the selection of the grid and after copying remove the rows and from data table and reload the data table values into the grid

oGrid.DataTable.Rows.Remove(x)

where x is integer i,e rownum

regards,

varma