Jump to content
Xtreme .Net Talk

help me to find a way to retrieve data partially without re-execution of query


Recommended Posts

Guest mkharati
Posted

I am using Visual Basic.NET for programming.

Database is Oracle 9i Enterprise Edition Release 9.0.1.1.1

Platform is Windows XP Professional.

 

I am going to execute an Oracle Query (or Stored Procedure) from my VB.NET environment.

This query returns 1,000,000 records and takes lots of time to be executed.

 

I found a way in .NET to fill DataSet with specific number of records. For Example I can show 100 records from position 10 to position 110 by this code:

MyDataAdapter.Fill (MyDataset, 10, 100,"TEST")

 

But my problem happens when I want to show 100 records from position 900,000 to position 900,100 by this code:

MyDataAdapter.Fill (MyDataset, 900000, 100,"TEST")

This line takes lots of times to be executed.

I think each time I run the above line in VB.NET, the query executes once again. And this is not what I expect.

 

Besides I used Microsoft.NET Oracle Client too, but still have problem.

 

Would you please kindly help me to find a way to retrieve data partially without re-execution of query?

 

Thanks for co-operation in advance.

  • 1 month later...
Posted
Do you really need to return all million records? Or is there some distinguishing characteristic that you can use to limit the amount of records. i.e. is there a indexed field that is sequenced, for instance? Or a date field that could be used to break the dataset into smaller chunks. If this is something that needs to be done on a regular basis, it would be worth your while to add an indexed field, auto filled from a sequence for this very purpose. Then, you can build a stored procedure in Oracle to return only the data you request. And, being indexed, the query wouldn't take long at all, whether the records were from the first 100 records, or the last.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...