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: [email protected]
[mailto:[email protected]] Im Auftrag von Nick Upson
Gesendet: Mittwoch, 21. März 2012 16:11
An: [email protected]
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 <[email protected]> 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
