AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren

Index wird nicht verwendet

Ein Thema von ZOD · begonnen am 21. Nov 2017 · letzter Beitrag vom 21. Nov 2017
Antwort Antwort
ZOD

Registriert seit: 6. Mai 2009
97 Beiträge
 
#1

Index wird nicht verwendet

  Alt 21. Nov 2017, 13:27
Datenbank: Firebird • Version: 2.5 • Zugriff über: ISQL / ibexpert
Hallo Zusammen,

ich habe folgende Datenbanktabelle:

Code:
/******************************************************************************/
/****              Generated by IBExpert 21.11.2017 13:19:19               ****/
/******************************************************************************/

/******************************************************************************/
/****     Following SET SQL DIALECT is just for the Database Comparer     ****/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/****                                Tables                               ****/
/******************************************************************************/
CREATE GENERATOR G_TEST;

CREATE TABLE T_TEST (
    ID        INTEGER NOT NULL,
    NAME      VARCHAR(201),
    KEY_VALUE INTEGER
);


/******************************************************************************/
/****                             Primary keys                            ****/
/******************************************************************************/

ALTER TABLE T_TEST ADD CONSTRAINT PK_T_TEST PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                               ****/
/******************************************************************************/
CREATE INDEX T_TEST_IDX1 ON T_TEST (NAME);
CREATE INDEX T_TEST_IDX2 ON T_TEST (KEY_VALUE);


/******************************************************************************/
/****                               Triggers                              ****/
/******************************************************************************/
SET TERM ^ ;


/******************************************************************************/
/****                         Triggers for tables                         ****/
/******************************************************************************/
/* Trigger: T_TEST_BI0 */
CREATE OR ALTER TRIGGER T_TEST_BI0 FOR T_TEST
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  /* Trigger text */
  if (new.id is null) then
    new.id = GEN_ID(G_TEST, 1);
end
^

SET TERM ; ^
Die Tabelle habe ich mit 30.000 Testdatensätzen befüllt.

Wenn ich nun folgenden Abruf mache:

Code:
select
t.*
from t_test t
where
(
  (:test_name = cast('*' as varchar(201)))
  or
  ((not (:test_name = cast('*' as varchar(201)))) and (f_lrtrim8(f_upper(t.name)) = f_lrtrim(f_upper(:test_name))))
)
and
(
  (:test_value = cast('*' as varchar(201)))
  or
  ((not (:test_value = cast('*' as varchar(201)))) and (t.key_value = :test_value))
)
und dabei folgende Parmeterbelegung verwende:

Code:
TEST_NAME = '*' <- also nur das Zeichen als String
TEST_VALUE = 12
dann wird folgender Plan verwendet:
Code:
Plan:
PLAN (T NATURAL)
Der Index wird also nicht benutzt.

Ändere ich die Abfrage wie folgt:

Code:
select
t.*
from t_test t
where
t.name = f_lrtrim(f_upper(:test_name))
and
t.key_value = :test_value
wird bei gleicher Parameterbelegung dieser Plan verwendet:

Code:
Plan:
PLAN (T INDEX (T_TEST_IDX1, T_TEST_IDX2))
Jetzt wird der Index benutzt.

Warum wird bei der ersten Abfrage der Index nicht verwendet und wie kann ich das vermeiden?
Ich vermute, dass dies an der Parameterabfrage liegt.

Anmerkung:
dies ist ein vereinfachtes Beispiel aus einer komplexeren Konstellation heraus.
Es ist daher wirklich nicht einfach möglich, die Parameter zu vermeiden bzw. anders zu belegen ;-(.



Danke.
  Mit Zitat antworten Zitat
ZOD

Registriert seit: 6. Mai 2009
97 Beiträge
 
#2

AW: Index wird nicht verwendet

  Alt 21. Nov 2017, 13:35
Sorry - es fehlt noch eine Information:

Wenn ich die Parameter wie folgt belege:

Code:
TEST_NAME = 'TEST' <- also nur die Zeichen als String
TEST_VALUE = 12
dann habe ich das gleiche Verhalten, der Plan für die erste Abfrage wir ohne Index erzeugt und bei der zweiten Abfrage mit Index.

Geändert von ZOD (21. Nov 2017 um 13:47 Uhr)
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#3

AW: Index wird nicht verwendet

  Alt 21. Nov 2017, 13:59
Mal so grob überflogen:
(f_lrtrim8(f_upper(t.name)) = f_lrtrim(f_upper(:test_name))
enthält eine Funktion die auf Feld angewendet wird.
Damit ist ein Index idR. nicht mehr zu gebrauchen.
Ausnahme sind extra dafür erstellte Function Based Indices.


Das OR kann auch eine Rolle spielen, wobei ich mir nicht sicher bin (wie bei allen Optimizern letztlich) wie geschickt der Optimizer die Anfrage und die Indizierung und die vorhandene "Datenlage" auswertet.

Was passieren kann:
1 OR zwingt das System dazu beide Bedingungen unabhängig zu prüfen.
(auch wenn hier die Besonderheit vorliegt, dass kein Feldinhalt geprüft wird, sondern ein Parameter selbst- was der Optimizer vielleicht auch nicht schnallt)
Mögliche Logik/Schlussfolgerung des Optimizers:
Wenn ich eh mehrere Dinge prüfen muss, ist es schneller, einmal alles zu scannen (egal welche Indizes da sind).

Die Funktionsproblematik Upper(Feldname) bekommst Du vielleicht in den Griff, wenn Du im Trigger schon das Upper machst und nicht mehr in der Abfrage. Geht natürlich nur, wenn der Originalwert unbedeutend ist.
Gruß, Jo
  Mit Zitat antworten Zitat
ZOD

Registriert seit: 6. Mai 2009
97 Beiträge
 
#4

AW: Index wird nicht verwendet

  Alt 21. Nov 2017, 14:12
@jobo
heute scheint mein zentrlaler Verarbeitungsknoten (Gehirn) wirklich einen Knoten zu haben.

Als erstes eine Fehlerkorrektur:

Code:
  (:test_value = cast('*' as varchar(201)))
  or
  ((not (:test_value = cast('*' as varchar(201)))) and (t.key_value = :test_value))
ist bei einem Integer-Feld (key_value) nicht sehr sinnvoll. Sorry - das ist mir beim "Vereinfachen" passiert.
Aber dieser Teil der
Code:
where
Bedingung kann auch einfach wegbleiben - das Verhalten ist das gleiche.

Aber das verstehe ich nicht:
Zitat:
(f_lrtrim8(f_upper(t.name)) = f_lrtrim(f_upper(:test_name))
enthält eine Funktion die auf Feld angewendet wird.
Damit ist ein Index idR. nicht mehr zu gebrauchen.
Bisher hatte ich hier nie Probleme. Als Funktionsbibliothe ist freeadhocudf eingebunden.
  Mit Zitat antworten Zitat
mkinzler
(Moderator)

Registriert seit: 9. Dez 2005
Ort: Heilbronn
39.858 Beiträge
 
Delphi 11 Alexandria
 
#5

AW: Index wird nicht verwendet

  Alt 21. Nov 2017, 14:19
Das Problem ist nicht die funktion sondern deren Verwendung in der Abfrage. Im index stehen die originalen mixed-case Strings der Tabelle mit voller Länge und nicht die getrimmten Uppercase-Varianten.
Wandele den Index in einen entsprechenden expression index, dann sollte der Index verwendet werden.
Markus Kinzler

Geändert von mkinzler (21. Nov 2017 um 14:22 Uhr)
  Mit Zitat antworten Zitat
bnreimer42

Registriert seit: 26. Mai 2013
Ort: Erlangen, Franken
126 Beiträge
 
Delphi 12 Athens
 
#6

AW: Index wird nicht verwendet

  Alt 21. Nov 2017, 14:21
Woher soll der DB-Server - also konkret der Optimizer wissen - wie die Funktion die auf Feld oder Parameter angewendet wird, diese verändert?

Es hilft, einen Index mit dem Ausdruck zu erstellen, auf den Du abprüfst.
Siehe http://www.firebirdtest.com/file/doc...5-ddl-idx.html unter Computed (Expression) Indexes bzw. ein Beispiel ganz unten.

Achte darauf, beim Index exakt die selben Funktionsaufrufe zu verwenden, wie bei der Abfrage, sonst nutzt er ihn nicht. Konkret ist es ja egal, ob erst trim oder erst upper angewendet wird.


Übrigens gibt es die Funktionen auch eingebaut:
https://firebirdsql.org/refdocs/lang...unc-upper.html
https://firebirdsql.org/refdocs/lang...func-trim.html

Ändert aber am Problem nichts.
Björn Reimer
  Mit Zitat antworten Zitat
ZOD

Registriert seit: 6. Mai 2009
97 Beiträge
 
#7

AW: Index wird nicht verwendet

  Alt 21. Nov 2017, 14:28
danke für die Antworten.

Verständnisfrage:
ich muss also IMMER einen Index anlegen, der EXAKT die Funktionsaufrufe der where-Bedingung erfüllt?
Bisher dachte ich immer, dass der Optimizer für die angesprochenen Spalten das Vorhandensein eines Index prüft und ihn dann verwendet,
eben auch bei Weiterbehandlung in einem Funktionsaufruf.
  Mit Zitat antworten Zitat
mkinzler
(Moderator)

Registriert seit: 9. Dez 2005
Ort: Heilbronn
39.858 Beiträge
 
Delphi 11 Alexandria
 
#8

AW: Index wird nicht verwendet

  Alt 21. Nov 2017, 14:33
Dies wäre dann aber nicht schneller als die Suche in der Originaltabelle.
Markus Kinzler
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#9

AW: Index wird nicht verwendet

  Alt 21. Nov 2017, 19:19
ich muss also IMMER einen Index anlegen, der EXAKT die Funktionsaufrufe der where-Bedingung erfüllt?
Wenn Du eine Funktion in der Whereclause auf ein Feld legst, ja.
jein.
nein.

Es ist ja relativ klar, dass man nicht für alle möglichen Abfragen der Welt funkionale Indizes bauen kann.

Du musst also immer überlegen, wie Du die Dinge anpackst.

Bspw. wäre ein sinnvolles Vorgehen:
Funktionsaufruf in Where Clause nur in hinreichend kleinen Teilmengen.
Einschränkung großer Mengen demzufolge über andere sinnvolle Kriterien wie Foreign Keys, Datumswerte, -Ranges, Klassen usw. usf. Hauptsache, die Einschränkungen sind stark und natürlich index fähig.

Wenn dann am Ende auch ein paar Hundert Records übrigbleiben, ist es kein Problem, darauf mit Funktionen zu arbeiten. Dabei ist es dann fallweise dennoch so, dass der Optimizer mal auf der falschen Fährte ist, dann muss man helfend eingreifen.

Bei mir sieht es z.B. so aus, dass ich teilweise Views verwende, die gar nicht ohne Where Clause abgefragt werden können, weil viel zu lahm. Erst im Kontext der Anwendung mit den Parametern, die fachlich sinnvoll und notwendig sind, spuckt der View schön brav Datensätze aus, blitzschnell.
Gruß, Jo
  Mit Zitat antworten Zitat
Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 18:16 Uhr.
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz