We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Please help me .. Data Filling on data bound grid is extremely slow?

Hi I know it is bit long, if any one can help me finding this solution. I am using VB .net 2003 with SQL Server 2000 and Sync 3.2.1.0 data bound grid. Q1) Filling of data bound grid is extremely slow, as well as Sunc combo is slow too with filling? My Code for Filling a Dataset for Items Table which contains only 5000 rows, ( I expect about 70,000 rows later) and assigning this dataset to grid as datasource is taking hell of time. Please tell me what I am doing wrong. This is where I felt the delay. lsTableName = "Is_Items" lDa.fill(lDs, lsTableName) lDt = lDs.tables.item(lsTableName) syncDbFind.DataSource = lDs.Tables(lsTableName) This is what I am doing. I have developed a "Find Form". With the filter option for selection. This "Find form", I Call from "Find button" from my every form. This is how I call Find Form, (each time user clicks on Find Button) Dim frmFind As New Find gsTableName = "Is_Items" GetPrimaryKeys(gsTableName) frmFind.ShowDialog(Me) Now in "Form Find" I place one Sync Grid Data Bound Grid and load this grid according to the table which I have in Global Variable ex. gsTableName = "Is_Items" Now on the Form Load of "Find Form" I have like this Private Sub Find_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load SetGrid() SetFilterBar() PopulateGrid(gsTableName) End Sub ''This is my SET Grid Procedure Private Sub SetGrid() syncDbFind.SortBehavior = GridSortBehavior.SingleClick syncDbFind.AllowDragSelectedCols = True syncDbFind.VerticalThumbTrack = False syncDbFind.HorizontalThumbTrack = False End Sub This is my Set Filter Private Sub SetFilterBar() Me.Cursor = Cursors.WaitCursor Me.syncDbFind.BeginUpdate() If Not (Me.theFilterBar Is Nothing) AndAlso theFilterBar.Wired Then ''reset the old table theFilterBar.RowFilter = "" theFilterBar.UnwireGrid() End If Me.syncDbFind.Model.ColWidths.ResizeToFit(GridRangeInfo.Row(0), GridResizeToFitOptions.NoShrinkSize) ''create the filter bar and display it theFilterBar = New GridFilterBar theFilterBar.WireGrid(Me.syncDbFind) Me.syncDbFind.EndUpdate() Me.Cursor = Cursors.Arrow End Sub And this is my PopulateGrid Procedure Private Sub PopulateGrid(ByVal p_TableName As String) Dim lsSqlq As String Dim lCn As SqlConnection Dim lDa = New SqlDataAdapter Dim lDs = New DataSet Dim lDt As DataTable Try lCn = New SqlConnection(gsConnectionString) If lCn.State = ConnectionState.Closed Then lCn.Open() End If Dim lsOrderBy As String Dim liCounter As Integer = 0 For liCounter = 0 To (gsPrimaryKey.Length - 1) lsOrderBy = lsOrderBy + gsPrimaryKey(liCounter) If liCounter <> gsPrimaryKey.Length - 1 Then lsOrderBy = lsOrderBy + "," End If Next lsSqlq = " Select * from " & p_TableName & " Order by " & lsOrderBy lDa = New SqlDataAdapter(lsSqlq, lCn) lCn.Close() Dim lsTableName = "dt" & p_TableName lDa.fill(lDs, lsTableName) lDt = lDs.tables.item(lsTableName) syncDbFind.DataSource = lDs.Tables(lsTableName) lDt.Dispose() Catch ex As Exception MsgBox(ex.Message) End Try End Sub I have only One Double Click Event () which is like. Try Dim cm As CurrencyManager = Me.BindingContext(Me.syncDbFind.DataSource, Me.syncDbFind.DataMember) Dim dr As DataRow = CType(cm.Current, DataRowView).Row Dim liCounter As Integer ReDim gsPrimaryKeyData(gsPrimaryKey.Length) For liCounter = 0 To (gsPrimaryKey.Length - 1) Select Case (dr(gsPrimaryKey(liCounter)).GetType().ToString) Case "System.String" gsPrimaryKeyData(liCounter) = "''" & dr(gsPrimaryKey(liCounter)).ToString() & "''" Case Else gsPrimaryKeyData(liCounter) = dr(gsPrimaryKey(liCounter)).ToString() End Select Next Me.Close() Me.Dispose() Catch ex As Exception MsgBox(ex.Message) End Try Please help me, how to load grid fast. Q2) How can I make filter combo Editable, so that user can type to select data and upon enter search the data or Data should appears by intellisence method, means I type T, Data starts with T should pop up, then if I type A after T (TA) then data starts with TA should pop up. In my case second character is not poping together rather it jumps to the Data starts with "A"

3 Replies

AD Administrator Syncfusion Team January 16, 2006 12:33 PM UTC

Hi Abdul, Issue 1: Try setting the DataSource call with grid.BeginUpdate() and grid.EndUpdate() to see if that improves the performance. syncDbFind.BeginUpdate(); syncDbFind.Binder.SuspendBinding(); syncDbFind.DataSource = lDs.Tables(lsTableName) syncDbFind.Binder.ResumeBinding(); syncDbFind.EndUpdate(); Please take a look at the Browse sample for Performance issue: \Essential Studio\3.3.0.1\Windows\Grid.Windows\Samples\DataBound\GridPerf example on GridDataBound Performance. Issue 2: you have to derive the GridFilterBar class for this. In the attached sample, the Filtering is done in the CurrentCellChanged Event of the GridFilterBar class. Refer to the sample for details. Let me know if you need futher assistance, With Regards, Madhan.

GridFilterBar_39668.zip


AM Abdul Majid January 16, 2006 02:19 PM UTC

Thanks Madan BUT 1) I see still it filling takes time. First of all my Dataadoptor (lDa) fill itself is very slow, it dies while fillig and again it takes time while assigning to DataTable (lDt). lDa.fill(lDs, lsTableName) lDt = lDs.tables.item(lsTableName) 2) Datasource of Grid seems better when you suggested, but it takes tramandous time on Fitlering theFilterBar = New GridFilterBar theFilterBar.WireGrid(Me.syncDbFind) Any idea about these issues. >Hi > >I know it is bit long, if any one can help me finding this solution. > >I am using VB .net 2003 with SQL Server 2000 >and Sync 3.2.1.0 data bound grid. > >Q1) Filling of data bound grid is extremely slow, as well as Sunc combo is slow too with filling? > >My Code for Filling a Dataset for Items Table which contains only 5000 rows, ( I expect about 70,000 rows later) and assigning this dataset to grid as datasource is taking hell of time. Please tell me what I am doing wrong. > >This is where I felt the delay. > >lsTableName = "Is_Items" > >lDa.fill(lDs, lsTableName) > >lDt = lDs.tables.item(lsTableName) > >syncDbFind.DataSource = lDs.Tables(lsTableName) > >This is what I am doing. > >I have developed a "Find Form". With the filter option for selection. >This "Find form", I Call from "Find button" from my every form. > >This is how I call Find Form, (each time user clicks on Find Button) > > Dim frmFind As New Find > > gsTableName = "Is_Items" > > GetPrimaryKeys(gsTableName) > > frmFind.ShowDialog(Me) > >Now in "Form Find" I place one Sync Grid Data Bound Grid and load this grid according to the table which I have in Global Variable ex. > >gsTableName = "Is_Items" > >Now on the Form Load of "Find Form" I have like this > >Private Sub Find_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load > > > SetGrid() > > SetFilterBar() > > PopulateGrid(gsTableName) > >End Sub > > >''This is my SET Grid Procedure > >Private Sub SetGrid() > > syncDbFind.SortBehavior = GridSortBehavior.SingleClick > syncDbFind.AllowDragSelectedCols = True > syncDbFind.VerticalThumbTrack = False > syncDbFind.HorizontalThumbTrack = False > > End Sub > >This is my Set Filter > >Private Sub SetFilterBar() > > Me.Cursor = Cursors.WaitCursor > > Me.syncDbFind.BeginUpdate() > >If Not (Me.theFilterBar Is Nothing) AndAlso theFilterBar.Wired Then > ''reset the old table > theFilterBar.RowFilter = "" > > theFilterBar.UnwireGrid() > End If > > > Me.syncDbFind.Model.ColWidths.ResizeToFit(GridRangeInfo.Row(0), GridResizeToFitOptions.NoShrinkSize) > > ''create the filter bar and display it > theFilterBar = New GridFilterBar > > theFilterBar.WireGrid(Me.syncDbFind) > > Me.syncDbFind.EndUpdate() > > Me.Cursor = Cursors.Arrow > > End Sub > >And this is my PopulateGrid Procedure > >Private Sub PopulateGrid(ByVal p_TableName As String) > > Dim lsSqlq As String > Dim lCn As SqlConnection > Dim lDa = New SqlDataAdapter > > Dim lDs = New DataSet > Dim lDt As DataTable > > Try > > lCn = New SqlConnection(gsConnectionString) > > If lCn.State = ConnectionState.Closed Then > lCn.Open() > End If > > Dim lsOrderBy As String > > Dim liCounter As Integer = 0 > > For liCounter = 0 To (gsPrimaryKey.Length - 1) > > lsOrderBy = lsOrderBy + gsPrimaryKey(liCounter) > > If liCounter <> gsPrimaryKey.Length - 1 Then > lsOrderBy = lsOrderBy + "," > End If > > Next > > lsSqlq = " Select * from " & p_TableName & " Order by " & lsOrderBy > > lDa = New SqlDataAdapter(lsSqlq, lCn) > > lCn.Close() > > Dim lsTableName = "dt" & p_TableName > > lDa.fill(lDs, lsTableName) > > lDt = lDs.tables.item(lsTableName) > > syncDbFind.DataSource = lDs.Tables(lsTableName) > > lDt.Dispose() > > Catch ex As Exception > > MsgBox(ex.Message) > > End Try > > End Sub > >I have only One Double Click Event () which is like. > > > Try > > Dim cm As CurrencyManager = Me.BindingContext(Me.syncDbFind.DataSource, Me.syncDbFind.DataMember) > > Dim dr As DataRow = CType(cm.Current, DataRowView).Row > > Dim liCounter As Integer > > ReDim gsPrimaryKeyData(gsPrimaryKey.Length) > > For liCounter = 0 To (gsPrimaryKey.Length - 1) > > Select Case (dr(gsPrimaryKey(liCounter)).GetType().ToString) > > Case "System.String" > > gsPrimaryKeyData(liCounter) = "''" & dr(gsPrimaryKey(liCounter)).ToString() & "''" > > Case Else > > gsPrimaryKeyData(liCounter) = dr(gsPrimaryKey(liCounter)).ToString() > > End Select > Next > > Me.Close() > Me.Dispose() > > Catch ex As Exception > > MsgBox(ex.Message) > > End Try > >Please help me, how to load grid fast. > > >Q2) How can I make filter combo Editable, so that user can type to select data and upon enter search the data or Data should appears by intellisence method, means I type T, Data starts with T should pop up, then if I type A after T (TA) then data starts with TA should pop up. In my case second character is not poping together rather it jumps to the Data starts with "A" >


AD Administrator Syncfusion Team January 18, 2006 07:39 AM UTC

Hi Abdul, Issue 1: Please try checking the number of I/O operations. I suspect that the Connection routine consumes much time. Here are links that will improve the Connection Performance. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/dotnetperftips.asp http://msdn.microsoft.com/msdnmag/issues/05/11/DataPoints/default.aspx Also Refer this Browser Sample \Essential Studio\3.3.0.1\Windows\Grid.Windows\Samples\DataBound\GridPerf for DataBoundGrid Performance Issue 2: The GridDataBoundGrid is capable of handling a large datasource with the GridFilterBar. Here is a Sample, that has 10000 rows and 25 columns and the performance is good. Regards, Madhan

Incident_22267.zip

Loader.
Live Chat Icon For mobile
Up arrow icon