?
Solved

What is Good...CURSOR or PLAIN SELECT Statement...

Posted on 2004-10-13
9
Medium Priority
?
830 Views
Last Modified: 2008-02-01
Hi...
I am writing a procedure. I am starting with opening a cursor and looping through each record. Now for each record I have to get other details from 5 different tables. I am plannig to create five different parametric cursors for all five tables. Also, I am planning to pass PK to these cursors to fetch data for the record fetched by first cursor.
First cursor is must...I know that all the five tables are going to return me one single record for the passed PK.
Is cursor approach is good ??? or should I just get the data using simple SELECT statement with WHERE clause ??? I am looking for SPEED ...I am expecting, first cursor will return 2000 rows. That  means if I am using CURSOR approach I am using five tables cursor 2000 times. Where as using SELECT statement I am hitting DB table directly each time for getting the record.
 
0
Comment
Question by:ushelke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 80 total points
ID: 12304389
Cursors are record-based operations, and SELECT's are set-based operations. Set-based is almost always far faster than record-based.
0
 
LVL 3

Expert Comment

by:jaramill
ID: 12304527
Well if you want to take data from these records and do some processing before inserting, then you use a CURSOR with the SQL statement being one big SQL with sub-queries.  If you're doing just a straight insert then jdlambert1's suggestion is correct.  INSERT INTO SELECT * FROM ... is always faster.
0
 

Author Comment

by:ushelke
ID: 12304545
I am taking this data and then going to use this data for processing purpose. I am also thinking of writing one single cursor for fetching data from all five tables rather than writing five different cursors. But not sure which is good approach. Five different cursor or one single cursor based on complicated SELECT statement with one parameter i.e my PK key from first cursor.  Please comment...
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 3

Expert Comment

by:jaramill
ID: 12304548
If you read my last post I say what you should do, and that is one single cursor based on a complex SQL statement.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12304553
Avoiding 4 cursors would be very beneficial. Use the single cursor, regardless of how complex that makes the SELECT statement.

Also, you can test the SELECT statement by itself. If it has performance problems, you should be able to improve that by adding appropriate indexes.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 12305734
SELECT is best. But you can use it only if it selects ONE row.
If your select statement selects more then one row you have to use
cursor. This is so because PL/SQL is not able to store a set of rows.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question