Solved

VB.net slow form/datagrid loading

Posted on 2009-07-15
8
1,159 Views
Last Modified: 2012-05-07
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
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

Open in new window

0
Comment
Question by:Shepwedd
  • 4
  • 2
  • 2
8 Comments
 
LVL 15

Expert Comment

by:spprivate
ID: 24859288
When do you enter the file name
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
0
 

Author Comment

by:Shepwedd
ID: 24859346
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.
0
 
LVL 15

Expert Comment

by:spprivate
ID: 24859747
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
0
 

Author Comment

by:Shepwedd
ID: 24860654
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?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24867635
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.ConnectionString

Why not store the connection string in settings and use it directly instead of create a connection just to get the connectionstring!
0
 

Author Comment

by:Shepwedd
ID: 24969018
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
0
 

Accepted Solution

by:
Shepwedd earned 0 total points
ID: 25077650
Solved myself. Was filling my dataset everytime a query was being performed...doh!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 25077717
It does not look like you are filling multiple times from the code above.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now