Performance issue with ODP.NET and FillSchema

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
Wed Apr 28 07:54:34 CDT 2004

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.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"David F." <davo_java@hotmail.com> wrote in message
news:18cd3022.0404280447.d18537b@posting.google.com...
> 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.

Frans
Wed Apr 28 10:04:37 CDT 2004

David F. wrote:

> 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.

--
Get LLBLGen Pro, the new O/R mapper for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP

davo_java
Thu Apr 29 08:20:46 CDT 2004

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
Thu Apr 29 12:11:42 CDT 2004

David F. wrote:

> 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.


--
Get LLBLGen Pro, the new O/R mapper for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP

Miha
Fri Apr 30 06:58:50 CDT 2004

Hi David,

"David F." <davo_java@hotmail.com> wrote in message
news:18cd3022.0404290520.35f18cd1@posting.google.com...
> 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.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

davo_java
Fri Apr 30 12:35:28 CDT 2004

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!