Speeding up Read only data access?

[mikezcg]

i am trying to speed up my code. i have commented old parts out, but
leave them there for information. I have tried a data adapter and
that is slower. The process is taking 10seconds, i would like to
atleast half it any ideas? :

Private Function CreateTblFromDR(ByVal dr As OleDbDataReader, ByVal
dt As DataTable) As DataTable ', ByVal overloadParam As String
Dim drow As DataRow
Dim Index As Integer
'Dim i, CkNo, InvDate, Deal, InvNo, InvAmt, ref, comment As
Integer
'InvDate = 0 InvAmt = 1 Deal = 2 InvNo = 3 ref = 4 CkNo = 5
comment = 6
dt.Clear()
Dim counter As Integer = dr.FieldCount - 1
Dim aobjFields(counter) As Object

Do While dr.Read
drow = dt.NewRow

dr.GetValues(aobjFields)
For Index = 0 To counter
On Error Resume Next
drow(Index) = CStr(aobjFields(Index))
Next
dt.Rows.Add(drow)

On Error GoTo 0
Loop
Return dt
End Function

 

[Jon Skeet [C# MVP]]

i am trying to speed up my code. i have commented old parts out, but
leave them there for information. I have tried a data adapter and
that is slower. The process is taking 10seconds, i would like to
atleast half it any ideas? :

Well, you're currently copying to an array and then copying the
contents of the array. Any reason you don't just call

For Index = 0 to counter
drow(Index) = dr.GetString(index)
Next

?

 

[Cor]

Hi Miezcg

You use very old style of VB.net coding.

Therefore I am not sure if your dataadapter approach was that what it could
have been.

May be first see that code before we start at this?

The approach from the datareader should normaly not be faster (in a way you
could see it)

Cor

 

[mikezcg]

the reason i copy from the datareader to the array is becuase its
faster to iterate through an array than it is through the fields of
the datareader. I was using the index version that you posted and
the array is faster.

I will post the da code.

 

[mikezcg]

What is old style vb.net coding? how am i doing it in the code i
posted?


Here is my da version its about 30% slower than my dr code

Private Sub fillDG(ByVal sql As String)
Try
da = New OleDbDataAdapter(sql, code.scn)

If Not ds.Tables("tblEmpExps") Is Nothing Then
ds.Tables("tblEmpExps").Clear()
' start = Date.Now
da.Fill(ds, "tblEmpExps")
dg.DataSource = ds.Tables("tblEmpExps")

Catch ex As Exception
MsgBox(ex.Message)
Finally
If Not da Is Nothing Then da.Dispose()
End Try
End Sub

 

[Cor]

Hi Mike,

In this code you use Try, catch and endTry blocks, not in the other one,

Where I was intrested in was in your Select clause, often people do it with
something as

Select * from Datatable

If they just would do
Select myfield1, myfield2 from Datatable Where myfield1 = @mytestfield"

Than it probably would go a lot faster.

I changed also a little bit your code

I hope this gives you the idea why I wrote it?

Cor

 

[mikezcg]

the reason i used try catch in this one and not the other is that try
catch is more expensive. if the datareader is passed to the function
the only issue could be a null being read. So i use on error, its
lighter than try catch and im really not doing error handling..



I will look at your code later,, but you were on the mark with the
sql!!

I was using "select person from .... where person like " &
cboEmp.text

NO WILD CARD WITH MY LIKE!~!! IT killed me.

I am now using ="&CboEmp.text


1 second data retrieval


HAPPY GUY NOW!!!!!!!!! YEAH BABY!!

 

[Jon Skeet [C# MVP]]

the reason i used try catch in this one and not the other is that try
catch is more expensive. if the datareader is passed to the function
the only issue could be a null being read. So i use on error, its
lighter than try catch and im really not doing error handling..

Are you *sure* "on error" is lighter? It looks to me as if it's
implemented with a try/catch anyway, but with *extra* code to simulate
VB error handling.

It's not actually *catching* exceptions which is expensive - it's
creating and throwing them in the first place.

I'm not a VB.NET expert, so don't necessarily take my word for it, but
I'd be very surprised to find "on error" speeding things up.