If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Cursors in DB's

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 6
Cursors in DB's

Cursors in Oracle vs SQL Server /Sybase

--------------------------------------------------------------------------------

Hi,
Would be great if someone can enlighten on this..

I have done MS SQL Server and Sybase dev work...and hvae allways been tolled that using Cursors is a bad idea performance wise and always go for a set based solution..I have no practical experince with Oracle, but form reading some books on Oracle...thier does not seem to be any restriction/advice saying not to use cursors...

Can anyone confrim this...
I mean there should be a big deffernce in how solutions are developed if Cursos can be used withthout a performance hit....
Reply With Quote
  #2 (permalink)  
Old
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
The same applies: use set processing in preference to cursors when possible. For example this:
Code:
insert into b (c1, c2, c3) 
  select d1, d2, d3 from a where d4=99;
is better than this:
Code:
declare
  cursor c is select d1, d2, d3 from a where d4=99;
begin
  for r in c loop
    insert into b (c1, c2, c3) values (r.d1, r.d2, r.d3);
  end loop;
end;
Not only is it a lot faster to type, it runs faster too.

That's not to say that cursors aren't extremely useful. Just don't over-use them.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Location: Greenville, SC (USA)
Posts: 1,155
I agree with Andrewst !! Cursors are GREAT when you need them, but if you can do the work WITHOUT cursors, DO IT !!!

HTH
Gregg
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 6
Cursors in Db

Ok guys thanks,
Was looking forward to do some Procedural type programming....find SQL a bit too boring!!
Ofcourse if Cursors have the same sort of penelty in Oracle as they do in MS SQL server/Sybase..then I will not be able to do Procedural programming inOracle as well...
Reply With Quote
  #5 (permalink)  
Old
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
There is plenty of opportunity for procedural programming in Oracle! That is why PL/SQL exists after all. Procedural code has its place - just not for everything!
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Location: Greenville, SC (USA)
Posts: 1,155
Be sure you're not confusing CURSORS with PL/SQL code. Cursors are a part of PL/SQL and are useful and necessary in certain conditions. There are MANY reasons to write PL/SQL code and it is NOT a performance deterent what so ever !!! That is unless you write bad SQL ... Then, the performance
of the code will be bad whether it is in SQL or PL/SQL .....

I try to put as much of the processing inside the database as possible (triggers, functions, procedures) all written in PL/SQL ... Let the database do what it is supposed to do !!

HTH
Gregg
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 6
Ok, last question...dont want to waste everyone's time due to my lack of knowledge!!!!

gbrabham.. I understand your statement about putting all the processing in the db(triggers,sp's) etc..

How do you compare/contrast that to..what seems to be the 'modern' method of development...Object Orientation..???
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 6
Ok, last question...dont want to waste everyone's time due to my lack of knowledge!!!!

gbrabham.. I understand your statement about putting all the processing in the db(triggers,sp's) etc..

How do you compare/contrast that to..what seems to be the 'modern' method of development...Object Orientation..???
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Location: Greenville, SC (USA)
Posts: 1,155
Every SQL statement has to be sent to the database, compiled and parsed into SGA then executed and send the results back.

With procs, triggers, etc ... the code is already compiled and parsed and if the code is something that is used over and over ... Pin it in the SGA.

Hard Parses in the SGA are a performance hit. I currently have a client that believed totally in Object Oriented solutions. I timed his query, rewrote it in PL/SQL and showed him the SIGNIFICANT timing difference.

I guess I'm just old school ...

HTH
Gregg
Reply With Quote
  #10 (permalink)  
Old
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
Quote:
Originally Posted by gbrabham
I guess I'm just old school ...
Me too!

Of course, PL/SQL includes OO programming these days, though I rarely find any use for it.

As for storing data as objects in the database... let's just not go there!
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 6
Quote:
Originally Posted by andrewst
Me too!

Of course, PL/SQL includes OO programming these days, though I rarely find any use for it.

As for storing data as objects in the database... let's just not go there!
Absolutley,
I agree as well...
I would condiser myself as a begining programmer/intermediate database developer...and trying to ge to get to the next level-larger problems to solve...so I start lookiing at books to learn a methodology, which I can use as a framework to 'think out' a soultion to a problem...
ALl books nowadays talk about Objects in the Middle tier...i/e not putting any real logic in the db...
To be honest this does not make sense to me...but if all the experts are saying this(put all logic in the Middle tier in Objects) ??????
Reply With Quote
  #12 (permalink)  
Old
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
Quote:
Originally Posted by RajRatnam
ALl books nowadays talk about Objects in the Middle tier...i/e not putting any real logic in the db...
To be honest this does not make sense to me...but if all the experts are saying this(put all logic in the Middle tier in Objects) ??????
Not ALL experts are saying that - choose your experts carefully! I would recommend reading books by Tom Kyte, and browsing his website AskTom for his take on these matters - for example here.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 6
Quote:
Originally Posted by andrewst
Not ALL experts are saying that - choose your experts carefully! I would recommend reading books by Tom Kyte, and browsing his website AskTom for his take on these matters - for example here.

Thanks for this reference material....really helpfull...
This is exactly the problem I am having...putting Business knowldge in the Middle Tier...I find putting the logic in the db in sp's much more straight forward...but again,keeping an eye on the job market they all want OO!!
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: France
Posts: 754
Well, god be blessed not ALL BOOKS advocates putting all the business logic in the middle tier !

Read one of Tom Kyte's books, or go on asktom to see some interesting discussions about Java beans and putting all the business logic inside them .

A new colleague of mine was used to developing this way in his former job, so I asked him to show me exactly how it works... all I can say is AWFUL ! This is a "nice" theory for "object modeling fanatics", but it is also the best way to kill the database performance for once and for all as soon as you write your first line of code. I'm all the more serious here : you won't ever have either reasonable performance or a scalable application until the WHOLE application with ALL its logic is being rebuilt.

Here are some reasons :

- Generally this kind of object philosophy goes with the idea that the application has to be database independent because "one never knows, we could use SQL Server in the future". This is simply the best way to write generic but highly inefficient SQL and forget that RDBMSs can have very different behaviours that will need rewriting anyway (locking mechanisms, automatic ids...)

- Each operation with the database is atomic. It's using stored procs, but just for one insert or one update... In fact I see it as a way to be able to say "See, we are using stored procs, so the db processes are very performant", but the way they are used, it is completely wrong.

- Each atomic operation needs a client-server round-trip whereas several atomic operations could have been gathered in a single stored procedure and need only ONE round-trip.

- PLSQL is the best language for data manipulation, it has been BUILT for just doing that and can do it brilliantly.

- Putting the business logic within stored procedures and forcing client applications to access data mainly (if not only) though them "protects" the data. I personally see them as "processing constraints" : data must follow business rules, let them follow them whatever the client application accessing them.

- SQL is generally better written by PLSQL developers then by Java developers, in part because Java developers are asked "not to bother about how the db works" whereas they would highly benefit of knowing the basic mechanisms of the RDBMS they are accessing.

The "object-relational" programming philosophy is like asking the db for the data structure (tables, primary keys, foreign keys), creating objects for each table and then do everything locally following business rules independently from the database : as Tom kyte puts it, the db is seen as a "black box" that will have to work anyway, but it's not the developer's job. WRONG WRONG WRONG, the developer's job is ALL about data and how they are processed, so how can one say "the developer only cares about data in his program, the interaction with the database is not his problem". Data are in the database, one way or another it's the db that accesses and processes data, and in the end the BIGGEST performance hits are on the db side, directly depending on how data are processed. If you say "I don't care about performance" then OK, you can use this philosophy, but don't use Oracle, use PostGres or a just indexed files because you are paying your licence for nothing since you don't use any of the many very performing ways Oracle provides for accessing and manipulating data. Now if like everyone you DO care about performance and don't want to have to tell your boss that you need twice the number of machines in 6 months and 10 times the number of machines in 2 years, plus several expensive oracle consultants to investigate why the db is so slow while they will be able to do about NOTHING, just DON't EVER THINK OF GOING THIS WAY.

HTH and Regards,

rbaraer
__________________
ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On