Quoting jankowalsky825 <jankowalsky825@...>:

I remember having to put where clause conditions on the join fields to
get Firebird to use the indices, for example:

where T.CLIENT_OUTID > 0 and C.SYMFONIANUMBER > 0

Assuming those conditions are always true, this might get the
optimizer to use the indices.

Louis Kleiman

> Hi guys.
>
> I've read whatever I could find about this problem. Suggestion is to
> create a proper indices and that should speed up the query but
> query optimizer do not take my indices into account and generates
> natural plan. I do not know what should I do?
>
> Here are my two tables:
>
> CREATE TABLE CLIENT
> (
> ID_CLIENT integer NOT NULL,
> RECORD_ID integer,
> RECORD_TIME timestamp DEFAULT CURRENT_TIMESTAMP,
> RECORD_ACTIVE integer DEFAULT 0,
> RECORD_ADD_USER integer,
> RECORD_EDIT_USER integer,
> SHORTNAME varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> NAME varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> CITY varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> STREET varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> ADRESS varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> HOMENUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> APPARTMENTNUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> POSTCODE varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> POST varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> CORESPCITY varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> CORESPSTREET varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> CORESPADRESS varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> CORESPHOMENUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> CORESPAPPARTMENTNUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> CORESPPOSTCODE varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> CORESPPOST varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> CONTACTNAME varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> CONTACTSURNAME varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> PHONE1 varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> PHONE2 varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> NOTICE varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> ISACTIVE integer DEFAULT 1,
> EXPORTED integer DEFAULT 0,
> NIP varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> REGON varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> TASKSBLOCKED integer DEFAULT 0,
> ARCHIVEFROMDATE date,
> SYMFONIANUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> TRASHNUMBER varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> TASKSBLOCKEDREASON varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> PAYTYPE integer,
> CLIENTCONFIRMATION integer,
> IDCLIENTGROUP integer,
> PURENIP varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> NOTICEPRINT integer,
> COUNTRYCODE varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> PESEL varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> KRS varchar(255) CHARACTER SET WIN1250 COLLATE PXW_PLK,
> CONSTRAINT PK_CLIENT PRIMARY KEY (ID_CLIENT)
> );
> CREATE INDEX CLIENT_ID_CLIENT_A ON CLIENT (ID_CLIENT);
> CREATE DESCENDING INDEX CLIENT_ID_CLIENT_D ON CLIENT (ID_CLIENT);
> CREATE INDEX CLIENT_SYMFONIANUMBER_A ON CLIENT (SYMFONIANUMBER);
> CREATE INDEX IDX_CLIENT ON CLIENT (RECORD_ACTIVE);
>
>
> CREATE GLOBAL TEMPORARY TABLE IMPORT_TEMP_LOCATION
> (
> OBJNUM integer NOT NULL,
> CITY varchar(255),
> IDCLIENT integer,
> IDLOCATION integer,
> LATITUDE integer,
> LONGITUDE integer,
> GEOCODED_LEVEL integer,
> POSITIONVERIFIED integer,
> CLIENT_NAME varchar(255),
> CLIENT_OUTID integer,
> STREET varchar(255),
> POSTCODE varchar(6),
> HOMENUMBER varchar(12),
> CONSTRAINT PK_IMPORT_TEMP_LOCATION_0 PRIMARY KEY (OBJNUM)
> )
> ON COMMIT PRESERVE ROWS;
> CREATE INDEX IDX_IMPORT_TEMP_LOCATION1 ON IMPORT_TEMP_LOCATION
> (CITY,POSTCODE,STREET,HOMENUMBER,CLIENT_NAME);
> CREATE INDEX IDX_IMPORT_TEMP_LOCATION2 ON IMPORT_TEMP_LOCATION
> (CITY,CLIENT_OUTID,STREET,POSTCODE,HOMENUMBER);
> CREATE INDEX IDX_IMPORT_TEMP_LOCATION3 ON IMPORT_TEMP_LOCATION
> (CLIENT_OUTID);
>
>
> In CLIENT table I have 167502 rows and in IMPORT_TEMP_LOCATION I
> have 203 rows and it takes 1:30 minutes to execute my query.
>
> SELECT C.ID_CLIENT FROM IMPORT_TEMP_LOCATION T
> LEFT OUTER JOIN CLIENT C ON (T.CLIENT_OUTID = C.SYMFONIANUMBER)
>
>
> I've recomputed indices statistics but it does not help.
>
> Here is the info about my query:
>
> Executing statement...
> Statement executed (elapsed time: 0.000s).
> 72911748 fetches, 0 marks, 2450838 reads, 0 writes.
> 0 inserts, 0 updates, 0 deletes, 0 index, 34006763 seq.
> Delta memory: 17956 bytes.
> Total execution time: 0:01:36 (hh:mm:ss)
> Script execution finished.
> Rolling back the transaction...
> Transaction rolled back (elapsed time: 0.001s).
> Starting transaction...
> Preparing statement: SELECT C.ID_CLIENT FROM IMPORT_TEMP_LOCATION T
> LEFT OUTER JOIN CLIENT C ON (T.CLIENT_OUTID = C.SYMFONIANUMBER)
> Statement prepared (elapsed time: 0.001s).
> Field #01: CLIENT.ID_CLIENT Alias:ID_CLIENT Type:INTEGER
> PLAN JOIN (T NATURAL, C NATURAL)
>
>
> Executing statement...
> Statement executed (elapsed time: 0.000s).
> 72911748 fetches, 0 marks, 2450840 reads, 0 writes.
> 0 inserts, 0 updates, 0 deletes, 0 index, 34006763 seq.
> Delta memory: 18072 bytes.
> Total execution time: 0:01:36 (hh:mm:ss)
> Script execution finished.
>
>
> Maybe the problem is that I use GLOBAL TEMPORARY TABLE ?
>
> Thanks for any help
>
>
>

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.