Hello Nick,

thanks for your quick response.

The query takes still one minute. Here the ddl of the table, at this time >
100000 records, for each unique id 30 records (30 pictures and the path)


CREATE GENERATOR GEN_T_PICTURES_ID;

CREATE TABLE T_PICTURES (
ID INTEGER NOT NULL,
KD_ID TIMESTAMP NOT NULL,
LI_ID INTEGER NOT NULL,
IO INTEGER,
BNR INTEGER,
PFAD VARCHAR(100) COLLATE DE_DE,
STATUS INTEGER
);

ALTER TABLE T_PICTURES ADD CONSTRAINT PK_T_PICTURES PRIMARY KEY (ID);

ALTER TABLE T_PICTURES ADD CONSTRAINT FK_T_PICTURES_1 FOREIGN KEY (KD_ID,
LI_ID) REFERENCES TKOPFDATEN (ID, LI) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE INDEX T_PICTURES_IDX1 ON T_PICTURES (STATUS);

SET TERM ^ ;

/* Trigger: T_PICTURES_BI */
CREATE OR ALTER TRIGGER T_PICTURES_BI FOR T_PICTURES
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_t_pictures_id,1);
end
^

SET TERM ; ^

-----Ursprüngliche Nachricht-----
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Im Auftrag von Nick Upson
Gesendet: Mittwoch, 21. März 2012 16:11
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Question about better performance Query

try:

select first 1 a.pfad

from t_pictures a where status <> 6 and

not exists (select 1 from t_pictures where status < 5 and kd_id = a.kd_id
and li_id = a.li_id)

into :pfad;


(also first 1 will return just 1 record but it could be any record in the
set as you have no "order by")

Nick Upson

On 21 March 2012 15:07, Olaf Kluge <olaf.kluge@...> wrote:

> **
>
>
> Hello,
>
> at this time I have create an statement:
>
> select first 1 a.pfad
>
> from t_pictures a where status <> 6 and
>
> (select count(*) from t_pictures where status < 5 and kd_id = a.kd_id
> and li_id = a.li_id) = 0
>
> into :pfad;
>
> pfad = path for an file
>
> Now we have thousands of records in this table and the execution takes
> some minutes. How can I make this statement better? I would check If
> there is for a record (unique with kd_id and li_id (primary key
> reference) one record with status < 5. If no, I can delete the folder
> then there is no now no picture in it.
>
> Thanks for helping.
>
> Best regards.
>
> Olaf
>
> [Non-text portions of this message have been removed]
>
>
>


[Non-text portions of this message have been removed]



------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the main
(top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links