on 10-07-2008 3:02 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
Thanks Yatsea... i will give it a try...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
11 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.