Performance problems with OracleDataReader

[Henning Fiese]

Hello NG,

I'm using the Oracle Data Access Components and I've encountered
serious performance problems with the OracleDataReader. The following
function fills the memberfields of an object with the content of a
reader:


internal void FillFromReader(IDataReader Reader)
{
m_id = (int)Reader["id"];
m_name = (string)Reader["name"];
// ... ~ 20 more lines of this...
}

Note that the call to Reader.Read() is made outside the function. With
the OracleDataReader this function needs an average of 2000 ticks to
complete. With the SQLDataReader, it needs only about 150 ticks.

What am I doing wrong? Is this a problem with the configuration of the
client or the dbms? Or is there a better way to implement
FillFromReader()?

Cheers
Henning

 

[Guest]

Henning,

If performance is your number 1 criteria for the code, then you might want
to try using ordinal numbers to access the fields in the reader, instead of
field names.

Kerry Moorman

 

[William \(Bill\) Vaughn]

If the purpose of the code is to build a DataTable, then use DataTable.Load
method.
Yes, referencing the columns by ordinal is far faster than using strings.
You might also investigate other data providers. Data Direct has a
well-respected provider that's supposed to be faster.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Henning Fiese]

Hi Bill,

thanks for your answer. Im not interested in using DataTables, i just
want to copy the contents of the reader to the corresponding member
fields of the objects. Using the OracleDataReader's int-indexer
instead of the string-indexer doesnt't seem to affect performance at
all.

Im just wondering why the access of the OracleDateReader's indexer is
so slow. The actual transfer of the data (from the db to the client)
is performed within the Read()-method, isn't it? What is happening
within those indexers that is so time-consuming?

Best regards
Henning

If the purpose of the code is to build a DataTable, then use DataTable.Load
method.
Yes, referencing the columns by ordinal is far faster than using strings.
You might also investigate other data providers. Data Direct has a
well-respected provider that's supposed to be faster.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speakerwww.betav.com/blog/billvawww.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visitwww.hitchhikerguides.netto get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hello NG,

I'm using the Oracle Data Access Components and I've encountered
serious performance problems with the OracleDataReader. The following
function fills the memberfields of an object with the content of a
reader:

internal void FillFromReader(IDataReader Reader)
{
m_id = (int)Reader["id"];
m_name = (string)Reader["name"];
// ... ~ 20 more lines of this...
}

Note that the call to Reader.Read() is made outside the function. With
the OracleDataReader this function needs an average of 2000 ticks to
complete. With the SQLDataReader, it needs only about 150 ticks.

What am I doing wrong? Is this a problem with the configuration of the
client or the dbms? Or is there a better way to implement
FillFromReader()?

Cheers
Henning

 

[William \(Bill\) Vaughn]

It has to do with how the data in each row is addressed. If you use an
ordinal, the addressing is simple to resolve. If not the class has to search
the list of columns (each time) and determine if the string provided is a
match. It can take 10 times longer to resolve using strings.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill,

thanks for your answer. Im not interested in using DataTables, i just
want to copy the contents of the reader to the corresponding member
fields of the objects. Using the OracleDataReader's int-indexer
instead of the string-indexer doesnt't seem to affect performance at
all.

Im just wondering why the access of the OracleDateReader's indexer is
so slow. The actual transfer of the data (from the db to the client)
is performed within the Read()-method, isn't it? What is happening
within those indexers that is so time-consuming?

Best regards
Henning

If the purpose of the code is to build a DataTable, then use
DataTable.Load
method.
Yes, referencing the columns by ordinal is far faster than using strings.
You might also investigate other data providers. Data Direct has a
well-respected provider that's supposed to be faster.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speakerwww.betav.com/blog/billvawww.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visitwww.hitchhikerguides.netto get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hello NG,

I'm using the Oracle Data Access Components and I've encountered
serious performance problems with the OracleDataReader. The following
function fills the memberfields of an object with the content of a
reader:

internal void FillFromReader(IDataReader Reader)
{
m_id = (int)Reader["id"];
m_name = (string)Reader["name"];
// ... ~ 20 more lines of this...
}

Note that the call to Reader.Read() is made outside the function. With
the OracleDataReader this function needs an average of 2000 ticks to
complete. With the SQLDataReader, it needs only about 150 ticks.

What am I doing wrong? Is this a problem with the configuration of the
client or the dbms? Or is there a better way to implement
FillFromReader()?

Cheers
Henning

 

[Henning Fiese]

But as I pointed out, it makes no difference which indexer I use, they
are both slow. Besides, mapping a string to an index is a task which
can be performed quite fast, so this shouldn't be the bottleneck.

It has to do with how the data in each row is addressed. If you use an
ordinal, the addressing is simple to resolve. If not the class has to search
the list of columns (each time) and determine if the string provided is a
match. It can take 10 times longer to resolve using strings.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speakerwww.betav.com/blog/billvawww.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visitwww.hitchhikerguides.netto get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill,

thanks for your answer. Im not interested in using DataTables, i just
want to copy the contents of the reader to the corresponding member
fields of the objects. Using the OracleDataReader's int-indexer
instead of the string-indexer doesnt't seem to affect performance at
all.

Im just wondering why the access of the OracleDateReader's indexer is
so slow. The actual transfer of the data (from the db to the client)
is performed within the Read()-method, isn't it? What is happening
within those indexers that is so time-consuming?

Best regards
Henning

If the purpose of the code is to build a DataTable, then use
DataTable.Load
method.
Yes, referencing the columns by ordinal is far faster than using strings.
You might also investigate other data providers. Data Direct has a
well-respected provider that's supposed to be faster.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speakerwww.betav.com/blog/billvawww.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visitwww.hitchhikerguides.nettoget more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hello NG,
I'm using the Oracle Data Access Components and I've encountered
serious performance problems with the OracleDataReader. The following
function fills the memberfields of an object with the content of a
reader:
internal void FillFromReader(IDataReader Reader)
{
m_id = (int)Reader["id"];
m_name = (string)Reader["name"];
// ... ~ 20 more lines of this...
}
Note that the call to Reader.Read() is made outside the function. With
the OracleDataReader this function needs an average of 2000 ticks to
complete. With the SQLDataReader, it needs only about 150 ticks.
What am I doing wrong? Is this a problem with the configuration of the
client or the dbms? Or is there a better way to implement
FillFromReader()?
Cheers
Henning

 

[sherifffruitfly]

Hello NG,

I'm using the Oracle Data Access Components and I've encountered
serious performance problems with the OracleDataReader. The following
function fills the memberfields of an object with the content of a
reader:

internal void FillFromReader(IDataReader Reader)
{
m_id = (int)Reader["id"];
m_name = (string)Reader["name"];
// ... ~ 20 more lines of this...

}

Note that the call to Reader.Read() is made outside the function. With
the OracleDataReader this function needs an average of 2000 ticks to
complete. With the SQLDataReader, it needs only about 150 ticks.

What am I doing wrong? Is this a problem with the configuration of the
client or the dbms? Or is there a better way to implement
FillFromReader()?

Cheers
Henning

Have you tried Oracle's .net provider?

http://www.oracle.com/technology/tech/windows/odpnet/ODP.NET-FOV.html

 

[Charles Zhang]

Find out which areas of your code are the most time-consuming.

If you have over 5000 or so records to fill, you might want to use use
ordinal (0, 1, 2, ... ) instead of names.

Also, you can try to use "GetValues" method to retrieve a row.

Charles Zhang
http://www.speedydb.com
(SpeedyDB ADO.NET Provider is the fastest, most secure ADO.NET Provider
over Wide Area Network)

Hello NG,

I'm using the Oracle Data Access Components and I've encountered
serious performance problems with the OracleDataReader. The following
function fills the memberfields of an object with the content of a
reader:

internal void FillFromReader(IDataReader Reader)
{
m_id = (int)Reader["id"];
m_name = (string)Reader["name"];
// ... ~ 20 more lines of this...

}

Note that the call to Reader.Read() is made outside the function. With
the OracleDataReader this function needs an average of 2000 ticks to
complete. With the SQLDataReader, it needs only about 150 ticks.

What am I doing wrong? Is this a problem with the configuration of the
client or the dbms? Or is there a better way to implement
FillFromReader()?

Cheers
Henning

Have you tried Oracle's .net provider?

http://www.oracle.com/technology/tech/windows/odpnet/ODP.NET-FOV.html

 

[Charles Zhang]

Find out which areas of your code are the most time-consuming.

If you have over 5000 or so records to fill, you might want to use use
ordinal (0, 1, 2, ... ) instead of names.

Also, you can try to use "GetValues" method to retrieve a row.

Charles Zhang
http://www.speedydb.com
(SpeedyDB ADO.NET Provider is the fastest, most secure ADO.NET Provider
over Wide Area Network)

Hello NG,

I'm using the Oracle Data Access Components and I've encountered
serious performance problems with the OracleDataReader. The following
function fills the memberfields of an object with the content of a
reader:

internal void FillFromReader(IDataReader Reader)
{
m_id = (int)Reader["id"];
m_name = (string)Reader["name"];
// ... ~ 20 more lines of this...

}

Note that the call to Reader.Read() is made outside the function. With
the OracleDataReader this function needs an average of 2000 ticks to
complete. With the SQLDataReader, it needs only about 150 ticks.

What am I doing wrong? Is this a problem with the configuration of the
client or the dbms? Or is there a better way to implement
FillFromReader()?

Cheers
Henning

Have you tried Oracle's .net provider?

http://www.oracle.com/technology/tech/windows/odpnet/ODP.NET-FOV.html