Hi Kjell

Kjell Rilbe wrote:

> Hi Sven Erling,
>
> The biggest diff between you and me is probably that you've used
> Firebird so much that you do things right without thinking. I don't.
> Reading your text, I note the following peculiarities you have to be
> aware of in Firebird and not in SQL Server:
>

On the other way around, (I used MSSQL 6.5 a long time ago :-) ) I had a
lot of problems when modifying values, MSSQL used to lock pages, and if
a lot of pages were locked on a single table the lock was "promoted" to
table lock. The get better performance one could use clustered indices
on low selectivity data that is frequently used to do searchs (like an
index on ProductCode on table SalesItem), you have a hundred different
products but a million sales, that leads to a low selectivity index on
SalesItem.ProductCode and a very common search will be
SalesItem.ProductCode = 'XPTO', clustered indices help a lot in those
cases, another use I did for Clustered Indices was on date fields (like
Invoice.CreateDate), that leads to all invoices with the same (or near
date) to keep on the safe data page (in the case this index leaf page),
since new invoices are inserted by diferent users at the same time, and
that such invoices will have the same date, it will go to the same data
page but since the MSSQL locks the data page, only one insert could
occurr at a time on that table, as you can see a really bad bottleneck.

I think every database has it's advantages/disadvantages, if there is a
single database server that has all advantages that wil be the only one
on the market, and if one database has only disadvantages it will die
very very soon on the same market. As opposed to what you say, I really
think a developer should understand the "inner" works of a database that
he is writing code for, or have someone skilled that could help to catch
those gotchas.

Note that my point about MSSQL was for a really old version, I don't
have experience with newer versions, but I know it removes the page lock
level as the most granular option, that it has row level locking and on
the lastest version MGA :-)

> 1. You add "+0" or "|| ''" to avoid using unappropriate indices.
>

At every release the optimizer get smarter, in the near future, for
sure, no one will need to tell the optimizer that this is a bad index,
and not to use it... Lets wait :-)

> 2. Previously you avoided "in <subselect>". Luckily this quirk seems to
> have been resolved now.
>

This is a point that should be adressed too, perhaps someway to identify
that the subselect is invariant, run it once on the beginning and use
the result as it was a like
select * from MyTable where MyField in (1, 2, 3, 4) that does an indexed
scan.

> 3. For low selectivity indices you add the PK column to the index.
>

This is gone for FB 2.0 the new index structure remove that problem with
a lot of duplicate values and the garbage collector, also the new index
structure are faster then the old one.

Did you give a try on FB 2.0 ? Maybe some of your problems could be
solved on the new version.

...snip...

> But what I'm saying is that for a developer who has to write
> applications that support different servers (and so can't spend weeks on
> learning all the peculiarities of every server he's going to support),
>

I completely disagree on this aspect as I said earlier.

> Firebird stands out as the one causing most problems regarding query
> performance. This is a real problem that I believe is stopping Firebird
> from becoming more successful than it is.
>

Can comment on this, I have developed systems for MSSQL (6.5), Oracle
(7). For MSSQL before I started to write the program I read an 800+ page
book from Ron Soukup (AFAIR the key developer behind MSSQL) and I can
assure you that I learned *a lot* of tricks to workaround performance
problems.

For the Oracle, that was a system for a big enterprise in Brazil, and
they have a lot of DBA's that has a lof of rules that should be
followed, all that rules are in place to avoid performance problems. I
hadn't studied Oracle in depth, since I was too much controlled by the
DBA's that I can't even use surrogate keys, I can't write views (as I
wish) and a lot of more rules. I stay on my place as a front-end
developer and forgot about trying to the the best as a back-end side
developer. After some years they come back asking me to change some PK's
on the key-central table from a natural key to a surrogate key, this
cause a lot of FK's to be redesigned, mey front-end should be redesigned
too, all reports involving those tables (wich was almost all !) and so
on. If they listenned to me and used surrogate key's on that table as I
suggested they have saved some bucks.

> Note: For the most part I've used Firebird and SQL Server, but I have
> also used DB2 and Oracle. In this context I have to admit that Oracle
> also has a lot of peculiarities and requires a lot of manual tuning.

Yeah, and the "quick start guide" is a 600+ page book :-)

> But
> on the other hand, if you invest in Oracle you probably do that fully
> aware of what it implies, and for a good(?) reason. DB2 also had a
> couple of quirks, for example I seem to recall a problem with searched
> deletes that didn't delete anything - that they raised an exception. So,
> I'm fully aware that Firebird isn't alone in having quirks.
>
> Kjell
>

Kjell, for sure the optimizer should be improved, and there are people
working on this, but I don't see it as bad as you say.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br