Funktion super langsam (ich versteh´s nicht)

[Saschbert]

Hallo Community,

ich habe da eine Funktion und einen Cursor, und beide läuft richtig lahm, dabei habe ich schon alles unternommen, um das ganze zu beschleunigen.

Zuerst die Problemstellung:
Es geht darum, herauszufinden ob eine Rechnung ins Jahr 2005 oder 2006 gehört. Dies mache ich Anhand von Belegen fest. Jeder Beleg hat ein eigenes Datum, und nun soll er pro Rechnung schauen, wieviele Beleg in 2005, bzw. wieviele in 2006 gehören. Je nachdem, wo der überwiegende Teil ist, dort in das Jahr soll diese Rechnung.

Nun habe ich die Funktion gestartet gehabt. Es betrifft 423.000 Rechnungen, also eine ganze Menge. Wie zu erwarten, ist mir mein Rechner abgeschmiert, nach 24 Tagen Laufzeit des Scripts, und es hat nichts gebracht, da ich keinen Cursor genutzt hatte (hatte gehofft, dann geht es schneller).

Nun habe ich folgende Dinge unternommen:
- alle relevanten Daten in eine neue Tabelle gesammelt, mit PK und Index
- alle entsprechenden Rechnungsnummern nochmals in eine weitere Tabelle, mit einem Feld "ZRA" (zeitliche Rechnungsabgrenzung), das mit 2005 oder 2006 gefüllt werden soll
- Cursor für die einzelnen Updatevorgänge gemacht

Naja, wie dem auch sei, es klappt zwar, aber sehr sehr schleppend. Und ich kann mir einfach nicht mehr vorstellen, woran das noch liegen kann.

1. Versuch auf unserem SQL2000 Cluster ... sehr langsam das ganze, und hat den Cluster unheimlich verlangsamt, so dass das Arbeiten auf den Datenbanken quasi unmöglich wurde

2. Versuch auf unserer kleinen Testumgebnung auf einerSQL2005´er Workstation, auf der nichts anderes läuft.

Insgesammt braucht er zurzeit auf der Testumgebung über zwei Tage für 2000 Rechnungen. Das ist quasi unakzeptabel. Vielleicht könnt Ihr Euch sowohl den Cursor als auch die Funktion mal anschauen. Findet Ihr das etwas, wodurch ich das ganze evtl. beschleunigen könnte?

Funktion:
declare @t table (PRecNr decimal(10), PicNr decimal(18), LDatum datetime) declare @zdatum datetime

insert into @t
select precnr, picnr, adatum
from temp_sozis where precnr = @precnr

set @zdatum = ( select min(zeitstempel) from temp_sozis
where precnr = @precnr and vorgangsart = 'Z'
)

update t
set ldatum = ( select max(von) from temp_sozis where picnr = t.picnr)
from @t t
where ldatum < ( select max(von) from temp_sozis where picnr = t.picnr)

update t
set ldatum = ( select max(bis) from temp_sozis where picnr = t.picnr)
from @t t
where ldatum < ( select max(bis) from temp_sozis where picnr = t.picnr)

if (select count(*) from @t where year(ldatum) >= @jahr) -- angebenes Jahr und später
<= (select count(*) from @t where year(ldatum) < @jahr) -- Vorjahre
and @zdatum <= @zradatum
set @jahr = @jahr - 1

return @jahr
end

Und noch der Cursor:
USE Testumgebung
GO
DECLARE sascha CURSOR FOR
SELECT *
FROM temp_sozis_precnr
WHERE zra not in (2005,2006)

OPEN sascha
GO

FETCH NEXT FROM sascha
GO

while( @@fetch_status = 0 )
begin
UPDATE temp_sozis_precnr SET zra = dbo.ZRA_sozis(precnr, 2006, '31.3.2006')
WHERE CURRENT OF sascha

fetch next from sascha
end

CLOSE sascha
DEALLOCATE sascha
GO

Für Hinweise bin ich super dankbar.

Lieben Gruß
Saschbert

[Andreas Kosch]

Hallo,
pauschale Aussagen sind immer zweifelhaft, aber ich würde zuerst folgendes machen:

a) Zum Test eine Datenbank verwenden, die im "Single-User-Modus" betrieben wird.

b) Die Daten in Tabellen ablegen, deren CLUSTERED INDEX eine Tabellenspalte nutzt, die als WHERE-Kriterium für zusammenhängende Vorgänge herangezogen wird. Eine SELECT MAX(x)-Abfrage wird dann sehr schnell sein, wenn x ein CLUSTERED INDEX ist.

c) Alle zusammengesetzten INDEXe und eventuell vorhandene TRIGGER deaktiveren/löschen. Da jede der 423.000 Rechnungen zu einem Schreibzugriff führt, stört der Overhead von Indexspalten, die beschrieben werden.

d) Es bleiben nur die INDEXe übrig, die von den SELECT-Abfragen auch wirklich eingebunden werden (Execution Plan des Optimizers).

e) Die <i>if (select count(*)</i>-Abfragen durch IF EXISTS ersetzen.

f) Die lokale TABLE-Variable ist nur für kleine Ergebnismengen geeignet, da es keine Optimierungen (Statistics, effiziente Ausfürungspläne) gibt. Der Einsatz eine "echten" temporären Tabelle ist bei größeren Datenmengen sinnvoll

[Saschbert]

Hallo,

ich versuch das gerade anzupassen/einzuändern.

Nun habe ich ein neues Problem: Kann ich nicht eine where-Klausel mehr oder weniger in eine Variable packen, und später einfach nur anfügen? Bei mir will das irgendwie nicht ...

DECLARE @variable varchar(30)
SET @variable = 'and x = 10'

.
.
.

WHERE v = 20
@variable

Warum das ganze? Ich habe mehrere Abfragen in dem ganzen, und möchte an einer Stelle eine weitere Klausel anzufügen. Sicherlich ginge es, wenn ich nur den Wert in eine Variable packe, und den Rest ins Script. Aber eine schönere Lösung wäre dies oben, wo ich eine komplette Klausel in eine Variable packen kann, die er verarbeitet.

Eine Alternative wäre auch das AND in das WHERE mit reinzuschreiben, aber dann direkt die Variable. Also quasi so:

WHERE v = 20
AND @variable

Vielen Dank für jede Hilfe, auch für die bisherige Hilfe, hat mich schonmal ein Stück voran gebracht.

Gruß
Saschber

[hwoess]

Dafür braucht man "dynamic SQL". Im einfachsten Fall schaut das dann etwa so aus:
exec ('select * from table where V = 20 ' + @variable)
Viel mehr Info dazu findest du hier:
http://www.insidesql.de/content/view/164/29/<br>
bye,
Helmu