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

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();

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

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)

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

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.

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

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.

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

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

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