GetValue is THAT slow??

  • Thread starter Thread starter Matt
  • Start date Start date
M

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
 
Hi Matt,

Ummm, because it has to create value array and value instances and move
values into them?
 
Matt said:
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

--
 
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
 
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
 
Back
Top