Too much data problem

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
moriay
Posts: 6
Joined: Thu 18 Dec 2008 11:09
Contact:

Too much data problem

Post by moriay » Thu 18 Dec 2008 11:27

Hi,

I have a website in dot.net 2.0 + DB Oracle.
I am dealing with loading a huge amount of data (select of 100, 000 and more).
The problem is that it's taking too much time.
Is there any built-in way (in Oracle) to define amount of rows to load at a time ? or some special procedure?

Thanks,
Moria

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 18 Dec 2008 14:27

The common way to solve such a problem is to implement manual paging.
Here is the query for retreiving the rows from DEPT table having row number between p1 and p2:

Code: Select all

SELECT deptno, dname, loc FROM (SELECT deptno, dname, loc, ROW_NUMBER() OVER (ORDER BY deptno) R FROM dept)  WHERE R BETWEEN :p1 and :p2;
Let me know if this answers your question.

moriay
Posts: 6
Joined: Thu 18 Dec 2008 11:09
Contact:

Post by moriay » Sun 21 Dec 2008 09:16

Hi,
Thanks for the reply.
I know the manual paging, but I am looking for a built-in way.
I know it's exist in Sql Server, So I assume it's exist also in Oracle.

By the way, what is the command "over" ?

Thanks a lot!

Moria

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Mon 22 Dec 2008 10:01

Do you use OracleDataSource component? It has built-in paging support, just like SqlDataSource does.

moriay
Posts: 6
Joined: Thu 18 Dec 2008 11:09
Contact:

Post by moriay » Tue 23 Dec 2008 08:14

No, I use OracleConnection.
But I didn't find OracleDataSource in the toolbox,
to use instead the ObjectDataSource ?

Thanks!

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 23 Dec 2008 09:50

Could you please tell us the edition of dotConnect for Oracle you are using? If the edition is professional (or professional trial), add the OracleDataSource component in your toolbox manually (right click in Toolbox | Choose Items).

moriay
Posts: 6
Joined: Thu 18 Dec 2008 11:09
Contact:

Post by moriay » Tue 23 Dec 2008 13:25

where can I check the editor of dotConnect ?
I use simply OracleConnection, OracleCommand and OracleDataAdapter.
By the way, it does not exist also in the 'Choose Items' dialog box.
Thanks!

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 23 Dec 2008 15:09

Please go to the Visual Studio Tools | Oracle | About dotConnect for Oracle. If you don't have the mentioned menu item in your Visual Studio, you are using the Express edition.

moriay
Posts: 6
Joined: Thu 18 Dec 2008 11:09
Contact:

Post by moriay » Wed 24 Dec 2008 08:10

Hi,
I am sorry, It was a misunderstanding.
I thought the forum has general help for Oracle, because I do'nt have your product 'dotConnect for Oracle'.
But does your product has a solution for my problem ?

Thank you very much,
Moria

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 24 Dec 2008 09:26

Yes, it does. The OracleDataSource component is available in design time in an ASP.NET web application, and it has built-in paging support. Please try using dotConnect for Oracle 5.00 Professional Trial (free use during 30 days).
You can download it here: http://www.devart.com/dotconnect/oracle/download.html

moriay
Posts: 6
Joined: Thu 18 Dec 2008 11:09
Contact:

Post by moriay » Thu 25 Dec 2008 09:07

Thank you very much!
I will try it.

Post Reply