Re: [firebird-support] FB 4 - Beta 1 - Unstable... just how unstable is it?

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

On 2019-09-17 09:25, Andrea Raimondi andrea.raimo...@gmail.com 
[firebird-support] wrote:
> Hello!
> 
> Just a quick q to try to figure out if it's worth for me to use it as
> the basis of a greenfield, not-so-high priority, project I am working
> on.
> 
> How unstable is FB4? Does it result in tables' data corruption, for
> example?
> Or does that mean that it'll hang for "no reason", as it were?

Unstable means just that: new features are still in flux and may change 
(in potentially breaking(!) ways), it contains bugs, it isn't feature 
complete, and there is no guarantee that a backup from the beta version 
will be restorable under the final Firebird 4 release. All in all it 
isn't ready for production usage.

Also be aware that the firebird-support list is only for released 
versions. Questions about development versions (including betas) should 
go to the firebird-devel list.

Mark

[firebird-support] FB 4 - Beta 1 - Unstable... just how unstable is it?

[Andrea Raimondi andrea.raimo...@gmail.com [firebird-support]]

Hello!

Just a quick q to try to figure out if it's worth for me to use it as the
basis of a greenfield, not-so-high priority, project I am working on.

How unstable is FB4? Does it result in tables' data corruption, for
example?
Or does that mean that it'll hang for "no reason", as it were?

Kind Regards.

A

AW: [firebird-support] Re: Does gbak use WireCompression?

[Köditz, Martin martin.koed...@it-syn.de [firebird-support]]

Hi Vlad,

> gbak is usual client application, it needs no special actions to use 
> WireCompression. Just put zlib1.dll near the fbclient.dll and set 
> WireCompression = true at client side (using firebird.conf or DPB). Make sure 
> zlib1.dll is present at server side also.

Thank you. That’s the info I needed.

Sure, restore on the remote is much faster. But sometimes I am just lazy.

Regards
Martin

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Dienstag, 17. September 2019 08:58
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Re: Does gbak use WireCompression?



---In 
firebird-support@yahoogroups.com, 
mailto:Martin.Koeditz@...>> wrote :

> I often perform restores on remote servers via WAN. Since this sometimes 
> takes a long time, I wonder if gbak > takes into account the parameter 
> WireCompression. What needs to be set as where?

  gbak is usual client application, it needs no special actions to use 
WireCompression. Just put zlib1.dll near the fbclient.dll and set 
WireCompression = true at client side (using firebird.conf or DPB). Make sure 
zlib1.dll is present at server side also.

  But much more efficient way to do restore via slow network is remote 
backup\restore (see CORE-2666).
Read doc\README.services_extension chapter (4) for details.

Regards,
Vlad

[firebird-support] Re: Does gbak use WireCompression?

[hv...@users.sourceforge.net [firebird-support]]

---In firebird-support@yahoogroups.com,  wrote :
 
 > I often perform restores on remote servers via WAN. Since this sometimes 
 > takes a long time, I wonder if gbak > takes into account the parameter 
 > WireCompression. What needs to be set as where?
 

   gbak is usual client application, it needs no special actions to use 
WireCompression. Just put zlib1.dll near the fbclient.dll and set 
WireCompression = true at client side (using firebird.conf or DPB). Make sure 
zlib1.dll is present at server side also.

 

   But much more efficient way to do restore via slow network is remote 
backup\restore (see CORE-2666).
 Read doc\README.services_extension chapter (4) for details.
 

 Regards,
 Vlad
 

 

Re: [firebird-support] Re: Does gbak use WireCompression?

['Alexey Kovyazin (ak)' a...@ib-aid.com [firebird-support]]

Martin,

Vlad head read my mind and answered :)
Try remote restore through service manager, it is much faster.

Regards,
Alexey Kovyazin

вт, 17 сент. 2019 г., 9:59 hv...@users.sourceforge.net [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> ---In firebird-support@yahoogroups.com,  wrote :
>
> > I often perform restores on remote servers via WAN. Since this sometimes
> takes a long time, I wonder if gbak > takes into account the parameter
> WireCompression. What needs to be set as where?
>
>   gbak is usual client application, it needs no special actions to use
> WireCompression. Just put zlib1.dll near the fbclient.dll and set
> WireCompression = true at client side (using firebird.conf or DPB). Make
> sure zlib1.dll is present at server side also.
>
>   But much more efficient way to do restore via slow network is remote
> backup\restore (see CORE-2666).
> Read doc\README.services_extension chapter (4) for details.
>
> Regards,
> Vlad
>
>
> 
>

[firebird-support] index use when using order by

['Andrew Zenz' and...@aimsoftware.com.au [firebird-support]]

Hi everyone.

 

I am experiencing an unusual situation.

 

I have a log table for tracking user changes to records.  Table is as
follows:

 

CREATE TABLE AIMLOG

(

  ID Integer NOT NULL,

  USERNAME Char(12) COLLATE EN_UK,

  USERNO Integer,

  LOGDATE Integer,

  LOGTIME Integer,

  LOGTABLE Char(30) COLLATE EN_UK,

  RECORDKEY Char(30) COLLATE EN_UK,

  RECORDACTION Char(10) COLLATE EN_UK,

  RECORDBEFORE Varchar(1000) COLLATE EN_UK,

  RECORDAFTER Varchar(1000) COLLATE EN_UK,

  CONSTRAINT AIMLOG_PK PRIMARY KEY (ID)

);

 

Using Flamerobin, if I want to find the last record I run the following
query:

 

SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE, a.LOGTIME, a.LOGTABLE,
a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE, a.RECORDAFTER

FROM AIMLOG a

order by a.id descending

 

The query takes about 15 seconds because it doesn't want to use the
primary key, AIMLOG_PK.  Plan below:

 

Starting transaction...

Preparing query: SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE,
a.LOGTIME, a.LOGTABLE, a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE,
a.RECORDAFTER

FROM AIMLOG a

order by a.id descending

 

Prepare time: 0.015s

Field #01: AIMLOG.ID Alias:ID Type:INTEGER

Field #02: AIMLOG.USERNAME Alias:USERNAME Type:STRING(12)

Field #03: AIMLOG.USERNO Alias:USERNO Type:INTEGER

Field #04: AIMLOG.LOGDATE Alias:LOGDATE Type:INTEGER

Field #05: AIMLOG.LOGTIME Alias:LOGTIME Type:INTEGER

Field #06: AIMLOG.LOGTABLE Alias:LOGTABLE Type:STRING(30)

Field #07: AIMLOG.RECORDKEY Alias:RECORDKEY Type:STRING(30)

Field #08: AIMLOG.RECORDACTION Alias:RECORDACTION Type:STRING(10)

Field #09: AIMLOG.RECORDBEFORE Alias:RECORDBEFORE Type:STRING(1000)

Field #10: AIMLOG.RECORDAFTER Alias:RECORDAFTER Type:STRING(1000)

PLAN SORT ((A NATURAL))

 

 

Executing...

Done.

267615 fetches, 0 marks, 3818 reads, 0 writes.

0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq.

Delta memory: 9460264 bytes.

Total execution time: 14.734s

Script execution finished.

 

Another simple query that should use the index (I would assume) but
doesn't is:

 

select max(a.id) from aimlog a

 

which uses the plan:

 

Starting transaction...

Preparing query: select max(a.id) from aimlog a

 

Prepare time: 0.015s

Field #01: .MAX Alias:MAX Type:INTEGER

PLAN (A NATURAL)

 

 

Executing...

Done.

267615 fetches, 0 marks, 3821 reads, 0 writes.

0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq.

Delta memory: 9724 bytes.

Total execution time: 0.172s

Script execution finished.

 

 

The application we develop in, Clarion, uses a mechanism of SET the
records in a key order (usually placing the cursor at the bottom or top
of the order, in this case top), then PREVIOUS to find the highest
record, then adding 1 to get the next ID.  (Don't question the method,
it works in the IDE we have never had an issue), however when the
application does it's SET/PREVIOUS it takes 10 to 15 seconds to
complete.  I can only gather that it is doing a NATURAL read as above
instead of using the index.

 

We don't seem to have the issue (as far as I can tell) with other tables
that use the SET/PREVIOUS mechanism.

 

Is it a property of the 'order by' to not use the index, is it a bug, is
it a fault in my table definition or am I missing the point?

 

Firebird 2.5.7   (WI-2.5.7.27050)  

 

Just realised I 'should' be using 2.5.8, but on a server running
WI-V2.5.8.27089 it still uses NATURAL.

 

Any pointers or ideas?  

 

Thanks.

 

Andrew Zenz

 

 

 

RE: [firebird-support] index use when using order by

['Andrew Zenz' and...@aimsoftware.com.au [firebird-support]]

I performed a trace and this is the result.  I suspected it was doing an
order by:

 

Preparing Statement 5694738 : SELECT
"ID","USERNAME","USERNO","LOGDATE","LOGTIME","LOGTABLE","RECORDKEY","REC
ORDACTION","RECORDBEFORE","RECORDAFTER" FROM "AIMLOG" WHERE ("ID" <= ? )
ORDER BY "ID" DESC  Time Taken:0.00 secs

Setting number of rows to fetch to 1 for Statement 5694738 Time
Taken:0.00 secs

Binding Column 1 to C type SLONG for Statement 5694738 Time Taken:0.00
secs

Binding Column 2 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding Column 3 to C type SSHORT for Statement 5694738 Time Taken:0.00
secs

Binding Column 4 to C type SLONG for Statement 5694738 Time Taken:0.00
secs

Binding Column 5 to C type SLONG for Statement 5694738 Time Taken:0.00
secs

Binding Column 6 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding Column 7 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding Column 8 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding Column 9 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding Column 10 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding ? 1 with C type SLONG for Statement 5694738 Time Taken:0.00 secs

Executing prepared Statement 5694738 Time Taken:15.73 secs

Fetching Row from Statement 5694738 Return Code: 0 Time Taken:0.00 secs

 

HTH

 

Andrew Zenz

 



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, 18 September 2019 10:11 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] index use when using order by

 

  

Hi everyone.

 

I am experiencing an unusual situation.

 

I have a log table for tracking user changes to records.  Table is as
follows:

 

CREATE TABLE AIMLOG

(

  ID Integer NOT NULL,

  USERNAME Char(12) COLLATE EN_UK,

  USERNO Integer,

  LOGDATE Integer,

  LOGTIME Integer,

  LOGTABLE Char(30) COLLATE EN_UK,

  RECORDKEY Char(30) COLLATE EN_UK,

  RECORDACTION Char(10) COLLATE EN_UK,

  RECORDBEFORE Varchar(1000) COLLATE EN_UK,

  RECORDAFTER Varchar(1000) COLLATE EN_UK,

  CONSTRAINT AIMLOG_PK PRIMARY KEY (ID)

);

 

Using Flamerobin, if I want to find the last record I run the following
query:

 

SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE, a.LOGTIME, a.LOGTABLE,
a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE, a.RECORDAFTER

FROM AIMLOG a

order by a.id descending

 

The query takes about 15 seconds because it doesn't want to use the
primary key, AIMLOG_PK.  Plan below:

 

Starting transaction...

Preparing query: SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE,
a.LOGTIME, a.LOGTABLE, a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE,
a.RECORDAFTER

FROM AIMLOG a

order by a.id descending

 

Prepare time: 0.015s

Field #01: AIMLOG.ID Alias:ID Type:INTEGER

Field #02: AIMLOG.USERNAME Alias:USERNAME Type:STRING(12)

Field #03: AIMLOG.USERNO Alias:USERNO Type:INTEGER

Field #04: AIMLOG.LOGDATE Alias:LOGDATE Type:INTEGER

Field #05: AIMLOG.LOGTIME Alias:LOGTIME Type:INTEGER

Field #06: AIMLOG.LOGTABLE Alias:LOGTABLE Type:STRING(30)

Field #07: AIMLOG.RECORDKEY Alias:RECORDKEY Type:STRING(30)

Field #08: AIMLOG.RECORDACTION Alias:RECORDACTION Type:STRING(10)

Field #09: AIMLOG.RECORDBEFORE Alias:RECORDBEFORE Type:STRING(1000)

Field #10: AIMLOG.RECORDAFTER Alias:RECORDAFTER Type:STRING(1000)

PLAN SORT ((A NATURAL))

 

 

Executing...

Done.

267615 fetches, 0 marks, 3818 reads, 0 writes.

0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq.

Delta memory: 9460264 bytes.

Total execution time: 14.734s

Script execution finished.

 

Another simple query that should use the index (I would assume) but
doesn't is:

 

select max(a.id) from aimlog a

 

which uses the plan:

 

Starting transaction...

Preparing query: select max(a.id) from aimlog a

 

Prepare time: 0.015s

Field #01: .MAX Alias:MAX Type:INTEGER

PLAN (A NATURAL)

 

 

Executing...

Done.

267615 fetches, 0 marks, 3821 reads, 0 writes.

0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq.

Delta memory: 9724 bytes.

Total execution time: 0.172s

Script execution finished.

 

 

The application we develop in, Clarion, uses a mechanism of SET the
records in a key order (usually placing the cursor at the bottom or top
of the order, in this case top), then PREVIOUS to find the highest
record, then adding 1 to get the next ID.  (Don't question the method,
it works in the IDE we have never had an issue), however when the
application does it's SET/PREVIOUS it takes 10 to 15 seconds to
complete.  I can only gather that it is doing a NATURAL read as above
instead of using the index.

 

We don't seem to have the issue (as far as I can tell) with other tables
that use the SET/PREVIOUS mechanism.

 

Is it a property of the 'order by' to not use the index, is it a bug, is
it a fault in my table definition or am I missing the point?

 

Firebird 2.5.7   (WI-2.5.7.27050)  

 

Just realised I 'should' be using 2.5.8, but 

RE: [firebird-support] index use when using order by

['Andrew Zenz' and...@aimsoftware.com.au [firebird-support]]

 and further, when I run the query in Flamerobin as it was in the
trace substituting 9 for the ?:

 

SELECT
"ID","USERNAME","USERNO","LOGDATE","LOGTIME","LOGFILE","RECORDKEY","RECO
RDACTION","RECORDBEFORE","RECORDAFTER" FROM "AIMLOG" WHERE ("ID" <=
99 ) ORDER BY "ID" DESC

 

The following plan is used, still taking almost 15 seconds.

 

Starting transaction...

Preparing query: SELECT
"ID","USERNAME","USERNO","LOGDATE","LOGTIME","LOGFILE","RECORDKEY","RECO
RDACTION","RECORDBEFORE","RECORDAFTER" FROM "AIMLOG" WHERE ("ID" <=
99 ) ORDER BY "ID" DESC

 

Prepare time: 0.031s

Field #01: AIMLOG.ID Alias:ID Type:INTEGER

Field #02: AIMLOG.USERNAME Alias:USERNAME Type:STRING(12)

Field #03: AIMLOG.USERNO Alias:USERNO Type:INTEGER

Field #04: AIMLOG.LOGDATE Alias:LOGDATE Type:INTEGER

Field #05: AIMLOG.LOGTIME Alias:LOGTIME Type:INTEGER

Field #06: AIMLOG.LOGFILE Alias:LOGFILE Type:STRING(30)

Field #07: AIMLOG.RECORDKEY Alias:RECORDKEY Type:STRING(30)

Field #08: AIMLOG.RECORDACTION Alias:RECORDACTION Type:STRING(10)

Field #09: AIMLOG.RECORDBEFORE Alias:RECORDBEFORE Type:STRING(1000)

Field #10: AIMLOG.RECORDAFTER Alias:RECORDAFTER Type:STRING(1000)

PLAN SORT ((AIMLOG INDEX (AIMLOG_PK)))

 

 

Executing...

Done.

260094 fetches, 0 marks, 3914 reads, 0 writes.

0 inserts, 0 updates, 0 deletes, 129996 index, 0 seq.

Delta memory: 9549504 bytes.

Total execution time: 14.391s

Script execution finished.

 

I am still confused/bemused about the delay.  Are my expectations, that
such a query should be almost instant, unreasonable?  Especially when a
query such as this is almost instant:

 

SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE, a.LOGTIME, a."LOGFILE",
a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE, a.RECORDAFTER

FROM AIMLOG a

where a.id = 123456

 

Starting transaction...

Preparing query: SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE,
a.LOGTIME, a."LOGFILE", a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE,
a.RECORDAFTER

FROM AIMLOG a

where a.id = 123456

 

Prepare time: 0.016s

Field #01: AIMLOG.ID Alias:ID Type:INTEGER

Field #02: AIMLOG.USERNAME Alias:USERNAME Type:STRING(12)

Field #03: AIMLOG.USERNO Alias:USERNO Type:INTEGER

Field #04: AIMLOG.LOGDATE Alias:LOGDATE Type:INTEGER

Field #05: AIMLOG.LOGTIME Alias:LOGTIME Type:INTEGER

Field #06: AIMLOG.LOGFILE Alias:LOGFILE Type:STRING(30)

Field #07: AIMLOG.RECORDKEY Alias:RECORDKEY Type:STRING(30)

Field #08: AIMLOG.RECORDACTION Alias:RECORDACTION Type:STRING(10)

Field #09: AIMLOG.RECORDBEFORE Alias:RECORDBEFORE Type:STRING(1000)

Field #10: AIMLOG.RECORDAFTER Alias:RECORDAFTER Type:STRING(1000)

PLAN (A INDEX (AIMLOG_PK))

 

 

Executing...

Done.

18 fetches, 0 marks, 2 reads, 0 writes.

0 inserts, 0 updates, 0 deletes, 4 index, 0 seq.

Delta memory: 20548 bytes.

Total execution time: 0.031s

Script execution finished.

 

Cheers,

 

Andrew Zenz

 

 



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, 18 September 2019 11:02 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] index use when using order by

 

  

I performed a trace and this is the result.  I suspected it was doing an
order by:

 

Preparing Statement 5694738 : SELECT
"ID","USERNAME","USERNO","LOGDATE","LOGTIME","LOGTABLE","RECORDKEY","REC
ORDACTION","RECORDBEFORE","RECORDAFTER" FROM "AIMLOG" WHERE ("ID" <= ? )
ORDER BY "ID" DESC  Time Taken:0.00 secs

Setting number of rows to fetch to 1 for Statement 5694738 Time
Taken:0.00 secs

Binding Column 1 to C type SLONG for Statement 5694738 Time Taken:0.00
secs

Binding Column 2 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding Column 3 to C type SSHORT for Statement 5694738 Time Taken:0.00
secs

Binding Column 4 to C type SLONG for Statement 5694738 Time Taken:0.00
secs

Binding Column 5 to C type SLONG for Statement 5694738 Time Taken:0.00
secs

Binding Column 6 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding Column 7 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding Column 8 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding Column 9 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding Column 10 to C type CHAR for Statement 5694738 Time Taken:0.00
secs

Binding ? 1 with C type SLONG for Statement 5694738 Time Taken:0.00 secs

Executing prepared Statement 5694738 Time Taken:15.73 secs

Fetching Row from Statement 5694738 Return Code: 0 Time Taken:0.00 secs

 

HTH

 

Andrew Zenz

 



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, 18 September 2019 10:11 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] index use when using order by

 

  

Hi everyone.

 

I am experiencing an unusual situation.

 

I have a log table for tracking user changes to records.  Table is as
follows:

 

CREATE TABLE AIMLOG

(

  ID Integer 

RE: [firebird-support] index use when using order by

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hiyour problem is that you have ascending index, which is not usable in 
descending queries. You need to create descending index and it then can be used 
in e.g. max queries. Ascending index is used in min queries and order by xx 
asc.Regards,Karol Bieniaszewski
null

RE: [firebird-support] index use when using order by

['Andrew Zenz' and...@aimsoftware.com.au [firebird-support]]

Thanks.

 

Excuse me while I find a brick wall to bash my head against.

 

Andrew Zenz

 

 



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, 18 September 2019 2:47 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] index use when using order by

 

  

Hi

 

your problem is that you have ascending index, which is not usable in
descending queries. You need to create descending index and it then can
be used in e.g. max queries. Ascending index is used in min queries and
order by xx asc.

 

 

 

Regards,

Karol Bieniaszewski

Re: [firebird-support] index use when using order by

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

On 2019-09-18 02:11, 'Andrew Zenz' and...@aimsoftware.com.au 
[firebird-support] wrote:
> Firebird 2.5.7   (WI-2.5.7.27050)
> 
> Just realised I 'should' be using 2.5.8, but on a server running
> WI-V2.5.8.27089 it still uses NATURAL.

Be aware, the latest version of Firebird 2.5 is 2.5.9, not 2.5.8.

Mark