We test simple procedure to update bigger number of records (400000) and we found on same HW so low performace using SQL 2005 (developer version), that it becomes unusable. On SQL 2000 it takes about 7 minutes, and on 2005 we do not get results in 10 hours!!! The procedure just makes data selection (it takes on both servers up to 1 minute) and than it updates rows in other table. Primary key of updated table is exact as is update condition, updated are columns wich just store values and are not part of any indexes. Select part selects about 380000 records, in destination table is about 400000 records. Has somebody idea where is problem On other way, data insert (400000 records) into table DWFTP takes on both servers about 7minutes. Here is procedure and DWFTP table definiton:
CREATE PROCEDURE DWImpFIBUInDWFTP @StandVom datetime AS
declare @TProjekt varchar(30),
@TPJahr integer,
@BABZeile varchar(5),
@Konto varchar(10),
@Buchung numeric(17,4),
@Vorbindung numeric(17,4),
@Zahlung numeric(17,4),
@Festlegung numeric(17,4),
@Sperre numeric(17,4)
DECLARE C1 CURSOR FOR
SELECT TProjekt, Jahr, BABZeile, Konto, Buchung, Vorbind, Festleg, Zahlung, SKonto
FROM ImportFIBU WHERE StandVom in (SELECT Max(StandVom) FROM ImportFIBU WHERE StandVom <=@StandVom GROUP BY Jahr)
OPEN C1
FETCH NEXT FROM C1
INTO @TProjekt, @TPJahr, @BABZeile, @Konto, @Buchung, @Vorbindung, @Festlegung, @Zahlung, @Sperre
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE DWFTP SET Buchung= @Buchung, Vorbindung= @Vorbindung, Zahlung= @Zahlung, Festlegung = @Festlegung, Sperre = @Sperre
WHERE
( TProjekt = @TProjekt AND TPJahr = @TPJahr AND StandVom = @StandVom AND BABZeile = @BABZeile AND Konto = @Konto)
FETCH NEXT FROM C1
INTO @TProjekt, @TPJahr, @BABZeile, @Konto, @Buchung, @Vorbindung, @Festlegung, @Zahlung, @Sperre
END
CLOSE C1
DEALLOCATE C1
CREATE TABLE DWFTP (
TProjekt VARCHAR(30) NOT NULL,
TPJahr NUMERIC(4) NOT NULL,
StandVom DATETIME NOT NULL,
BABZeile VARCHAR(5) NOT NULL,
Konto VARCHAR(10) NOT NULL,
BZeileText VARCHAR(70) NULL,
BW NUMERIC(17,4) DEFAULT 0.00 NULL,
AP NUMERIC(17,4) DEFAULT 0.00 NULL,
Buchung NUMERIC(17,4) DEFAULT 0.00 NULL,
Vorbindung NUMERIC(17,4) DEFAULT 0.00 NULL,
Zahlung NUMERIC(17,4) DEFAULT 0.00 NULL,
Festlegung NUMERIC(17,4) DEFAULT 0.00 NULL,
Sperre NUMERIC(17,4) DEFAULT 0.00 NULL,
ItemLevel NUMERIC(28) DEFAULT 0 NOT NULL,
Art VARCHAR(1) NOT NULL,
BW_S NUMERIC(17,4) DEFAULT 0.00 NULL,
AP_S NUMERIC(17,4) DEFAULT 0.00 NULL,
Buchung_S NUMERIC(17,4) DEFAULT 0.00 NULL,
Vorbindung_S NUMERIC(17,4) DEFAULT 0.00 NULL,
Zahlung_S NUMERIC(17,4) DEFAULT 0.00 NULL,
Festlegung_S NUMERIC(17,4) DEFAULT 0.00 NULL,
Sperre_S NUMERIC(17,4) DEFAULT 0.00 NULL,
BN NUMERIC(17,4) DEFAULT 0.00 NULL,
KA NUMERIC(17,4) DEFAULT 0.00 NULL,
BN_S NUMERIC(17,4) DEFAULT 0.00 NULL,
KA_S NUMERIC(17,4) DEFAULT 0.00 NULL,
PRIMARY KEY (TProjekt, TPJahr, StandVom, BABZeile, Konto)
)
GO
CREATE INDEX XIF672DWFTP ON DWFTP (TProjekt, TPJahr, StandVom)
GO
CREATE INDEX XIE1DWFTP ON DWFTP (TProjekt, StandVom, BABZeile, Konto)
GO

Why so big (terrible) performance difference between SQL 2000 and 2005
three twos
what's the Profiler telling you
NJDESI
Today I do tracing just that stored procedure. It looks that fetching data takes longer than update. I don't know why. The trace file is stored here (60kB) http://www.mms-softec.sk/petrik/trace/trace1.trc
Do you have an idea