locked
More efficient code RRS feed

  • Question

  • User213023536 posted

    Hello, I'm fairly new to ASP.net and have been building a webpage for my extended family.  I've never used VB before, and would like help in perhaps creating more system resource efficient code.  So far I've built a calendar page that will display family events and birthdays when a day is clicked on, that is read from Microsoft Access Database tables.  However, much of my code involves a Big O notation of (n^2) with nested loops.  Here is my code:


    'This sub connects to a database with tables that contain dates and events associated with that date, and when a date is clicked on in the calendar, events associated with that date are displayed in a GridView
        Protected Sub CurrentDay_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles EventCalendar.SelectionChanged
    
            ' Set the label and the textbox to display the date that is clicked on in the Calendar
            Dim selectedDay As Date = EventCalendar.SelectedDate.Date
            Dim selectedDayofYear As Integer = EventCalendar.SelectedDate.DayOfYear()
            DateLabel.Text = selectedDay
            DateBox.Text = selectedDay
    
            'Establish a connection to a Microsoft Access Database and create Data Sets based on some of the tables
            Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\The Saxton Family\Documents\Calendar Events.mdb;User Id=admin;Password=;")
            Dim dateEvents As New OleDbDataAdapter("select Date from Table1", cn)
            Dim events As New OleDbDataAdapter("select Event, Location, Time from Table1", cn)
            Dim eventsControl As New OleDbDataAdapter("select Event, Location, Time from Table1", cn)
            Dim addressTableAdapter As New OleDbDataAdapter("select * from Addresses", cn)
            Dim birthdayAdapter As New OleDbDataAdapter("select Birthdate from Addresses", cn)
            Dim birthdayNameAdapter As New OleDbDataAdapter("select FirstName, LastName from Addresses", cn)
            Dim addressTableDataSet As New DataSet
            Dim birthdayDataSet As New DataSet
            Dim birthdayNameDataSet As New DataSet
            Dim ds2 As New DataSet
            Dim ds As New DataSet
            addressTableAdapter.Fill(addressTableDataSet, "Address Table")
            birthdayAdapter.Fill(birthdayDataSet, "Birthday Table")
            birthdayNameAdapter.Fill(birthdayNameDataSet, "Birthday Name Table")
            dateEvents.Fill(ds, "Date")
            events.Fill(ds2, "Date Info")
    
            'Setting up some variables needed to handle the code in the coming loops
            Dim eventDate As New Date
            Dim multipleEventDate As New Date
            Dim nonEventDate As New Date
            Dim loopCount As New Integer
            loopCount = (ds2.Tables("Date Info").Rows().Count() - 1)
    
    
            'Looping through each row in my Data Set
            For i = 0 To loopCount
                'This code takes a date from a microsoft access database table and displays any events that are listed for a date in a GridView when the date is clicked on in the Calendar
                eventDate = ds.Tables("Date").Rows(i).Item(0)
                If eventDate = selectedDay Then
                    For j = 0 To loopCount
                        multipleEventDate = ds.Tables("Date").Rows(j).Item(0)
                        If i <> j Then
                            If multipleEventDate = eventDate Then
                                Me.EventGridView.DataSource = ds2
                                Me.EventGridView.DataBind()
                            Else
                                ds2.Tables("Date Info").Rows(j).Delete()
                                Me.EventGridView.DataSource = ds2
                                Me.EventGridView.DataBind()
                            End If
                        End If
                    Next j
                End If
                If eventDate <> selectedDay Then
                    ds2.Tables("Date Info").Rows(i).Delete()
                    Me.EventGridView.DataSource = ds2
                    Me.EventGridView.DataBind()
                End If
            Next i
    
            'Same code copy & pasted almost from above but displays the first and last name of people who have birthdays on the day clicked on in the calendar in a separate gridview
            Dim birthDate As New Date
            Dim birthDateDayofYear As New Integer
            Dim multipleBirthDate As New Date
            Dim birthdayLoopCount As New Integer
            birthdayLoopCount = (birthdayDataSet.Tables("Birthday Table").Rows().Count() - 1)
            For i = 0 To birthdayLoopCount
    
                birthDate = birthdayDataSet.Tables("Birthday Table").Rows(i).Item(0)
                birthDateDayofYear = birthDate.DayOfYear
                If birthDateDayofYear = selectedDayofYear Then
                    For j = 0 To birthdayLoopCount
                        multipleBirthDate = birthdayDataSet.Tables("Birthday Table").Rows(j).Item(0)
                        If i <> j Then
                            If multipleBirthDate = birthDate Then
                                Me.BirthdayGridView.DataSource = birthdayNameDataSet
                                Me.BirthdayGridView.DataBind()
                            Else
                                birthdayNameDataSet.Tables("Birthday Name Table").Rows(j).Delete()
                                Me.BirthdayGridView.DataSource = birthdayNameDataSet
                                Me.BirthdayGridView.DataBind()
                            End If
                        End If
                    Next j
                End If
                If birthDateDayofYear <> selectedDayofYear Then
                    birthdayNameDataSet.Tables("Birthday Name Table").Rows(i).Delete()
                    Me.BirthdayGridView.DataSource = birthdayNameDataSet
                    Me.BirthdayGridView.DataBind()
                End If
            Next i
        End Sub


    The way that I've done this is to delete rows from the dataset that hold dates that are not the same as the date that the user clicks on.  But days with multiple events would delete each other, so to fix it I did a nested loop in which I compare each row of the table to every other row in the table, and if one row comes across another row that holds the same date, it'll keep them both.  Is there a way perhaps I can do this without nested loops?  This code is tested and works great, the problem is that I have a huge family, and as events and birthdays are piled in there, this code can be looping thousands of times.  I know this is a big read, and I appreciate your time.


    Notes: There are two Access tables this code reads from, one called Table1 that contains family events with the fields: Date, Event(a description of the event), Location, Time.  The other table is called Addresses and contains family contact information, and has the fields: First Name, Last Name, Address, City, State, Email Address, Home Phone, Cell Phone, Birthday

    Sunday, August 29, 2010 1:51 AM

Answers

  • User-1828494216 posted

    I don't know why you are using so many DataApdapters and then so many DataSets and loops... That is why your app is slow.

    You should do it with one DataAdapter. Dataset can have multiple tables! You are looping trough DataSet.. there are better ways of searching dataset.


    I don't know what to do... i could write all day about this or take an hour and build this app for you (which i would do for some $$$ :D:D:D joke ;) )

    I think the best way for you is to grab some book about coding and some beer and learn some more...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 29, 2010 5:55 PM

All replies

  • User-1828494216 posted

    Hello, I'm fairly new to ASP.net and have been building a webpage for my extended family.  I've never used VB before, and would like help in perhaps creating more system resource efficient code.  So far I've built a calendar page that will display family events and birthdays when a day is clicked on, that is read from Microsoft Access Database tables. 


    Based on bolded text in quote, i would like to provide following link: http://www.obout.com/calendar/calendar_task_list.aspx

    That could solve your issue without much of a code and i think the product is free.

    Sunday, August 29, 2010 11:20 AM
  • User213023536 posted

    Wow that's awesome stuff.  I appreciate it!  Although my other main purpose is to have something on a resume or for potential employers to see what I've created, and I would like to write my own code instead of use something ready-made like that.  I will however, be checking out that source code and see if I might come up with a solution, the speed to which the sample updates when you click on a date is faster than mine and I only have like 5-6 fake events and birthdays in there for testing.  I'll mark this as resolved if I come up with a solution from the source code of that tool.  Again, much appreciated.

    Sunday, August 29, 2010 5:29 PM
  • User-1828494216 posted

    I don't know why you are using so many DataApdapters and then so many DataSets and loops... That is why your app is slow.

    You should do it with one DataAdapter. Dataset can have multiple tables! You are looping trough DataSet.. there are better ways of searching dataset.


    I don't know what to do... i could write all day about this or take an hour and build this app for you (which i would do for some $$$ :D:D:D joke ;) )

    I think the best way for you is to grab some book about coding and some beer and learn some more...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 29, 2010 5:55 PM