Re: [firebird-support] Statement freezes firebird

[Elmar Haneke el...@haneke.de [firebird-support]]

>  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

Re: [firebird-support] Statement freezes firebird

[Matthias Winkler spmm...@gmail.com [firebird-support]]

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 unwanted
programattically in the application. The query for the first 10 is only
0.8s... So this will greatly improve the thing with the old and new version.

Of course theoretically comparing two sets can be expensive.

Thanks for the profound answers!!!

Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] <
firebird-support@yahoogroups.com> schrieb am Sa., 25. Jan. 2020, 11:27:

> 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)
> > related to sorting, or double sorting, or sorting within "sub"-queries.
>
>Yes, somewhere there was fixed a bug producing wrong query result if
> NOT IN was used.
>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

Re: [firebird-support] Statement freezes firebird

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

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)
> related to sorting, or double sorting, or sorting within "sub"-queries.

   Yes, somewhere there was fixed a bug producing wrong query result if NOT IN 
was used.


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

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

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Statement freezes firebird

[setysvar setys...@gmail.com [firebird-support]]

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 NOT IN is worse than IN) and it does take 
time to consider 324 million tuples (18000*18000).


Your particular query I would have rewritten:

select ID
from TBL_TEST
order by ID desc
rows 3 to 10

but as you write, it is a simplified statement, so

select first 8 t1.ID
from TBL_TEST t1
join TBL_TEST t2 on t1.ID < t2.ID
join TBL_TEST t3 on t2.ID < t3.ID
order by t1.ID desc

or

select first 8 t1.ID
from TBL_TEST t1
where exists( select *
  from TBL_TEST t2
  join TBL_TEST t3 on t2.ID < t3.ID
  where t1.ID < t2.ID )
order by t1.ID desc

or even

execute block returns( ID integer )
as
declare variable ID1 integer;
declare variable ID2 integer;
begin
  for select first 2 ID
  from TBL_TEST
  order by ID desc
  into :ID2 do
    if ( ID1 is null ) then
  ID1 = ID2;
  for select ID
  from TBL_TEST
  where ID not in (:ID1, :ID2) -- not in is OK when used with 
constants, just avoid subselects

  order by ID desc
  do
    suspend;
end

may be more appropriate starting points for fixing your real query if 
your not satisfied with creating a descending index.


I know nothing about Firebird 2.1.3, maybe that particular version 
contains changes that speeds up simple cases like yours but with side 
effects that made the developers remove this change later. I just know 
that Firebird 0.9.4 convinced me to shun away from (NOT) IN ().


HTH,
Set

Den 25.01.2020 07:59, skrev Matthias Winkler spmm...@gmail.com 
[firebird-support]:



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"-queries.

BR

Matthias





On Fri, Jan 24, 2020 at 3:53 PM Karol Bieniaszewski 
liviusliv...@poczta.onet.pl  
[firebird-support] > wrote:


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 you really need it then such subquery should be well indexed.


>> „Is there a way to force limiting the result set before sorting
it?”

Create descending index on ID column, than this subquery will go
throught index without sorting.

It will be instant i suppose

Regards,

Karol Bieniaszewski

Re: [firebird-support] Statement freezes firebird

[Matthias Winkler spmm...@gmail.com [firebird-support]]

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"-queries.

BR

Matthias





On Fri, Jan 24, 2020 at 3:53 PM Karol Bieniaszewski
liviusliv...@poczta.onet.pl [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> 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 you really need it then such subquery should be well indexed.
>
>
> >> „Is there a way to force limiting the result set before sorting it?”
>
>
>
> Create descending index on ID column, than this subquery will go throught
> index without sorting.
>
>
>
> It will be instant i suppose
>
>
>
> Regards,
>
> Karol Bieniaszewski
>
>
>
> 
>

Re: [firebird-support] Statement freezes firebird

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

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.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

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

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Statement freezes firebird

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

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 each
> itaretion. It cannot
> be fast.

It sounds to me like this is should be optimized by executing the 
uncorrelated sub-queries only once.

Mark

Re: [firebird-support] Statement freezes firebird

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

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.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

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

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Statement freezes firebird

[Matthias Winkler spmm...@gmail.com [firebird-support]]

@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 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".
>
>Learn how Firebird works: http://www.ibase.ru/performance/
>Pay a special attention to http://www.ibase.ru/dataaccesspaths/
>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

Re: [firebird-support] Statement freezes firebird

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

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".

   Learn how Firebird works: http://www.ibase.ru/performance/
   Pay a special attention to http://www.ibase.ru/dataaccesspaths/


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

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

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Statement freezes firebird

[Matthias Winkler spmm...@gmail.com [firebird-support]]

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 last "order by" it just takes forever...

  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

Any ideas?



On Fri, Jan 24, 2020 at 2:39 PM 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 each
> itaretion. It cannot
> be fast.
>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

Re: [firebird-support] Statement freezes firebird

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

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.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

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

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Statement freezes firebird

[Gabor Boros mlngl...@bgss.hu [firebird-support]]

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






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

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

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Statement freezes firebird

[Matthias Winkler spmm...@gmail.com [firebird-support]]

@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 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 order statements the query runs fine and takes
> > around 3s.
> >
> > Can somebody explain what execution plan is made for this statement
> > and
> > why it might sent my server into 100% CPU?
> >
> > Note: The statement here is simplified from my real usecase. The
> > statement itself might
> > not look useful. But that is not the point.
>
> Firebird 2.1 has been end-of-life for more than five years. Can you
> reproduce this with Firebird 3.0?
>
> Mark
> 
>

Re: [firebird-support] Statement freezes firebird

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

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 order by ID desc)
>> order by ID desc
> 
> The used table has about 18`000 entries.
> 
>  If I remove ONE of the order statements the query runs fine and takes
> around 3s.
> 
> Can somebody explain what execution plan is made for this statement
> and
> why it might sent my server into 100% CPU?
> 
> Note: The statement here is simplified from my real usecase. The
> statement itself might
> not look useful. But that is not the point.

Firebird 2.1 has been end-of-life for more than five years. Can you 
reproduce this with Firebird 3.0?

Mark