April 5, 2009 at 9:02 pm
Bruce W Cassidy (4/5/2009)
[font="Verdana"]It puzzles me as well, and I've commented earlier on that. "row-by-row" is just as agonising in Oracle as it is in SQL Server.About the only thing I can think of is that Oracle allows you to pass around reference cursors as a way of returning a result set from a stored procedure. That's about the only acceptable use of cursors in Oracle I can think of.
[/font]
One of the things I keep hearing (and I'm no Oracle developer by any means) is that Oracle has very highly optimized cursors. I don't know if this is true, but it would help explain all the Oracle code I keep running across with cursors on top of cursors. I also see a lot of other oddities in Oracle code that seem a little odd to me, but apparently provide performance or other benefits on Oracle.
April 5, 2009 at 9:24 pm
[font="Verdana"]I bet you hear that from Oracle developers who have never bothered to performance tune their code or bother to learn how to write set-based code.
No. Oracle does not have "highly optimised cursors". In fact, I found the performance of the T-SQL cursors somewhat better than those Oracle has. Okay, admittedly I haven't touched Oracle since version 10g. But in versions 7, 8, 9i, 10g... the cursors suck. I spent a lot of time rewriting poorly performing code that was cursor based into set-based code.
[/font]
April 5, 2009 at 9:30 pm
I've heard it from people at various other software companies as well. It seems to be a very common misconception out there. Like I said, I haven't done a whole lot with Oracle myself other than converting various Oracle code to SQL Server, but I've seen a lot of cursors and other things that leave me scratching my head sometimes 🙂
April 5, 2009 at 9:46 pm
[font="Verdana"]To be frank, in my experience the issues with cursor-based code in Oracle have been concealed by the huge overkill of hardware assigned to Oracle databases. The same database on SQL Server would be running on smaller hardware, so issues with poor coding would be more apparent.
I think that's likely to be less the case now. Oracle databases have been flocking to commodity servers (usually Linux based Intel hardware) for years now, and the difference in the hardware base between comparible Oracle and SQL Server solutions has largely disappeared.
In my personal experience, I took some code that had been produced by a commercial Oracle development company to refresh a data rollup used for reporting. It was taking 22 hours to run. Sure enough, it was written as a cursor within a cursor within a cursor. I rewrote it as one set-based update and one set-based insert, and saw the time drop to under 20 minutes.
I had another example where I got even more of a change in code execution times (19 hours down to 2 minutes), but that was a cursor over a database link: about the worst possible use of a cursor you can imagine. Sadly, the original code had been written by a developer in the company I worked for.
If Steve ever starts up the Oracle version of this site, I'm sure that the first thing that will happen will be the experienced Oracle developers bemoaning the prevalent use of cursors in database code. 😀
[/font]
April 5, 2009 at 9:47 pm
Mike C (4/5/2009)
steve dassin (4/4/2009)
Mike C (4/4/2009)
Jeff Moden (4/4/2009)
steve dassin (4/4/2009)
C'mon do you really have something against parentheses?Actually, yeah. 😉
Personally I hate those curly braces. Growl.
C'mon you got something against the three stooges too? And I'm not asking you to write in Moe and Larry too. But no Curly no tables, no rows and no lists, Want do you want to do manipulate just numbers and strings? 🙂
I always preferred Abbott and Costello. Even the Marx Brothers. The Three Stooges never really did it for me, no.
"But no Curly no tables, no rows and no lists..." This seems to imply that there's no way to represent tables, rows and "lists" without curly braces. I can only assume this holds true specifically for whatever it is you're selling?
My apologies. I certainly didn't mean to imply curlies were the only way to specify tables and table expressions. Here are some simple examples of Curly and parenthesis in various expression.
//Declare table variables (composables).
var Abbot:=table
{
row{10249 OrderID,51 ProductID,40 Quantity},
row{10250,41,10},
row{10248,72,5}
};
var Groucho:=Orders group by {ShipCountry} add{Count() Cnt}
where Cnt<10;
var Zeppo:=SQLQuery("SELECT OrderID,ProductID,Quantity
FROM [Order Details]"); //T-sql pass-thru query.
select ToTable('A,B,C'.Split(','));//Convert a delimited string to a list then a table.
select ToTable({'A','B','C'}); //Convert a declared list to a table.
create view MyView
(
(
(Orders where ShipCountry in {'Norway','Poland'})
join
OrderDetails
)
{OrderID,CustomerID,ProductID,ShipCountry,Quantity,Freight}
)
group by {CustomerID,ShipCountry} add{Count() Cnt,Max(Quantity) MaxQty,Max(Freight) MaxFrt};
adorn{key{CustomerID},key{ShipCountry}};
select MyView['SANTG' by {CustomerID}] //A row
select table{MyView['Poland' by {ShipCountry}]};//A row converted to a table.
I'm not trying to sell you anything. I'm trying to get you to go in for some trippy stuff 🙂
best,
steve
April 5, 2009 at 10:06 pm
Bruce W Cassidy (4/5/2009)
[font="Verdana"]To be frank, in my experience the issues with cursor-based code in Oracle have been concealed by the huge overkill of hardware assigned to Oracle databases. The same database on SQL Server would be running on smaller hardware, so issues with poor coding would be more apparent.I think that's likely to be less the case now. Oracle databases have been flocking to commodity servers (usually Linux based Intel hardware) for years now, and the difference in the hardware base between comparible Oracle and SQL Server solutions has largely disappeared.
In my personal experience, I took some code that had been produced by a commercial Oracle development company to refresh a data rollup used for reporting. It was taking 22 hours to run. Sure enough, it was written as a cursor within a cursor within a cursor. I rewrote it as one set-based update and one set-based insert, and saw the time drop to under 20 minutes.
I had another example where I got even more of a change in code execution times (19 hours down to 2 minutes), but that was a cursor over a database link: about the worst possible use of a cursor you can imagine. Sadly, the original code had been written by a developer in the company I worked for.
If Steve ever starts up the Oracle version of this site, I'm sure that the first thing that will happen will be the experienced Oracle developers bemoaning the prevalent use of cursors in database code. 😀
[/font]
Forget the Oracle version of this site -- I can think of dozens of people who could use this type of mythbusting information that visit this site. Steve might even be willing to publish an article on it here. I know about 20 or 30 folks off the top of my Blackberry list who need to know this. Some developers who come from Oracle backgrounds over to SQL Server carry their bad habits with them. And a lot of other developers who come from C#, C++, VB, etc., carry their loop-based array ideas over with them. I think everyone could use better information.
April 5, 2009 at 10:08 pm
steve dassin (4/5/2009)
My apologies. I certainly didn't mean to imply curlies were the only way to specify tables and table expressions. Here are some simple examples of Curly and parenthesis in various expression.//Declare table variables (composables).
var Abbot:=table
{
row{10249 OrderID,51 ProductID,40 Quantity},
row{10250,41,10},
row{10248,72,5}
};
var Groucho:=Orders group by {ShipCountry} add{Count() Cnt}
where Cnt<10;
var Zeppo:=SQLQuery("SELECT OrderID,ProductID,Quantity
FROM [Order Details]"); //T-sql pass-thru query.
select ToTable('A,B,C'.Split(','));//Convert a delimited string to a list then a table.
select ToTable({'A','B','C'}); //Convert a declared list to a table.
create view MyView
(
(
(Orders where ShipCountry in {'Norway','Poland'})
join
OrderDetails
)
{OrderID,CustomerID,ProductID,ShipCountry,Quantity,Freight}
)
group by {CustomerID,ShipCountry} add{Count() Cnt,Max(Quantity) MaxQty,Max(Freight) MaxFrt};
adorn{key{CustomerID},key{ShipCountry}};
select MyView['SANTG' by {CustomerID}] //A row
select table{MyView['Poland' by {ShipCountry}]};//A row converted to a table.
I'm not trying to sell you anything. I'm trying to get you to go in for some trippy stuff 🙂
best,
steve
Looks an awful lot like I'd imagine LINQ in VisualPascal.NET, if I were into imagining such things.
April 5, 2009 at 10:21 pm
Mike C (4/5/2009)
Forget the Oracle version of this site -- I can think of dozens of people who could use this type of mythbusting information that visit this site. Steve might even be willing to publish an article on it here. I know about 20 or 30 folks off the top of my Blackberry list who need to know this. Some developers who come from Oracle backgrounds over to SQL Server carry their bad habits with them. And a lot of other developers who come from C#, C++, VB, etc., carry their loop-based array ideas over with them. I think everyone could use better information.
[font="Verdana"]As with everything, the proof is in the data. Just put together a cursor-based bit of code with the comparible set-based bit of code, and compare the differences.
Here's some other food for thought: Oracle introduced the MERGE command well before it arrived in SQL Server. MERGE is used to do set-based inserts and updates in one go (and often called an "UPSERT"), usually to avoid a cursor-based solution -- although you can do the equivalent with a couple of set-based statements.
So if cursors are so great in Oracle, why the pressing need for a MERGE command?
Anyway, it's not hard to come up with test cases to compare cursors to set-based. I suspect if I took some time and did a google search, I might even be able to find some already.
But then, I already know (from my own experience) that cursors in Oracle suck. 😀
[/font]
April 5, 2009 at 10:38 pm
Bruce W Cassidy (4/5/2009)
Mike C (4/5/2009)
Forget the Oracle version of this site -- I can think of dozens of people who could use this type of mythbusting information that visit this site. Steve might even be willing to publish an article on it here. I know about 20 or 30 folks off the top of my Blackberry list who need to know this. Some developers who come from Oracle backgrounds over to SQL Server carry their bad habits with them. And a lot of other developers who come from C#, C++, VB, etc., carry their loop-based array ideas over with them. I think everyone could use better information.[font="Verdana"]As with everything, the proof is in the data. Just put together a cursor-based bit of code with the comparible set-based bit of code, and compare the differences.
Here's some other food for thought: Oracle introduced the MERGE command well before it arrived in SQL Server. MERGE is used to do set-based inserts and updates in one go (and often called an "UPSERT"), usually to avoid a cursor-based solution -- although you can do the equivalent with a couple of set-based statements.
So if cursors are so great in Oracle, why the pressing need for a MERGE command?
Anyway, it's not hard to come up with test cases to compare cursors to set-based. I suspect if I took some time and did a google search, I might even be able to find some already.
But then, I already know (from my own experience) that cursors in Oracle suck. 😀
[/font]
You brought up one of the main issues that I've spent time (with others) trying to press MS about. Namely introducing support for new ANSI/ISO features faster. Oracle is good about implementing new features and functionality really quickly... sometimes they even get their features out to market before the ISO standards updates are approved. I suspect this has probably bitten them in the butt a couple of times, but on the whole Oracle developers get a lot of great features and functionality really early in the cycle. Ranking and windowing function support are a great example of that; the MERGE statement (originally planned for SQL 2005, btw, but pulled at the last minute -- and now back in SQL 2008) is another.
But anyway, the point being that Oracle may well have just implemented MERGE because it was being added to the ISO standard, but not out of any particular pressure from developers (I can't really say one way or the other).
April 5, 2009 at 10:44 pm
Mike C (4/5/2009)
Oracle is good about implementing new features and functionality really quickly... sometimes they even get their features out to market before the ISO standards updates are approved.
[font="Verdana"]I've always thought it was a bit of leap-frog happening. I remember that SQL Server got ANSI join syntax long before Oracle did. I hated having to go back to coding SQL in Oracle and trying to remember how to do outer joins...
I miss NATURAL JOIN though. Is that an ANSI SQL feature, or an Oracle feature?
[/font]
April 5, 2009 at 11:56 pm
Mike C (4/5/2009)
steve dassin (4/5/2009)
My apologies. I certainly didn't mean to imply curlies were the only way to specify tables and table expressions. Here are some simple examples of Curly and parenthesis in various expression.//Declare table variables (composables).
var Abbot:=table
{
row{10249 OrderID,51 ProductID,40 Quantity},
row{10250,41,10},
row{10248,72,5}
};
var Groucho:=Orders group by {ShipCountry} add{Count() Cnt}
where Cnt<10;
var Zeppo:=SQLQuery("SELECT OrderID,ProductID,Quantity
FROM [Order Details]"); //T-sql pass-thru query.
select ToTable('A,B,C'.Split(','));//Convert a delimited string to a list then a table.
select ToTable({'A','B','C'}); //Convert a declared list to a table.
create view MyView
(
(
(Orders where ShipCountry in {'Norway','Poland'})
join
OrderDetails
)
{OrderID,CustomerID,ProductID,ShipCountry,Quantity,Freight}
)
group by {CustomerID,ShipCountry} add{Count() Cnt,Max(Quantity) MaxQty,Max(Freight) MaxFrt};
adorn{key{CustomerID},key{ShipCountry}};
select MyView['SANTG' by {CustomerID}] //A row
select table{MyView['Poland' by {ShipCountry}]};//A row converted to a table.
I'm not trying to sell you anything. I'm trying to get you to go in for some trippy stuff 🙂
best,
steve
Looks an awful lot like I'd imagine LINQ in VisualPascal.NET, if I were into imagining such things.
CREATE TABLE T (A INT,B VARCHAR(25))
SELECT A,B FROM T
CREATE VIEW T1 SELECT A,B FROM T
SELECT A,B INTO T1
FROM T1
CREATE PROCEDURE T3
SELECT A,B FROM T
CREATE FUNCTION T4(A VARCHAR(25))
RETURNS T5 TABLE(.....
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
What do all these sql table operations have in common? None of them have anything to do nor any relationship to the concept of a variable, a variable like you could declare for a number or string. A language in search of even a rudimentary computer science!:-) Developers, especially new developers, are struck not so much with the problem of learning a declarative syntax but with the problem of manipulating things in an environment devoid of variables. And you wonder why most developers think sql is nuts?:-) The fundamental idea of a relational system is introducing the idea of a strongly typed variable that replaces the sql non-variable table. And if you're going to jack around tables as variables it should be relatively easy to envision
a type of environment much more friendly to variables than sql. Sql can safely ignore the concept because it doesn't exist there! The basic problem, as people like C. J. Date have found out, is getting sql folks to get their head out of what is essentially a 'constant' (an sql table) and into something that is variable. This is really nothing more than basic computer science and the science that lies at the base of what a relational db is all about. Sql folks have their heads in a very different place namely declarative programming. But this type of thinking is not at the root of things. First come variable and types. But because they have been separated from such considerations, sql folks have a hard time getting their heads around these ideas. Hell why should they, they've gottin along without types and variables forever!:-) Most of my blog articles are about what an environment looks like when a declarative language is within a system where everything is expressed thru variables. Don't expect it to look
anything like sql. Nor expect it to be a copy of C#. But you should imagine it as a place you'd at least like to visit. You can then make up your mind if you'd want to live there 🙂
best,
steve
April 6, 2009 at 12:30 am
Bruce W Cassidy (4/5/2009)
Mike C (4/5/2009)
Oracle is good about implementing new features and functionality really quickly... sometimes they even get their features out to market before the ISO standards updates are approved.[font="Verdana"]I've always thought it was a bit of leap-frog happening. I remember that SQL Server got ANSI join syntax long before Oracle did. I hated having to go back to coding SQL in Oracle and trying to remember how to do outer joins...
I miss NATURAL JOIN though. Is that an ANSI SQL feature, or an Oracle feature?
[/font]
I believe it was in one of the ANSI/ISO standards, maybe SQL:1999? But I don't think it really caught on. The fact that you had to maintain strict column naming between tables made it a little hard to backport to legacy databases that didn't do such a good job of maintaining strict naming conventions. Another ANSI/ISO feature that never seemed to catch on was the Boolean data type.
As far as the features, SQL Server 2000 was SQL-92 compliant. And it came out the year after the SQL:1999 standard... SQL Server 2008 doesn't really claim compliance with ANSI/ISO, but from what I could gather it's SQL-92 compliant with a few additional SQL:2003 features and a lot of vendor-specific niceties, many carried over from SQL Server 2005.
April 6, 2009 at 12:37 am
steve dassin (4/5/2009)
CREATE TABLE T (A INT,B VARCHAR(25))SELECT A,B FROM T
CREATE VIEW T1 SELECT A,B FROM T
SELECT A,B INTO T1
FROM T1
CREATE PROCEDURE T3
SELECT A,B FROM T
CREATE FUNCTION T4(A VARCHAR(25))
RETURNS T5 TABLE(.....
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
What do all these sql table operations have in common? None of them have anything to do nor any relationship to the concept of a variable, a variable like you could declare for a number or string. A language in search of even a rudimentary computer science!:-) Developers, especially new developers, are struck not so much with the problem of learning a declarative syntax but with the problem of manipulating things in an environment devoid of variables. And you wonder why most developers think sql is nuts?:-) The fundamental idea of a relational system is introducing the idea of a strongly typed variable that replaces the sql non-variable table. And if you're going to jack around tables as variables it should be relatively easy to envision
a type of environment much more friendly to variables than sql. Sql can safely ignore the concept because it doesn't exist there! The basic problem, as people like C. J. Date have found out, is getting sql folks to get their head out of what is essentially a 'constant' (an sql table) and into something that is variable. This is really nothing more than basic computer science and the science that lies at the base of what a relational db is all about. Sql folks have their heads in a very different place namely declarative programming. But this type of thinking is not at the root of things. First come variable and types. But because they have been separated from such considerations, sql folks have a hard time getting their heads around these ideas. Hell why should they, they've gottin along without types and variables forever!:-) Most of my blog articles are about what an environment looks like when a declarative language is within a system where everything is expressed thru variables. Don't expect it to look
anything like sql. Nor expect it to be a copy of C#. But you should imagine it as a place you'd at least like to visit. You can then make up your mind if you'd want to live there 🙂
best,
steve
The example you posted still looks an awful lot (to me) like what I get with LINQ on C# (or VB). Not an exact copy, but very similar functionality and syntax. Like your sample, I can assign a result to a variable and easily convert between lists, hash tables, arrays, etc. I can also intersperse declarative query syntax with procedural code at will. The fact that I already know C# and don't need to learn a new language or new tools helps a bit also.
April 6, 2009 at 1:40 am
steve dassin (4/5/2009)
getting sql folks to get their head out of what is essentially a 'constant' (an sql table) and into something that is variable.
Such a pile of c..p!
What makes sql table a 'constant'???
_____________
Code for TallyGenerator
April 6, 2009 at 3:39 am
Sergiy (4/6/2009)
steve dassin (4/5/2009)
getting sql folks to get their head out of what is essentially a 'constant' (an sql table) and into something that is variable.Such a pile of c..p!
What makes sql table a 'constant'???
Well I have a tacit agreement with the moderator to play nice and not raise the ire of any more participants here so I'll play nice 🙂 An sql table is certainly not a variable. If it's not then what then is it? I thought I was being kind and considerate calling it a constant (it has a value but no type. What is something with no type? Certainly not something that can support assignment). If that offends you perhaps you prefer a computer science type of lox 🙂 Of course the rectangular idea of rows and columns doesn't quite fit the idea of a bagel. It seems that trying to morph a lox into a variable offends you. Perhaps you can't envision programming without a lot of salt:-) I'm not sure how many here think variables are overrated. I'm just trying to reaquaint them to a long forgotten concept 🙂
best behavior steve,
Viewing 15 posts - 436 through 450 (of 465 total)
You must be logged in to reply to this topic. Login to reply