Home Forums WinForms controls Xceed Grid for WinForms Detail grid data loading time?

Viewing 12 posts - 1 through 12 (of 12 total)
  • Author
    Posts
  • User (Old forums)
    Member
    Post count: 23064
    #16376 |

    I have a dataset with three tables.

    Table1 is shown in the main grid.
    There is a Master/Detail relationship from Table1 to Table2 and this is added as a detail grid.
    There is also a Master/Detail relationship from Table1 to Table3 and this is added as a second detail grid.

                    Dim detail As New Xceed.Grid.DetailGrid()
                    Dim TitleRow As New Xceed.Grid.TextRow
                    TitleRow.Text = “Best Bin”
                    detail.Title = “Best Bin”
                    detail.HeaderRows.Add(TitleRow)
                    detail.HeaderRows.Add(New Xceed.Grid.ColumnManagerRow())
                    detail.ApplyStyleSheet(Xceed.Grid.StyleSheet.Xceed)
                    detail.Collapsed = True
                    detail.ReportStyle.Visible = False

                    detail.DataSource = Nothing
                    detail.DataMember = “MasterBin_DetailBin”

                    detail.UpdateDetailGrids()
                    xgrdBinTest.DetailGridTemplates.Add(detail)

    From the first line to after the call to DetailGridTemplates.Add(detail) it takes about 7 seconds.  BUT…….

    Immediately following the above code I have the following to add the second detail grid.

                    Dim SubDetail As New Xceed.Grid.DetailGrid()
                    Dim SubTitleRow As New Xceed.Grid.TextRow
                    SubDetail.Title = “Alternate Bin”
                    SubTitleRow.Text = “Alternate Bin”
                    SubDetail.HeaderRows.Add(SubTitleRow)
                    SubDetail.HeaderRows.Add(New Xceed.Grid.ColumnManagerRow())
                    SubDetail.ApplyStyleSheet(Xceed.Grid.StyleSheet.Xceed)
                    SubDetail.Collapsed = True
                    SubDetail.ReportStyle.Visible = False

                    SubDetail.DataSource = Nothing
                    SubDetail.DataMember = “DetailBin_SubDetailBin”

                    SubDetail.UpdateDetailGrids()
                    xgrdBinTest.DetailGridTemplates.Add(SubDetail)

    The  timing for this block is 0.0.

     

    Why is it that the first drtail grid takes so long while the second takes almost no time at all? 

    The first detail table contains only 9,000 rows and the second table contains only 30,000 rows TOTAL, not for each detail, but total rows.  These will vary for each of the main grid rows.

    I am puzzled as to why the setup for the second detail grid is so much faster than the first.

     

     Brian

    Imported from legacy forums. Posted by Brian (had 1727 views)

    User (Old forums)
    Member
    Post count: 23064

    Well we’re not sure what could explain the 0.0 score for the second DetailGrid, but the 7 sec for the first one is problematic.  Is the code inside a GridControl.BeginInit and EndInit block?  If so, and if the grid templates are set to be collapsed, no detail rows are actually created, so the loading should not be longer than simply binding the grid to the master source.

    The following should not be the problem, but just in case, you can remove the UpdateDetailGrids calls on the DetailGrids.  The only one you really need is the UpdateDetailGrids on the GridControl as such, and only once all DetailGridTemplates have been added.

    i.e.:

    gridControl.BeginInit();

    gridControl.SetDataBindings( mainDataSource, “mainDataMember” )

    DetailGrid detailGridTemplate1 = new DetailGrid();
    detailGridTemplate1.SetDataBindings( mainDataSource, “mainDataMemberName.MasterDetail1Relationship” );
    gridControl.DetailGridTemplates.Add( detailGridTemplate1 );

    DetailGrid detailGridTemplate2 = new DetailGrid();
    detailGridTemplate2.SetDataBindings( mainDataSource, “mainDataMemberName.MasterDetail2Relationship” );
    gridControl.DetailGridTemplates.Add( detailGridTemplate2 );

    gridControl.UpdateDetailGrids();

    gridControl.EndInit();

     

    Imported from legacy forums. Posted by André (had 666 views)

    User (Old forums)
    Member
    Post count: 23064

    I removed the call to UpdateDetailGrids.

    I have now isolated the fact that that including the two detail grids is causing a long delay after the call to EndInit on the master grid.

     If I remove the two detail grids the time to execute EndInit is only a few milliseconds.  If I include the two detail grids, (with the initial state collapsed) using detail.Collapsed = True property the time to show the grid is about 11.5 seconds!!.

    Here is the code for setting up the grid with the detail grids. SetxColProperties is a simple function that just sets some of the common properties list display index, visible,report with and formatting if they are passed in and has no effect on the total time. (I removed it once already with no noticeable impact on the 11.5 seconds.)

     Total row count for the master is about 175 rows and the detail grids contain anywhere from 5 rows each to 1100 rows each.  The total row count for all the detail grids combined is only 32,000 rows.

                LocalGrid.DetailGridTemplates.Clear()
                LocalGrid.BeginInit()
                LocalGrid.RowSelectorPane.Visible = False

                LocalGrid.DataSource = LocalData.ReturnDataset
                LocalGrid.DataMember = LocalData.ReturnDataset.Tables(0).TableName

                LocalGrid.GroupTemplates.Clear()

                LocalGrid.FixedHeaderRows.Clear()
                LocalGrid.FixedHeaderRows.Add(New Xceed.Grid.GroupByRow())
                LocalGrid.FixedHeaderRows(0).Visible = Me.ctxBinConditionsShowGroupManagerBar.Checked

                LocalGrid.FixedHeaderRows.Add(New Xceed.Grid.ColumnManagerRow())

                ‘LocalGrid.ApplyStyleSheet(Xceed.Grid.StyleSheet.TheLargeBlues)
                ‘With this option if you change the sorting or grouping of any of the detail grids it will apply that to all of the detail grids
                LocalGrid.SynchronizeDetailGrids = True
     

                ‘Detail Grid Code-Create the detail grid on if the relationship was created.
                If LocalData.ReturnDataset.Relations.Contains(“MasterBin_DetailBin”) = True Then
                    Dim detail As New Xceed.Grid.DetailGrid()
                    Dim TitleRow As New Xceed.Grid.TextRow

                    TitleRow.Text = “Best Wafers”
                    detail.Title = “Best Wafers”
                    detail.HeaderRows.Add(TitleRow)
                    detail.HeaderRows.Add(New Xceed.Grid.ColumnManagerRow())
                    detail.ApplyStyleSheet(Xceed.Grid.StyleSheet.Xceed)
                    detail.Collapsed = True
                    detail.ReportStyle.Visible = False

                    Dim xDetailColumn As Xceed.Grid.DataBoundColumn
                    Dim ColName As String = “”
                    detail.AutoCreateColumns = False
                    For Each oCol As DataColumn In LocalData.ReturnDataset.Tables(1).Columns
                        ColName = oCol.ColumnName
                        If (ColName <> “Diameter”) AndAlso _
                            (ColName <> “Substrate”) AndAlso _
                            (ColName <> “Material_Category”) AndAlso _
                            (ColName <> “Bin”) AndAlso _
                            (ColName <> “Priority”) Then
                            xDetailColumn = New Xceed.Grid.DataBoundColumn(oCol.ColumnName)
                            detail.Columns.Add(xDetailColumn)
                        End If
                    Next
                    SetxColProperties(detail.Columns(“LotID”), 0, 104)
                    SetxColProperties(detail.Columns(“RunID”), 1, 104)
                    SetxColProperties(detail.Columns(“YD_PercentVf”), 2, 104, “P”)
                    SetxColProperties(detail.Columns(“PassingYield”), 3, 104, “P”)
                    SetxColProperties(detail.Columns(“PreferredYield”), 4, 104, “P”)
                    SetxColProperties(detail.Columns(“ReservationYield”), 5, 104, “P”)
                    SetxColProperties(detail.Columns(“PriorityFactor”), 6, 104)
                    SetxColProperties(detail.Columns(“PAvg_Wave_Percent”), 7, 104, “P”)
                    SetxColProperties(detail.Columns(“PAvg_Int_Percent”), 8, 104, “P”)
                    SetxColProperties(detail.Columns(“MaterialName”), 9, 104)
                    SetxColProperties(detail.Columns(“HoldingLetter”), 10, 104)
                    SetxColProperties(detail.Columns(“HoldingNumber”), 11, 104)
                    detail.Columns(“TestingBin”).Visible = False
                    AddHandler detail.CollapsedChanged, AddressOf BinTestDetailColapsedChanged
                    AddHandler detail.AddingDataRow, AddressOf BinTestingRowHighlight

                    detail.DataSource = Nothing
                    detail.DataMember = “MasterBin_DetailBin”

                    LocalGrid.DetailGridTemplates.Add(detail)
                End If

                 If LocalData.ReturnDataset.Relations.Contains(“DetailBin_SubDetailBin”) = True Then
                    Dim SubDetail As New Xceed.Grid.DetailGrid()
                    Dim SubTitleRow As New Xceed.Grid.TextRow
                    SubDetail.Title = “Alternate Wafers”
                    SubTitleRow.Text = “Alternate Wafers”
                    SubDetail.HeaderRows.Add(SubTitleRow)
                    SubDetail.HeaderRows.Add(New Xceed.Grid.ColumnManagerRow())
                    SubDetail.ApplyStyleSheet(Xceed.Grid.StyleSheet.Xceed)
                    SubDetail.Collapsed = True
                    SubDetail.ReportStyle.Visible = False

                    Dim xColumn As Xceed.Grid.DataBoundColumn
                    Dim ColName As String = “”

                    SubDetail.AutoCreateColumns = False
                    For Each oCol As DataColumn In LocalData.ReturnDataset.Tables(2).Columns
                        ColName = oCol.ColumnName
                        If (ColName <> “Diameter”) AndAlso _
                            (ColName <> “Substrate”) AndAlso _
                            (ColName <> “Material_Category”) AndAlso _
                            (ColName <> “Bin”) AndAlso _
                            (ColName <> “Priority”) Then

                            xColumn = New Xceed.Grid.DataBoundColumn(oCol.ColumnName)
                            SubDetail.Columns.Add(xColumn)
                        End If
                    Next
                    SetxColProperties(SubDetail.Columns(“LotID”), 0, 104)
                    SetxColProperties(SubDetail.Columns(“RunID”), 1, 104)
                    SetxColProperties(SubDetail.Columns(“YD_PercentVf”), 2, 104, “P”)
                    SetxColProperties(SubDetail.Columns(“PassingYield”), 3, 104, “P”)
                    SetxColProperties(SubDetail.Columns(“PreferredYield”), 4, 104, “P”)
                    SetxColProperties(SubDetail.Columns(“ReservationYield”), 5, 104, “P”)
                    SetxColProperties(SubDetail.Columns(“PriorityFactor”), 6, 104)
                    SetxColProperties(SubDetail.Columns(“MaterialName”), 7, 104)
                    SetxColProperties(SubDetail.Columns(“HoldingLetter”), 8, 104)
                    SetxColProperties(SubDetail.Columns(“HoldingNumber”), 9, 104)
                    SubDetail.Columns(“TestingBin”).Visible = False

                    AddHandler SubDetail.CollapsedChanged, AddressOf BinTestDetailColapsedChanged
                    AddHandler SubDetail.AddingDataRow, AddressOf BinTestingRowHighlight

                    SubDetail.DataSource = Nothing
                    SubDetail.DataMember = “DetailBin_SubDetailBin”
                    LocalGrid.DetailGridTemplates.Add(SubDetail)
                End If

                 LocalGrid.Columns(“TestingBin”).Visible = False
                SetxColProperties(LocalGrid.Columns(“Diameter”), 0, 50, “”, False, Xceed.Grid.SortDirection.None, “Diameter”, False)
                AdjustXceedColumnWidth(LocalGrid, “Diameter”, “Diameter”)

                LocalGrid.Columns(“Substrate”).VisibleIndex = 1
                LocalGrid.Columns(“Substrate”).Title = “Substrate”
                LocalGrid.Columns(“Substrate”).ReportStyle.Width = 50

                LocalGrid.Columns(“Material_Category”).VisibleIndex = 2
                LocalGrid.Columns(“Material_Category”).Title = “Material”
                LocalGrid.Columns(“Material_Category”).ReportStyle.Width = 140

                LocalGrid.Columns(“Priority”).VisibleIndex = 3
                LocalGrid.Columns(“Priority”).ReportStyle.Width = 80

                LocalGrid.Columns(“Bin”).VisibleIndex = 4
                LocalGrid.Columns(“Bin”).ReportStyle.Width = 120
                LocalGrid.Columns(“Bin”).SortDirection = Xceed.Grid.SortDirection.Ascending

                LocalGrid.Columns(“BestBinLotCount”).VisibleIndex = 5
                LocalGrid.Columns(“BestBinLotCount”).Title = “Best Bin Count”
                LocalGrid.Columns(“BestBinLotCount”).ReportStyle.Width = 70
                AdjustXceedColumnWidth(LocalGrid, “BestBinLotCount”, “Best Bin Count”)

                LocalGrid.Columns(“AltBinLotCount”).VisibleIndex = 6
                LocalGrid.Columns(“AltBinLotCount”).Title = “Alt Bin Count”
                LocalGrid.Columns(“AltBinLotCount”).ReportStyle.Width = 70
                AdjustXceedColumnWidth(LocalGrid, “AltBinLotCount”, “Alt Bin Count”)

                LocalGrid.Columns(“BestBinAvg_YD_PercentVf”).VisibleIndex = 7
                LocalGrid.Columns(“BestBinAvg_YD_PercentVf”).FormatSpecifier = “P”
                LocalGrid.Columns(“BestBinAvg_YD_PercentVf”).Title = “AVG Yield”
                LocalGrid.Columns(“BestBinAvg_YD_PercentVf”).ReportStyle.Width = 104

                LocalGrid.Columns(“PassingYield”).VisibleIndex = 8
                LocalGrid.Columns(“PassingYield”).FormatSpecifier = “P”
                LocalGrid.Columns(“PassingYield”).Title = “Pass Yield”
                LocalGrid.Columns(“PassingYield”).ReportStyle.Width = 104

                LocalGrid.Columns(“PreferredYield”).VisibleIndex = 9
                LocalGrid.Columns(“PreferredYield”).FormatSpecifier = “P”
                LocalGrid.Columns(“PreferredYield”).Title = “Hold Yield”
                LocalGrid.Columns(“PreferredYield”).ReportStyle.Width = 104

                LocalGrid.Columns(“ReservationYield”).VisibleIndex = 10
                LocalGrid.Columns(“ReservationYield”).FormatSpecifier = “P”
                LocalGrid.Columns(“ReservationYield”).Title = “Res Yield”
                LocalGrid.Columns(“ReservationYield”).ReportStyle.Width = 104

                LocalGrid.Columns(“PriorityFactor”).VisibleIndex = 11
                LocalGrid.Columns(“PriorityFactor”).Title = “PF”
                LocalGrid.Columns(“PriorityFactor”).ReportStyle.Width = 40

                SetxColProperties(LocalGrid.Columns(“WIPDate”), -1, 50, “G”, False, Xceed.Grid.SortDirection.None, “WIPDate”, False)
                AdjustXceedColumnWidth(LocalGrid, “WIPDate”, “2008/12/30 12:12:12 PM “)
                LocalGrid.GroupTemplates.Add(New Xceed.Grid.Group(“Diameter”))
                LocalGrid.GroupTemplates.Add(New Xceed.Grid.Group(“Substrate”))
                LocalGrid.GroupTemplates.Add(New Xceed.Grid.Group(“Material_Category”))

    I start timing here
    LocalGrid.EndInit()
    I end timing here

    If I remove all the detail grid code and just leave the main grid code the time after the call to EndInit is a few miliseconds, but with the detail grid code in place the time ends up being 11.5 seconds.

    This seems like a lot of time to load at most 30,000 rows.  I know in the past I have loaded 250,000+ rows in a single master grid in less time.

    How can I make the detail grids faster?

     

    Brian

     

    Imported from legacy forums. Posted by Brian (had 692 views)

    User (Old forums)
    Member
    Post count: 23064

    There are a couple potential problems in the way you bind your grids.  First, when binding the SubDetail, you use a relation name that seems to refer to a relationship between table 2 and table 3, not between table1 and table 3, as you mentioned in your first post, but you add this DetailGrid to the master Grid, not to the first DetailGrid.

    i.e.:

                    SubDetail.DataMember = “DetailBin_SubDetailBin”
                    LocalGrid.DetailGridTemplates.Add(SubDetail)

    Can you provide more details on this?

    Second, the “standard” way to do the binding would be as the following (assuming a relationship between table1 and table3) :

                LocalGrid.DataSource = LocalData.ReturnDataset.Tables(0)
                LocalGrid.DataMember = “”

                detail.DataSource = LocalData.ReturnDataset.Tables(0)
                detail.DataMember = “MasterBin_DetailBin”

                SubDetail.DataSource = LocalData.ReturnDataset.Tables(0)
                SubDetail.DataMember = “Master_SubDetailBin”

    or

                LocalGrid.DataSource = LocalData.ReturnDataset
                LocalGrid.DataMember = “Table0Name”

                detail.DataSource = LocalData.ReturnDataset
                detail.DataMember = “Table0Name.MasterBin_DetailBin”

                SubDetail.DataSource = LocalData.ReturnDataset
                SubDetail.DataMember = “Table0Name.Master_SubDetailBin”

     

    Now if the relationship in the DB is really between table2 and table3, it should be like the following :

                LocalGrid.DataSource = LocalData.ReturnDataset.Tables(0)
                LocalGrid.DataMember = “”

                detail.DataSource = LocalData.ReturnDataset.Tables(0)
                detail.DataMember = “MasterBin_DetailBin”

                SubDetail.DataSource = LocalData.ReturnDataset.Tables(0)
                SubDetail.DataMember = “MasterBin_DetailBin.DetailBin_SubDetailBin”

    And then add the DetailGrid to the other DetailGrid :

                detail.DetailGridTemplates.Add(SubDetail)

     

    Imported from legacy forums. Posted by André (had 655 views)

    User (Old forums)
    Member
    Post count: 23064

    Sorry, I did a crappy job of naming the relationships.

    They are as: Table1 to Table2 and Table1 to Table3

    I changed the bindings to your first set of examples:

                LocalGrid.DataSource = LocalData.ReturnDataset.Tables(0)
                LocalGrid.DataMember = “”

                detail.DataSource = LocalData.ReturnDataset.Tables(0)
                detail.DataMember = “MasterBin_DetailBin”

                SubDetail.DataSource = LocalData.ReturnDataset.Tables(0)
                SubDetail.DataMember = “Master_SubDetailBin”

    The data displays as it should but the timing is the same.  Virtually no time is consume in the binding of the detail grids but the call to EndInit Takes 12.5 seconds.  And as before if I remove the code to attach the detail grids the time drops to 90 miliseconds.

     

    It appears to be directly linked to the number of rows in each of the datatable that the relationship is based on.  If I only add the first detail grid with approximately 1000 rows it takes about 2 seconds for the call to EndInit, but if I add only the second detail grid which has about 29,000 rows it take 9.8 seconds.  So it appears that even though I have it set to collapsed it still loads all the data and it takes a lot of time to do so.

     I was under the impression that setting it to collapsed that the data was not loaded, and it does appear to take a lot of time compared to laoding 30,000 records in just a master grid.  Any ideas???

     Brian

     

     

     

    Imported from legacy forums. Posted by Brian (had 463 views)

    User (Old forums)
    Member
    Post count: 23064

    Which version of the grid are you using?  Normally the DataRows of the DetailGrids should not be generated if they are set to be collapsed, but that’s from version 3.6 and up.

    Can you just add the following code before binding the grid, and let us the know the results?

    DataView test = LocalData.ReturnDataset.Tables(0).DefaultView;

    test = LocalData.ReturnDataset.Tables(1).DefaultView;

    test = LocalData.ReturnDataset.Tables(2).DefaultView;

     

    There is normally a delay when the DataView is first created, but it should be fairly short, so we just want to make sure this is in fact the case.

    Imported from legacy forums. Posted by André (had 463 views)

    User (Old forums)
    Member
    Post count: 23064

    We are using Grid.Net version 3.7 we just purchased the licenses a few weeks ago.

    I added the additional code and it took 104ms to execute your additional code and it did not change the other times.  It is still taking 12.5 – 13 seconds after the call to EndInit.

     Brian

    Imported from legacy forums. Posted by Brian (had 571 views)

    User (Old forums)
    Member
    Post count: 23064

    I have put a sample app together for you with Xml files for the schema and data.  How do I send it to you?

     It is based on actual data and the way we show the grid.  the timings are very close to our production app.

    Imported from legacy forums. Posted by Brian (had 660 views)

    User (Old forums)
    Member
    Post count: 23064

    Send it to support@xceedsoft.com with a reference to this post.

    Imported from legacy forums. Posted by André (had 386 views)

    User (Old forums)
    Member
    Post count: 23064

    I sent the zip file with the source code and the data XML files for all the grids.  Please advise if there is anything else I can do to help.

     

    Brian

    Imported from legacy forums. Posted by Brian (had 961 views)

    User (Old forums)
    Member
    Post count: 23064

    We identified the problem and found a solution.

    What happens is that, even though the detail grids rows are not created as long as they are not expanded, the grid still binds to the data (that is, DataRow.Cells(“Relation”).Value ).  This forces the DataView of the relation to be created for every master row/ detail grid, that is, for every master row, a new DataView with only the corresponding data is generated.  You can see that this is time consuming…  and we can’t control that, this is how .NET binding works.

    Now, we can delay this DataView creation to the time the actual detail grid is expended.  To accomplish that, the DataSource and DataMember must not be set on the template detail grids, but rather at the CollapsedChanged event.

    For example :

      Private Sub BinTestDetailColapsedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim LocalDetail As Xceed.Grid.DetailGrid = Nothing

        LocalDetail = DirectCast(sender, Xceed.Grid.DetailGrid)
        If Not LocalDetail.Collapsed Then

          If LocalDetail.Title.Contains(“Best”) Then
            LocalDetail.SetDataBinding( LocalDetail.ParentDataRow.Cells(“MasterBin_DetailBin”).Value, Nothing)
          Else
            LocalDetail.SetDataBinding( LocalDetail.ParentDataRow.Cells(“MasterBin_SubDetailBin”).Value, Nothing)
          End If
        End If

    Imported from legacy forums. Posted by André (had 578 views)

    User (Old forums)
    Member
    Post count: 23064

    That did the trick, the grid load is immediate and the expansion of the detail grids is very quick.  The users are very happy and I appreciate your help with this matter.

     This is what I call REAL support.  It may have taken a few days to get it all together but that is to be expected at times.  This is also the reason I like Xceed.

    Thanks again, your a life saver…

     

    Brian

    Imported from legacy forums. Posted by Brian (had 1466 views)

Viewing 12 posts - 1 through 12 (of 12 total)
  • You must be logged in to reply to this topic.