0

I'm writing a C# application that should run an Oracle-Select query and perform some calculations for each line.

The select query is very big and takes a long time.

In the current application design, I should wait until the query finishes retrieving all the data from the database in order to start the required computations on each row.

I was wondering if there is a way to get the first query results as the database engine find them.

Means that : Instead of waiting for the database engine to find all the rows that correspond to my query and return them, get the result since the first row found by the database engine.

At the end the computation required for each line will start as long as the first line found in the database and hence the total run time will be less.

The idea here is not about how to speed up an Oracle query or adding any index. It's more about getting overlapping computations to optimize more the computations.

Sorry if it's a dump question and thank you in advance.

I'm using Oracle 11g and the Query may just be as simple as (but returns hundreds of thousands of rows)

Select * from Table Where Condition1;

I run the explain plan for my query :

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   251 |   122K|   656K  (1)| 00:07:40 |
|*  1 |  TABLE ACCESS FULL| TABLE1  |   251 |   122K|   656K  (1)| 00:07:40 |
-----------------------------------------------------------------------------
Thomas Carlton
  • 5,344
  • 10
  • 63
  • 126
  • 1
    Mostly ... Hmm.. Almost always, the slowness happens in background processs on fetching the rows.(Disk I/O, sorting.. etc) So, when the results are available, DB anyways return to you fast. You might need to write some [pipe-lined functions](http://stackoverflow.com/questions/21171349/difference-between-table-function-and-pipelined-function/21172153#21172153) to suit your needs! There's Also [FIRST_ROWS](http://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#4942) hint that might interest you! – Maheswaran Ravisankar Aug 19 '14 at 13:52
  • Can you detail more your idea please ? Thank you for your patience. – Thomas Carlton Aug 19 '14 at 13:54
  • What kind of computations are you doing in the application? Could these be done in the query instead? – ninesided Aug 19 '14 at 14:00
  • Please add the following to your question: SQL statement and Oracle Database version number. Please indicate if you are using the Oracle Data Provider for .NET. That information will help in providing the proper tips. – Black Frog Aug 19 '14 at 14:12
  • Unfortonately the computations con not be done in the query. It's a pricing of financial instruments based on third-party API's – Thomas Carlton Aug 19 '14 at 14:12
  • Care to show the query? If it has a sort (`DISTINCT`, `GROUP BY`, `ORDER BY` etc.) you have no choice but wait until the entire result set is materialized, otherwise the first rows _will be_ returned immediately. – mustaccio Aug 19 '14 at 14:13
  • I agree with you but the query doesn't have any Distinct, Group By, Order By, Sort. – Thomas Carlton Aug 19 '14 at 14:15
  • What is your pre-fetch size ? Probably, your query is not using the available indexes and the full table scan itself is taking so much of time to extract the results. Can you show us how the `PLAN` table results look like – Maheswaran Ravisankar Aug 19 '14 at 14:20
  • I didn't specify any prefetch size. What's the plan table please ? – Thomas Carlton Aug 19 '14 at 14:32
  • Try `EXPLAIN PLAN ` . You should see `Plan Explained`. Then try `SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)`. That should give you the optimizer's plan for this query. Which shows the CPU cost involved. – Maheswaran Ravisankar Aug 19 '14 at 14:36
  • I added the Explain plan. Basically it's a query that filters some data based on a non-indexed-field. I don't have the privilege to change the table structures or add any index. – Thomas Carlton Aug 19 '14 at 14:43
  • Your Plan shows it `ALL` burden on the DB (656K as cost!! Too much of CPU usage). You *should* fine tune your query. Try adding index to the columns you use in `WHERE`. There's no other way. I dont think the HINTS would help you! – Maheswaran Ravisankar Aug 19 '14 at 14:50
  • Unfortonately I can't change the tables structure and add an index. But again this solution focuses on reducing the execution time of the query and not starting computations before the query finishes which is the basic idea. – Thomas Carlton Aug 19 '14 at 14:53
  • Thomas, DB doesn't give you any of the data, unless it process it completely! So, your requirement is impossible. Only thing, we can try out is pull the entire table data in your local collection(DataTable), and try processing in batches! – Maheswaran Ravisankar Aug 19 '14 at 14:54

1 Answers1

1

Oracle has an all rows strategy and a first rows strategy.

Usually, Oracle will, when possible, do a first rows strategy when possible. The simplest example of that would be something like:

select * from emp;

Here, there is no join, there's no sorting, etc, so, Oracle will begin to return rows immediately, as it reads through the EMP table.

On the other hand, this is a simple example of an all rows strategy:

select * from emp order by surname;

Here, we're asking for sort on SURNAME, so, we cannot begin to immediately return results. The table must be read in its entirety, and then sorted, before we can return the first row.

There are other factors as well. If you're joining tables, a NESTED LOOPS join will execute with a first rows strategy, whereas a HASH JOIN will (necessarily) employ an all rows strategy.

Ultimately, which is better, which you will want, is going to be dependent on your application. If you're doing stuff that the user directly interacts with, you'll probably want first rows, to not keep the user waiting. For batch jobs, all rows is (probably) better.

Finally, the optimizer can be influenced with the ALL_ROWS and FIRST_ROWS_n hints.

halfer
  • 19,824
  • 17
  • 99
  • 186
Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
  • Thank you for your answer. Assuming there is no join, no sort... How can you get the first results from oracle in C# application ? – Thomas Carlton Aug 19 '14 at 14:46
  • Well, I'm not a C# programmer, but in general, you'd define the cursor, based on your SQL query, then open the cursor, and fetch from it. How exactly to do that, is specific to the language and API that you're using. – Mark J. Bobak Aug 19 '14 at 14:51
  • Here's an example from another Stackoverflow posting: http://stackoverflow.com/questions/12568100/connecting-to-oracle-database-through-c – Mark J. Bobak Aug 19 '14 at 14:53
  • Tried this with a simple query but the code still waits until the query finishes at the database side, before moving to the line after. Thank you though – Thomas Carlton Aug 19 '14 at 14:58
  • Hmm...maybe that was a bad example. As I said, I'm not a C# guy. However, I'm confident that the explanation given in my answer above is accurate. You just need to figure out how to page through cursor result set from C#. – Mark J. Bobak Aug 19 '14 at 21:38