Adapter Fill method taking too much time.

  • Thread starter Thread starter Joyjit Mukherjee
  • Start date Start date
J

Joyjit Mukherjee

Hi,

For our .NET Windows Forms based distributed application, we are using ODP
..NET (R 9.2.0.4) to connect to Oracle 9i R 2. The query to fetch data from
the table is expected to return around 100, 000 rows. From the UI, we are
connecting to a production box to call a Web Service which, alongwith the
Data Access Layer classes, composes our middle tier. Now the call to Fill
the Dataset using a dataadapter is taking more than 2 minutes to return,
which significantly hurts our performance.

Can any one point out any crack to this viz. using server side API cursors
or the like.

Any help will be greatly appreciated.

Thanks
Joyjit
 
Hi Joyjit,

It is difficult to say from your description where the bottleneck is, but
believe me, Fill is fast.
Are you sure that Fill is the culprit?
Also, retrieving 100k records is quite a lot. Do you have enough memory to
prevent swaping, etc?
 
Hello Miha,

I firmly beleive the bottleneck is in the fill method because as a whole,
the timings for the same query is as follows: -

Tier Operations
Time Taken
UI (Client) Send the request
negligible
Middle Tier (WebService) Send the request to DAL
3 sec.
Middle Tier (DAL) Send the request to Oracle
1 sec.
Middle Tier (DAL) Receive the Sys_RefCursor
147 sec.
Middle Tier (WeB Service) Formatting & send it back to the client
17 sec.
UI (Client) Binds the grid
6 sec.

Also, memory, hardware is state of the art, so that is not causing hitchs, I
think.

regards
Joyjit



Miha Markic said:
Hi Joyjit,

It is difficult to say from your description where the bottleneck is, but
believe me, Fill is fast.
Are you sure that Fill is the culprit?
Also, retrieving 100k records is quite a lot. Do you have enough memory to
prevent swaping, etc?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Joyjit Mukherjee said:
Hi,

For our .NET Windows Forms based distributed application, we are using ODP
.NET (R 9.2.0.4) to connect to Oracle 9i R 2. The query to fetch data from
the table is expected to return around 100, 000 rows. From the UI, we are
connecting to a production box to call a Web Service which, alongwith the
Data Access Layer classes, composes our middle tier. Now the call to Fill
the Dataset using a dataadapter is taking more than 2 minutes to return,
which significantly hurts our performance.

Can any one point out any crack to this viz. using server side API cursors
or the like.

Any help will be greatly appreciated.

Thanks
Joyjit
Click to expand...
Click to expand...
 
Hi,

Again, it is not clear what is taking that much time (which method that is).
Did you try using a profiler?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Joyjit Mukherjee said:
Hello Miha,

I firmly beleive the bottleneck is in the fill method because as a whole,
the timings for the same query is as follows: -

Tier Operations
Time Taken
UI (Client) Send the request
negligible
Middle Tier (WebService) Send the request to DAL
3 sec.
Middle Tier (DAL) Send the request to Oracle
1 sec.
Middle Tier (DAL) Receive the Sys_RefCursor
147 sec.
Middle Tier (WeB Service) Formatting & send it back to the
client
17 sec.
UI (Client) Binds the grid
6 sec.

Also, memory, hardware is state of the art, so that is not causing hitchs,
I
think.

regards
Joyjit



Miha Markic said:
Hi Joyjit,

It is difficult to say from your description where the bottleneck is, but
believe me, Fill is fast.
Are you sure that Fill is the culprit?
Also, retrieving 100k records is quite a lot. Do you have enough memory
to
prevent swaping, etc?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Joyjit Mukherjee said:
Hi,

For our .NET Windows Forms based distributed application, we are using ODP
.NET (R 9.2.0.4) to connect to Oracle 9i R 2. The query to fetch data from
the table is expected to return around 100, 000 rows. From the UI, we are
connecting to a production box to call a Web Service which, alongwith the
Data Access Layer classes, composes our middle tier. Now the call to Fill
the Dataset using a dataadapter is taking more than 2 minutes to
return,
which significantly hurts our performance.

Can any one point out any crack to this viz. using server side API cursors
or the like.

Any help will be greatly appreciated.

Thanks
Joyjit
Click to expand...
Click to expand...
Click to expand...
 
The call to the DB from the DAL which fetches the Sys_RefCursor is taking
around 147 seconds. Rest of the method calls are OK.

regards
Joyjit


Miha Markic said:
Hi,

Again, it is not clear what is taking that much time (which method that is).
Did you try using a profiler?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Joyjit Mukherjee said:
Hello Miha,

I firmly beleive the bottleneck is in the fill method because as a whole,
the timings for the same query is as follows: -

Tier Operations
Time Taken
UI (Client) Send the request
negligible
Middle Tier (WebService) Send the request to DAL
3 sec.
Middle Tier (DAL) Send the request to Oracle
1 sec.
Middle Tier (DAL) Receive the Sys_RefCursor
147 sec.
Middle Tier (WeB Service) Formatting & send it back to the
client
17 sec.
UI (Client) Binds the grid
6 sec.

Also, memory, hardware is state of the art, so that is not causing hitchs,
I
think.

regards
Joyjit



Miha Markic said:
Hi Joyjit,

It is difficult to say from your description where the bottleneck is, but
believe me, Fill is fast.
Are you sure that Fill is the culprit?
Also, retrieving 100k records is quite a lot. Do you have enough memory
to
prevent swaping, etc?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Hi,

For our .NET Windows Forms based distributed application, we are
Click to expand...
using
ODP
.NET (R 9.2.0.4) to connect to Oracle 9i R 2. The query to fetch data from
the table is expected to return around 100, 000 rows. From the UI, we are
connecting to a production box to call a Web Service which, alongwith the
Data Access Layer classes, composes our middle tier. Now the call to Fill
the Dataset using a dataadapter is taking more than 2 minutes to
return,
which significantly hurts our performance.

Can any one point out any crack to this viz. using server side API cursors
or the like.

Any help will be greatly appreciated.

Thanks
Joyjit
Click to expand...
Click to expand...
Click to expand...
 
Any help will be greatly appreciated.
Click to expand...

Don't forget that controls that host the 100,000 records on the UI can
affect your application performance too.
BTW, who has the time and power to view all these 100,000 records ? I would
use paging method instead.

John
 
Back
Top