Is it better to use cursor or analytical functions in a stored procedure for performance improvement?
+2
A:
It's always preferable to not use cursors if you don't have to.
OMG Ponies
2010-08-15 14:56:15
Where did you get that fairy tale from? It might be preferable to avoid _explicit_ cursors because you can forget to close them. But implict cursors are no problem at all and absolutely unavoidable if you retreive more than one row.
Codo
2010-08-15 16:21:01
Most don't know the difference, partly because "cursor" typically means "explicit cursor" - like how "view" does not mean "materialized view". Your caveat about implicit cursors retrieving more than one row is enough to demonstrate that usage depends on details--which we don't have--but a SET based solution is still preferable (and likely, because SET programming is not like procedural/OO programming).
OMG Ponies
2010-08-15 20:55:11
@Codo: If one can perform the needed operations in a single SQL statement vs. using PL/SQL cursors to perform selects and, one assumes, later inserts, updates, or deletes, one should. That's OMG Ponies' point.
Adam Musch
2010-08-16 05:17:53
I agree: if something can be done with a single statement using any aggregation technique ("regular" group by, analytical functions) this is almost always faster than processing the same data in a cursor loop
a_horse_with_no_name
2010-08-16 16:07:36
+3
A:
It is better to use pure SQL in a set-wise fashion than to use cursors to process data RBAR. This is because the context switching between the PL/SQL and SQL engines is an overhead. Cursors and all their additional code are also an overhead.
Analytic functions are an excellent extension to SQL which allow us to do stuff in a SELECT statement which previously would have required procedural code.
Of course, if you're looking to process large amounts then bulk collection and the FORALL statement are definitely the best approach. If you need to use the LIMIT clause then explicit cursors are unavoidable.
APC
2010-08-15 16:38:09
A:
Using Pure SQL statement is always is better choice compared to cursor. Cursors are used when it is cumbersome to process the data using pure SOL.
Using cursor we process one row each. With bulk collection we reduce context switching but we still be processing only few thousand rows in a cycle using Limit Clause. Using pure SQL, it is possible to insert or update more rows provided enough resources are available. Rollback segment is a limiting factor. In one project I wrote very complex pure SQL to insert millions of rows in very large data warehousing environment with failure. The DBA had made very large rollback segment available. I could not achieve that kind of performance with bulk collection.
Please beware, most companies will not give you that kind of facility. So please take a decision based on the available resources. You can use combination of both.
Vishnu Gupta
2010-08-29 17:47:39