Shepwedd
asked on
VB.net slow form/datagrid loading
I'm populating a datagrid on the loading of a form. The code works fine but now that all the data has been imported into the sql database, the form is taking ages to load. I'm not sure if i'm just not filling the datagrid correctly. Any help would be appreciated.
Thanks
Naheed
Thanks
Naheed
Public Class frmShareInfo
Dim cmd As SqlCommand
Dim myAdapter As New SqlDataAdapter
Dim comBuilder As SqlCommandBuilder
Dim ds As DataSet
Dim mytable As DataTable
Dim myrow As Data.DataRow
Dim rownumber As Integer
Dim txtaccount = frmMain.txtFilename.Text
Dim con As New SqlConnection
Private Sub frmShareInfo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim sqlConnection1 As New GlobalConnection
con.ConnectionString = sqlConnection1.ConnectionString
Try
frmEnterAccountInfo.Close()
frmMain.Hide()
Me.ShareInfoTableAdapter.Fill(Me.SharesDataSet.ShareInfo)
myAdapter.SelectCommand = New SqlCommand("SELECT * FROM ShareInfo WHERE Filename = '" & (txtaccount) & "'", con)
comBuilder = New SqlCommandBuilder(myAdapter)
con.Open()
'rownumber = 0
FillDataGrid()
con.Close()
Catch ex As Exception
End Try
End Sub
Private Sub FillDataGrid()
Try
ds = New DataSet()
myAdapter.Fill(ds, "ShareInfoTable")
mytable = ds.Tables("ShareInfoTable")
DataGridView1.DataSource = ds.Tables("ShareInfoTable")
DataGridView1.DataMember = "ShareInfoTable"
DataGridView1.Columns(0).Visible = False
DataGridView1.Columns(6).Visible = False
Catch ex As Exception
End Try
End Sub
End Class
ASKER
The filename is entered in the main form (frmMain) and is accessed by frmShareInfo via...
Dim txtaccount = frmMain.txtFilename.Text
txtaccount is then used in the query.
Dim txtaccount = frmMain.txtFilename.Text
txtaccount is then used in the query.
Is the query running quicker in the back end query analyzer.If not you might need to do indexing on filename field.
Also try to use specific query like select f1,f2 instead of *.
Next thing also would be do use paging,so that you dont have to load all at once
Also try to use specific query like select f1,f2 instead of *.
Next thing also would be do use paging,so that you dont have to load all at once
ASKER
the query runs fine in the query analyzer, and i've tried using a specific query and that's made no difference.
Some of the data that's being returned is just a couple of rows so i don't think paging's an issue.
Any other ideas?
Some of the data that's being returned is just a couple of rows so i don't think paging's an issue.
Any other ideas?
Using the VirtualMode on the datagridview may help but there is also some room for optimizing your code. For example, why do you need this
mytable = ds.Tables("ShareInfoTable" )
?
I dont see mytable being used anywhere. Also, use a DataTable instead of a DataSet to populate the grid because its only one table. Also, why do you need this?
Dim sqlConnection1 As New GlobalConnection
con.ConnectionString = sqlConnection1.ConnectionS tring
Why not store the connection string in settings and use it directly instead of create a connection just to get the connectionstring!
mytable = ds.Tables("ShareInfoTable"
?
I dont see mytable being used anywhere. Also, use a DataTable instead of a DataSet to populate the grid because its only one table. Also, why do you need this?
Dim sqlConnection1 As New GlobalConnection
con.ConnectionString = sqlConnection1.ConnectionS
Why not store the connection string in settings and use it directly instead of create a connection just to get the connectionstring!
ASKER
Thanks for your suggestions - I tried them but it didn't make any difference.
What i did find while trawling the net was this site http://www.developerdotstar.com/community/node/706 which did help a lot, it's apparently a painting issue rather than a data issue.
It's a lot better than it was but still not brilliant so any other suggestions will be greatly appreciated.
Thanks
What i did find while trawling the net was this site http://www.developerdotstar.com/community/node/706 which did help a lot, it's apparently a painting issue rather than a data issue.
It's a lot better than it was but still not brilliant so any other suggestions will be greatly appreciated.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It does not look like you are filling multiple times from the code above.
Because first time when the form loads,there is not parameter.In that case your query will fail for the where condition.
Pass a default value in the query for the first time (form load) and then based on some event reload the grid for the desired parameter