[firebird-support] RE: how to insert image blob in Firebird in PHP and ADODB

[g80rock]

Thanks Helen!
 

 I found info about that you have to create a filter if you don't use the 
standard ones in Firebird.
 So i just set the subtype to 0, and added a field that tells which image type 
it is.
 I also read that Firebird was unable to tell which type it is. So i just put 
the binary data in it, without any image headers instead.
 

 It works great now. It took a lot of time searching.
 

 I am happy now:)

Re: [firebird-support] how to insert image blob in Firebird in PHP and ADODB

[Helen Borrie]

At 07:30 a.m. 22/01/2014, g80r...@gmail.com wrote:


>I have done it in Delphi, but now i have the same problem finding how to do it 
>in PHP.
>
>I searched two days.
>
>I need to store image in the database and not as a file on the server.
>i have set blob field to subtype -1.
>
>i first do base64_encode for the file in PHP and then try to insert it in SQL.
>But get error:
>General error: -413 filter not found to convert type 1 to type -1

The reason for that error is that BLOB sub_type -1 would not exist unless you 
had written and declared a custom BLOB filter for that sub_type.


>The code is:
>$imgSrc='testimage.jpg');
>$img_src = $imgSrc;
>$imgbinary = fread(fopen($img_src, "r"), filesize($img_src));
>
>$img_str = base64_encode($imgbinary);
>$sql="insert into info (ID,LOGO) values('1','$img_str')";
>
>I think it has to do something with the header type. as JPG or something.

Maybe for PHP (I don't know about that) but Firebird has no knowledge of what 
is inside a non-text BLOB.  Normally you would just store the image contents as 
a BINARY BLOB (sub_type 0).

>this only gives error.
>and i just can't find any examples on net.
>is there anyone that have an example to how to do it in PHP?
>
>i really need help on this.

Then my advice is to ask on the Firebird-PHP list - someone there will surely 
have done this.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__ 

[firebird-support] RE: how to insert image blob in Firebird in PHP and ADODB

[g80rock]

i got it to work.
 

 for now i am using PDO for Firebird, but it should be the same as ADODB. maybe.
 i did it like this, if anyone want to know. i also have to create a field for 
the image type, so i can upload different types, and show them as the correct 
types.
 i also had to set subtype to 0 in the image field.
 

  function savelogo_to_db($imgSrc,$dbh)
  {
 $img_src = $imgSrc;
 $imgbinary = fread(fopen($img_src, "r"), filesize($img_src));
 $img_str = base64_encode($imgbinary);
try
{
  $img_str = base64_encode($imgbinary);
  $sql="insert into logo (test) values('$img_str')";
  $result=run_sql($dbh,$sql);
}
catch (Exception $e)
{
  echo "Failed: " . $e->getMessage();
};
 }
 

 

 // and here is the function for retrieving image from the database, and show 
it.
 function get_logo_blob_image($dbh)
 {
$sql="select test from logo";
$result=db_fetch_rows($dbh,$sql);
  if ($result!==false)
  {
$row = $result->fetch();
$imgbinary=$row['TEST'];
echo '';
  }
 }
 

 this is only a test i made for making it work. and it does.
 it was hard to find any samples, so i just had to try on my own.
 

Re: [firebird-support] Very very very slow FB 2.5.2 64bit performance on Windows 2008 R2

[Roland Turcan]

[firebird-support] “Tracking” (debugging) what is happening in a Firebird 2.5 database

[ropopa01]

 Hi all
 I need some advice in how to handle the following:

Server Firebird 2.5 (I use Delphi XE2).
1.I have 2 structure identical database.
2.I copy 1-2-3 records from one table database to another.
3.I have some triggers and SP on the table that I copy.

trigger After Insert (execute one stored procedure that recompute some other 
records from the same table).

The database are identical but in one database the after insert work ok and in 
the other one no. 
 Also both triggers ARE working. (for testing purpose I raise an exception and 
is fire the exception).

So i want to find a way to track what is happening and to find the problem. 
 How to monitor/debug this situation. 
also how to track deadlock?

 what tools exist for this kind of trouble?
 
any advice?
tks a lot
Razvan

[firebird-support] how to insert image blob in Firebird in PHP and ADODB

[g80rock]

I have done it in Delphi, but now i have the same problem finding how to do it 
in PHP. 
 I searched two days.
 

 I need to store image in the database and not as a file on the server.
 i have set blob field to subtype -1.
 

 i first do base64_encode for the file in PHP and then try to insert it in SQL.
 But get error:
 General error: -413 filter not found to convert type 1 to type -1

 

 The code is:
 $imgSrc='testimage.jpg');

 $img_src = $imgSrc;
 $imgbinary = fread(fopen($img_src, "r"), filesize($img_src));
 


 $img_str = base64_encode($imgbinary);
 $sql="insert into info (ID,LOGO) values('1','$img_str')";
 

 I think it has to do something with the header type. as JPG or something.
 

 this only gives error.
 and i just can't find any examples on net.
 is there anyone that have an example to how to do it in PHP?
 

 i really need help on this.

Re: [firebird-support] Very very very slow FB 2.5.2 64bit performance on Windows 2008 R2

[Hugo Eyng]

I changed the paramter FileSystemCacheSize = 0 to FileSystemCacheSize = 
20 in the firebird.conf

as suggested in:

http://dyemanov.blogspot.com.br/2012/03/firebird-vs-windows-file-system-caching.html

Hugo

Em 21/01/2014 12:06, Roland Turcan escreveu:


Yes, I am interested too.

What was the key to get rid of this problem?

Thanks in advance.

<<< 21.01.2014 15:29 - Fabiano - Desenvolvimento SCI 
"fabi...@sci10.com.br" >>>



How you solved your problem?

*De:* firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] *Em nome de *Hugo Eyng

*Enviada em:* terça-feira, 21 de janeiro de 2014 10:24
*Para:* firebird-support@yahoogroups.com
*Assunto:* Re: [firebird-support] Very very very slow FB 2.5.2 64bit 
performance on Windows 2008 R2



Hi Helen.

Thanks for your answer.

You are right.

But the "Windows 64 file cache performance"  was a problem, as said Sean.

Só 'reserving' 10GB as a RAM DRIVE grant that I would have always 
available RAM.


But now I solved the 'cache performance' and I will not need RAM DRIVE 
anymore.


Even so, the FB performance is not compatible to the hardware used to 
run it.

Em 20/01/2014 23:12, Helen Borrie escreveu:

At 02:01 p.m. 21/01/2014, Hugo Eyng wrote:

>As Firebird do not use available RAM I created a RAM DRIVE with 10GB 
and pointed parameter 'TempDirectories' (firebird.conf) to this RAM 
DRIVE, but FB just uses it rarely in very big 'SELECT'. OK, when FB 
uses the RAM DRIVE it increases a SELECT speed in more than 80%. I 
expected FB could use this for every SELECTS and so improve the 
application.


Fb uses RAM directly for sorts, if enough is available. It only takes 
the sort sets to disk if available RAM is insufficient.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__


--


Atenciosamente,

Hugo Eyng


How you solved your problem?

*De:* firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] *Em nome de *Hugo Eyng

*Enviada em:* terça-feira, 21 de janeiro de 2014 10:24
*Para:* firebird-support@yahoogroups.com
*Assunto:* Re: [firebird-support] Very very very slow FB 2.5.2 64bit 
performance on Windows 2008 R2



Hi Helen.

Thanks for your answer.

You are right.

But the "Windows 64 file cache performance"  was a problem, as said Sean.

Só 'reserving' 10GB as a RAM DRIVE grant that I would have always 
available RAM.


But now I solved the 'cache performance' and I will not need RAM DRIVE 
anymore.


Even so, the FB performance is not compatible to the hardware used to 
run it.

Em 20/01/2014 23:12, Helen Borrie escreveu:

At 02:01 p.m. 21/01/2014, Hugo Eyng wrote:

>As Firebird do not use available RAM I created a RAM DRIVE with 10GB 
and pointed parameter 'TempDirectories' (firebird.conf) to this RAM 
DRIVE, but FB just uses it rarely in very big 'SELECT'. OK, when FB 
uses the RAM DRIVE it increases a SELECT speed in more than 80%. I 
expected FB could use this for every SELECTS and so improve the 
application.


Fb uses RAM directly for sorts, if enough is available. It only takes 
the sort sets to disk if available RAM is insufficient.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__


--


Atenciosamente,

Hugo Eyng





/--
Best regards, TRoland
/http://www.rotursoft.sk
http://exekutor.rotursoft.sk




--


Atenciosamente,

Hugo Eyng

Re: [firebird-support] Very very very slow FB 2.5.2 64bit performance on Windows 2008 R2

[Hugo Eyng]

Hi Fabiano.

The numbers of CristalDiskMarkSx64 are better:

662 MB/s read and 169 MB/s write

I don´t know de difference between CristalDiskMarkX64 and DiskMarkSx64

But DiskMarkSx64 gave me better numbers.

Hugo

Em 21/01/2014 07:52, fabianoas...@gmail.com escreveu:


Maybe your problem is that you buy a internet server from dell, not a 
database server!
What is the diference? Internet servers from Dell does not have a disk 
controller WITH physical cache.
Run CrystalDiskMark or other disk benckmark on this computer and in 
another desktop computer. If it is slow you need to replace your disk 
controller.
Also read from Helen sayd. You are loosing the point when using a 
gente ram disk.


Em 20/01/2014 23:01, "Hugo Eyng" > escreveu:


Hi.
I am facing a similar situation.
32GB RAM
Dell PowerEdge R420
Intel Xeon CPU E5-2609 two processors 2.40ghz 8 cores
RAID

Firebird 2.5.2 do not use the available resources of the server
even I try to login to the server just one user.
I am using SuperClassic

I felt a little diference by modifying parameters in firebird.conf
like DefaultDbCachePages = 2250 and some others
This increases performance in about 30% in my case
But I expected more.

As Firebird do not use available RAM I created a RAM DRIVE with
10GB and pointed parameter 'TempDirectories' (firebird.conf) to
this RAM DRIVE, but FB just uses it rarely in very big 'SELECT'.
OK, when FB uses the RAM DRIVE it increases a SELECT speed in more
than 80%.  I expected FB could use this for every SELECTS and so
improve the application.

Hugo Eyng

Em 20/01/2014 20:51, Roland Turcan escreveu:


Hello guys,

I found some info about problems with performance when FB is running
on Win2008R2 which is domain controller.
But in my case is it not DC, but the performance is the worst I met.
It is new server running 10GB of RAM, RAID, ... it is quite new
server box.

It cannot be, that basically dummy update on table with about 700 000
records is executing over one hour. CPU is relaxing, HDD is relaxing
too and I cannot force server to fun faster.

What can I check?

Thanks in advance.

-- 
Best regards, TRoland

http://www.rotursoft.sk
http://exekutor.rotursoft.sk



-- 



Atenciosamente,

Hugo Eyng




--


Atenciosamente,

Hugo Eyng

Re: [firebird-support] Very very very slow FB 2.5.2 64bit performance on Windows 2008 R2

[Hugo Eyng]

Hi Fabiano.

My server:

 * Intel® Xeon® E5-2609 v2 2.50GHz,10M Cache, 6.4GT/s QPI, No Turbo,
   4C, 80W, Max Mem 1333MHz
 * 8GB RDIMM, 1600MT/s, Low Volt, Dual Rank, x4 Data Width
 * HD 6 x 300GB SAS, 10K RPM, 6GBPS Hot-Plug de 2.5"
 * PowerEdgeR620,Intel®Xeon®E-26XXv2Processors


CristalDiskMark:

Seq 468 MB/s Read161 MB/s Write(Ten times faster than a notebook 
vostro 1510 intel core 2 duo 1.8ghz)





Em 21/01/2014 07:52, fabianoas...@gmail.com escreveu:


Maybe your problem is that you buy a internet server from dell, not a 
database server!
What is the diference? Internet servers from Dell does not have a disk 
controller WITH physical cache.
Run CrystalDiskMark or other disk benckmark on this computer and in 
another desktop computer. If it is slow you need to replace your disk 
controller.
Also read from Helen sayd. You are loosing the point when using a 
gente ram disk.


Em 20/01/2014 23:01, "Hugo Eyng" > escreveu:


Hi.
I am facing a similar situation.
32GB RAM
Dell PowerEdge R420
Intel Xeon CPU E5-2609 two processors 2.40ghz 8 cores
RAID

Firebird 2.5.2 do not use the available resources of the server
even I try to login to the server just one user.
I am using SuperClassic

I felt a little diference by modifying parameters in firebird.conf
like DefaultDbCachePages = 2250 and some others
This increases performance in about 30% in my case
But I expected more.

As Firebird do not use available RAM I created a RAM DRIVE with
10GB and pointed parameter 'TempDirectories' (firebird.conf) to
this RAM DRIVE, but FB just uses it rarely in very big 'SELECT'.
OK, when FB uses the RAM DRIVE it increases a SELECT speed in more
than 80%.  I expected FB could use this for every SELECTS and so
improve the application.

Hugo Eyng

Em 20/01/2014 20:51, Roland Turcan escreveu:


Hello guys,

I found some info about problems with performance when FB is running
on Win2008R2 which is domain controller.
But in my case is it not DC, but the performance is the worst I met.
It is new server running 10GB of RAM, RAID, ... it is quite new
server box.

It cannot be, that basically dummy update on table with about 700 000
records is executing over one hour. CPU is relaxing, HDD is relaxing
too and I cannot force server to fun faster.

What can I check?

Thanks in advance.

-- 
Best regards, TRoland

http://www.rotursoft.sk
http://exekutor.rotursoft.sk



-- 



Atenciosamente,

Hugo Eyng




--


Atenciosamente,

Hugo Eyng

Re: RES: [firebird-support] Very very very slow FB 2.5.2 64bit performance on Windows 2008 R2

[Roland Turcan]

Re: [firebird-support] Best Practice in Use of Colon

[Ivan Přenosil]

>Where the use of : is optional, as best practice should : be used or avoided.
>Or in such cases use of : is irrelevant in terms of performance.

Inside "pure" sql statements it is manadatory (to distinguish between parameter 
name and column name),
on other places it does not matter, the parser accepts both variants (and they 
are identical)

>  2.  I'm appending two stored procedures, one omits colon (Para 3 below) and 
> another uses colon (Para 4 below) for fetching 
> exactly the same data.  The FlameRobin's Statistics TAB output on running the 
> procedures (Firebird 3 on Windows 7 32 Bit)  is 
> listed as under for both the procedures, please notice the difference 
> (omitting colon appears to be lowering performance) :
>
> OMITS COLON:-  USES COLON:-
>
> Executing statement... Executing 
> statement...
> Statement executed (elapsed time: 0.000s). Statement executed 
> (elapsed time: 0.000s).
> 16541 fetches, 2 marks, 5 reads, 2 writes. 760 fetches, 0 
> marks, 0 reads, 0 writes.
> 0 inserts, 0 updates, 0 deletes, 72 index, 8064 seq.  0 inserts, 0 
> updates, 0 deletes, 71 index, 264 seq.
> Delta memory: 170752 bytes.  Delta memory: 
> 126824 bytes.
> Total execution time: 0.174s  Total execution 
> time: 0.049s
> Script execution finished.  Script 
> execution finished.

The first execute is longer because it also includes starting the transaction, 
fetching metadata etc.

Ivan 

Re: [firebird-support] Best Practice in Use of Colon

[LtColRDSChauhan]

1.  Grateful for your response please.  Apologies for putting up my
question ambiguously.

2.  I'm appending two stored procedures, one omits colon (Para 3 below) and
another uses colon (Para 4 below) for fetching exactly the same data.  The
FlameRobin's Statistics TAB output on running the procedures (Firebird 3 on
Windows 7 32 Bit)  is listed as under for both the procedures, please
notice the difference (omitting colon appears to be lowering performance) :

OMITS COLON:-  USES COLON:-

Executing statement... Executing
statement...
Statement executed (elapsed time: 0.000s). Statement
executed (elapsed time: 0.000s).
16541 fetches, 2 marks, 5 reads, 2 writes. 760 fetches, 0
marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 72 index, 8064 seq.  0 inserts, 0
updates, 0 deletes, 71 index, 264 seq.
Delta memory: 170752 bytes.  Delta memory:
126824 bytes.
Total execution time: 0.174s  Total
execution time: 0.049s
Script execution finished.  Script
execution finished.


3.  Stored Procedure With Colon Omitted in the output parameters :-

SET TERM ^ ;
ALTER PROCEDURE SP_PATIENTREGISTRATION
RETURNS (
REGDID bigint,
BILLNO bigint,
INFOID bigint,
HOSPSRVCID smallint,
REFBYDRID integer,
CHIEFCONSULTANTID integer,
COMPANY varchar(80),
COMPANYEMPLOYEEID varchar(80),
HEALTHCHKUP varchar(80),
MLCINFORMANT varchar(80),
DISCHSUMBYTES integer,
MLCPOLICEIDNO varchar(80),
DISCHTYPE varchar(80),
DISCHSUMMARY blob sub_type 0,
IPDNO bigint,
TIMEADMIT time,
ROOM varchar(80),
BED varchar(80),
NAMERELATIVE varchar(80),
CONTACTNORELATIVE varchar(80),
LIKEDISCHARGEDATE timestamp,
DISCHARGEDATE timestamp,
REMARKS varchar(80) )
AS
BEGIN
FOR SELECT
a.REGDID,
a.BILLNO,
a.INFOID,
a.HOSPSRVCID,
a.REFBYDRID,
a.CHIEFCONSULTANTID,
a.COMPANY,
a.COMPANYEMPLOYEEID,
a.HEALTHCHKUP,
a.MLCINFORMANT,
a.DISCHSUMBYTES,
a.MLCPOLICEIDNO,
a.DISCHTYPE,
a.DISCHSUMMARY,
a.IPDNO,
a.TIMEADMIT,
a.ROOM,
a.BED,
a.NAMERELATIVE,
a.CONTACTNORELATIVE,
a.LIKEDISCHARGEDATE,
a.DISCHARGEDATE,
a.REMARKS
FROM PATIENTREGISTRATION a
INTO
REGDID,
BILLNO,
INFOID,
HOSPSRVCID,
REFBYDRID,
CHIEFCONSULTANTID,
COMPANY,
COMPANYEMPLOYEEID,
HEALTHCHKUP,
MLCINFORMANT,
DISCHSUMBYTES,
MLCPOLICEIDNO,
DISCHTYPE,
DISCHSUMMARY,
IPDNO,
TIMEADMIT,
ROOM,
BED,
NAMERELATIVE,
CONTACTNORELATIVE,
LIKEDISCHARGEDATE,
DISCHARGEDATE,
REMARKS
DO
BEGIN
SUSPEND;
END
END^
SET TERM ; ^


GRANT EXECUTE
 ON PROCEDURE SP_PATIENTREGISTRATION TO  SYSDBA WITH GRANT OPTION;

4.  Stored Procedure With Colon used in the output parameters  :-

SET TERM ^ ;
ALTER PROCEDURE SP_PATIENTREGISTRATION_1
RETURNS (
REGDID bigint,
BILLNO bigint,
INFOID bigint,
HOSPSRVCID smallint,
REFBYDRID integer,
CHIEFCONSULTANTID integer,
COMPANY varchar(80),
COMPANYEMPLOYEEID varchar(80),
HEALTHCHKUP varchar(80),
MLCINFORMANT varchar(80),
DISCHSUMBYTES integer,
MLCPOLICEIDNO varchar(80),
DISCHTYPE varchar(80),
DISCHSUMMARY blob sub_type 0,
IPDNO bigint,
TIMEADMIT time,
ROOM varchar(80),
BED varchar(80),
NAMERELATIVE varchar(80),
CONTACTNORELATIVE varchar(80),
LIKEDISCHARGEDATE timestamp,
DISCHARGEDATE timestamp,
REMARKS varchar(80) )
AS
BEGIN
FOR SELECT
a.REGDID,
a.BILLNO,
a.INFOID,
a.HOSPSRVCID,
a.REFBYDRID,
a.CHIEFCONSULTANTID,
a.COMPANY,
a.COMPANYEMPLOYEEID,
a.HEALTHCHKUP,
a.MLCINFORMANT,
a.DISCHSUMBYTES,
a.MLCPOLICEIDNO,
a.DISCHTYPE,
a.DISCHSUMMARY,
a.IPDNO,
a.TIMEADMIT,
a.ROOM,
a.BED,
a.NAMERELATIVE,
a.CONTACTNORELATIVE,
a.LIKEDISCHARGEDATE,
a.DISCHARGEDATE,
a.REMARKS
FROM PATIENTREGISTRATION a
INTO
:REGDID,
:BILLNO,
:INFOID,
:HOSPSRVCID,
:REFBYDRID,
:CHIEFCONSULTANTID,
:COMPANY,
:COMPANYEMPLOYEEID,
:HEALTHCHKUP,
:MLCINFORMANT,
:DISCHSUMBYTES,
:MLCPOLICEIDNO,
:DISCHTYPE,
:DISCHSUMMARY,
:IPDNO,
:TIMEADMIT,
:ROOM,
:BED,
:NAMERELATIVE,
:CONTACTNORELATIVE,
:LIKEDISCHARGEDATE,
:DISCHARGEDATE,
:REMARKS
DO
BEGIN
SUSPEND;
END
END^
SET TERM ; ^


GRANT EXECUTE
 ON PROCEDURE SP_PATIENTREGISTRAT

RES: [firebird-support] Very very very slow FB 2.5.2 64bit performance on Windows 2008 R2

[Fabiano - Desenvolvimento SCI]

How you solved your problem?

 

De: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Em nome de Hugo Eyng
Enviada em: terça-feira, 21 de janeiro de 2014 10:24
Para: firebird-support@yahoogroups.com
Assunto: Re: [firebird-support] Very very very slow FB 2.5.2 64bit
performance on Windows 2008 R2

 

  

Hi Helen.

Thanks for your answer.

You are right.

But the "Windows 64 file cache performance"  was a problem, as said Sean.

Só 'reserving' 10GB as a RAM DRIVE grant that I would have always available
RAM.

But now I solved the 'cache performance' and I will not need RAM DRIVE
anymore.

Even so, the FB performance is not compatible to the hardware used to run
it. 

Em 20/01/2014 23:12, Helen Borrie escreveu:

  

At 02:01 p.m. 21/01/2014, Hugo Eyng wrote:

>As Firebird do not use available RAM I created a RAM DRIVE with 10GB and
pointed parameter 'TempDirectories' (firebird.conf) to this RAM DRIVE, but
FB just uses it rarely in very big 'SELECT'. OK, when FB uses the RAM DRIVE
it increases a SELECT speed in more than 80%. I expected FB could use this
for every SELECTS and so improve the application.

Fb uses RAM directly for sorts, if enough is available. It only takes the
sort sets to disk if available RAM is insufficient. 

Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__ 





-- 
 
 
Atenciosamente,
 
Hugo Eyng

Re: [firebird-support] Very very very slow FB 2.5.2 64bit performance on Windows 2008 R2

[Hugo Eyng]

Hi Helen.

Thanks for your answer.

You are right.

But the "Windows 64 file cache performance"  was a problem, as said Sean.

Só 'reserving' 10GB as a RAM DRIVE grant that I would have always 
available RAM.


But now I solved the 'cache performance' and I will not need RAM DRIVE 
anymore.


Even so, the FB performance is not compatible to the hardware used to 
run it.

Em 20/01/2014 23:12, Helen Borrie escreveu:


At 02:01 p.m. 21/01/2014, Hugo Eyng wrote:

>As Firebird do not use available RAM I created a RAM DRIVE with 10GB 
and pointed parameter 'TempDirectories' (firebird.conf) to this RAM 
DRIVE, but FB just uses it rarely in very big 'SELECT'. OK, when FB 
uses the RAM DRIVE it increases a SELECT speed in more than 80%. I 
expected FB could use this for every SELECTS and so improve the 
application.


Fb uses RAM directly for sorts, if enough is available. It only takes 
the sort sets to disk if available RAM is insufficient.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__




--


Atenciosamente,

Hugo Eyng

Re: [firebird-support] Re: Adding a column to a primary key

[Tim Ward]

On 21/01/2014 13:10, Svein Erling Tysvær wrote:


What's the reason you think you need another ID column, Tim?  
There may be other reasons for you trying to partition your PK, if you 
told us why, then maybe someone may come up with a simpler way to 
solve your problem than add another part to the PK.


It's a long complicated story - this particular approach is the one that 
involves redesigning as little as possible of the overall system 
architecture. Other approaches are, indeed, no doubt, possible, but I'm 
starting with the one(s) that appear to give (even with the problems 
I've identified so far) the shortest implementation time.


(It probably doesn't help that I'm more used to other databases which 
seem less keen on stopping you doing things because of dependencies.)


--
Tim Ward

Re: [firebird-support] Re: Adding a column to a primary key

[Tim Ward]

On 21/01/2014 11:21, tomconl...@gmail.com wrote:


Tim - suggest you download the lightweight cross-platform tool for 
Firebird (flamerobin - http://www.flamerobin.org/) and:


1. Connect to your db

2. Select the table and look at 'Dependencies' page.

That looks helpful, thanks. But it still gives me a manual process, 
which, with 26 tables to do, still has scope for being slightly tedious!


--
Tim Ward

[firebird-support] Re: Adding a column to a primary key

[Svein Erling Tysvær]

>I'm not at all sure I'm going to like the answer to this one.
>
>I've got a bunch of tables each with a numeric synthetic single column primary 
>key, as in
>
>>CREATE TABLE A
>(
>A_ID BIGINT NOT NULL,
>... // other columns
>CONSTRAINT PK_A PRIMARY KEY( A_ID )
>);
>
>(and so for TABLE B, C etc, to M say).
>
>I now need to partition these tables by some other factor Z_ID, which itself 
>is a foreign key to somewhere else, so that they become
>
>CREATE TABLE A
>(
>A_ID BIGINT NOT NULL,
>Z_ID BIGINT NOT NULL,
>... // other columns
>CONSTRAINT PK_A PRIMARY KEY( A_ID, Z_ID ),
>CONSTRAINT FK_A_Z FOREIGN KEY( Z_ID ) REFERENCES Z( Z_ID )
>);
>
>so that instead of containing no more than one row for A_ID = 42, it can now 
>contain one row for A_ID = 42 for Z_ID = 17, and another row for A_ID = 42 but 
>with a different Z_ID = 87.
>
>There's an additional twist that some of these tables refer to each other, eg 
>table C might have an A_ID column and a FOREIGN KEY( A_ID ) REFERENCES A( A_ID 
>), and this'll need to be changed to FOREIGN KEY( >A_ID, Z_ID ) REFERENCES A( 
>A_ID, Z_ID ), so the new column Z_ID in table C is both a column of the 
>primary key and a column in a foreign key.
>
>The question is: what has to go into the upgrade script?
>
>(It probably doesn't help much, but I can guarantee that none of the tables 
>affected will contain any data at the time we want to run the upgrade scripts, 
>and the application will not be running.)
>
>All I've found so far is suggestions that you have to drop the old primary key 
>constraint and create the new one, which also means dropping and re-creating 
>everything that references the old primary key, which at >first sight sounds 
>like a complete nightmare.
>
>Is that really the only way?
>
>If so, are there any useful recipes somewhere for finding all the dependent 
>objects that need deleting and re-creating? - I'm aware that the system tables 
>are documented in appendix V of the Firebird book but I'm >afraid it's not 
>immediately obvious to me how to use this information to identify the 
>particular dependencies I'm after.

What's the reason you think you need another ID column, Tim? If it is adding 
branches or something, an alternative might be to have different ranges, either 
letting one branch have, say, 1-99, another 100-199 etc. Another 
option is to have different offsets for each branch and use GEN_ID(, 100) 
rather than GEN_ID(, 1) so that one branch gets 101, 201, 301... another 
branch 102, 202, 302 etc. There may be other reasons for you trying to 
partition your PK, if you told us why, then maybe someone may come up with a 
simpler way to solve your problem than add another part to the PK.

HTH,
Set

[firebird-support] Re: Adding a column to a primary key

[tomconlon7]

Tim - suggest you download the lightweight cross-platform tool for Firebird 
(flamerobin - http://www.flamerobin.org/) and: 
 

 1. Connect to your db
 2. Select the table and look at 'Dependencies' page.
 

 I don't know of any other way to do what you want to do but given it is a 
relational db that makes perfect sense also.
 Tom
 

 

[firebird-support] Re: Adding a column to a primary key

[Tim Ward]

Nobody?

On 17/01/2014 14:34, Tim Ward wrote:

I'm not at all sure I'm going to like the answer to this one.

I've got a bunch of tables each with a numeric synthetic single column 
primary key, as in


CREATE TABLE A
(
A_ID BIGINT NOT NULL,
... // other columns
CONSTRAINT PK_A PRIMARY KEY( A_ID )
);

(and so for TABLE B, C etc, to M say).

I now need to partition these tables by some other factor Z_ID, which 
itself is a foreign key to somewhere else, so that they become


CREATE TABLE A
(
A_ID BIGINT NOT NULL,
Z_ID BIGINT NOT NULL,
... // other columns
CONSTRAINT PK_A PRIMARY KEY( A_ID, Z_ID ),
CONSTRAINT FK_A_Z FOREIGN KEY( Z_ID ) REFERENCES Z( Z_ID )
);

so that instead of containing no more than one row for A_ID = 42, it 
can now contain one row for A_ID = 42 for Z_ID = 17, and another row 
for A_ID = 42 but with a different Z_ID = 87.


There's an additional twist that some of these tables refer to each 
other, eg table C might have an A_ID column and a FOREIGN KEY( A_ID ) 
REFERENCES A( A_ID ), and this'll need to be changed to FOREIGN KEY( 
A_ID, Z_ID ) REFERENCES A( A_ID, Z_ID ), so the new column Z_ID in 
table C is /both/ a column of the primary key /and/ a column in a 
foreign key.


The question is: what has to go into the upgrade script?

(It probably doesn't help much, but I can guarantee that none of the 
tables affected will contain any data at the time we want to run the 
upgrade scripts, and the application will not be running.)


All I've found so far is suggestions that you have to drop the old 
primary key constraint and create the new one, which also means 
dropping and re-creating everything that references the old primary 
key, which at first sight sounds like a complete nightmare.


Is that really the only way?

If so, are there any useful recipes somewhere for /finding/ all the 
dependent objects that need deleting and re-creating? - I'm aware that 
the system tables are documented in appendix V of the Firebird book 
but I'm afraid it's not immediately obvious to me how to use this 
information to identify the particular dependencies I'm after.

--
Tim Ward



--
Tim Ward

Re: [firebird-support] Best Practice in Use of Colon

[Svein Erling Tysvær]

>Where the use of : is optional, as best practice should : be used or avoided.
>Or in such cases use of : is irrelevant in terms of performance.

Hi!

I think your question can be interpreted a couple of ways. If the question is 
"should I use a parameter or a constant in a query", then there's no big 
difference. However, using a parameter has two potentially important 
advantages: If a query will be executed several times, then it is much quicker 
to prepare a query once and just change the parameter(s) for each execution 
than to execute several different, but similar queries. Moreover, a query with 
parameters are not prone to sql injection attacks the same way as a dynamically 
created query.

If the question is "should I use parameters or separate queries if a field may 
or may not be part of the WHERE clause", then there's no set answer, using it 
like

SELECT ...
FROM ...
WHERE FieldName = COALESCE(:Parameter, FieldName)

means that no index can be used for FieldName, which is only OK on small tables 
or if there are other parts of the WHERE clause that can utilise indexes to 
limit the result set.

Please rephrase Your question if it is different from both ways I've 
interpreted it or something is still unclear.

HTH,
Set

Re: [firebird-support] Very very very slow FB 2.5.2 64bit performance on Windows 2008 R2

[fabianoaspro]

Maybe your problem is that you buy a internet server from dell, not a
database server!
What is the diference? Internet servers from Dell does not have a disk
controller WITH physical cache.
Run CrystalDiskMark or other disk benckmark on this computer and in another
desktop computer. If it is slow you need to replace your disk controller.
Also read from Helen sayd. You are loosing the point when using a gente ram
disk.
Em 20/01/2014 23:01, "Hugo Eyng"  escreveu:

>
>
> Hi.
> I am facing a similar situation.
> 32GB RAM
> Dell PowerEdge R420
> Intel Xeon CPU E5-2609 two processors 2.40ghz 8 cores
> RAID
>
> Firebird 2.5.2 do not use the available resources of the server
> even I try to login to the server just one user.
> I am using SuperClassic
>
> I felt a little diference by modifying parameters in firebird.conf
> like DefaultDbCachePages = 2250 and some others
> This increases performance in about 30% in my case
> But I expected more.
>
> As Firebird do not use available RAM I created a RAM DRIVE with 10GB and
> pointed parameter 'TempDirectories' (firebird.conf) to this RAM DRIVE, but
> FB just uses it rarely in very big 'SELECT'. OK, when FB uses the RAM DRIVE
> it increases a SELECT speed in more than 80%.  I expected FB could use this
> for every SELECTS and so improve the application.
>
> Hugo Eyng
>
> Em 20/01/2014 20:51, Roland Turcan escreveu:
>
>
>
> Hello guys,
>
> I found some info about problems with performance when FB is running
> on Win2008R2 which is domain controller.
> But in my case is it not DC, but the performance is the worst I met.
> It is new server running 10GB of RAM, RAID, ... it is quite new
> server box.
>
> It cannot be, that basically dummy update on table with about 700 000
> records is executing over one hour. CPU is relaxing, HDD is relaxing
> too and I cannot force server to fun faster.
>
> What can I check?
>
> Thanks in advance.
>
> --
> Best regards, TRoland
> http://www.rotursoft.sk
> http://exekutor.rotursoft.sk
>
>
> --
>
>
> Atenciosamente,
>
> Hugo Eyng
>
>  
>