WHAT!

10 minutes to execute that simple query? Something is seriously wrong, it doesn't take 10 minutes to go through 18000 records - it should finish in a few seconds at worst...

I have no idea how it could take this long. I'm curious enough to want to have a look at your database (and download Fb 2.1 RC1), but I haven't any time to do it this evening. If it doesn't contain any sensitive or secret information, it would be nice if you either uploaded it to this yahoogroup (attachments aren't allowed) or pasted the CREATE TABLE, CREATE INDEX statements etc. to this list. I assume MOVI.PROC to be reasonably selective?

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of danyschaer
Sent: 25. februar 2008 15:33
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: derived table within EXISTS (was: Different performance Superserver VS. Embeded ?)

Hi Set,

Yeah, more choices = more complex.

Is truth, as I told before, our select statments are not perfect,
they can be better, I we will improve them.

I think I understand what you say about the use of the EXISTS clause,
and I will see if I can do things in other ways.

But also is truth we tested them on Oracle and Postgree, and the
results are fine.

I don't like to say that, but I tested your suggested sintaxis, and
does not run instantly as you expect, it need a little more time than
mine:

Starting transaction...
Preparing query: select p.PROC, p.EXP1 as EXPE, p.ACTO, p.DEMA,
p.OBSE, t.DSCR as D_TPRO
from PROC p
left join TPRO t on p.TPRO=t.TPRO
where exists(select * from MOVI m
where p.PROC = m.PROC
and m.TIPO = 'B')
order by lower(p.ACTO)
Prepare time: 00:00:00.
Field #01: PROC.PROC Alias:PROC Type:STRING(10)
Field #02: PROC.EXP1 Alias:EXPE Type:STRING(18)
Field #03: PROC.ACTO Alias:ACTO Type:STRING(200)
Field #04: PROC.DEMA Alias:DEMA Type:STRING(200)
Field #05: PROC.OBSE Alias:OBSE Type:STRING(120)
Field #06: TPRO.DSCR Alias:D_TPRO Type:STRING(120)
PLAN (M INDEX (MOVI_PROC))
PLAN SORT (JOIN (P NATURAL, T INDEX (TPRO_PK)))


Executing...
Done.
429914 fetches, 8 marks, 139758 reads, 8 writes.
0 inserts, 0 updates, 0 deletes, 151675 index, 33586 seq.
Delta memory: 49644 bytes.
Execute time: 00:10:25.

Of course I want to contribute with developers team, just tell me
what I have to do. I can upload my test database and so.

BTW, we people like you and Helen, this forum is a very nice place
for living and working <G>, THANK YOU !!

Dany