Index bei StoredProcezure

[IBExpert]

sende doch mal die kompletten metadaten deiner testtabelle (also mit create table und indizes usw, findest du in ibexpert auf der seite ddl

und dann noch den Inhalt der Seite Performanceanalyse in IBExpert beim sql editor (ggf als screenshot mit allen infos)

Dann kann man mehr sagen. so auf anhieb würde ich aber denke, das dabei ein kombinierter index über beide felder
ein ganz brauchbares ergebnis liefern sollte und keineswgs 51 minuten brauchen, das muss auch komplett ohne
indizes eine sehr lahm gurke sein oder eine sehr unglückliche Tabellendefinition (viele und zu lange
varchar/char felder zum beispiel).

[Dumpfbacke]

Es hat leider etwas länger gedauert. Ich habe mal nur die beiden benötigten Felder in eine extra Tabelle in eine extra Datenbak geschoben. Dann die beiden Felder aus der original Tabelle mit den Werten befüllt. Hier die gewünschten Daten.

Delphi-Quellcode:

SET SQL DIALECT 3;0



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE TABELLE1 (
    FELD1  VARCHAR(25),
    FELD2  VARCHAR(4)
);




/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX TABELLE1_IDX1 ON TABELLE1 (FELD1, FELD2);


/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/

Die Procedure Feld1 ist ein VarChar25 und Feld2 ist ein Varschar4

Delphi-Quellcode:

begin
  /* Procedure Text */
  for
  Select TB.Feld1, TB.feld2
  From Tabelle1 TB
   into :Feld1,Feld2
  do
  suspend;
end

Die Abfrage auf die Tabelle selbst

Delphi-Quellcode:

Select Feld1,Feld2
from tabelle1
where Feld1 starting with 'A' and Feld2 starting with '7'
Group by Feld1, Feld2

Ergibt unter Performace Analysis

Delphi-Quellcode:

Plan
PLAN (TABELLE1 ORDER TABELLE1_IDX1 INDEX (TABELLE1_IDX1))

------ Performance info ------
Prepare time = 0ms
Execute time = 4s 968ms
Avg fetch time = 261,47 ms
Current memory = 12.261.872
Max memory = 133.414.344
Memory buffers = 1.024
Reads from disk to cache = 31.451
Writes from cache to disk = 0
Fetches from cache = 8.549.654

Die Abfrage auf die Procedure

Delphi-Quellcode:

Select Feld1,Feld2
from NEW_PROCEDURE
where Feld1 starting with 'A' and Feld2 starting with '7'
Group by Feld1, Feld2

Ergibt unter Performace Analysis

Delphi-Quellcode:

Plan
PLAN SORT ((TB NATURAL))

------ Performance info ------
Prepare time = 0ms
Execute time = 8m 56s 250ms
Avg fetch time = 28.223,68 ms
Current memory = 29.439.032
Max memory = 133.414.344
Memory buffers = 1.024
Reads from disk to cache = 680.390
Writes from cache to disk = 0
Fetches from cache = 174.652.879

Da ja hier die Tabelle nur noch zwei Felder hat geht es vermutlich etwas besser aber der Index wird hier einfach nicht genutzt. Wie gesagt ich habe hier eine Tabelle mit nur den zwei Felder angelegt und nur diese befüllt. Das ganze ich schon merkwürde und ich weiß nicht warum er hier keinen Index nehemen möchte.

Ja die Kiste ist nicht die schnellste aber für das was wir damit machen recht es uns noch aus. Werden wir irgendwann mal tauschen müssen.

Vielen Dank schon einmal für die Hilfe.

[Delphi.Narium]

Wenn mich nicht alles täuscht, machst Du folgendes:

In der Prozedur holst Du alle 86 Mio Datensätze ohne Einschränkung aus der Tabelle.

Über das Ergebnis machst Du nun eine Abfrage mit einer Einschränkung in der Wherebedingung.

Meiner Meinung nach muss die einschränkende Wherebedingung in die Prozedure, da nur dort die Nutzung des Index sinnvoll möglich ist.

Letztlich machst Du aktuell sowas:

SQL-Code:

select feld1, feld2 from
(
  select TB.Feld1, TB.feld2 from Tabelle1 TB  -- ohne Einschränkung ist die Nutzung eines Index nicht sinnvoll
)
where Feld1 starting with 'A' and Feld2 starting with '7' -- und hier gibt es keinen Index, der genutzt werden könnte.

Sinngemäß sollte sowas gehen: (mit minimaler Datenmenge getestet und hingedaddelt)

SQL-Code:

SET TERM ^ ;
create procedure NEW_PROCEDURE(Start1 varchar(32), Start2 varchar(32))
  returns (Feld1 varchar(32), Feld2 varchar(32))
as
begin
  for
    select TB.Feld1, TB.feld2
    from Tabelle1 TB
    where TB.Feld1 starting with :Start1 and TB.Feld2 starting with :Start2
    group by TB.Feld1, TB.Feld2 
    into :Feld1, Feld2
  do
    suspend;
end^

SET TERM ; ^

Aufruf: Select Feld1, Feld2 from NEW_PROCEDURE('A','7')
Zumindest sollte das mal einen Versuch wert sein.

Ansonsten Doku zu Rate ziehen: FOR SELECT ... INTO ... DO, zweites Beispiel. FireBird - Create PROCEDURE

[IBExpert]

wenn du auf einer sp einen select machst, dann wird dessen datenmenge immer wie Delphi.Narium
schon schrieb komplett ausgewertet (es sei denn da drin sind first oder rows) anweisungen,
das ist aber unwichtig in diesem Kontext.

Es ist wirklich do das die SP intern alle 86 mio records aklappert und
die mit dem Suspend an die ergebnismenge sendet, sprich das was dann
außerhalb der sp als where bedingung dann dessen ergebnisse noch einschränkt.

Im Gegensatz zu views, die eigentlich nur platzhalter für komplexe selects sind
und daher vom optimierer die intern benutzten mit anderen tabellen indiziert
abgefragt werden können, geht das bei sp gar nicht.

Beispiel

Code:

create procedure test
returns (tier varchar(20))
as
begin
  tier='Hund';
  suspend;
  tier='Katze';
  suspend;
  tier='Maus';
  suspend;
end

auch diese datenmenge kannst du mit einem select abfragen, per join mit
anderen daten verknüpfen, aber es ist offensichtlich, das da gar keine
tabelle intern benutzt wird. Ein Select außerhalb einer SP kann die
ergebnismenge zwar noch beliebig einschränken oder sortieren, der
optimierer in fb hab dabei auch nichts damit zu tun.