[firebird-support] Difference between embedded and classic server behaviour

2013-09-11 Thread garethm 
Hi,

When attempting to run queries with a large number of expressions in the where 
clause, I have found a difference in behaviour between embedded and classic 
server: 

Note that this is a simplified case from my application's actual behaviour.

I am using Firebird 2.5.2 Security Update 1 build 26540, 64-bit on Windows 7 
64-bit. I am testing using application developed with Delphi XE2 Update 4 
Hotfix 1 and IBObjects 5.2.0 Build 6. I am running my tests on Windows 7 SP1 
64-bit. The same database file is used for all tests. The database is using the 
UTF8 character set, as are the connections to the database.

I generate a query that includes a number of tests ORed to each other. Each 
test just checks to see if the NAME field is equal to the string form of a 
number one larger that the one before, producing n terms in the WHERE clause.

For example, if n is 5, the following query is used:

SELECT * FROM Bldg B WHERE
B.Name = '1' 
OR B.Name = '2' 
OR B.Name = '3' 
OR B.Name = '4' 
OR B.Name = '5'

When run against classic server, I can have at most 3173 terms in the WHERE 
clause before the query string itself reaches a length of 65530 characters and 
then stops working. This is pretty much expected behaviour.

When run using the embedded engine, I can successfully have 1052 terms in the 
WHERE clause. In my tests, 1053, 1054, and 1056 terms in the WHERE clause cause 
Firebird to hang when attempting to prepare the query (1052 terms would prepare 
in less than a second; after 30 seconds I killed the test for these values). 
Testing with 1088 or more terms causes the test application to crash with a 
stack overflow in the fbembed.dll. 1052 terms has a query string of length 
20989 characters, while 1053 terms has a query string of length 21010.

I have not tested any other configurations.

Is there a difference to the limits on queries that can be run when working 
with the embedded engine that I need to be aware of to avoid this 
hanging/crashing behaviour?

Regards,
Gareth Marshall

[firebird-support] Re: Understanding query performance changes

2013-06-19 Thread garethm 
Hi Set,

Thank you very much for your help! Your query runs much faster than even the 
original query did, and is easier to understand.

Regards,
Gareth

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær 
svein.erling.tysvaer@... wrote:

 Hi,
 
 In upgrading from Firebird 2.0 to Firebird 2.5, I have a query that has 
 dramatically changed performance. It would be great if I could
 understand the performance information given in FlameRobin, and how this can 
 be used to return the query to its former performance.
 
 The query run is the following:
 SELECT Phrase.*
 FROM Phrase
   INNER JOIN (
 SELECT DISTINCT Phrase.PhraseKey 
 FROM Phrase,
 (
   SELECT PhraseKey, ParentPhraseKey 
   FROM Phrase 
   WHERE 
 Description CONTAINING 'Some Value'
 AND PhraseologyKey = (SELECT PhraseologyKey FROM Phraseology WHERE 
  Name = 'Some Name')
 ) Matches
 WHERE 
   (
 ( ( SELECT Flag FROM HAS_PHRASEOLOGY_PARENT( Phrase.PhraseKey, 
  Matches.PhraseKey ) ) = 1 )
 OR ( ( SELECT Flag FROM HAS_PHRASEOLOGY_PARENT( Matches.PhraseKey, 
  Phrase.PhraseKey ) ) = 1 )
   )
   ) T ON (Phrase.PhraseKey = T.PhraseKey) OR (Phrase.ParentPhraseKey = 
  T.PhraseKey) 
 
 HAS_PHRASEOLOGY_PARENT is a stored procedure with the following definition:
 SET TERM ^ ;
 CREATE PROCEDURE HAS_PHRASEOLOGY_PARENT (
   PHRASEKEY Char(18) CHARACTER SET OCTETS,
   PARENTPHRASEKEY Char(18) CHARACTER SET OCTETS ) RETURNS ( FLAG Integer ) 
  AS DECLARE VARIABLE CurrentKey CHAR(18) CHARACTER SET OCTETS; 
 BEGIN
   CurrentKey = PhraseKey;
   SELECT 0 FROM rdb$database INTO FLAG;
   WHILE (CurrentKey IS NOT NULL) DO
   BEGIN  
 IF (CurrentKey = ParentPhraseKey) THEN  
 BEGIN  
   Flag = 1;  
   CurrentKey = NULL;  
 END ELSE BEGIN  
   SELECT ParentPhraseKey 
 FROM Phrase 
 WHERE PhraseKey = :CurrentKey INTO :CurrentKey;  
 END
   END
   SUSPEND;
 END^
 SET TERM ; ^
 The query plan for both database versions is:
 PLAN (T MATCHES PHRASEOLOGY INDEX (RDB$33)) PLAN (RDB$DATABASE 
 NATURAL)(PHRASE INDEX (RDB$PRIMARY31)) PLAN (RDB$DATABASE NATURAL)
 (PHRASE INDEX (RDB$PRIMARY31)) PLAN JOIN (SORT (JOIN (T PHRASE NATURAL, T 
 MATCHES PHRASE NATURAL)), 
 PHRASE INDEX (RDB$PRIMARY31, PHRASEPARENTSORTORD))
 
 Wow, Gareth, a query that is both lean and unreadable simultaneously!
 
 With identical plans, I'm not knowledgeable enough to answer your question, 
 so rather than answering it, I wonder if the below select returns the desired 
 result and how it is performancewise?
 
 with recursive Matches as
 (SELECT p.PhraseKey
  FROM Phrase p
  JOIN Phraseology Po on p.PhraseologyKey = po.PhraseologyKey
  WHERE p.Description CONTAINING 'Some Value'
AND po.Name = 'Some Name'),
 RecursiveParent as
 (SELECT p.PhraseKey, p.ParentPhraseKey
  FROM Phrase p
  JOIN Matches m on p.PhraseKey = m.PhraseKey
  UNION ALL
  SELECT p.PhraseKey, p.ParentPhraseKey
  FROM Phrase p
  JOIN RecursiveParent rp on p.PhraseKey = rp.ParentPhraseKey),
 RecursiveChild as
 (SELECT p.PhraseKey, p.ParentPhraseKey
  FROM Phrase p
  JOIN Matches m on p.PhraseKey = m.PhraseKey
  UNION ALL
  SELECT p.PhraseKey, p.ParentPhraseKey
  FROM Phrase p
  JOIN RecursiveChild rc on p.ParentPhraseKey = rc.PhraseKey)
 
 SELECT p.*
 FROM Phrase p
 JOIN RecursiveParent rp on p.PhraseKey = rp.PhraseKey
 UNION
 SELECT p.*
 FROM Phrase p
 JOIN RecursiveChild rc on p.PhraseKey = rc.PhraseKey
 
 HTH,
 Set

[firebird-support] Understanding query performance changes

2013-06-18 Thread garethm 
Hi,

In upgrading from Firebird 2.0 to Firebird 2.5, I have a query that has 
dramatically changed performance. It would be great if I could understand the 
performance information given in FlameRobin, and how this can be used to return 
the query to its former performance.

The statistics reported by FlameRobin for the two database versions are as 
follows:

Firebird 2.5:
827,493,131 fetches, 6 marks, 509 reads, 6 writes.
0 inserts, 0 updates, 0 deletes, 2,376,096 index, 200,925,311 seq.
Delta memory: 118,904 bytes.
Total execution time: 00:13:04 (hh:mm:ss)

Firebird 2.0:
16,930,040 fetches, 6 marks, 122,122 reads, 6 writes.
0 inserts, 0 updates, 0 deletes, 2,376,082 index, 1,252,998 seq.
Delta memory: 88,432 bytes
Total execution time: 18.109s

The Firebird 2.5 query was run with the Windows Classic Server 2.5.1.26351 
64-bit. The database has the ODS Version 11.2 with a page size of 8192 and a 
default character set of UTF8. Page buffers is set to 2048.

The Firebird 2.0 query was run with the Windows Classic Server 2.0.5.13206 
32-bit. The database has ODS Version 11 with a page size of 4096 and a default 
character set of ASCII. Page buffers is set to 75.

The query run is the following:
SELECT Phrase.*
FROM Phrase
  INNER JOIN (
SELECT DISTINCT Phrase.PhraseKey 
FROM Phrase,
(
  SELECT PhraseKey, ParentPhraseKey 
  FROM Phrase 
  WHERE 
Description CONTAINING 'Some Value'
AND PhraseologyKey = (SELECT PhraseologyKey FROM Phraseology WHERE Name 
= 'Some Name')
) Matches
WHERE 
  (
( ( SELECT Flag FROM HAS_PHRASEOLOGY_PARENT( Phrase.PhraseKey, 
Matches.PhraseKey ) ) = 1 )
OR ( ( SELECT Flag FROM HAS_PHRASEOLOGY_PARENT( Matches.PhraseKey, 
Phrase.PhraseKey ) ) = 1 )
  )
  ) T ON (Phrase.PhraseKey = T.PhraseKey) OR (Phrase.ParentPhraseKey = 
T.PhraseKey) 

HAS_PHRASEOLOGY_PARENT is a stored procedure with the following definition:
SET TERM ^ ;
CREATE PROCEDURE HAS_PHRASEOLOGY_PARENT (
  PHRASEKEY Char(18) CHARACTER SET OCTETS,
  PARENTPHRASEKEY Char(18) CHARACTER SET OCTETS )
RETURNS ( FLAG Integer )
AS
DECLARE VARIABLE CurrentKey CHAR(18) CHARACTER SET OCTETS;  
BEGIN  
  CurrentKey = PhraseKey;  
  SELECT 0 FROM rdb$database INTO FLAG;  
  WHILE (CurrentKey IS NOT NULL) DO  
  BEGIN  
IF (CurrentKey = ParentPhraseKey) THEN  
BEGIN  
  Flag = 1;  
  CurrentKey = NULL;  
END ELSE BEGIN  
  SELECT ParentPhraseKey 
FROM Phrase 
WHERE PhraseKey = :CurrentKey INTO :CurrentKey;  
END  
  END  
  SUSPEND;  
END^
SET TERM ; ^

The query plan for both database versions is:
PLAN (T MATCHES PHRASEOLOGY INDEX (RDB$33))
PLAN (RDB$DATABASE NATURAL)(PHRASE INDEX (RDB$PRIMARY31))
PLAN (RDB$DATABASE NATURAL)(PHRASE INDEX (RDB$PRIMARY31))
PLAN JOIN (SORT (JOIN (T PHRASE NATURAL, T MATCHES PHRASE NATURAL)), PHRASE 
INDEX (RDB$PRIMARY31, PHRASEPARENTSORTORD))

The schema of the PHRASE table is:
CREATE TABLE PHRASE
(
  PHRASEKEY Char(18) CHARACTER SET OCTETS NOT NULL,
  PHRASEOLOGYKEY Char(18) CHARACTER SET OCTETS NOT NULL,
  CODE Varchar(20),
  DESCRIPTION Blob sub_type 1,
  PARENTPHRASEKEY Char(18) CHARACTER SET OCTETS,
  UNIT Varchar(12),
  SORTORD Integer NOT NULL,
  DATEADD Timestamp NOT NULL,
  DATEMOD Timestamp NOT NULL,
  PRIMARY KEY (PHRASEKEY)
);
CREATE INDEX PHRASEPARENTSORTORD ON PHRASE (PARENTPHRASEKEY,SORTORD);

The schema of the PHRASEOLOGY table is:
CREATE TABLE PHRASEOLOGY
(
  PHRASEOLOGYKEY Char(18) CHARACTER SET OCTETS NOT NULL,
  NAME Varchar(50) NOT NULL,
  NOTES Blob sub_type 1,
  DATEADD Timestamp NOT NULL,
  DATEMOD Timestamp NOT NULL,
  PRIMARY KEY (PHRASEOLOGYKEY),
  UNIQUE (NAME)
);

Regards,
Gareth Marshall

[firebird-support] Query slowness

2013-06-03 Thread garethm 
Hi,

I am currently using Firebird 2.5.1.26351 using Classic Server 64-bit. The 
database file was created with Firebird 2.5.1.26351 and is 546MB with a page 
size of 8192. The server and client are both running on my Windows 7 64-bit 
development machine.

I ran the following query using FlameRobin:

SELECT COUNT(*) 
FROM T1

This query took 8 minutes 56 seconds to run, and returned a result of 131822 
rows.

I then closed FlameRobin and reopened it. The same query ran in 0.117s.

I know that doing a database sweep can cause queries to run slowly, and 
Flamerobin indicates that the sweep interval is set to 2. Is the sweep the 
only possible reason for the performance difference between the two runs of the 
query with classic server if each run is done on a new connection to the 
database?

Is 9 minutes a typical time to sweep a table with 131822 records in a 546MB 
database?

Regards,
Gareth Marshall

[firebird-support] Firebird and RAID

2013-02-03 Thread garethm 
Hi,

What factors do we need to be aware of relating to Firebird and RAID 
controllers?

We have a customer using a Firebird 2.0.6 Classic Server database on a single 
disk connected to an IBM ServeRAID M5015 SAS/SATA Controller. The disk is not 
part of any RAID array. The server is running Windows 2008 R2 64-bit.

Performance in this configuration is much slower than when the disk is not 
connected to the RAID controller. Other applications and database services 
running on the same hardware do not have any difference in performance based on 
how the disk is connected to the computer.

Extensive consultation with IBM has not resolved the issue, and they feel that 
Firebird must be the cause of the performance issues.

I have spent some time researching this topic, and came across a case in 2004 
where someone had a similar experience with Firebird 1.5 and Windows 2000.[1] I 
also found a case in 2010 where a user had found issues with writes above a 
certain size with an unknown RAID controller.[2]

Are there any RAID controllers known to be incompatible with Firebird?

Are there recommendations we can make to our customers to prevent them from 
experiencing issues like these when attempting to deploy RAID based servers?

We are currently migrating to Firebird 2.5. Have any issues been fixed that 
would have an impact on performance when running on RAID?

Regards,
Gareth Marshall

[1] 
http://forums.devshed.com/firebird-sql-development-61/firebird-and-raid-176267.html
[2] http://tech.groups.yahoo.com/group/firebird-support/message/111015