RE: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - CHECK Constraint Change Question

['stwizard' stwiz...@att.net [firebird-support]]

Thanks Mark

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, July 23, 2015 8:22 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - CHECK 
Constraint Change Question

 

  

On Thu, 23 Jul 2015 08:15:35 -0500, "'stwizard' stwiz...@att.net
[firebird-support]"  wrote:
> Greetings All,
> 
> In the Firebird 2 Migration & Installation guide 
>
http://www.firebirdsql.org/file/documentation/release_notes/Firebird-2_1_6-I
> nstallation.pdf
> on page 4 it states the following:
> 
> CHECK Constraint Change
> Formerly, CHECK constraints were not SQL standard-compliant in regard
to
> the handling of NULL. For example, 
> CHECK (DEPTNO IN (10, 20, 30)) 
> should allow NULL in the DEPTNO column but it did not.
> 
> In Firebird 2.0, if you need to make NULL invalid in a CHECK
constraint,
> you must do so explicitly by extending the constraint. Using the example
> above:
> CHECK (DEPTNO IN (10, 20, 30) AND DEPTNO IS NOT NULL)
> 
> I have a field called FTP_FORCE_LOWER_CASE defined as SmallInt Not Null
> with
> the following constraint
> 
> /* (0) False, (1) True */
> FTP_FORCE_LOWER_CASE IN (0, 1)
> 
> Do I need to change it to this even though the Not Null column is
checked? 
> /* (0) False, (1) True */
> FTP_FORCE_LOWER_CASE IN (0, 1) AND FTP_FORCE_LOWER_CASE IS NOT NULL

No, you don't need to change it, it is already enforced by the NOT NULL
constraint. The note in the release notes is just to let you know what to
check for if your application depended on the old behavior (intentionally
or unintentionally).

Mark





[Non-text portions of this message have been removed]

RE: [firebird-support] Migration Guide for Firebird 1.5.3 to 2.5.4

['stwizard' stwiz...@att.net [firebird-support]]

Thanks Mark

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, July 23, 2015 7:40 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Migration Guide for Firebird 1.5.3 to 2.5.4

 

  

On Thu, 23 Jul 2015 07:16:18 -0500, "'stwizard' stwiz...@att.net
[firebird-support]"  wrote:
> Thank you Helen for your reply,
> 
> I'll do as you suggest and read the release notes (in order) and I may
have
> question after reading them.

Just know that upgrading the database (apart from the metadata character
set upgrade) is probably the least of your worries. Make sure you test your
applications intensively before doing a production upgrade.

> Should I continue to ask all my questions in this thread or should I ask
> each in a separate post?

That is up to you, but I'd start a new thread for a new question.

> For Instance, I do have a few questions currently:
> 
> 1) The Character Set of my current database is NONE. Is this OK or
should
> I
> set it to something else? Or it this done automatically during the
> restore?

The restore doesn't change the default character set of a database, NONE
is a valid default and switching that to something else can have
implications that only you and your application developers can check.

> 2) The database I want to upgrade is 25,231,544 KB, so I have a
question.
> Is it possible to do a metadata only backup followed by a restore of the
> metadata only in 2.5.4 to see if there are any issues with Stored
> procedure,
> Triggers, constraints, keywords, etc before attempting the full backup
> and
> restore?

Yes:
http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/gbak-recipies.html#gbak-recipies-metadata

Mark





[Non-text portions of this message have been removed]

[firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Existence Predicates NOT IN Question

['stwizard' stwiz...@att.net [firebird-support]]

Greetings All,

In the Firebird 2 Migration & Installation guide 
 

 
http://www.firebirdsql.org/file/documentation/release_notes/Firebird-2_1_6-Installation.pdf
 
on page 8 under the performance section it states the following:

 

The following changes should be noted as possible sources of performance loss:

Existence Predicates NOT IN and ALL May Be Slow

 

Firebird and, before that, InterBase, have produced incorrect results for the 
logical existence predicates ALL and NOT IN for many years. That problem has 
been corrected in Firebird 2.0, but the change means that indexes on the inner 
tables cannot be used and performance may be slow compared to the same query's 
performance in V.1.5. “Inner tables” are the tables used in the subquery 
argument inside an ALL or NOT IN expression.

 

So my questions:

 

This only applies when there is a JOIN in the SQL statement correct?

 

In other words it does not apply to a stored procedure like the following does 
it?

 

SET TERM  ^^ ;

CREATE PROCEDURE SPS_ATTORNEY_STATE (

  V_ATTORNEY_ID Integer)

returns (

  R_ASSOCIATED SmallInt,

  R_ATTORNEY_ID Integer,

  R_STATE_CODE Char(2),

  R_STATE_NAME VarChar(35),

  R_CREATE_DATE TimeStamp,

  R_CREATE_USER SmallInt,

  R_MODIFY_DATE TimeStamp,

  R_MODIFY_USER SmallInt,

  R_COURT_NO VarChar(10))

AS

/*

  Author   : Michael Tuttle

  Date : 10/02/2006

  Purpose  :

*/

begin

  FOR SELECT CAST(1 AS SMALLINT) AS ASSOCIATED,

 A.ATTORNEY_ID,

 A.STATE_CODE,

 S.NAME AS STATE_NAME,

 A.CREATE_DATE,

 A.CREATE_USER,

 A.MODIFY_DATE,

 A.MODIFY_USER,

 A.COURT_NO

FROM ATTORNEY_STATE A

JOIN STATE S ON S.STATE_CODE = A.STATE_CODE

   WHERE A.ATTORNEY_ID = :V_ATTORNEY_ID

 

UNION

 

  SELECT CAST(0 AS SMALLINT) AS ASSOCIATED,

 CAST(:V_ATTORNEY_ID AS INTEGER) AS ATTORNEY_ID,

 S.STATE_CODE,

 S.NAME AS STATE_NAME,

 CAST(NULL AS TIMESTAMP) AS CREATE_DATE,

 CAST(NULL AS SMALLINT) AS CREATE_USER,

 CAST(NULL AS TIMESTAMP) AS MODIFY_DATE,

 CAST(NULL AS SMALLINT) AS MODIFY_USER,

 CAST(NULL AS VARCHAR(10)) AS COURT_NO

FROM STATE S

   WHERE S.STATE_CODE NOT IN (SELECT A2.STATE_CODE

FROM ATTORNEY_STATE A2

   WHERE A2.ATTORNEY_ID = :V_ATTORNEY_ID)

 

  INTO :R_ASSOCIATED, :R_ATTORNEY_ID, :R_STATE_CODE, :R_STATE_NAME, 
:R_CREATE_DATE, :R_CREATE_USER, :R_MODIFY_DATE, :R_MODIFY_USER, :R_COURT_NO DO

BEGIN

  SUSPEND;

END

end

^^

SET TERM ;  ^^

 

Thanks,

Mike

Re: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Existence Predicates NOT IN Question

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

 >Existence Predicates NOT IN and ALL May Be Slow
 >
 >Firebird and, before that, InterBase, have produced incorrect results 
for the logical existence predicates ALL and NOT IN for many years.
 >That problem has been corrected in Firebird 2.0, but the change means 
that indexes on the inner tables cannot be used and performance
 >may be slow compared to the same query's performance in V.1.5. “Inner 
tables” are the tables used in the subquery argument inside an
 >ALL or NOT IN expression.
 >
 >So my questions:
 >
 >This only applies when there is a JOIN in the SQL statement correct?
 >In other words it does not apply to a stored procedure like the 
following does it?

Hi Mike!

On www.firebirdsql.org/manual/nullguide-predicates.html, I find

"Bug alert

All Firebird versions before 2.0 contain a bug that causes [NOT] IN to 
return the wrong result if an index is active on the subselect and one 
of the following conditions is true:

A is NULL and the subselect doesn't return any NULLs, or
A is not NULL and the subselect result set doesn't contain A but does 
contain NULL(s).
Please realise that an index may be active even if it has not been 
created explicitly, namely if a key is defined on A.

Example: Table TA has a column A with values { 3, 8 }. Table TB has a 
column B containing { 2, 8, 1, NULL }. The expressions:

A [not] in ( select B from TB )
should both return NULL for A = 3, because of the NULL in B. But if B is 
indexed, IN returns false and NOT IN returns true. As a result, the query

select A from TA where A not in ( select B from TB )
returns a dataset with one record – containing the field with value 3 – 
while it should have returned an empty set. Other errors may also occur, 
e.g. if you use “NOT IN” in an IF, CASE or WHILE statement."

which I assume to be the error you're talking about. So, yes, it can 
apply to cases like the procedure you describe.

One of the first things I learnt when starting with InterBase/Firebird, 
was to never use IN () - it took far longer than I expected. 
This was either with InterBase 5.6 or Firebird 0.9.4, but I've never had 
a need for IN () since I learnt about EXISTS. So, I'd 
recommend you to change your SP to:

SET TERM  ^^ ;
CREATE PROCEDURE SPS_ATTORNEY_STATE (
   V_ATTORNEY_ID Integer)
returns (
   R_ASSOCIATED SmallInt,
   R_ATTORNEY_ID Integer,
   R_STATE_CODE Char(2),
   R_STATE_NAME VarChar(35),
   R_CREATE_DATE TimeStamp,
   R_CREATE_USER SmallInt,
   R_MODIFY_DATE TimeStamp,
   R_MODIFY_USER SmallInt,
   R_COURT_NO VarChar(10))
AS
/*
   Author   : Michael Tuttle
   Date : 10/02/2006
   Purpose  :
*/
begin
   FOR SELECT CAST(1 AS SMALLINT) AS ASSOCIATED,
  A.ATTORNEY_ID,
  A.STATE_CODE,
  S.NAME AS STATE_NAME,
  A.CREATE_DATE,
  A.CREATE_USER,
  A.MODIFY_DATE,
  A.MODIFY_USER,
  A.COURT_NO
 FROM ATTORNEY_STATE A
 JOIN STATE S ON S.STATE_CODE = A.STATE_CODE
WHERE A.ATTORNEY_ID = :V_ATTORNEY_ID
 UNION
   SELECT CAST(0 AS SMALLINT) AS ASSOCIATED,
  CAST(:V_ATTORNEY_ID AS INTEGER) AS ATTORNEY_ID,
  S.STATE_CODE,
  S.NAME AS STATE_NAME,
  CAST(NULL AS TIMESTAMP) AS CREATE_DATE,
  CAST(NULL AS SMALLINT) AS CREATE_USER,
  CAST(NULL AS TIMESTAMP) AS MODIFY_DATE,
  CAST(NULL AS SMALLINT) AS MODIFY_USER,
  CAST(NULL AS VARCHAR(10)) AS COURT_NO
 FROM STATE S
WHERE  NOT EXISTS (SELECT *
 FROM ATTORNEY_STATE A2
WHERE A2.ATTORNEY_ID = :V_ATTORNEY_ID
  AND A2.STATE_CODE = S.STATE_CODE)
   INTO :R_ASSOCIATED, :R_ATTORNEY_ID, :R_STATE_CODE, :R_STATE_NAME, 
:R_CREATE_DATE, :R_CREATE_USER, :R_MODIFY_DATE, :R_MODIFY_USER, 
:R_COURT_NO DO
 BEGIN
   SUSPEND;
 END
end
^^
SET TERM ;  ^^

regardless of whether you have performance problems or not. Firebird 
have greatly improved since version 0.9.4 and NOT IN may or may not be 
slow with 2.5, but it is so simple to replace IN with EXISTS, and 
IN() is never quicker than EXISTS (it can be equally quick), 
so I see no reason for ever using IN ().

Unfortunately, I have no answer to whether or not this bug have given 
you incorrect results on older Firebird versions or whether or not you 
ought to modify your old code.

By the way, in your particular case, I think I'd consider changing the 
procedure a bit more, but that eliminates the subselect and hence, your 
original question:

SET TERM  ^^ ;
CREATE PROCEDURE SPS_ATTORNEY_STATE (
   V_ATTORNEY_ID Integer)
returns (
   R_ASSOCIATED SmallInt,
   R_ATTORNEY_ID Integer,
   R_STATE_CODE Char(2),
   R_STATE_NAME VarChar(35),
   R_CREATE_DATE TimeStamp,
   R_CREATE_USER SmallInt,
   R_MODIFY_DATE TimeStamp,
   R_MODIFY_USER SmallInt,
   R_COURT_NO VarChar(10))
AS
/*
   Author   : Michael Tuttle
   Date : 10/0

Rif: Re: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Existence Predicates NOT IN Question

['Raffaele Confalone' r.confal...@libero.it [firebird-support]]

without considering the merits of your problem, I can say that the response
time of the selects depends on more factors: the first and 'the structure of
the indexes on the table but also on how and' written queries. Without know
the details, I can say that the absence of a group by in the presence of
many records that match the where can cause loss of time.

SELECT max(CAST(0 AS SMALLINT)) AS ASSOCIATED,
max(CAST(:V_ATTORNEY_ID AS INTEGER)) AS ATTORNEY_ID,
S.STATE_CODE,
S.STATE_NAME,
max(CAST(NULL AS TIMESTAMP)) AS CREATE_DATE,
max(CAST(NULL AS SMALLINT)) AS CREATE_USER,
max(CAST(NULL AS TIMESTAMP)) AS MODIFY_DATE,
max(CAST(NULL AS SMALLINT)) AS MODIFY_USER,
max(CAST(NULL AS VARCHAR(10))) AS COURT_NO
FROM STATE S
WHERE NOT EXISTS (SELECT max(STATE_CODE) STATE_CODE,max(NAME) STATE_NAME
FROM ATTORNEY_STATE A2
 WHERE A2.ATTORNEY_ID = :V_ATTORNEY_ID AND A2.STATE_CODE = S.STATE_CODE
 GROUP BY ATTORNEY_ID)
GROUP BY ATTORNEY_ID,STATE_CODE,STATE_NAME

pay no attention to errors committed by me is the idea that counts.
 
 
 
 
 
---Messaggio originale---
 
Da: setysvar setys...@gmail.com [firebird-support]
Data: 07/24/15 14:49:07
A: firebird-support@yahoogroups.com
Oggetto: Re: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Existence
Predicates NOT IN Question
 
  
>Existence Predicates NOT IN and ALL May Be Slow
>
>Firebird and, before that, InterBase, have produced incorrect results 
for the logical existence predicates ALL and NOT IN for many years.
>That problem has been corrected in Firebird 2.0, but the change means 
that indexes on the inner tables cannot be used and performance
>may be slow compared to the same query's performance in V.1.5. “Inner 
tables” are the tables used in the subquery argument inside an
>ALL or NOT IN expression.
>
>So my questions:
>
>This only applies when there is a JOIN in the SQL statement correct?
>In other words it does not apply to a stored procedure like the 
following does it?

Hi Mike!

On www.firebirdsql.org/manual/nullguide-predicates.html, I find

"Bug alert

All Firebird versions before 2.0 contain a bug that causes [NOT] IN to 
return the wrong result if an index is active on the subselect and one 
of the following conditions is true:

A is NULL and the subselect doesn't return any NULLs, or
A is not NULL and the subselect result set doesn't contain A but does 
contain NULL(s).
Please realise that an index may be active even if it has not been 
created explicitly, namely if a key is defined on A.

Example: Table TA has a column A with values { 3, 8 }. Table TB has a 
column B containing { 2, 8, 1, NULL }. The expressions:

A [not] in ( select B from TB )
should both return NULL for A = 3, because of the NULL in B. But if B is 
indexed, IN returns false and NOT IN returns true. As a result, the query

select A from TA where A not in ( select B from TB )
returns a dataset with one record – containing the field with value 3 – 
while it should have returned an empty set. Other errors may also occur, 
e.g. if you use “NOT IN” in an IF, CASE or WHILE statement."

which I assume to be the error you're talking about. So, yes, it can 
apply to cases like the procedure you describe.

One of the first things I learnt when starting with InterBase/Firebird, 
was to never use IN () - it took far longer than I expected. 
This was either with InterBase 5.6 or Firebird 0.9.4, but I've never had 
a need for IN () since I learnt about EXISTS. So, I'd 
recommend you to change your SP to:

SET TERM ^^ ;
CREATE PROCEDURE SPS_ATTORNEY_STATE (
V_ATTORNEY_ID Integer)
returns (
R_ASSOCIATED SmallInt,
R_ATTORNEY_ID Integer,
R_STATE_CODE Char(2),
R_STATE_NAME VarChar(35),
R_CREATE_DATE TimeStamp,
R_CREATE_USER SmallInt,
R_MODIFY_DATE TimeStamp,
R_MODIFY_USER SmallInt,
R_COURT_NO VarChar(10))
AS
/*
Author : Michael Tuttle
Date : 10/02/2006
Purpose :
*/
begin
FOR SELECT CAST(1 AS SMALLINT) AS ASSOCIATED,
A.ATTORNEY_ID,
A.STATE_CODE,
S.NAME AS STATE_NAME,
A.CREATE_DATE,
A.CREATE_USER,
A.MODIFY_DATE,
A.MODIFY_USER,
A.COURT_NO
FROM ATTORNEY_STATE A
JOIN STATE S ON S.STATE_CODE = A.STATE_CODE
WHERE A.ATTORNEY_ID = :V_ATTORNEY_ID
UNION
SELECT CAST(0 AS SMALLINT) AS ASSOCIATED,
CAST(:V_ATTORNEY_ID AS INTEGER) AS ATTORNEY_ID,
S.STATE_CODE,
S.NAME AS STATE_NAME,
CAST(NULL AS TIMESTAMP) AS CREATE_DATE,
CAST(NULL AS SMALLINT) AS CREATE_USER,
CAST(NULL AS TIMESTAMP) AS MODIFY_DATE,
CAST(NULL AS SMALLINT) AS MODIFY_USER,
CAST(NULL AS VARCHAR(10)) AS COURT_NO
FROM STATE S
WHERE NOT EXISTS (SELECT *
FROM ATTORNEY_STATE A2
WHERE A2.ATTORNEY_ID = :V_ATTORNEY_ID
AND A2.STATE_CODE = S.STATE_CODE)
INTO :R_ASSOCIATED, :R_ATTORNEY_ID, :R_STATE_CODE, :R_STATE_NAME, 
:R_CREATE_DATE, :R_CREATE_USER, :R_MODIFY_DATE, :R_MODIFY_USER, 
:R_COURT_NO DO
BEGIN
SUSPEND;
END
end
^^
SET TERM ; ^^

regardless of whether you have performance problems or not. Firebird 
have greatly improved since version 0.9.4 and NOT IN may or may not be 
slow with 2.5, but it is so simple to replace IN with EXISTS, and 
IN() is never q

Re: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Existence Predicates NOT IN Question

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

On 24-7-2015 13:25, 'stwizard' stwiz...@att.net [firebird-support] wrote:
> Greetings All,
>
> In the Firebird 2 Migration & Installation guide
> http://www.firebirdsql.org/file/documentation/release_notes/Firebird-2_1_6-Installation.pdf
> on page 8 under the performance section it states the following:
>
> The following changes should be noted as possible sources of performance
> loss:
>
> Existence Predicates NOT IN and ALL May Be Slow
>
> Firebird and, before that, InterBase, have produced incorrect results
> for the logical existence predicates ALL and NOT IN for many years. That
> problem has been corrected in Firebird2.0, but the change means
> thatindexes on the inner tables cannot be used and performance may be
> slow compared to the same query's performance in V.1.5. “Inner tables”
> are the tables used in the subquery argument inside an ALL or NOT IN
> expression.
>
> So my questions:
>
> This only applies when there is a JOIN in the SQL statement correct?

No, everywhere you use NOT IN or ALL this applies. The documentation is 
talking about the inner table materialized by the select inside NOT IN 
(select ...). That condition can be replaced with an NOT EXISTS which - 
usually - performs better.

Mark
-- 
Mark Rotteveel

RE: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Existence Predicates NOT IN Question

['stwizard' stwiz...@att.net [firebird-support]]

Set, thank you for the very detailed response as it is VERY helpful and 
informative.  

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Friday, July 24, 2015 7:49 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Existence 
Predicates NOT IN Question

 

  

>Existence Predicates NOT IN and ALL May Be Slow
>
>Firebird and, before that, InterBase, have produced incorrect results 
for the logical existence predicates ALL and NOT IN for many years.
>That problem has been corrected in Firebird 2.0, but the change means 
that indexes on the inner tables cannot be used and performance
>may be slow compared to the same query's performance in V.1.5. “Inner 
tables” are the tables used in the subquery argument inside an
>ALL or NOT IN expression.
>
>So my questions:
>
>This only applies when there is a JOIN in the SQL statement correct?
>In other words it does not apply to a stored procedure like the 
following does it?

Hi Mike!

On www.firebirdsql.org/manual/nullguide-predicates.html, I find

"Bug alert

All Firebird versions before 2.0 contain a bug that causes [NOT] IN to 
return the wrong result if an index is active on the subselect and one 
of the following conditions is true:

A is NULL and the subselect doesn't return any NULLs, or
A is not NULL and the subselect result set doesn't contain A but does 
contain NULL(s).
Please realise that an index may be active even if it has not been 
created explicitly, namely if a key is defined on A.

Example: Table TA has a column A with values { 3, 8 }. Table TB has a 
column B containing { 2, 8, 1, NULL }. The expressions:

A [not] in ( select B from TB )
should both return NULL for A = 3, because of the NULL in B. But if B is 
indexed, IN returns false and NOT IN returns true. As a result, the query

select A from TA where A not in ( select B from TB )
returns a dataset with one record – containing the field with value 3 – 
while it should have returned an empty set. Other errors may also occur, 
e.g. if you use “NOT IN” in an IF, CASE or WHILE statement."

which I assume to be the error you're talking about. So, yes, it can 
apply to cases like the procedure you describe.

One of the first things I learnt when starting with InterBase/Firebird, 
was to never use IN () - it took far longer than I expected. 
This was either with InterBase 5.6 or Firebird 0.9.4, but I've never had 
a need for IN () since I learnt about EXISTS. So, I'd 
recommend you to change your SP to:

SET TERM ^^ ;
CREATE PROCEDURE SPS_ATTORNEY_STATE (
V_ATTORNEY_ID Integer)
returns (
R_ASSOCIATED SmallInt,
R_ATTORNEY_ID Integer,
R_STATE_CODE Char(2),
R_STATE_NAME VarChar(35),
R_CREATE_DATE TimeStamp,
R_CREATE_USER SmallInt,
R_MODIFY_DATE TimeStamp,
R_MODIFY_USER SmallInt,
R_COURT_NO VarChar(10))
AS
/*
Author : Michael Tuttle
Date : 10/02/2006
Purpose :
*/
begin
FOR SELECT CAST(1 AS SMALLINT) AS ASSOCIATED,
A.ATTORNEY_ID,
A.STATE_CODE,
S.NAME AS STATE_NAME,
A.CREATE_DATE,
A.CREATE_USER,
A.MODIFY_DATE,
A.MODIFY_USER,
A.COURT_NO
FROM ATTORNEY_STATE A
JOIN STATE S ON S.STATE_CODE = A.STATE_CODE
WHERE A.ATTORNEY_ID = :V_ATTORNEY_ID
UNION
SELECT CAST(0 AS SMALLINT) AS ASSOCIATED,
CAST(:V_ATTORNEY_ID AS INTEGER) AS ATTORNEY_ID,
S.STATE_CODE,
S.NAME AS STATE_NAME,
CAST(NULL AS TIMESTAMP) AS CREATE_DATE,
CAST(NULL AS SMALLINT) AS CREATE_USER,
CAST(NULL AS TIMESTAMP) AS MODIFY_DATE,
CAST(NULL AS SMALLINT) AS MODIFY_USER,
CAST(NULL AS VARCHAR(10)) AS COURT_NO
FROM STATE S
WHERE NOT EXISTS (SELECT *
FROM ATTORNEY_STATE A2
WHERE A2.ATTORNEY_ID = :V_ATTORNEY_ID
AND A2.STATE_CODE = S.STATE_CODE)
INTO :R_ASSOCIATED, :R_ATTORNEY_ID, :R_STATE_CODE, :R_STATE_NAME, 
:R_CREATE_DATE, :R_CREATE_USER, :R_MODIFY_DATE, :R_MODIFY_USER, 
:R_COURT_NO DO
BEGIN
SUSPEND;
END
end
^^
SET TERM ; ^^

regardless of whether you have performance problems or not. Firebird 
have greatly improved since version 0.9.4 and NOT IN may or may not be 
slow with 2.5, but it is so simple to replace IN with EXISTS, and 
IN() is never quicker than EXISTS (it can be equally quick), 
so I see no reason for ever using IN ().

Unfortunately, I have no answer to whether or not this bug have given 
you incorrect results on older Firebird versions or whether or not you 
ought to modify your old code.

By the way, in your particular case, I think I'd consider changing the 
procedure a bit more, but that eliminates the subselect and hence, your 
original question:

SET TERM ^^ ;
CREATE PROCEDURE SPS_ATTORNEY_STATE (
V_ATTORNEY_ID Integer)
returns (
R_ASSOCIATED SmallInt,
R_ATTORNEY_ID Integer,
R_STATE_CODE Char(2),
R_STATE_NAME VarChar(35),
R_CREATE_DATE TimeStamp,
R_CREATE_USER SmallInt,
R_MODIFY_DATE TimeStamp,
R_MODIFY_USER SmallInt,
R_COURT_NO VarChar(10))
AS
/*
Author : Michael Tuttle
Date : 10/02/2006
Purpose :
*/
begin
FOR SELECT CAST(IIF(A.STATE_CODE IS NULL, 0, 1) AS SMALLINT),
:V_ATTORNEY_ID,
S.STATE_CODE,
S.NAME,
A.CREATE_DATE,
A.CREATE_USER,
A.MO

RE: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Existence Predicates NOT IN Question

['stwizard' stwiz...@att.net [firebird-support]]

Mark, thanks for the clarification on this.  Makes sense now.  Appreciate all 
the help.

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Friday, July 24, 2015 9:40 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Existence 
Predicates NOT IN Question

 

  

On 24-7-2015 13:25, 'stwizard' stwiz...@att.net [firebird-support] wrote:
> Greetings All,
>
> In the Firebird 2 Migration & Installation guide
> http://www.firebirdsql.org/file/documentation/release_notes/Firebird-2_1_6-Installation.pdf
> on page 8 under the performance section it states the following:
>
> The following changes should be noted as possible sources of performance
> loss:
>
> Existence Predicates NOT IN and ALL May Be Slow
>
> Firebird and, before that, InterBase, have produced incorrect results
> for the logical existence predicates ALL and NOT IN for many years. That
> problem has been corrected in Firebird2.0, but the change means
> thatindexes on the inner tables cannot be used and performance may be
> slow compared to the same query's performance in V.1.5. “Inner tables”
> are the tables used in the subquery argument inside an ALL or NOT IN
> expression.
>
> So my questions:
>
> This only applies when there is a JOIN in the SQL statement correct?

No, everywhere you use NOT IN or ALL this applies. The documentation is 
talking about the inner table materialized by the select inside NOT IN 
(select ...). That condition can be replaced with an NOT EXISTS which - 
usually - performs better.

Mark
-- 
Mark Rotteveel





[Non-text portions of this message have been removed]

Re: [firebird-support] Problem with FB database that freezes

[conver...@gmail.com [firebird-support]]

Hi there,
 

 Can the connection errors in the log contribute to the increasing gap between 
the OAT and NT?
 

 Regards,
 

 -Eduardo

Re: [firebird-support] Problem with FB database that freezes

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,


OAT is an active transaction. You can see it alive if you analyze MON$ 
snaphot (you can use trial of our FBMonLogger).


So, disconnects are not related with Next-OAT gap - because this gap is 
caused by some open transaction which you can easily identify.


However, disconnects can lead to forced rollback, and as a result, OIT 
could stuck, and it will lead to increased gap OIT-OST, which is also 
not good.


For more details about OIT, OST and OAT read this ppt
http://www.slideshare.net/ibsurgeon/3-how-transactionswork

Regards,
Alexey Kovyazin
IBSurgeon





Hi there,


Can the connection errors in the log contribute to the increasing gap 
between the OAT and NT?



Regards,


-Eduardo

Re: [firebird-support] Problem with FB database that freezes

[conver...@gmail.com [firebird-support]]

 >As you are using 
 >FB TraceManager, it is a simple mouse-click via the context-menu in the 
 >parsed gstat output to locate the OAT in the monitoring tables.
 

 Thanks Thomas. I've located the OAT in the monitoring tables as you mention. 
Can you please elaborate a bit on how to find the client application or process 
that started this transaction? Is this something FBTM can help us with?
 

 Regards,
 

 -Eduardo

Re: [firebird-support] Problem with FB database that freezes

[Thomas Steinmaurer t...@iblogmanager.com [firebird-support]]

Hi Eduardo,

> 
>
>  >As you are using
>  >FB TraceManager, it is a simple mouse-click via the context-menu in the
>  >parsed gstat output to locate the OAT in the monitoring tables.
>
>
> Thanks Thomas. I've located the OAT in the monitoring tables as you
> mention. Can you please elaborate a bit on how to find the client
> application or process that started this transaction? Is this something
> FBTM can help us with?

The transaction monitoring table has an attachment id which should be 
also available in the attachments monitoring table. This will give you 
the client/host, remote process name etc.


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Problem with FB database that freezes

[conver...@gmail.com [firebird-support]]

Thanks Thomas. Today we had a performance problem about 3 hours ago, we had to 
reboot the Windows server to solve it. Prior to the restart, the OAT-NT gap was 
120479. After the restart it was 33.
 

 Interestingly enough, currently the gap is 354354 as I write this. That's 
almost three times the gap we had at the time of the restart, and performance 
has been normal since we restarted. 
 

 Thanks everyone for the help, any other pointers are much welcomed.
 

 Best Regards,
 

 -Eduardo

Re: [firebird-support] Problem with FB database that freezes

[Fabiano Kureck - Desenvolvimento SCI fabi...@sci10.com.br [firebird-support]]

Take at look at the HDD usage. Is HDD been used around 100% when slowly 
appears?


On 24/07/2015 16:00, conver...@gmail.com [firebird-support] wrote:


Thanks Thomas. Today we had a performance problem about 3 hours ago, 
we had to reboot the Windows server to solve it. Prior to the restart, 
the OAT-NT gap was 120479. After the restart it was 33.



Interestingly enough, currently the gap is 354354 as I write this. 
That's almost three times the gap we had at the time of the restart, 
and performance has been normal since we restarted.



Thanks everyone for the help, any other pointers are much welcomed.


Best Regards,


-Eduardo

[firebird-support] Firebird

[Fabiano Kureck - Desenvolvimento SCI fabi...@sci10.com.br [firebird-support]]

Você é Brasileiro? Estou acompanhando uma mensagem sua no grupo. Se 
falar português posso te ajudar melhor, por email, por aqui.

--

[firebird-support] FreeUDFLib 64 Bit version

['stwizard' stwiz...@att.net [firebird-support]]

Greetings,

 

Does anyone know where I can get a 64 bit version of the FreeUDFLib.dll?

 

I know Jeff Overcash (TeamB) has done it per this link, but I have no idea how 
to get ahold of him

http://codeverge.com/embarcadero.interbase.general/freeudflib-and-developing-udfs/1083840

 

Thanks,

Mike

 

 

 

Re: Rif: [firebird-support] Chararacter set NONE to ISO8859_1

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

In the process of moving the data back and forth, using a temporary 
field of the right character set, you have to drop the original field an 
then recreate it.


The process of dropping a field is a bit nasty because you have first to 
comment all the strored procedures and triggers in which this field is 
used, drop the field, and then uncomment those stored procedures and 
triggers.


I wonder if the following strategy has any pitfall:

1) Create the temporary field of the right character set
2) Fill it with data, using CAST to OCTETS as suggested by "The Firebird 
Book"

3) Alter the original field type, changing it to the right character set
4) Fill it with data from the temprorary field
5) Drop the temporary field

Notice that only the 2nd step could raise conversion errors, and you 
have to manage them there.
In the 4th step no error could occur since both fields are of the same 
type and character set.


Thanks

El 23/07/15 a las 08:44, Aldo Caruso aldo.car...@argencasas.com 
[firebird-support] escibió:


Pretty clear.
Thank you.

El 23/07/15 a las 05:54, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escibió:


On Wed, 22 Jul 2015 21:09:12 -0300, "Aldo Caruso
aldo.car...@argencasas.com
[firebird-support]"  wrote:
> Raffaele,
>
> thanks for your answer.
> The question is what prevents data loss in the first procedure ?
> Suppose the filed in question is
>
> FIRST_NAME VARCHAR(60) CHARACTER SET NONE
>
> so lets create a temporary field
>
> TMP_NAME VARCHAR(60) CHARACATER SET ISO8859_1
>
> and then lets fill it
>
> UPDATE TABLE1 SET TMP_NAME = CAST(FIRST_NAME AS VARCHAR(60)
> CHARACTER SET OCTETS)
>
> Whichever character (from 0 to 255) were in FIRST_NAME would be 
blindly

> copied to TMP_NAME.
> This has the same effect as assuming that the characters in FIRST_NAME
> where loaded as ISO8859_1, so simply changing FIRST_NAME type from 
NONE

> to ISO8859_1 would be equivalent.

No, if you alter the character set of an existing column, then the old
data will remain in its old format and format version, and will only be
converted when selected/queried. Leading potentially to conversion errors
at run time (there are a number of bytes in ISO-8859-1 that are not valid
to use). If you create a new column and transfer the data, the validity
check is done at the moment of transfer and you can fix any problems at
that time.

The important difference between these two is the time the conversion
error might occur.

Mark