Results 1 to 9 of 9

Thread: How can I make the execution faster?

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2022
    Posts
    14

    How can I make the execution faster?

    I have this scratch program.
    Using VB.net, MS access 2016

    On this image is when I first load the program.
    Name:  Untitled1.jpg
Views: 252
Size:  21.2 KB

    On this image when I click the prev button
    Name:  Untitled2.jpg
Views: 256
Size:  22.1 KB

    The datagridview on right changes the record. (I used only 1-3, but when I finally done with it, I will change it to the calendar days)

    My problem is everytime I click the prev button or next button, there is a 2-3 secs delay on the changing of records of datagridview, what more if I change it to number of days. the code I'm using on this button is below:

    Code:
    Private Sub GettingData()
            Cleartable() 'Clearing the datagridview
            newmnths = DateTime.ParseExact(Label1.Text, "MMMM", System.Globalization.CultureInfo.CurrentCulture).Month
            Try
                Conb() 'This my module of connection string
                Dim remark(2) As String
                For i As Integer = 0 To DataGridView2.RowCount - 1
                    For d As Integer = 1 To 3 'To be change to calendar days
                        empid = DataGridView2.Rows(i).Cells.Item(0).Value
                        Ldate = newmnths & "/" & d & "/2022"
                        str = "Select * from Savetable Where ID = @ID and Leavedate = @Leavedate"
                        cmd = New OleDbCommand(str, conn)
                        cmd.Parameters.AddWithValue("ID", empid)
                        cmd.Parameters.AddWithValue("Leavedate", Ldate)
                        dr = cmd.ExecuteReader
                        While dr.Read()
                            remark(d - 1) = dr(2)
                            'If d = 1 Then
                        End While
                    Next
                    str = "update showtable set  1 = '" & remark(0) & "' , 2 = '" & remark(1) & "' , 3 = '" & remark(2) & "'Where id = @ID " 'dr(2) is the remark column
                    cmd = New OleDbCommand(str, conn)
                            cmd.Parameters.Add("ID", OleDbType.Integer).Value = empid
                            cmd.ExecuteNonQuery()
                    Next
            Catch
            End Try
            conn.Close()
            LoadRightDataGrid()
        End Sub
    I hope you can help me for this. THanks
    Last edited by Shaggy Hiker; Jun 20th, 2022 at 08:08 PM. Reason: Changed the QUOTE tags to CODE tags.

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

    Re: How can I make the execution faster?

    The first thing to do is figure out WHY it is taking so long. The best way that I've found to do this is to use the Stopwatch object. Start it before the part you want to time, then stop it afterwards and look at the EllapsedMilliseconds. By doing this around various bits of code, you can find the part that is causing the slowdown. There are many possibilities, so guessing isn't all that worthwhile.

    1) You have that ClearTable() method. Depending on what that does, it could be the problem, so time that.

    2) I have no idea what you mean by a module for connection string, so that could certainly be a problem. It's not a great way to deal with a connection, but for Access, it might be okay. Depends on what is in that Conb() method.

    3) There's also that LoadRightDataGrid(), which could take some time, depending on what that does.

    4) You are swallowing exceptions. Exception handlers cost nothing if no exception is raised, but exceptions cost a LOT. If you are swallowing actual exceptions, then that is definitely going to take noticeable time. Either get rid of the exception handler or do something with any exception. You should have database access in an exception handler, but you need to KNOW about any exceptions raised, because they will be very costly in terms of time. If there are exceptions that you can avoid, then you should be avoiding them. Ignoring them just means that you can't improve the code. At the very least, put up a messagebox so you KNOW that an exception happened and what it was.

    5) However, while all of that might be correct, and 4 is a likely suspect, the form of that code is even more likely to be the problem. You have two nested loops, and inside the innermost...you hit the database. That means a potentially very large number of database hits. Timing how long that loop takes would be a good idea. It might not be all that long, since the inner loop only happens three times, so the total number of DB hits is 3 * the rows in the DGV, and you are only showing a few rows, so perhaps only 9 DB hits, which really shouldn't take 2-3 seconds, even if you don't have indexes on the tables.

    Timing is the first thing to do. The database stuff seems like it could be readily optimized, but if you are throwing exceptions, then the DB stuff might not matter. The other methods might also be the issue. So, while the DB stuff seems like it could be improved, timing is necessary to figure out whether or not that's where the problem actually lies. If that DOES turn out to be the problem, then it certainly looks like you should be able to get rid of most, if not all, of that. After all, you can get empid once per iteration of the outer loop, rather than getting it in each inner loop. You don't even need the inner loop, because you are only looking for three dates, and you know what they will be, since d will be 1, 2, and 3, in turn. I don't see where newmnths is coming from, so I assume it's external. Therefore, you can avoid doing all those concatenations buy just making up the three dates right off before you do the looping.

    Beyond that, the UPDATE can be written such that the SELECT never needs to happen. It's a much more complicated UPDATE statement, but it certainly looks possible. Still, figuring out whether or not the slowdown is due to the loops is the first step, because there isn't much point in improving that if it doesn't solve anything.
    My usual boring signature: Nothing

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    108,680

    Re: How can I make the execution faster?

    Note that QUOTE tags are for quotes, while CODE tags are for code. If you're posting code, use CODE tags. Most importantly, that will maintain indenting and make code much easier to read.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jun 2022
    Posts
    14

    Re: How can I make the execution faster?

    Quote Originally Posted by Shaggy Hiker View Post
    The first thing to do is figure out WHY it is taking so long. The best way that I've found to do this is to use the Stopwatch object. Start it before the part you want to time, then stop it afterwards and look at the EllapsedMilliseconds. By doing this around various bits of code, you can find the part that is causing the slowdown. There are many possibilities, so guessing isn't all that worthwhile.

    1) You have that ClearTable() method. Depending on what that does, it could be the problem, so time that.

    2) I have no idea what you mean by a module for connection string, so that could certainly be a problem. It's not a great way to deal with a connection, but for Access, it might be okay. Depends on what is in that Conb() method.

    3) There's also that LoadRightDataGrid(), which could take some time, depending on what that does.

    4) You are swallowing exceptions. Exception handlers cost nothing if no exception is raised, but exceptions cost a LOT. If you are swallowing actual exceptions, then that is definitely going to take noticeable time. Either get rid of the exception handler or do something with any exception. You should have database access in an exception handler, but you need to KNOW about any exceptions raised, because they will be very costly in terms of time. If there are exceptions that you can avoid, then you should be avoiding them. Ignoring them just means that you can't improve the code. At the very least, put up a messagebox so you KNOW that an exception happened and what it was.

    5) However, while all of that might be correct, and 4 is a likely suspect, the form of that code is even more likely to be the problem. You have two nested loops, and inside the innermost...you hit the database. That means a potentially very large number of database hits. Timing how long that loop takes would be a good idea. It might not be all that long, since the inner loop only happens three times, so the total number of DB hits is 3 * the rows in the DGV, and you are only showing a few rows, so perhaps only 9 DB hits, which really shouldn't take 2-3 seconds, even if you don't have indexes on the tables.

    Timing is the first thing to do. The database stuff seems like it could be readily optimized, but if you are throwing exceptions, then the DB stuff might not matter. The other methods might also be the issue. So, while the DB stuff seems like it could be improved, timing is necessary to figure out whether or not that's where the problem actually lies. If that DOES turn out to be the problem, then it certainly looks like you should be able to get rid of most, if not all, of that. After all, you can get empid once per iteration of the outer loop, rather than getting it in each inner loop. You don't even need the inner loop, because you are only looking for three dates, and you know what they will be, since d will be 1, 2, and 3, in turn. I don't see where newmnths is coming from, so I assume it's external. Therefore, you can avoid doing all those concatenations buy just making up the three dates right off before you do the looping.

    Beyond that, the UPDATE can be written such that the SELECT never needs to happen. It's a much more complicated UPDATE statement, but it certainly looks possible. Still, figuring out whether or not the slowdown is due to the loops is the first step, because there isn't much point in improving that if it doesn't solve anything.

    Thanks for the reply

    1. as of the cleartable I tried to remove it and I realize it's still do the same even without that so it take less a sec. It only take 1-2 secs.

    2. my conb() code is below. I just call it everytime I need to connect to database.
    Imports System.Data.OleDb

    Code:
    Module ConData
    
        Public conn As OleDbConnection '
        'Public connstr As String = "" & My.Computer.Name & System.Environment.CurrentDirectory.ToString.Remove(0, 2) & "\Attdata.accdb" '  
        Public connstr As String = System.Environment.CurrentDirectory.ToString & "\Attdata.accdb" '  
    
        Public Sub Conb()
    
            Try
                conn = New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data source = " & connstr & "")
                conn.Open()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Dim stringer As String = Application.StartupPath()
                MsgBox(stringer)
                Dim stringer2 As String = Application.ExecutablePath()
                MsgBox(stringer2)
                Dim stringer3 As String = My.Application.Info.DirectoryPath
                MsgBox(stringer3)
            End Try
        End Sub
    End Module
    3. LoadRightDataGrid is simple loading the table from access to datagrid.
    Code:
    Private Sub LoadRightDataGrid()
            Try
                Conb()
                ds = New DataSet
                table = ds.Tables
                str = "Select * From Showtable"
                da = New OleDb.OleDbDataAdapter(str, conn)
                da.Fill(ds, "Showtable")
                Dim view As New DataView(table(0))
                source.DataSource = view
                DataGridView2.DataSource = view
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            conn.Close()
            DataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            DataGridView2.Columns(0).Visible = False
            DataGridView2.Sort(DataGridView2.Columns(1),
            System.ComponentModel.ListSortDirection.Ascending)
            DataGridView2.ReadOnly = True
        End Sub
    4. I don't know that exception cost a lot, but I'm really gonna remove it when my coding is final, I just use to see what the problems to be encounter while I'm doing a scrap code. So I will do your suggestion, I'll try to remove this for a moment if it will make faster. THen I'll give an update.
    Last edited by Shaggy Hiker; Jun 20th, 2022 at 08:09 PM.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    37,457

    Re: How can I make the execution faster?

    That's not a great way to deal with a connection, but it also won't take any time.

    What would be better would be to get rid of that function and make use of a Using block:
    Code:
    Using con As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data source = " & connstr & "")
     'Your Code here.
    End Using
    The advantage of the Using block is that it will properly clean up the connection even if an exception is thrown, or if you were to have a Return statement in the middle of that. You don't have to close or dispose the connection, as the using block will take care of it all. Still, it won't be any faster than the way you are doing it, so that isn't the issue.

    You shouldn't be 'getting rid of things and seeing how it works'. Time the code. Not with your view, but with a Stopwatch object. There is no other way to figure this out.

    However, having seen the LoadRightDataGrid, you probably ought to rethink the whole thing. The whole point of binding a datatable to a datagridview (you never did need a dataset, as that's just a collection of datatables and you only have one) is so that you can then make changes to the datatable and push the whole thing back to the database in one step. What you're doing is painful, as you are getting the data, adjusting it in the database, then getting it again. What you should be doing is getting it into a datatable, making changes in the datatable, then writing the whole thing back to the database in one Update statement, and only call that once ALL the changes you want to make have been made.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2022
    Posts
    14

    Re: How can I make the execution faster?

    Quote Originally Posted by Shaggy Hiker View Post
    That's not a great way to deal with a connection, but it also won't take any time.

    What would be better would be to get rid of that function and make use of a Using block:
    Code:
    Using con As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data source = " & connstr & "")
     'Your Code here.
    End Using
    The advantage of the Using block is that it will properly clean up the connection even if an exception is thrown, or if you were to have a Return statement in the middle of that. You don't have to close or dispose the connection, as the using block will take care of it all. Still, it won't be any faster than the way you are doing it, so that isn't the issue.

    You shouldn't be 'getting rid of things and seeing how it works'. Time the code. Not with your view, but with a Stopwatch object. There is no other way to figure this out.

    However, having seen the LoadRightDataGrid, you probably ought to rethink the whole thing. The whole point of binding a datatable to a datagridview (you never did need a dataset, as that's just a collection of datatables and you only have one) is so that you can then make changes to the datatable and push the whole thing back to the database in one step. What you're doing is painful, as you are getting the data, adjusting it in the database, then getting it again. What you should be doing is getting it into a datatable, making changes in the datatable, then writing the whole thing back to the database in one Update statement, and only call that once ALL the changes you want to make have been made.

    HI Shaggy,
    Base on the Stopwatch object, the Loadrightdatagrid is the responsible for delay, it's the longest time consumed, 2nd is left datagrid because they almost have the same code.
    Can you help me with this one? How to do it in proper way. Thank you so much, a sample can also be a big help.

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    37,457

    Re: How can I make the execution faster?

    I don't understand what you are doing well enough, but you could do this as a start:

    1) Create a datatable at form scope, which will hold the information from ShowTable.

    2) Rewrite LoadRightDataGrid like this (though this is freehand, and might have some errors):

    Code:
    private yourDatatable As new Datatable
    
    Private Sub LoadRightDataGrid()
            Try
               str = "Select * From Showtable"            
               using cn As New OleDBConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data source = " & connstr )
                 Using da As New OleDB.OleDBDataAdapter(str, conn)
                  da.Fill(yourDatatable)
                  DataGridView2.DataSource =yourDatatable
                 End Using
                End Using
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            
            DataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            DataGridView2.Columns(0).Visible = False
            DataGridView2.Sort(DataGridView2.Columns(1),
            System.ComponentModel.ListSortDirection.Ascending)
            DataGridView2.ReadOnly = True
        End Sub
    Call this method only one time, probably when the form loads.

    3) Then rewrite GettingData:
    a) Don't clear the table.
    b) Create the three dates as three strings right off, not in a loop. You know the month, you know the days, and you know the year, so using a loop isn't necessary.
    c) It may still be necessary to do the SELECT call, but there are a couple different alternatives that might be possible. For one thing, you only need a single field from a single record, so rather than SELECT *, get JUST the one field you want. By doing this, you can use ExecuteScalar, which returns a single value and is faster than getting a datareader. The other alternative would be to use a single select for all three dates that you want, but that would only make sense if the ID is going to be the same for all three.
    d) Get rid of the UPDATE query. Instead, just write the information into the datatable created in LoadRightDataGrid. That will be faster than running the UPDATE query.
    e) Whenever you want to save the datatable back to the database, you can do so with the Update method of the dataadapter. That would look virtually the same as loading the table shown in LoadRightDataGrid, except that you'd need a CommandBuilder object to generate the commands, and you'd call Update rather than Fill. Of course, the best way to do that would be to make several changes, then push them all back to the DB at one time, so you'd have to decide when would be best to do that.

    The general design is that you are using a datatable as an in-memory snapshot of some part of the database. You make whatever changes you want to that in-memory snapshot (that's the datatable). Making changes to an in-memory construct is FAR faster than working with a database directly, so make as many changes as you want to the datatable. At some point, when it seems appropriate, write all of the changes back to the database, which is usually pretty easy to do with the .Update method of the dataadapter.

    You might find that you want to write all the updates back to the database as the final step in the GettingData method. That seems kind of likely, but you might find that you can wait until later, such as when the form closes, or the program closes, or something like that. Pushing those updates back to the DB is fairly quick, so you can do whatever makes sense to you.
    My usual boring signature: Nothing

  8. #8

    Thread Starter
    New Member
    Join Date
    Jun 2022
    Posts
    14

    Re: How can I make the execution faster?

    Thanks for this, I will try it immediately, when I do my coding again and will give an update.
    I'm a little busy right now so I can't do the coding. Thanks for this it's a really big help

  9. #9

    Thread Starter
    New Member
    Join Date
    Jun 2022
    Posts
    14

    Re: How can I make the execution faster?

    Just an update, I change my database to SQL and it's much more faster. Thanks for your time

Posting Permissions

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



Click Here to Expand Forum to Full Width