Performance issue with ODP.NET and FillSchema

[David F.]

We are using ODP.NET version 9.2.0.401 with the Oracle 9i Client
Release (9.2.0.1) and the database server is Oracle 8i.
After establishing the connection to the database we use the
FillSchema method to populate a DataSet and takes more than 20 seconds
to complete the execution. (We are using a dummy table with 50
records).

Here is the simplified code used for the test:
try
{
OracleDataAdapter da;
OracleConnection conn = new OracleConnection("User
Id=xxx;Password=xxx;Data source=ORASERVER;pooling=true");
conn.Open();
OracleCommand cmd = new OracleCommand("Select * from Orders", conn);
DataSet ds = new DataSet();

da = new OracleDataAdapter(cmd);
da.FillSchema(ds, SchemaType.Mapped, "dept");
conn.Close();
}
catch(Exception ex)
{
System.Console.WriteLine(ex.ToString());
}


If we run the same code against an Oracle 9i the response time is
acceptable.

Has anybody goy any idea about this issue?

Thanks a lot.

 

[Miha Markic [MVP C#]]

Hi David,

While I don't know the answer, you might use strong typed datasets instead
(you create them at design time, by using wizard perhaps).
So, no runtime penality will ever occur.

 

[Frans Bouma [C# MVP]]

We are using ODP.NET version 9.2.0.401 with the Oracle 9i Client
Release (9.2.0.1) and the database server is Oracle 8i.
After establishing the connection to the database we use the
FillSchema method to populate a DataSet and takes more than 20 seconds
to complete the execution. (We are using a dummy table with 50
records).

Here is the simplified code used for the test:
try
{
OracleDataAdapter da;
OracleConnection conn = new OracleConnection("User
Id=xxx;Password=xxx;Data source=ORASERVER;pooling=true");
conn.Open();
OracleCommand cmd = new OracleCommand("Select * from Orders", conn);
DataSet ds = new DataSet();

da = new OracleDataAdapter(cmd);
da.FillSchema(ds, SchemaType.Mapped, "dept");
conn.Close();
}
catch(Exception ex)
{
System.Console.WriteLine(ex.ToString());
}


If we run the same code against an Oracle 9i the response time is
acceptable.

Has anybody goy any idea about this issue?

FillSchema reads metadata from the ALL_* views if I'm not mistaken. THese
are very big on oracle systems with lots of schemas and objects. If you want
to test the performance of these views, try:

SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='Orders'. I'm pretty sure it
is as slow as your query

Frans.

 

[David F.]

Thanks to Frans and Miha for yours inmediate answer to my problem.

Unfortunately, I cannot use strong typed dataset because specific
development requirements, I need to create datasets in runtime.

Frans, I tried that query
SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='Orders'

and Oracle performance is very nice.

In addition, I tried FillSchema using System.Data.OracleClient instead
ODP.NET and performance is VERY nice!!!

Is it possible that ODP.NET has compatibility issues with Oracle 8i???

 

[Frans Bouma [C# MVP]]

Thanks to Frans and Miha for yours inmediate answer to my problem.

Unfortunately, I cannot use strong typed dataset because specific
development requirements, I need to create datasets in runtime.

Frans, I tried that query
SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='Orders'

and Oracle performance is very nice.

In addition, I tried FillSchema using System.Data.OracleClient instead
ODP.NET and performance is VERY nice!!!

Is it possible that ODP.NET has compatibility issues with Oracle 8i???

I don't know, have you checked the oracle forums for issues like this?

Frans.

 

[Miha Markic [MVP C#]]

Hi David,

Thanks to Frans and Miha for yours inmediate answer to my problem.

Unfortunately, I cannot use strong typed dataset because specific
development requirements, I need to create datasets in runtime.
Ok.

Frans, I tried that query
SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='Orders'

and Oracle performance is very nice.

In addition, I tried FillSchema using System.Data.OracleClient instead
ODP.NET and performance is VERY nice!!!

Is it possible that ODP.NET has compatibility issues with Oracle 8i???

Certainly it is possible. As Frans suggested you might search for an answer
in oracle ngs or forums.

 

[David F.]

Oks... I just posted this issue in Oracle Forums. I'll tell you when I
have an answer.

Thanks a lot to both of you!