DatGridView loading too slow

Hello,

I’m having a issue with trying to keep the load time down when populating the DataGridView. Can anyone look at my code below and give me some examples of where I can make some changes in order to cut the time down? I’ve already disabled RowsHeader View and RowsHeaderWidth.

Thanks in advance!

Code:

---

Private Sub INV_Extended_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Try

            Dim LinkConnInv As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = R:\1-Inbox-Jason\RHP_File_Pool; Extended Properties = dBase III; User ID=;Password="
            Dim conInv As OleDbConnection = New OleDbConnection()
            conInv.ConnectionString = LinkConnInv
            conInv.Open()


            Dim QueryInv As String = "Select PARTNO, DESCRIPT, MODELNO, MANUFACTER, ONHAND, ONORDER from INV"
            Dim adapaterInv As OleDbDataAdapter = New OleDbDataAdapter(QueryInv, conInv)
            Dim dsInv As DataSet = New DataSet()
            adapaterInv.Fill(dsInv)
            conInv.Close()
            conInv.Dispose()
            Me.DataGridViewINV.DataSource = dsInv.Tables(0)

            Dim INVcolPARTNO As DataGridViewColumn = DataGridViewINV.Columns(0)
            INVcolPARTNO.Visible = True
            Dim INVcolDESCRIPT As DataGridViewColumn = DataGridViewINV.Columns(1)
            INVcolDESCRIPT.Visible = True
            Dim INVcolMODELNO As DataGridViewColumn = DataGridViewINV.Columns(2)
            INVcolMODELNO.Visible = True
            Dim INVcolMANCFACT As DataGridViewColumn = DataGridViewINV.Columns(3)
            INVcolMANCFACT.Visible = True
            Dim INVcolONHAND As DataGridViewColumn = DataGridViewINV.Columns(4)
            INVcolONHAND.Visible = True
            Dim INVcolONORDER As DataGridViewColumn = DataGridViewINV.Columns(5)
            INVcolONORDER.Visible = True

        Catch ex As Exception
            MsgBox("Error while connecting to the database." & vbNewLine & ex.Message)
        End Try

    End Sub

---

There's nothing in that code that looks like it should be particularly slow, and nothing in the code that can be greatly improved in speed. There are some minor things you can do, such as using just a datatable rather than a dataset (which is just a collection of datatables, so you are effectively creating an array with only one item in it), and not using local variables for toggling visibility, but all of those changes combined won't take half a millisecond off the speed, so they are barely worth doing.

So, the problem has to be in the performance of the query itself. The query appears pretty simple, so improving the SQL isn't an option. That pretty much leaves only one alternative: You are returning a HUGE number of records (or a smaller number over a really bad connection). Nobody can look at more than a few dozen records at a time, so what you could do would be to change the SQL to only return a certain number of records and show just those records. Essentially, you'd be creating pages to show rather than showing all the records at once.

Yes, I was afraid of that. I'm guessing its the connection (wireless). One thought, what if I changed the adapter to reader? Would that give me any boost in performance? I've heard that reader has a better performance but has some limitations. Any thoughts?

Quote:

---

One thought, what if I changed the adapter to reader? Would that give me any boost in performance? I've heard that reader has a better performance but has some limitations. Any thoughts?

---

You not going to notice much difference i am afraid. Its not the tools that your are using that is the problem

How many records are you returning out of interest? a common way of dealing with large amounts of records is to page them. So you only bring back say 100 at a time, and have paging buttons which load the next set of 100 records.

This will significantly speed up your app and still provides you users with a nice way to access all the records!

Quote:

---

I'm guessing its the connection (wireless)

---

If it is your connection speed then you have 1 option, get a faster or wired connection !!!

The only concern I have with paging them is I’m using a textbox to filter the DataGridView. Is it possible to filter pages?

ah so your doing inline filtering.... yes you do have a problem.

Quote:

---

Is it possible to filter pages?

---

yes but you filter will only work on the paged data set not the full data set !!

You can have both paging and inline filtering but that wont fix your loading speed issue!.


Let return to your speed issue first - how many record are you trying to load into your grid?

Yeah, you are going to have to rethink this.

The datareader is slightly faster, but not enough to make a noticeable difference in this case. It is all about the number of records being returned. I would go a step further than the question NSA asked, and ask you this: Do you EVER need to see all the records before a filter is applied?

The reason I ask is that the most obvious way to rethink this would be to start with a filter first. Don't show anything in the grid until the user types something in the textbox, because whatever they type in the textbox is likely to GREATLY reduce the number of records that need to be returned. If they can type in all, or most, of the search term, then the records might be narrowed down to just a few.

Alternatively, if the user will be searching on one field, you might get back the distinct items from that field (a distinct list of names, for instance), and use that list in an autocomplete combobox (rather than a textbox), such that the user starts typing, sees similar items from the list, and can quickly zero in on the particular item they want. That would then restrict the search to much fewer records.

Attachment 126597

Attached is a picture of the program.

@ Frendzied It's filtering through 5 different databases at one time (several 1000 records at a time). I'm running a timer (start stop set at 300) on the textbox change event, which is working perfectly to kill the delay when the user types in the desired characters.

@ Shaggy "NSA asked, and ask you this: Do you EVER need to see all the records before a filter is applied?"...That is a really good point. I think I'm going to have to rethink some of this.