Re: FN-FORUM: Databases

date posted 5th August 2003 21:17

Richard Parratt wrote:

>
>So what *do* you suggest!
>
Depends upon what you want to do, how much value you put on data
integrity, time to recover etc.

>I guess any software is going to have limitations. I'd tend to use MySQL
>until the lack of stored procs, etc. becomes an issue, then move to
>SS2000.
>
For non commercial, non transactional work MySQL does what it says on
the tin.

Postgres is a tad messy via perl but works well, has real RI,
transactions and triggers
and is stable.

SS7 for some of the projects I have been involved in is just a tad limited
and unscalable. Its nice to see SS2K finally implementing overflow pages :-)

When SS funally gets partioning it will catch up with Oracle 8 :-)

As said elsewhere oracle 9 is bloatware :-)

I have yet to review firebird. http://sourceforge.net/projects/firebird/
but it does sound promising.

> (first time I've heard of the transaction limits - got a
>reference?)
>
Have the refs stored at work. Did a quick google search and cannot find it. It was on dbforums

if my currently failing (migraine) memory is working :-)

If I remember correctly it is not a limit of ODBC/JDBC or ADO but due to
the sp_prepare/execute
cycle. In order to get the performance you have to ensure you have an
'Active' connection so that
the sp_prepare is done once and values bound for each sp_execute(). Even
so people were mentioning
seeing limits of around 100 IPS. Note that I was inserting blobs into an
existing large table with a multi field PK.

The objective was to insert many millions of records in a few minutes
and after trying many ways -including
building sp's with a batch of inserts etc DTS was used which gives the
required performance .

If you hold a pool of sp_prepare cursors open and execute the 'inner'
ones many K times you start to get
varchar truncation. It is a combination of a lnumebr of open connections
and high re-use in a short
period. Adding usleeps removes teh problem. I tracked the values right
through to the ADOBD OLE
calls and the driver was not truncating the values - they were even
being passed through when query
profilers was enabled - but when you looked at the DB you found
truncated values. Nasty.

>Having recently done an Oracle project, I've become even more convinced
>that its enterprise features (versus SQL Server) are 95% FUD.
>
Hmm - Oracle 9 is IMHO no longer a database but an APP server. However
if we ignore that,
Oracle at the high end does have some neat tricks out of the box, such
as partitioning that from
what I have seen SS7 is sadly lacking. I still consider SS7 to be little
more than a flaky toy
when used with anything other than MS technology.

Choice of database is similar to choice of language. Oracle is a big and
complex beast to get the
best out of and tools such as SQL/DBA are big and expensive beasts.
saying this TOAD is cheap
and truly "whomps ass" and a midwest friend of mine likes to say :-)
Having used (mainly) Oracle, Ingres, Postgres, Sybase and MySQL for far
too many years you learn not only
the way to get the best out of each one but something of the 'ethic'
behind the system. Oracle is
and always will be a big beast, Ingres (was) a light, but highly
technicaly database, Postgres was
originally an academic exercise and MySQL was a toy.

These days all of the above are competing in some cases head to head for
business. A few years ago
oracle went 'light' and even went 'raw iron' to circument the obstacles
place in its way by redmond. :-)
MySQL having said that RI, transactions and triggers are a waste of time
are now loudly proclaiming
having all three on tap.

I have plenty of criticisms of both SS7 and Oracle (lately SS7 backups
are a thing of irritation)
but the one good thing Oracle did was to open up the backup and recovery
interfaces to third parties.
With Oracle you can buy a range of products that will manage your backup
and recovery strategy
If the standard oracle backup tool is trash, you can always buy a
professional toolkit that will
do the job - you have a choice.

>And the
>tools you get in-the-box are soooo primitive. Worse than MySQL really..
>
Depends upon the box :-) We used to pay 2K a years for the Oracle
reseller/dev program.
For this you get a 'free' oracle dev licence for kit of your choice -
ours was an E class
sun server. And whole heap of oracle tools/CDs to play with - and
credits for
discounted/free oracle training. This became more expensive and limited.
No more
training credits apart from truly awful CBT courses. IMHO No longer
worth the money.

OTOH I have a mandrake Oracle8 iin a box with a full commrcial licenec
for circa 50UKP :-)
I cannot remmebr if it came with enterprise manager but doubt it but
then I am used to
writing TNS config files by hand from the bad old days :-)

IMHO tools such as Oracle forms always were trash - and probably always
will be.
No comparison with even something like access or delphi :-)

If you want all singing and dancing applications go the MS route or the
JavaDB route.

If you want a scalable high performance, commercial RDBMS backend then
you are far more
limited in choice. Having said this you are pretty much stuck with SS2K
if you are a
MS shop.

My choice is MySQL, Oracle, Postgress DB2 or something like FIrebird
(when known stable)
Even if FB is not stable I can probably provide client support as source
code is available.

Horses for courses :-)