populate textbox/label faster

[darkcat02]

hi everyone,

i would like to ask how am i be able to populate those controls faster... i already put an index to the table.. and yet... my program hangs up for a little while.. it takes about 10 seconds or so... and sometimes... it takes too much of my cpu usage... i use OleDbDataReader to get the data...

here is an example of my code...

Dim cmdBookInfo As String = "SELECT BV.BookNum, BV.Chapter, BV.Verse, BV.Narration, " & _
            "BB.BookName FROM BibleVerses BV, BibleBooks BB " & _
            "Where BV.BookNum = " & BookNumTag & _
            " AND BV.BookNum = BB.BookNum  ORDER By Chapter, Verse"

            cnnBL.Open()

            Dim WCReader As OleDb.OleDbDataReader
            Dim WCCommand As New OleDb.OleDbCommand(cmdBookInfo, cnnBL)
            WCReader = WCCommand.ExecuteReader()

            If WCReader.HasRows Then
                Do While WCReader.Read()
                    Dim chapter, verse, narration, nr As String
                    chapter = WCReader("Chapter")
                    verse = WCReader("Verse")
                    narration = WCReader("Narration")
                    nr = "CHAPTER"
                    If chapter = 0 Then
                        If verse = 0 Then
                            LabelNarration.Text &= ""
                        End If
                    Else
                        If verse = 0 And narration.ToLower.Contains(nr.ToLower) Then
                            LabelNarration.Text &= ""
                        ElseIf verse = 1 Then
                            LabelNarration.Text &= nr & " " & chapter & vbCrLf & vbCrLf
                            LabelNarration.Text &= verse & " "
                            LabelNarration.Text &= narration & vbCrLf & vbCrLf
                        Else
                            LabelNarration.Text &= verse & " "
                            LabelNarration.Text &= narration & vbCrLf & vbCrLf
                        End If
                    End If
                Loop
            Else
                LabelNarration.Text = "There are no data."
            End If

Dim cmdBibleInfo As String = "SELECT BV.BookNum, BV.Chapter, BV.Verse, BV.Narration, " & _
            "BB.BookName FROM BibleVerses BV, BibleBooks BB " & _
            "Where BV.BookNum = " & BookNumTag & _
            " AND BV.BookNum = BB.BookNum  ORDER By Chapter, Verse"

            Dim InfoReader As OleDb.OleDbDataReader
            Dim InfoCommand As New OleDb.OleDbCommand(cmdBibleInfo, cnnBL)
            InfoReader = InfoCommand.ExecuteReader()
            If InfoReader.HasRows Then
                While InfoReader.Read()
                    LabelBookName.Text = InfoReader.GetValue(InfoReader.GetOrdinal("BookName")).ToString() ' BookNum

                    Dim Chapter, Verse As Integer
                    Chapter = InfoReader.GetValue(InfoReader.GetOrdinal("Chapter")).ToString() ' Chapter
                    Verse = InfoReader.GetValue(InfoReader.GetOrdinal("Verse")).ToString() ' Verse
                    LabelChapterVerse.Text = " "

                    BookNumName = "Book " & BookNumTag & " : " & LabelBookName.Text
                End While
            Else
                'MsgBox("No Data")
            End If
            InfoCommand.Dispose()

            WCCommand.Dispose()
            cnnBL.Close()

i populate the textbox and the treeview at the same time when i click the button... any ideas

 

[picoflop]

cjard would suggest to click the link "datawalkthrough 2.0" in his signature

anyway: One thing is, that you should not construct long strings with "&". Strings are immutable in .Net and so everytime you add something a new instance is created, the old one dropped and so on. The longer your string gets, the more times it consumes.

Use System.Text.StringBuilder to construct the string and when you're done, pass it to the control.text you want to set.

and if you have any time consuming operation it might be worth to check what system.componentmodel.backgroundworker can do for you

 

[darkcat02]

cjard would suggest to click the link "datawalkthrough 2.0" in his signature

anyway: One thing is, that you should not construct long strings with "&". Strings are immutable in .Net and so everytime you add something a new instance is created, the old one dropped and so on. The longer your string gets, the more times it consumes.

Use System.Text.StringBuilder to construct the string and when you're done, pass it to the control.text you want to set.

and if you have any time consuming operation it might be worth to check what system.componentmodel.backgroundworker can do for you

thanks ^_^ stringbuilder is the answer.. hope it doesnt have errors on the future... ^^

 

[cjard]

cjard would suggest to click the link "datawalkthrough 2.0" in his signature

Indeed, though it might not solve the problem here. It is likely that a search is being made of a field that is not indexed or the search cannot be optimized by use of an index so the whole database is being scanned

anyway: One thing is, that you should not construct long strings with "&". Strings are immutable in .Net and so everytime you add something a new instance is created, the old one dropped and so on. The longer your string gets, the more times it consumes.

True, though if the results are only a few rows, it is unlikely to cause a 10 second delay. A recent discussion on the topic has lead to me revising my stringbuilder advice: if 2 or 3 strings are to be concatenated it is faster to concatenate them (in one go). If many tens or hundreds are to be done, then use a stringbuilder.

 

[picoflop]

though if the results are only a few rows, it is unlikely to cause a 10 second delay

I'm not a big bible expert, but I assume that his select command (shown in the first line) should select quite some "chapters" and "verses" - probably some kind of TOC of the selected "book". Then all that stuff is conc'ed into a single label-control.
Therefore I would also assume that not using a label but a datagridview and doing the thing with a dataadapter would problably solve the problem too. Since I'm also not an expert in that, I ref'ed your link