Log in

No account? Create an account
current entries friends' entries archives about me Previous Previous Next Next
Technical Life - cellophane — LiveJournal
the story of an invisible girl
Technical Life
read 16 comments | talk to me!
kyril From: kyril Date: March 31st, 2008 03:19 am (UTC) (Link)
We just switched from Sybase to Oracle (don't do it if you have the option, in either direction! If you have to do it, isolate every single bit of your SQL into a separately testable thing, have folks who understand the application test those things, then think about starting the conversion...there's gotchas going back and forth!) and while we nominally have DBA support we didn't have before, in practice Google and I are still the actual application DBAs. And while TOAD has some nice views (I like the session browser), if it doesn't have what you want (e.g. sort sessions by total CPU time or most recent call) you have to google for it and use SQL anyway...

On the other hand, having started 8 years ago on this application as a big OO proponent, I discovered a lot of nice OO concepts (e.g. double dispatching, inheritance in general) are not useful or easy to misuse much of the time. Of course, other ideas like information hiding/encapsulation are much more broadly applicable and don't need OO to do. Proliferations of classes and actors are bad. Inheritance where it isn't useful is bad. Sometimes the data just needs to be dumb data, or structures, and sometimes a piece of the application needs to be taken out of the OO level (even if you have classes to load and persist the records and handle business behaviors) and treated as a data flow application and done in SQL or awk some such. On the other hand, if your database has any qualms about parallelizing, if it's heavy enough to convert to SQL you may eventually wish you had stayed with the OO code and could run 48 of them simultaneously.

Having used JUnit seriously only on a couple pieces, I can say making code testable does cause certain "funny" design decisions...plus pretty much every private method becomes protected for me as I need to make at least one "testable" subclass that fakes out the parts I'm not trying to use in any given test. It's kind of a pain. I wish I had (or we were willing to add on) a macro language for all of our Java, awk, ESP (batch scheduler) etc. code, if only to make development vs. test vs. production environment customizations easy.

(Why would anyone want to make stuff private when it could be protected? I usually don't get that...unless you're making classes to sell without the source code or something.)
pstscrpt From: pstscrpt Date: March 31st, 2008 03:45 am (UTC) (Link)
I expect Sybase to SQL Server would've been a lot less work. You have to be willing to keep Windows servers around, though. And I tend to favor architectures where the database is the heart of the system (ideally to the point of banning business logic that isn't in the database), but one of those means you're never going to change the back-end.

I discovered a lot of nice OO concepts (e.g. double dispatching, inheritance in general)
I've had some tasks where inheritance all over the place seems appropriate and some where I'll extend maybe one class in a project. It mostly seems to depend on how much I end up modeling the problem.

One thing I think I have decided is that if you're using a class, it should be none of your business what it extends. Polymorphism should come from interfaces and code reuse should come from inheritance (or macros). Mixing and matching interfaces and derived classes like .Net and Jave do is just a conceptual confusion.

kyril From: kyril Date: April 1st, 2008 12:40 pm (UTC) (Link)
I can't imagine ditching Sybase for SQL Server. Certainly not on the large HP-UX boxes our application runs on, but even for a smaller application that was always on a wintel platform I'd really want serious cost justification before I even considered it. Oracle has gotchas Sybase doesn't (or maybe just different gotchas), some things with it are faster...but if you "talk to it wrong" some things are much slower, even if your queries are perfectly indexed.

Part of the art of programming is deciding where to partition the application and put in opaque interfaces, and where boundaries are unnecessary because "it's all one thing". Hard and fast rules are dumb...unless some rules are harder and faster than others.
pstscrpt From: pstscrpt Date: April 1st, 2008 01:09 pm (UTC) (Link)
I'd really want serious cost justification before I even considered it.
I figured you were changing because you couldn't find Sybase people, anymore. Maybe not.

if you "talk to it wrong" some things are much slower
That's probably true of any of them, but I have particularly noticed that the Oracle optimizer really doesn't seem to be very bright. I'm mostly comparing Oracle 9i to SQL Server 2000, though, and that might not be entirely fair.

Hard and fast rules are dumb
Hard and fast rules in general are dumb, as every problem is different. Hard and fast rules within one system, though, tell you where to look when you have trouble.
kyril From: kyril Date: April 2nd, 2008 06:41 am (UTC) (Link)
The optimization is somewhat different: in Oracle, you want to prepare statements or use stored procedures wherever possible, using BEGIN/END; makes parsing slow beyond all sanity and even "soft parses" (all the literature implies if it's not a "hard parse" it's not too bad) take 90+% of the time a hard parse takes.

The empty string turns into NULL, and it's not true in Oracle that NULL = NULL, nor NULL != NULL, though NULL IS NULL is true. So any time you might have empty strings or NULL values, you have to add a separate "is NULL" case. (In Sybase, '' = ' ' and '' is not null; null = null, and not (null <> null) is also true.)

Usually it doesn't pick stupid query plans if your statistics are vaguely up to date. Usually. But there's subtle things that make it stupid. And even if you have two tables ordered by indexes on the key you join them on, sometimes it still likes to hash the tables (equivalent of Sybase REFORMATTING if quicker) rather than doing nested loops.

Oracle Index Organized Tables seem very much like Sybase Clustered Indexes, except Oracle proponents say they're totally different. And they say Oracle's "clusters" are a third totally different thing...but they don't really look it, from my perspective.

Sybase and Oracle like to miss an index and convert the indexed columns to your literal's type rather than convert your literal to the type supported by the index. But at least in Sybase you can specify a BINARY literal.

Oracle 10G parallelizes (esp. the select part of multi-table insert/select) better than Sybase 12.5 but maybe not better than the current Sybase 15.

The list goes on...

...and no we didn't run out of Sybase people, we thought we had more Oracle support than what little we got. And a premium was placed on using Company Standard Products, plus Oracle goes on someone else's budget but Sybase was on ours. They won't even let us use SQL Server unless the application is internal and puny.
pstscrpt From: pstscrpt Date: April 2nd, 2008 01:18 pm (UTC) (Link)
stored procedures wherever possible
75% of my work since June has been PL/SQL programming, so I've got that part covered. Would the hard parse be where it does the hash comparison to see if it already has that SQL compiled?

it's not true in Oracle that NULL = NULL
SQL Server is descended from Sybase, but they corrected the "Null = Null" thing in SQL Server 7 (the version before 2000), and it returns Null, like it's supposed to. I was just getting started in databases then, and was surprised to learn that, because I didn't know you could successfully compare Null to Null. Anyway, I'm kinda surprised Sybase hasn't changed that, yet.

People have been complaining about Oracle treating empty strings as Null for many years. I hear rumors they're going to fix it, but nothing concrete.

But there's subtle things that make it stupid.
Yup. One thing I've learned is that, even though a WHERE EXISTS may seem like it's saying exactly what I want and ought to be more efficient, it's likely to have 300 times the execution cost of a derived table equivalent. I'm not really sure, though, if the optimizer really does just have more shortcomings, or it's that I had eight years of experience learning what SQL Server likes.

Sybase and Oracle like to miss an index and convert the indexed columns to your literal's type rather than convert your literal to the type supported by the index.
SQL Server does that, too. You can have a VarChar column and do a select "Where MyColumn = 3", and it will work until somebody puts in a value that can't be interpreted as a number.

And a premium was placed on using Company Standard Products
That's how we wound up using Oracle's Java tools at Talk America (after they bought out LDMI, where I used to work). The idea was that we could just call up Oracle for help, no matter where in the stack a problem occurred. I think that ended when Talk America was bought by Cavelier, though.
read 16 comments | talk to me!