October 1, 2009 at 5:37 am
Hi there,
I have a situation, which has peeked my interest and decided to drop it here in the hope to learn something about Oracle. I know, this is an SQL Server site, still...
We have this application which communicates with our SQL Server 2005 solely through the use of cursors. Slowly this gives all kinds of complications and downgrades performance, but that is not my question. I am told that said application was originally written for the use with Oracle databases and that Oracle handles cursor operations much better then other DBMS's. I have tried to Google for info on this, but cannot find any overview or such about how Oracle deals with cursors and why Oracle would be more efficient about it. Also I have never read that the use of cursors is less a problem with Oracle: cursors are much slower then set-based coding and should only be used when absolutely no other simple way of coding is possible.
So, can any1 enlighten me? Any link to a (Oracle) site where this is explained? Is it indeed true, that Oracle deals with cursors better then other DBMS's?
Greetz,
Hans Brouwer
October 1, 2009 at 3:20 pm
It is NOT true that Oracle deals with cursors better compared to set based code than SQL Server does. Set based code will beat the pants off of cursors even in Oracle.
The problem is, applications can't get a result set from Oracle like they can from SQL Server. It's been a couple of years since I've touched Oracle but, IIRC, the only way that an application get anything from Oracle is to build a "reference cursor" to spool the data to the app. That's why you see so many cursor like things in an app that talks to Oracle.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2009 at 3:00 pm
im with jeff on this.. i dont think they work any better.. cursors are a horrible horrible solution for the most part.. the only plus to oracles over sql that i've ever found is that they're easier to write..
could it be you're not doing your garbage collection inside sql cursors and you were inside oracle? disposing of variables and closing out the cursor when you're done etc
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
October 3, 2009 at 3:08 am
Tnx for answering both.
The problems we have are being solved, that's not the issue. It's just that several times now I am told about this Oracle feature. Since I've never dealt with Oracle it peaked my interest.
Tnx again,
FreeHansje
Greetz,
Hans Brouwer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply