Question fast retrieve of data from ms access using datareader

[darkcat02]

anyone knows how to retrieve data faster from ms access using datareader?? in just a split second? because im not satisfied with the time it loads... and also it took a lot of memory/cpu usage in my pc just to load it...

 

[kulrom]

Of course DataReader is the best choice when retrieving large amounts of data because the data is not cached in memory.

I guess your problem arises when you populate the control(s) on the form so please post the code or explain what exactly you are doing. Thanks

 

[darkcat02]

Dim level As Integer = e.Node.Level

Dim dbFile As String
dbFile = strBibleDataPath & strBibleFilename

Dim cnnBL As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" _
& dbFile & ";Mode=Read;Jet OLEDBatabase Password=" & strBiblePw(strBibleID))

If level = 1 Then 'BookChapter Populate

ElseIf level = 2 Then 'BookVerse Populate
'Reset Labels
LabelChapterVerse.Text = "" 'Chapter & Verse
LabelNarration.Text = "" 'Narration

cnnBL.Open()

''When Select Chapter Only POpulate All Verses in Chapter
Dim cmdTest As String
cmdTest = "SELECT Chapter, Verse, Narration FROM BibleVerses Where BookNum = " _
& LabelBookNum.Text & " AND Chapter = " & LabelBookChapter.Text & " ORDER BY Verse"

Dim BLCommandTst As New OleDb.OleDbCommand(cmdTest, cnnBL)
Dim BLReaderTst As OleDb.OleDbDataReader
BLReaderTst = BLCommandTst.ExecuteReader()
If BLReaderTst.Read() Then
Do While BLReaderTst.Read()
Dim verse, narration As String
verse = BLReaderTst("Verse")
narration = BLReaderTst("Narration")
LabelNarration.Text &= verse & " "
LabelNarration.Text &= narration & vbCrLf & vbCrLf
Loop
Else
LabelNarration.Text = "There are no data."
End If

LabelChapterVerse.Text = "Chapter " & LabelBookChapter.Text

''When Select Chapter and Verse
Dim BLReader As OleDb.OleDbDataReader

Dim cmdBibleChapter As String = "SELECT Verse, Chapter, BookNum FROM BibleVerses Where BookNum = " _
& LabelBookNum.Text & " AND Chapter = " & LabelBookChapter.Text & " ORDER BY Verse"
Dim BLCommand As New OleDb.OleDbCommand(cmdBibleChapter, cnnBL)

BLReader = BLCommand.ExecuteReader()

While BLReader.Read()
Dim BookName As New TreeNode()
BookName.Text = "Verse " & BLReader("Verse")
BookName.Tag = BLReader("Chapter")
e.Node.Nodes.Add(BookName)
End While

BLCommand.Dispose()
cnnBL.Close()

e.Node.Expand()
End If

 

[cjard]

Please read this: Visual Basic .NET Forums - BB Code List and edit your post to use CODE tags

Youre doing your data access a very old way; See the DW2 link in my signature, section Creating a Simple Data App. Then read the Creating a Form to Search Data section. Also google for Forms Over Data Video

Lastly, youre searching by certain columns; are they indexed?

 

[darkcat02]

i am using access database. it was an old system which im currently updating and adding some new functions... it was requested that the codes should still remain like the other code on how it was coded... so i have no choice...

im going to check tomorrow if it was indexed...