100.000 Datensätze in 22 Minuten - geht das noch schneller?

[Piro]

Moin zusammen,

ich muss mit einer Stored Prozedure um die 100.000 Datensätze importieren. Später können das bis zu 500.000 Datensätze werden

Durch die Stored Prozedure "UPSERT" läuft der Prozess ca. 22 Minuten. Geht das noch schneller? Ich werde den Job Nachts ausführen. Dadurch ist die Zeit nicht so kritisch. Es wäre aber trotzdem gut zu wissen, ob ich schon alles optimiert habe.

In diesem Thread sieht man die abgespeckte Prozedure sowie den Quellcode.
http://www.delphipraxis.net/159900-s...tgestellt.html

Danke im Voraus für Verbesserungen und Ideen.
Sven

[mkinzler]

Ich würde alle oder zumindest mehrere Inserts innerhalb einer Transktion ausführen und nicht jeden insert in einen
MSSQL sollte zudem MERGE kennen

[borwin]

Wie sieht die Zieltabelle aus? Werden die Werte auf mehrer Tabellen verteilt?
Gibt es noch Trigger zu der Tabelle? Viewiel Indexe sind in der Tabelle?
Wie sehen die Daten aus? Welches Format haben die Importdaten?
Erfolgt der Import über einen Client?

Fragen die für eine Bewertung notwendig sind.

Gruß Borwin

[Piro]

Der Import der Daten erfolgt von einem Client (lässt sich nicht ändern). Die Daten kommen aus einer Textdatei (Semikolon getrennt).
Es wird nur eine SQL Tabelle verwendet, die nur einen Primärschlüssel besitzt.

Code:

...
CREATE TABLE [dbo].[mat_beweg](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [snr] [nvarchar](9) NOT NULL,
   [b_ftag] [nvarchar](4) NOT NULL,
   [b_datum] [nvarchar](8) NOT NULL,
   [b_art] [nvarchar](2) NULL,
   [b_folge] [nvarchar](3) NULL,
   [ktr] [nvarchar](9) NULL,
   [znr] [nvarchar](3) NULL,
   [sinr] [nvarchar](7) NULL,
   [kst] [nvarchar](4) NULL,
   [k_art] [nvarchar](2) NULL,
   [umbnr] [nvarchar](5) NULL,
   [kz_es] [nvarchar](1) NULL,
   [bestand] [nvarchar](12) NULL,
   [menge] [nvarchar](12) NULL,
   [menge_storno] [nvarchar](12) NULL,
   [wemnr] [nvarchar](6) NULL,
   [bnr] [nvarchar](9) NULL,
   [lnr] [nvarchar](11) NULL,
   [preise] [nvarchar](2) NULL,
   [bpreis] [nvarchar](10) NULL,
   [preisart] [nvarchar](1) NULL,
   [dpreis] [nvarchar](10) NULL,
   [bwert] [nvarchar](11) NULL,
   [buch_datum] [nvarchar](8) NULL,
   [buch_grund] [nvarchar](20) NULL,
   [buch_user] [nvarchar](8) NULL,
   [zchnr] [nvarchar](38) NULL,
   [benennung] [nvarchar](36) NULL,
   [lieferant] [nvarchar](18) NULL,
   [lastupdate] [datetime] NOT NULL,
 CONSTRAINT [PK_mat_beweg] PRIMARY KEY CLUSTERED 
(
   [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Stored Procedure "UPSERT"

Code:

/****** Objekt:  StoredProcedure [dbo].[sp_upsert]    Skriptdatum: 04/18/2011 12:58:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_upsert]

   -- Add the parameters for the stored procedure here
   @snr nvarchar(9),
   @b_ftag nvarchar(4),
   @b_datum nvarchar(8),
        ..., 
   @lastupdate datetime
AS
SET transaction isolation level SERIALIZABLE
SET NOCOUNT ON
BEGIN TRANSACTION Upsert
   
   update mat_beweg set snr = @snr, 
                         b_ftag = @b_ftag,
                         b_datum = @b_datum,
                         ...
         lastupdate = @lastupdate 
               where snr = @snr

   if @@rowcount = 0 and @@error=0
   begin
      insert into mat_beweg (snr, b_ftag, b_datum, ..., lastupdate) 
                    values (@snr, @b_ftag, @b_datum, ..., @lastupdate)
   end
COMMIT TRANSACTION Upsert

Aufruf aus dem Program

Delphi-Quellcode:

...
with sp_upsert do
begin
  ProcedureName := 'sp_upsert';

  // Parameter-Liste aktualisieren
  Parameters.Refresh;

  Prepared := True;

  for i:=0 to 100000 do // symbolisch für die Datensätze
  begin
    Parameters.ParamValues['@snr']        := IntToStr(i);
    Parameters.ParamValues['@b_ftag']     := '6272';
    Parameters.ParamValues['@b_datum']    := '20110418';
    Parameters.ParamValues['@lastupdate'] := Now;
    ...
    ExecProc;
  end;

  Prepared := False;
end;

Das ganze ist nur ein einfacher Import (INSERT oder UPDATE). Ich hoffe die Infos sind ausreichend. Ein Merge funktioniert nicht, da ich SQL Server 2005 Express habe und die Funktion erst ab 2008 existiert.

[Sir Rufo]

leg mal auf

Code:

snr

einen (Unique-) Index, dann geht das auch schneller

[taveuni]

Hallo,

Ich bin mir nicht sicher ob eine mehrfach aufgerufene SP für einen einfachen INSERT überhaupt
einen Performance Vorteil bringt.

Bezüglich des fehlenden MERGE:
Es gibt die Möglichkeit mit UNION ALL

Delphi-Quellcode:

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5

Die SELECTS kannst Du im Loop setzen.

[Sir Rufo]

Zitat von taveuni:

Hallo,

Ich bin mir nicht sicher ob eine mehrfach aufgerufene SP für einen einfachen INSERT überhaupt
einen Performance Vorteil bringt.

Doch, dafür wird die Anweisung ja Prepared

Zitat von taveuni:

Bezüglich des fehlenden MERGE:
Es gibt die Möglichkeit mit UNION ALL

Delphi-Quellcode:

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5

Die SELECTS kannst Du im Loop setzen.

Nur, dass dann keine Parameter gehen

[borwin]

Mit dem SQL-Server habe ich zwar nicht viel am Hut.
Ich würde noch folgendes überlegen. Das gesamte Textfile der Procedure übergeben und dann in der SP
über einen Loop das Insert ausführen. Ich denke das bring bestimmt was.
Das Ergebnis würde mich schon interessieren. Sollte auch nicht so schwerr sein.


Gruß Borwin

[jobo]

Ich würde in jedem Fall das commit weglassen, so etwas gehört m.E. nicht an eine solche Stelle.
(Nicht nur wg. Performance- du hast dann nicht nur 100000 Datensätze, sondern eben auch 100000 commits, mit denen Du nichts gewinnst, es bedeutet auch Totalverlust der Transaktionskontrolle)

Wenn möglich/ nötig würde ich die Datei auf den Server laden und von dort weiterverarbeiten. Da ich nicht weiß, ob ms sql beim Zugriff auf externe Daten in diesem Fall auch mit Where Clause brauchbar arbeitet, empfiehlt sich evtl. eine separate Interface Tabelle, die nur die CSV Daten aufnimmt. Die kann mit externen Tools wie BCP(?) oder mit Bulk Insert from File befüllt werden. Im 2. Schritt dann Insert/Update, aber nicht satzweise, bitte.

[Blup]

Für optimale Geschwindig würde ich ein SQL-Script mit allen Insert und Update auf dem Client erzeugen und in einem Schritt durch den Server ausführen lassen.