> select first(8) ID from TBL_TEST
>
> where ID not in (select first(2) ID from TBL_TEST order by ID desc)
> order by ID desc
>
the execution plan can be show e.g. with flamerobin client.
The result should be the same as "select first 8 skip 2 from TBL_TEST
ORDER BY ID DESC"
Elmar
Hi HTH,
thanks for the detailed explainations.
I "inherited" the sql code but updated the firebird version. And was
wondering what was ruining the performance.
Basically I want the set of first 10 entries but without 2 with a special
property.
I will just query the first 10 then and remove the
25.01.2020 07:59, Matthias Winkler spmm...@gmail.com [firebird-support] wrote:
> The major problem is, that with firebird 2.1.3 the query also completes
> within 3 seconds.
> Something seems to have changed with the optimizer, execution plan or
> whatever (~ sorry I
> dont know the terms here)
>
As Karol writes, introducing a descending index on the ID field ought to
make your query quick (Firebird indices - and keys - are unidirectional).
Firebird has never been good with IN(), I was bitten around
1999, changed to using EXISTS and for the last 20 years I have never
missed IN() (and N
Thanks for all your replies:
The major problem is, that with firebird 2.1.3 the query also completes
within 3 seconds.
Something seems to have changed with the optimizer, execution plan or
whatever (~ sorry I dont know the terms here)
related to sorting, or double sorting, or sorting within "sub"-
Hi
„select first(8) ID from TBL_TEST
where ID not in (select first(2) ID from TBL_TEST order by ID desc)
order by ID desc”
few things:
Just hint but „first” is not function First(8) is same as First 8 😉
You should avoid NOT IN queries and use EXISTS/NOT EXISTS instead.
But if
24.01.2020 15:19, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote:
> It sounds to me like this is should be optimized by executing the
> uncorrelated sub-queries only once.
Yes, but Firebird optimizer cannot do it.
--
WBR, SD.
-
On 2020-01-24 14:39, Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support] wrote:
> 24.01.2020 14:29, Matthias Winkler spmm...@gmail.com [firebird-support]
> wrote:
>> I see the very same issue with firebird 3.0.4. Any ideas?
>
>You made nested loop on 18000*18000 records with sorting on
24.01.2020 15:01, Matthias Winkler spmm...@gmail.com [firebird-support] wrote:
> Is there a way to force limiting the result set before sorting it?
No. It is also meaningless because you in fact will get several
pseudo-random records.
Such result hardly have a practical purpose.
--
WBR,
@SD: Is there a way to force limiting the result set before sorting it?
On Fri, Jan 24, 2020 at 2:55 PM Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support] wrote:
> 24.01.2020 14:49, Matthias Winkler spmm...@gmail.com [firebird-support]
> wrote:
> > The result are just 8 entries. Sorting th
24.01.2020 14:49, Matthias Winkler spmm...@gmail.com [firebird-support] wrote:
> The result are just 8 entries. Sorting those 3 entries should then take no
> time at all, right?
Wrong. Sorting happens before result set limit apply.
> Any ideas?
Rule number one: never use "not in".
Lea
The first to lines of the query alone give a result in ~3 seconds:
select first(8) ID from TBL_TEST
where ID not in (select first(2) ID from TBL_TEST order by ID desc)
The result are just 8 entries. Sorting those 3 entries should then take no
time at all, right?
But by adding the la
24.01.2020 14:29, Matthias Winkler spmm...@gmail.com [firebird-support] wrote:
> I see the very same issue with firebird 3.0.4. Any ideas?
You made nested loop on 18000*18000 records with sorting on each itaretion.
It cannot
be fast.
--
WBR, SD.
-
2020. 01. 24. 14:29 keltezéssel, Matthias Winkler spmm...@gmail.com
[firebird-support] írta:
> @Mark: I see the very same issue with firebird 3.0.4. Any ideas?
I suggest try with 3.0.5 also.
Gabor
@Mark: I see the very same issue with firebird 3.0.4. Any ideas?
On Fri, Jan 24, 2020 at 1:46 PM Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] wrote:
>
>
> On 2020-01-24 13:29, Matthias Winkler spmm...@gmail.com
> [firebird-support] wrote:
> > Hello,
> >
> > With Firebird 2.1.7 this s
On 2020-01-24 13:29, Matthias Winkler spmm...@gmail.com
[firebird-support] wrote:
> Hello,
>
> With Firebird 2.1.7 this statement freezes my firebird server
> completely. Firebird will
> take 100% CPU.
>
>> select first(8) ID from TBL_TEST
>> where ID not in (select first(2) ID from TBL_TEST ord
Hello,
With Firebird 2.1.7 this statement freezes my firebird server completely.
Firebird will
take 100% CPU.
select first(8) ID from TBL_TEST
where ID not in (select first(2) ID from TBL_TEST order by ID desc)
order by ID desc
The used table has about 18`000 entries.
If I remove ONE of the
17 matches
Mail list logo
