dcsimg
Results 1 to 9 of 9

Thread: DatGridView loading too slow

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2015
    Posts
    16

    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

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,450

    Re: DatGridView loading too slow

    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.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Apr 2015
    Posts
    16

    Re: DatGridView loading too slow

    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?

  4. #4
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,585

    Re: DatGridView loading too slow

    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!
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  5. #5
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,585

    Re: DatGridView loading too slow

    I'm guessing its the connection (wireless)
    If it is your connection speed then you have 1 option, get a faster or wired connection !!!
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  6. #6

    Thread Starter
    Junior Member
    Join Date
    Apr 2015
    Posts
    16

    Re: DatGridView loading too slow

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

  7. #7
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,585

    Re: DatGridView loading too slow

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

    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?
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  8. #8
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,450

    Re: DatGridView loading too slow

    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.
    My usual boring signature: Nothing

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Apr 2015
    Posts
    16

    Re: DatGridView loading too slow

    Name:  sapshot.jpg
Views: 42
Size:  37.5 KB

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width