GetValue is THAT slow??

[Matt]

Simple example:

Open a connection loop through the data.
When I don't use .GetValues I get super fast speed,
upwards of 30,000 records per second. When use the
..GetValues I only get 5000 records per second.

Why does GetValues slow this down so much? Doesn't
the DataReader object already have the information
in memory? It should take a billionth of a second
to move it into the array...

Anyway to speed this up?


Dim myConnection As New OdbcConnection(myConnString)
Dim myCommand As New OdbcCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim m_dataRead As OdbcDataReader
m_dataRead = myCommand.ExecuteReader()

Dim Elements() As Object
ReDim Elements(20) ' returning 20 fields...


While m_dataRead.Read()


'** WHEN I COMMENT OUT THIS LINE I
'** GET 30000 RECORDS PER SECOND, WHEN
'** I UNCOMMENT THIS LINE AND USE .GetValues
'** I ONLY GET 5000 RECORDS PER SECOND??

m_dataRead.GetValues(Elements)

End While


m_dataRead.Close()
myConnection.Close()


I built a job in DTS using this same ODBC Driver and SQL and
it was able to read the data at about 12000 records
per second - that includes writing it back out! How
is DTS doing it?

Thanks,
Matt

 

[Miha Markic [MVP C#]]

Hi Matt,

Ummm, because it has to create value array and value instances and move
values into them?

 

[Cor Ligthert [MVP]]

Matt,

If it is not for compatible reasons with VB6 you would try to avoid the
Redim. That is copying consequently the existing Array. (It can be useful
for by instance a byteArrays as well).

Use the arraylist for that. That is only adding the rows as objects to that
list.

http://msdn.microsoft.com/library/d...frlrfsystemcollectionsarraylistclasstopic.asp

I hope this helps,

Cor

 

[Frans Bouma [C# MVP]]

Simple example:

Open a connection loop through the data.
When I don't use .GetValues I get super fast speed,
upwards of 30,000 records per second. When use the
.GetValues I only get 5000 records per second.

Why does GetValues slow this down so much? Doesn't
the DataReader object already have the information
in memory? It should take a billionth of a second
to move it into the array...

Anyway to speed this up?

There's no way. If you take a look into the code using reflector,
you'll see it has to do some stuff like converting the original TDS
data to a .NET type, check if it's null etc. This takes time.

DTS is a very low level system, which gets a lot of data throughput by
avoiding lots of things you normally need in an application. Don't
compare the two, as .NET never will win the match with DTS.

FB

--

 

[Matt]

Thanks FB -

There's no way. If you take a look into the code using reflector,
you'll see it has to do some stuff like converting the original TDS
data to a .NET type, check if it's null etc. This takes time.

Makes sense...

DTS is a very low level system, which gets a lot of data throughput by
avoiding lots of things you normally need in an application. Don't
compare the two, as .NET never will win the match with DTS.

That stinks, of course I have people looking at DTS and then my program
and looking at me like an idiot. Are there any low level 3rd party
drivers available I can use to call from .NET that will get this low
level speed?
Or.... what if I accessed the ODBC API directly from .NET? I suppose I
would be doing the same conversion work that System.Data.Odbc is doing,
huh... no sense in re-inventing the wheel.

Well, thanks for the responses, I appreciate your valuable input.

Matt

 

[Matt]

Thanks FB -

There's no way. If you take a look into the code using reflector,
you'll see it has to do some stuff like converting the original TDS
data to a .NET type, check if it's null etc. This takes time.

Makes sense...

DTS is a very low level system, which gets a lot of data throughput by
avoiding lots of things you normally need in an application. Don't
compare the two, as .NET never will win the match with DTS.

That stinks, of course I have people looking at DTS and then my program
and looking at me like an idiot. Are there any low level 3rd party
drivers available I can use to call from .NET that will get this low
level speed?
Or.... what if I accessed the ODBC API directly from .NET? I suppose I
would be doing the same conversion work that System.Data.Odbc is doing,
huh... no sense in re-inventing the wheel.

Well, thanks for the responses, I appreciate your valuable input.

Matt