Philippe Makowski wrote:

> we have an opportunity to have a session at Solution Linux 2007 (Paris)
> I will talk about migration from MsSQL to Firebird
> but I need some material, if you can give me some specific details about your
> experience of this kind of migration, I will be pleased .

I migrated some databases from MSSQL2000 to Firebird. The main problem
were the stored procedures and triggers which had to be rewritten. Not
complex, but there were a lot of them. One of the problems IIRC were
that SQL Server doesn't have Date type, only Timestamp, so there was
dillema whether to use Date in FB in case customer decides to switch
back. I'm not 100% sure about this, you may need to check.

The conversion was done by dumping the database DDL to a file, editing
it and creating a FB database from scratch. After that we used
IBDataPump or whatever is the name of that free tool that can pump the
data from any BDE-registrable database to IB/FB. Yes, we used BDE engine
for this one, and connected to MSSQL via ODBC connection. Actually, we
pumped to FB database in dialect 1, and later changed it to be a dialect
3 database.

All this happened in 2002 I think, so forgive me if I missed anything. I
see that you got no usable replies so far, so I figure even this is
better than nothing.

Anyway, after the conversion, the main problem with FB were views (and
STILL are). They have a lot of UNIONS and work painfully slow on FB
because FB doesn't use indexes. That customer still complains that FB is
much slower than MSSQL. For those interested, if you have a view like this:

create view v1 (x, y, z)
as
select x, y, z from table1
union all
select x, y, z from table2;

Both table1 and table2 have index their X field. Now, when you run:

select * from v1 where x = 10;

The plan is NATURAL for both tables. In the database we migrated from
MSSQL those tables have millions of records. The queries on MSSQL
sometimes work 1000 or more times faster. I guess I don't have to
explain a benefit of index to anyone here.

For some uses we rewrote some procedures to use:

create procedure p1 (x integer)
returns (y, z) as
begin
for select y, z from table1 where x = :x
into :y, :z
do suspend;

for select y, z from table2 where x = :x
into :y, :z
do suspend;
end

This works really fast, but we can't cover all the possible combinations
as there would be hundreds of procedures (some of the views have 20+
columns).

As I understand some FB2.x version should fix this and I'm eagerly
awaiting...

--
Milan Babuskov
http://swoes.blogspot.com/
http://www.flamerobin.org