Hi,

I'm not sure if this should take so long to process and that's why I
must ask:

Using Windows XP Home SP2 , Firebird Superserver 1.5.2
MS SQL Server Express database is on the same computer.

I'm importing not so large database from MS SQL Express. Database is
about 2,1 Gb in size.There is one table with a few milions of rows and
the second table has more then 100 milions rows (and is connected to
the first by FK (One to Many relation)).
Data pump is done using IBDataPump utility directly using some not so
complex SQL (but not direct table->table)


This is a first table:





CREATE TABLE SPRZEDAZ (
ID PK /* PK = BIGINT NOT NULL */,
IDMAG INTEGER NOT NULL,
IDKONTRH FK /* FK = BIGINT */,
TYP TYPDOK /* TYPDOK = VARCHAR(5) NOT NULL */,
NRDOK FK /* FK = BIGINT */,
WYSTAWIL USERID /* USERID = CHAR(20) DEFAULT USER NOT NULL */,
DATAOPERACJI STAMP /* STAMP = TIMESTAMP DEFAULT 'NOW' NOT NULL */,
DATASPRZEDAZY STAMP /* STAMP = TIMESTAMP DEFAULT 'NOW' NOT NULL */,
GODZINA SMALLINT DEFAULT 0 NOT NULL,
OSOBA VARCHAR(50) COLLATE PXW_PLK,
TERMINPL DATE NOT NULL,
RODZAJPL INTEGER NOT NULL,
NRKASY INTEGER DEFAULT NULL,
NRKASJERA INTEGER DEFAULT NULL,
NAZWAKASJERA VARCHAR(20) DEFAULT NULL COLLATE PXW_PLK,
FISKALNY BOOL /* BOOL = CHAR(1) NOT NULL CHECK (VALUE IN
('T','N')) */,
RABAT DECIMAL(18,4) DEFAULT 0 NOT NULL,
AKTYWNY AKTYWNYDOK /* AKTYWNYDOK = CHAR(1) DEFAULT 'A' NOT
NULL CHECK (VALUE IN ('A','B','U')) */,
UWAGI KOMENTARZ /* KOMENTARZ = VARCHAR(255) DEFAULT NULL */,
KOMENTARZ KOMENTARZ /* KOMENTARZ = VARCHAR(255) DEFAULT NULL */,
MARKER MARKER /* MARKER = CHAR(1) DEFAULT 'M' NOT NULL CHECK
(VALUE IN('M','O')) */,
GUID VARCHAR(30) DEFAULT NULL COLLATE PXW_PLK
);






ALTER TABLE SPRZEDAZ ADD CHECK ((GODZINA >= 0) AND (GODZINA <= 24));
ALTER TABLE SPRZEDAZ ADD CONSTRAINT SPRZEDAZ_PK PRIMARY KEY (ID);

CREATE INDEX SPRZEDAZ_IDX_DATA ON SPRZEDAZ (DATASPRZEDAZY, GODZINA, NRDOK);


After 2 hours I had imported *only* two milions of records from the
first table.

This is done using this procedure:

CREATE PROCEDURE INS_SPRZEDAZ (
ID BIGINT,
DATA DATE,
GODZINA SMALLINT,
IDMAG INTEGER,
KOMENTARZ VARCHAR(255),
FORMAPL CHAR(1),
RABAT DECIMAL(18,4))
AS
DECLARE VARIABLE FRMPL INTEGER;
BEGIN
SELECT FIRST 1 ID FROM FORMYPLATNOSCI WHERE SYMBOL = :FORMAPL INTO FRMPL;
UPDATE SPRZEDAZ SET
DATASPRZEDAZY=:DATA,
GODZINA=:GODZINA,
IDMAG=:IDMAG,
KOMENTARZ=:KOMENTARZ,
RODZAJPL=:FRMPL,
RABAT=:RABAT
WHERE ID=:ID;
IF (ROW_COUNT=0) THEN
INSERT INTO
SPRZEDAZ(ID,NRDOK,IDMAG,IDKONTRH,TYP,DATASPRZEDAZY,TERMINPL,GODZINA,RODZAJPL,FISKALNY,RABAT,KOMENTARZ)
VALUES(:ID,NULL,:IDMAG,1,'PA',:DATA,:DATA, :GODZINA, :FRMPL,
'T',:RABAT,:KOMENTARZ);
END


Nothing special I suppose.

There is one trick here : one trigger on that table is using a procedure
to get auditable doc number, but even if I disable this trigger I cannot
get much better performance.
I think it may be related to indexes on that table, right ?

Should I consider removing indexes and recreate it when data pump will
be finished ?
Or maybe it will be faster to pump data first to external file and use
such Firebird feature to import it later ?


Regards
Boguslaw Brandys