ODP: [firebird-support] Trigger created as inactive

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

First change group from yahoo to google group.

And without whole reproducible script no one can help i suppose

Regards,
Karol Bieniaszewski

[firebird-support] Next attachment ID

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

How can i read Next Attachement ID?
I know that i can read it by gstat -h but how to read it from connection?
Is there something simple like for OAT – read from MON$DATABASE or API 
isc_transaction_info?

Regards,
Karol Bieniaszewski

ODP: [firebird-support] IMPORTANT MESSGAGE: This group is moving

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

I have joined but i cannot change name to show instead my email name.
I have followed hint on the group to go to settings – but to change this 
settings i must login. I do not have google accout to login.
How to change it?

Regards,
Karol Bieniaszewski

ODP: ODP: ODP: ODP: [firebird-support] SQL slower after N executions?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

You can try also pseudo inner join (left join with where clause)

Instead of

T1 INNER JOIN T2 ON T1.ID1=T2.ID2

change it to

T1 LEFT JOIN T2 ON T1.ID1=T2.ID2
WHERE
T2. ID2 IS NOT NULL

regards,
Karol Bieniaszewski

ODP: [firebird-support] Timestamp in different regions

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

I do not suppose that this is Firebird message.
This looks like client program message. 

regards,
Karol Bieniaszewski

ODP: ODP: ODP: [firebird-support] SQL slower after N executions?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

I suppose you have two different queries – one with where clause and one 
without on one of the tables involved in the query.
Are you sure that the queries are the same?

But also it is quite usual that after new data changes the plan is about to 
change.
Is this true for your case that some table got more records after fill then 
previously second table?
Especially this one?:

You can try also modify your query to have static plan (some +0) or update 
index statistics after some count of operations.

Regards,
Karol Bieniaszewski


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

ODP: ODP: [firebird-support] SQL slower after N executions?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Simply run query with join two tables MON$ATATACHEMENTS and MON$TRANSACTIONS – 
you can also be interested with joining with MON$STATEMENTS
And then you can see which attachement consume this transaction (you can 
retrive IP port and proces ID (PID)

Pozdrawiam,
Karol Bieniaszewski

ODP: [firebird-support] SQL slower after N executions?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

There are many possibilities without access i can only hint you:
Look at MON$Tranasctions maybe you have active one which stop garbage collecion.
Look also at sort buffer setting if firebird.conf
Look at settings about buffers in database itself (gfix -h show you value).
Look also at automatic sweep settings (also gfix -h show you value).
Update Firebird to most recent version official is 3.0.5 but i use most recent 
snapshot without problems.

More can be tell after some details provided.

regards,
Karol Bieniaszewski

ODP: [firebird-support] Re: Explicit Login with Windows Domain username

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

Ask self – do you have Windows Domain supported 2 passwords? No. You can have 2 
different autentication methods.
On Firebird3 you have autentication plugins. You can implement one self or use 
apropiate design on your rest service.

Better to not allow all users to have same password is to redesign you REST 
service.
You should have login method on your rest service and user should recive token. 
And he/she should comunicate with this token in new requestes to other rest 
methods.
I can only hint to also use hmac with this token design to do not allow someone 
to recive token only and override user privileges.

Regards,
Karol Bieniaszewski

RE: [firebird-support] Forgot sysdba password

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

HiSimply replace security3.fdb by ine from the zip install and initialize 
sysdbaRegards,Karol Bieniaszewski
null

ODP: [firebird-support] Re: is it possible to drop all indexes, except those related to PKand FK?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

If you join with contraints you have better control.
An personally i prefere easier way, like simple select.
If you really need to delete all instead of PK and FK you should not exclude 
uniques as you ommit custom unique indexes.

SELECT
'DROP INDEX ' || TRIM(I.RDB$INDEX_NAME) || ';', RC.RDB$CONSTRAINT_TYPE
FROM
RDB$INDICES I
LEFT JOIN RDB$RELATION_CONSTRAINTS RC ON RC.RDB$CONSTRAINT_NAME = 
I.RDB$INDEX_NAME
WHERE
I.RDB$SYSTEM_FLAG <> 1
AND RC.RDB$CONSTRAINT_TYPE NOT IN ('PRIMARY KEY', 'FOREIGN KEY') 
–eventually add also ‘UNIQUE’ here if you really need

regards,
Karol Bieniaszewski

RE: [firebird-support] RE: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode?

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

"The mappings in our database survive one cycle of this but disappear on the 
second cycle (tested using gbak from Firebird 3.0.5). 
Changing the backup procedure only hides this issue (if you actually have to 
restore a backup then you're halfway there…)"Go to the Firebird bug tacker and 
create ticket there with description and 
sample.tracker.firebirdsql.orgRegards,Karol Bieniaszewski
null

Re: [firebird-support] Firebird client connection timeout

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

I do not remember from memory but look for "netsh" commandRegards,Karol 
Bieniaszewski
null

Re: [firebird-support] Page buffers & cache threshold

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

HiI do not know which rel notes have you read but this one is quite good 
abouthttps://firebirdsql.org/rlsnotesh/rnfb25-fbconf-fscache.htmlRegards,Karol 
Bieniaszewski
null

RE: [firebird-support] Page buffers & cache threshold

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

About 3"DefaultDbCachePages" in firebird.conf is used only at server start. 
Then restart is needed. But you have 2 more options and you should check it 
maybe you override default. Buffers you can set also in databases.conf and in 
db file itself by gfix buffers.And it is used in priority.1. buffers set in db 
by gfix if 0 then go to point 2.2. Setting in databases.conf if not present go 
to point 3.3. Setting from firebird.confRegards,Karol Bieniaszewski
 Oryginalna wiadomość Od: "Kjell Rilbe 
kjell.ri...@marknadsinformation.se [firebird-support]" 
 Data: 08.03.2020  10:10  (GMT+01:00) Do: 
firebird-support@yahoogroups.com Temat: [firebird-support] Page buffers & cache 
threshold 
 



  



  
  
  Hi,

Trying to figure out the best settings for my database and app usage. 
Firebird 3.0.4 SuperServer on virtualized Windows Server 2016 with 40 
Gbyte RAM and fast SSD disks (separate disks for OS and DB). Database 
almost 200 Gbyte. Page size 16k.

We can assume that no other heavy work is running on the server. The app 
itself uses a few Gbyte RAM.

I have set in databases.conf:
DefaultDbCachePages = 1310720
FileSystemCacheThreshold = 640K

And in firebird.conf:

FileSystemCacheSize = 50%
TempCacheLimit = 4G
MaxUnflushedWrites = 100
MaxUnflushedWriteTime = 60

All other settings (except some security ones) are set to default.

1. Would you recommend any changes to these settings, and why?

2. I see that the firebird.exe process has commit size about 2.2 Gbyte, 
which seems small considering the DefaultDbCachePages = 1310720 setting. 
Shouldn't this result in a page cache of 20 Gbyte?

3. I did change the DefaultDbCachePages setting without restarting the 
superserver process, but I did make sure all connections to the database 
in questions were closed. Does this setting take effect only when the 
server process is restarted?

4. It's not clear to me how the FileSystemCacheThreshold setting 
actually works. I have read the release notes for FB 2.5 as well as the 
comments inside firebird.conf and a couple of support list messages. But 
I still find it a bit muddy... I can see that the database file is 
cached, using RAMMap.exe. It reports "Standby" as "28733844 K" for that 
file.

It seems that the file system cache works and keeps large parts of the 
database file in the cache, but I do realize that the batch will for the 
most part need to read "new" data. The important thing ought to be to 
keep the indexes in cache. Right?

Reason/background: The use case is a batch operation that will do mostly 
(only?) reads and will read large parts of the database. The batch does 
its job in "chunks", and I can see that after a while the time to 
process each chunk goes up from about half a minute to about a full 
minute, or even worse. Restarting the app makes it go back to about half 
a minute per chunk, but just stopping the batch operation (without 
restarting the app) doesn't seem to help. I will investigate this more, 
of course. It's possible that the app has some garbage piling up that 
might slow things down.

Mvh,
Kjell

Kjell Rilbe
Telefon: 0733-44 24 64

Marknadsinformation i Sverige AB
Sundbybergs Torg 1 H 7 tr
172 67 Sundbyberg, Sweden
www.marknadsinformation.se 
08-514 905 90

Företagskontakt.se  
Personkontakt.se  DI Gasell 2018DI Gasell 
2019

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




 

Re: AW: [firebird-support] order by takes too long

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

Pseudo inner join is left join with where clause about not null from left 
joined tableSelectFromTable1 t1 left join Table2 t2 on 
t1.id=t2.id_t1Wheret2.id is not nullRegards,Karol Bieniaszewski
null

Re: AW: [firebird-support] order by takes too long

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

HiYou can always change inner join to pseudo inner join without affecting 
resultset. But better you should find the reason of this slowdown as it looks 
like index without recent stats or wrong indexing in join. But it also can be 
something else.Regards,Karol Bieniaszewski
null

RE: AW: [firebird-support] order by takes too long

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

This advice should be oppositeYou should not have order by in the view.Only 
exception is when you always do only simple select * from myviewRegards,Karol 
Bieniaszewski
null

Re: AW: [firebird-support] Scalability of connection numbers of client-server solution with Firebird 3.0?

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

Hi MarkWhat is the reason for such limit?Microsoft say only limit by available 
RAMhttps://docs.microsoft.com/en-us/windows/win32/winsock/maximum-number-of-sockets-supported-2Regards,Karol
 Bieniaszewski
null

RE: [firebird-support] order by takes too long

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

HiWithout analysis i can only advice to create descending index.Create 
descending index ixd_mytable__timestamp on mytable(timestamp)Regards,Karol 
Bieniaszewski
null

Re: [firebird-support] How to "remap" the count datatype from BIGINT to INTEGER in firebird 3

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

It is not whole true.It is developer choice to use persistent fields or not.I 
do not see any benefit to use persistent fields and because of this i have not 
problem with count in my Delphi applications. I have reported the possible 
problem few years ago when fb3 beta was in action. But this was marginal 
problem and was forgotten then.Regards,Karol Bieniaszewski
null

RE: [firebird-support] (When to) recalculate statistics ?

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

HiTo take decision you first must know what statistics are for server e.g. 
Firebird. Statistics contain info how selective is some index. If it is PK you 
know that every entry have only one record. For UK you have same but you can 
have multiple nulls. For other indexes you can have for one entry multiple 
references to records. Simple sample index of people name. You can have 
multiple people with same name.Index selectivity is used when Firebird 
optimizer choose plan for the query. Without information about selectivity or 
with not recent information it can decide wrongly and generate "wrong" query 
plan. And query can then run slow. If your queries have good plan and run fast 
then you can e.g stop recalculating statistics at all.As you can see it 
depend..You must decide how offten. Regards,Karol Bieniaszewski
null

RE: [firebird-support] Scalability of connection numbers of client-server solution with Firebird 3.0?

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

HiThe limits what you have presented are from past years when the hardware was 
the limit here. Now when ssd exists and desktop computers have avg 16GB RAM 
this is no more a problem.But application must be well written. I am not sure 
if it is as you still use IBX. But maybe you use extensively TClientDataSet to 
cache data as IBX do not make it for you.The best is to migrate to Firedac. But 
without migration you should observe your transactions management e.g look into 
mon$transactions.Regards,Karol Bieniaszewski
null

ODP: [firebird-support] Statement freezes firebird

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

  „select first(8) ID from TBL_TEST
  where ID not in (select first(2) ID from TBL_TEST order by ID desc)
  order by ID desc”

few things:

Just hint but „first” is not function First(8) is same as First 8  

You should avoid NOT IN queries and use EXISTS/NOT EXISTS instead.
But if you really need it then such subquery should be well indexed.

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

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

It will be instant i suppose

Regards,
Karol Bieniaszewski

RE: [firebird-support] Help with error message (Firebird 3)

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

HiThe most probability is that you store it twice in same transacion or other 
connection do the same. Less probabiliti is that index is corrupted some way. 
Regards,Karol Bieniaszewski
null

RE: [firebird-support] Union with or without distinct in the separate queries?

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

This depend only on reduction scale.If your particullar queries have many 
duplicates e.g 90% or more, than separate distinct can be better from resource 
POV. But if it is opposite, then eliminate particular distinct operations can 
have huge performance boost.Regards,Karol Bieniaszewski
 Oryginalna wiadomość Od: "Kjell Rilbe 
kjell.ri...@marknadsinformation.se [firebird-support]" 
 Data: 02.01.2020  21:49  (GMT+01:00) Do: 
firebird-support@yahoogroups.com Temat: [firebird-support] Union with or 
without distinct in the separate queries? 
 



  



  
  
  Hi,



Not important, just got curious: Consider a query like this:



select c1 from t1

union select c1 from t2;



This will eliminate all duplicates, since I didn't specify "union all". 

But would this be more or less efficient than (or the same as) this:



select distinct c1 from t1

union select distinct c1 from t2;



I'm thinking that the latter will reduce the number of records to 

consider "as early as possible" any may reduce resource usage that way. 

On the other hand it would involve three "distinct" operations rather 

than a single one... So maybe the answer is that it depends on the data, 

which indices are available and applicable, and may differ from case to 

case? Or perhaps the query engine recognizes that the two queries are 

logically equivalent and executes them identically?



Regards,

Kjell




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




 

RE: [firebird-support] Plan problem en CTE

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

HiWhy "list(x.tbl2_pk_id)"?Result of list is a blob string with one single 
value like '1,2,3' not a 3 records '1', '2', '3'.And using "in" is not a good 
choice here. Change query to e.g. 'exists' or better some 'join'Regards,Karol 
Bieniaszewski
null

Re: [firebird-support] backup security3.fdb

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

I do not understand problem asked aboutSecurity3.fdb.It is normal database same 
as others. If you know how to backup any database no difference for 
security3.fdbRegards,Karol Bieniaszewski
null

ODP: [firebird-support] Round the Time

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Now possibility:
Exctract + cast
Substring + cast

But you can vote on something more usefull:
http://tracker.firebirdsql.org/browse/CORE-5623

regards,
Karol Bieniaszewski

ODP: [firebird-support] Re: A recommendation (database migration)

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

>> That’s part of the issue - I’m a Delphi guy and not sure I could get it 
>> cross platform.

Delphi is crossplatform. 
It suport Windows, macOS, iOS, Android and now Linux from one codebase.

Regards,
Karol Bieniaszewski

ODP: [firebird-support] Insert into Select

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

Whay not two separate "update or insert” for parent and child?
You can use „merge” for massive „"update or insert” on single table.

Regards,
Karol Bieniaszewski

ODP: [firebird-support] What key word specifies a search for an entire word?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

>>Not sure what pattern is.

Pattern replace unknown leter(s) sumbols are „%” multiple letters, „_” single 
letter
e.g.
‘Ne%flix’ will find Neflix, Netflix, Netflix ….

>>I prefer the word LIKE.  Is easier to avoid errors vs. using '='.  Are there 
>>advantages to using '='?

If you use like with param Firebird cannot use index on field firstName or e.g. 
expression index Upper(firstName)

As your param have unknown value. It can be ‘Netflix’ or ‘%Netflix’, or whatever
But if you use „=” it simply can use index if such exists..

Regards,
Karol Bieniaszewski. 

ODP: [firebird-support] What key word specifies a search for an entire word?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Do you use pattern or simple string? 
If it is simple string then better is using „=” instead of like.

"SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) = lower( @p0 )"

Regards,
Karol Bieniaszewski

ODP: [firebird-support] Grant execute on function.

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Is „users1” a user name or role name?
If role, then check if user have this role granted and it connect using the 
role.

Regards,
Karol Bieniaszewski

Re: [firebird-support] Grant execute on function.

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

Did you reconnect after grant?Regards,Karol Bieniaszewski
null

ODP: [firebird-support] ISC ERROR CODE:335544721

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

>>I havent' Interbase installed on this computer, only Firebird...

Are you sure? Have you checked gds32.dll in Windows/System32 and 
Windows/SysWoW64 if it is Firebird?

Regards,
Karol Bieniaszewski

Re: [firebird-support] How to index this table

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

Show the query plan.And what abput the second advice about temporary table 
istead of multiple or?Regards,Karol Bieniaszewski
null

Re: [firebird-support] Re: How to index this table

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

Regards,Karol Bieniaszewski
>>However, I am not sure why Karol suggested the construct of composite 
>>indicies in his replyI have suggested opposite "Create separate indexes", 
>>maybe my English construction was not good.Regards,Karol Bieniaszewski

ODP: [firebird-support] How to index this table

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

Your problem is that you have (X or X) and (Y or Y)
Firebird cannot use composite index here.
Maybe it can somehow make something like (X1 and Y1) or (X1 and Y2) or … or (X1 
and YN) or (X2 and Y1) or (X2 and Y2) …. (XN and YN)
But it will be never optimal.

Create separate indexes for magasin and caisse – then Firebird can use BITMAP 
OR + BITMAP AND

Another optimisation will be instead of multiple OR put all values into 
temporary table and then join with it.
Any other optimisations depend on value propagation in particular fields 
involved in the where clause.

Regards,
Karol Bieniaszewski

Re: AW: [firebird-support] CTE difficult question

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

Hi,
 
from your description i really do not know what is working for you and what is 
not working.
And your expectation.
 
but to understand recursive CTE look at simple sample. Recursive CTE work 
throught tree.
 
### metadata ###
CREATE TABLE TEST_TREE
(
  ID INTEGER NOT NULL,
  ID_HEADER INTEGER,
  CONSTRAINT PK_TEST_TREE__ID PRIMARY KEY (ID)
);
CREATE INDEX IXA_TEST_TREE__ID_HEADER ON TEST_TREE (ID_HEADER);
### test data ###
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('1', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('2', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('3', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('4', '1');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('5', '4');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('6', '2');
### test query ###
 WITH RECURSIVE
 R_TREE AS
 (
 SELECT TT.ID AS A, CAST(TT.ID AS VARCHAR(100)) AS ASUM, 0 AS LEVEL
 FROM TEST_TREE TT
 WHERE TT.ID_HEADER IS NULL
 
 UNION ALL
     
 SELECT TT.ID AS A, RT.ASUM || '_' || TT.ID, LEVEL + 1
 FROM TEST_TREE TT JOIN R_TREE RT ON RT.A = TT.ID_HEADER
 )
 SELECT
 *
 
 FROM
 R_TREE RT2 INNER JOIN TEST_TREE TT2 ON TT2.ID=RT2.A
 
###
 
run it and then addapt to your needs, as your situation looks same to me
 
regards,
Karol Bieniaszewski
 
 

Re: [firebird-support] Where clause from date field with wrong format

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

1. Change parameter to be date, not string containing date.2. Reformat it 
before use in query3. Reformat it in query using cast as date using 
substringRegards,Karol Bieniaszewski
null

Re: [firebird-support] Re: Off-Topic: Firebird future

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

You are joking, command line tool in this days is only for scripting. Using it 
in everyday work is a bad joke. Gui tool have scrolling, local sorting, 
filtering, exporting...Regards,Karol Bieniaszewski
null

Re: [firebird-support] Off-Topic: Firebird future

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

Hi
 
lack of GUI tool is a realy big problem, especially for newcommers.
As you have pointed flamerobin looks like abandoned project. It have some 
problems but was quite good tool.
 
But to compare Firebird to other servers like Postgress few points must be 
taken.
1. Firebird have simple instalation and its size is really small. It is simple 
to administration by novice users.
2. Firebird database file is single flat file. It is simple to backup. In 
Postgress it is some folder with number in the name and many files. Novice user 
do not know how to backup it.
3. Firebird have genius embeded version. I use it on near every webpage as i do 
not depend on preinstalled version on the provider side. I am also not limited 
in size of my database :)
   It is working on medium trust servers :)
4. I have made comparission in speed for my biggest project, with not so big 
database file. It is only 50GB. I have catch near the all sql from the system. 
And the resutls are:
   66% of sql are run faster on Postgress 34% are run faster on Firebird. One 
critical for us sql was run faster on Firebird side and it is winner for us 
here.
   I know this is specific comparisoin but i have analysed where the problems 
are.
   I describe it in next points (5..9)
5. Firebird do not have index only scans and include clause in index. This is 
the biggest lack. Because of this i have migreted ~800GB database, 
noncommercial project based on GAIA DR2 data from Firebird. Look at 
http://tracker.firebirdsql.org/browse/CORE-5909.
6. Firebird do not have parallel operations.
7. Firebird indexes are not bidirectional. You must specify asc desc. (this is 
not so big problem as you can crate both separately).
8. Firebird compound indexes cannot use parts from composite index if middle 
part is missed. E.g. 1,2,4 but 3 is missed. Postgres catch this.
9. Lack of hash/merge algorithms for outer joins. Also believe that it will be 
in FB4 :)
10. Firebird triggers and procedures are really good. I know that newcomers 
have problems with it i do not know why. Maybe something obvious is missed in 
the documentation.
11. Firebird miss some obvious feature which made me crasy. Import from CSV 
file. I have proposed simple fix for this 
http://tracker.firebirdsql.org/browse/CORE-5818 but it was rejected by the team 
as the more common/extended version is needed.
    I understand this, but more extended version will be implemented maybe in 
next 10 years but my simple proposition i suppose can be implemented in few 
days. I know it must be then maitained when more extended version will be 
avaiable. This is a know every day developer problem i understand.
12. Lack of lateral join, i believe that it will be in FB4 :)
13. Firebird have really good system monitoring. It have good trace and MON$ 
tables. I like it very much.
14. Firebird have non detailed info in query plan. Compared to other servers.
15. Firebird lack of running query plan as it depend strongly on plan gethered 
at prepare time. It cannot addapt to value provided in parameter by histogram 
use.
16. Firebird have really good CTE also recursive where you can provide almost 
every feature avaiable in normal sql.
17. Firebird have quite good support. But i suppose, team do not have dedicated 
person for this point. Compared to Interbase it is still much much better :) IB 
paid support is something what should be banned. We have migrated all projects 
from Interbase to Firebird because of this.
18. Lack of grouping sets.
19. Finally Firebird 3 is really stable for us. No problems occurred (fingers 
crossed). This is something what also cannot be missed.
 
Finally i can only say really good words about Firebird team. With such limited 
resources they do really big progress. Compare how many persons are in Firebird 
team and how many in Postgress.
The problem is that team is overloaded and new peresons also when provide pull 
requests cannot be carried by the hand from the team as team have not time and 
circle is closed.
I see that some users provide pull requests and as they looks finished stay not 
merged. 
This should be improved as such person can provide more pull requests and maybe 
in the future will be in the team as a member. Maybe not, but every brick 
counts.
E.g. https://github.com/FirebirdSQL/firebird/pull/204
 
I greet everyone warmly and i hope that the Firebird team will grow in the 
future
Karol Bieniaszewski
 

Re: [firebird-support] Basic Re-Start

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

Hisimply download Firebird 3 installer from the official site e.g 64 bit. If 
your app is also 64 bit, no more needed. If it is 32 bit then download also 
32bit version and install minimal client. Your serwer will be listening on the 
localhost 127.0.0.1 on port 3050.Regards,Karol Bieniaszewski
null

Re: [firebird-support] Upgrade to FB 3 or Wait for FB 4

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

HiThis depend of how long do you need to wait. FB4 is not released. After 
released it can contain some buggs then probably you must wait some more 
time.This is the same as all other software. Should i upgrade windows xp to 
win7 or directly go to win10? Today win10 but 2 years ago the answer will be 
different.You must decide and especially test "all" before use.Regards,Karol 
Bieniaszewski
null

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] FB3 Embedded Firedac Error

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

Hi1. Is it Windows application?2. Is it 32 bit application or 64 bit?3. Which 
version of Delphi?4. How your Firedac connection params looks 
like?Regards,Karol Bieniaszewski
null

ODP: AW: [firebird-support] Converting with parameters stored in variables?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

>>Trunc instead of pow?

I only mean that you do not need to operate on strings to separate number.

A = 123.45 – numeric not a double
A1 = TRUNC(A)
A2 = (A-A1)*POWER(10,3)

Then 
A1 = 123
A2 = 450

SELECT LPAD(TRUNC(X.A), 10, '0') || '.' || LPAD((X.A-TRUNC(X.A))*X.B, 3, '0'), 
FROM (SELECT 100.12 AS A, POWER(10,3) AS B FROM RDB$DATABASE) X

Regards,
Karol Bieniaszewski

Re: AW: [firebird-support] Converting with parameters stored in variables?

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

Hi,You can do this simpler by using trunc.But i ask why do you need this format 
in resultset? Formatting numbers is a client side task, same as for 
DATE.Regards,Karol Bieniaszewski
null

ODP: [firebird-support] Converting with parameters stored in variables?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

This is called padding.
Simple example (but not what you want you must padd also decimal point)
SELECT LPAD(CAST(100 AS NUMERIC(10,3)), 14, '0') FROM RDB$DATABASE

But you must tell us what is your real problem, as you need padding for what?

Pozdrawiam,
Karol Bieniaszewski

Re: AW: [firebird-support] memory bug?

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

HiYou have buffers set inside database. Your current setting is 256MB ( 
65536×4096) so if this is your only database then Firebird should not eat whole 
memory. But this depend on your sort buffer also.You should look at your system 
and monitor hardware especially HDD wait.Also look at transaction management, 
as you have ~4k transactions gap between oldest active and next 
one.Regards,Karol Bieniaszewski
null

ODP: [firebird-support] memory bug?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi
>>we are using firebird 3.0.1 Superserver 32 Bit on a Windows Server 2008 32 
>>Bit. 
First, why 3.0.1 it contain many bugs fixed already. Use official 3.0.4 or 
snapshot version.
>>Currently we have all 60 days the problem, that our Applications works not 
>>well, the firebird-process uses almost 2GB of RAM and this is seemingly the 
>>limit of an 32 Bit process. In this case, I cannot connect >>with my tool to 
>>see the monitoring tables, no new connection can be established. 
What is you buffers settings (in firebird.conf, or database.conf or in db 
itself). And maybe its time to upgrade hardware as current one looks not 
enought for your needs? 

regards,
Karol Bieniaszewski

Re: Re: Re: Re: [firebird-support] Is there any logical difference?

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

It looks like the issue does not exists.
I have thinked that i compare
 
NOT 
     (
     EXISTS()
     OR
     EXISTS()
     )
 
but this was really NOT EXISTS AND SOMETHING or EXISTS
 
and logic table for "NOT EXISTS AND SOMETHING" is:
 
false and NULL = false 
but 
true and NULL = NULL 
 
which is now understandable
 
thank you for your time
Karol Bieniaszewski

Re: Re: Re: [firebird-support] Is there any logical difference?

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

>>I certainly agree with you that if EXISTS returns false, then NOT EXISTS 
>>should return true.
>>It could make some sense if both EXISTS and NOT EXISTS returned  
>>(though it should be documented somewhere), but not that one returns false 
>>and the other .
>>When you write this in the tracker, I think it would be good if you included 
>>a very simple reproducible test case.
 
>>Set
 
on the tracker as 
http://tracker.firebirdsql.org/browse/CORE-6120
 
reproducible yes, simple - not yet ;-)
 
regards,
Karol Bieniaszewski
 

Re: [firebird-support] CTE Spaces between every day

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

Hi,
 
simply add loop inside
do
  begin
     suspend;
    --put here some loop
    while something do
      begin
          --modify output variables here
          suspend;
      end
  end;
 
regards,
Karol Bieniaszewski

Re: Re: [firebird-support] Is there any logical difference?

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

 
>>Thank you Set for the help. I must do one more test on monday. I must modify 
>>Case to comparision
>>CASE WHEN EXISTS() IS NULL THEN -1 ...
>>And we will see if exists can return something else then TRUE or FALSE
 
i have tested it now and i suppose result is wrong
 

 
SELECT
EXISTS() return False
FROM
WHERE

 

 
but
SELECT
NOT EXISTS() return NULL!!! instead of True
FROM
WHERE
 

 
i suppose it is an error and should be reported to the tracker?
 
 
Regards,
Karol Bieniaszewski

Re: [firebird-support] Is there any logical difference?

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

>> but if you're on a
  different versionI am on FB3.0.5


>> So, do the 211 rows returned only in
  case 2 have null in any of the columns compared in the exists?Both exists 
in case 1 and 2 are exactly the same without any modifications. And as i am 
looking for NOT exists than in query inside exists is empty resultset and then 
exists should return false. Thank you Set for the help. I must do one more test 
on monday. I must modify Case to comparisionCASE WHEN EXISTS() IS NULL THEN -1 
...And we will see if exists can return something else then TRUE or 
FALSE8Regards,Karol Bieniaszewski
null

[firebird-support] Is there any logical difference?

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

Hi
 
I have the query where this make a difference and i do not know why...
Is there any logical difference between this two statements?
 
1. -
NOT 
     (
     EXISTS()
     OR
     EXISTS()
     )
2. -
 
CASE WHEN EXISTS() THEN 1 ELSE 0 
+
CASE WHEN EXISTS() THEN 1 ELSE 0
= 0
 

the query plans are exactly the same. 
First query return nothing, second one return 211 records.
 
 
regards
Karol Beieniaszewski

[firebird-support] Is there any logical difference?

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

Hi
 
I have the query where this make a difference and i do not know why...
Is there any logical difference between this two statements? Or some sql 
feature difference?
 
1. -
NOT 
     (
     EXISTS()
     OR
     EXISTS()
     )
2. -
 
CASE WHEN EXISTS() THEN 1 ELSE 0 END
+
CASE WHEN EXISTS() THEN 1 ELSE 0 END
= 0
 

the query plans are exactly the same. 
First query return nothing, second one return 211 records.
 
 
regards
Karol Beieniaszewski
 
PS. I resend my email as previous from Thu, 08 Aug 2019 15:00:25 +0200 is still 
not visible on the group

ODP: [firebird-support] Missing system triggers for foreign key constraints

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

Really 2.5.2? It is from November 06, 2012.
You miss few years or bugfixing and improvement. Current version of 2.5 serises 
is 2.5.9.

Regards,
Karol Bieniaszewski

Re: [firebird-support] avoid query subselect

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

Hi,
 
i suppose this one is much faster
 
SELECT P.ID
, CASE WHEN EXISTS(SELECT * FROM  CHILD C WHERE C.ID_PARENT=P.ID AND 
C.MYFIELD=3)
 
) 
THEN 1 ELSE 0 END AS ISDATA
  FROM PARENT P

INNER JOIN TABLE3 T3 ON P.ID=T3.
INNER JOIN TABLE3 T4 ON P.ID=T4.
...
 
 
Regards,
Karol Biieniaszewski

[firebird-support] Understand query plan Filter after Filter

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

Hi,
 
what does it mean if below i have twice -> Filter -> Filter?
 
 
Select Expression
    -> Filter
        -> Nested Loop Join (outer)
            -> Filter
                -> Table "RDB$DATABASE" as "X RDB$DATABASE" Full Scan
            -> Filter
                -> Filter
                    -> Table "RDB$RELATION_FIELDS" as "RF" Access By ID
                        -> Bitmap
                            -> Index "RDB$INDEX_4" Range Scan (full match)
Select Expression
    -> Filter
        -> Nested Loop Join (outer)
            -> Filter
                -> Table "RDB$RELATIONS" as "R" Full Scan
            -> Filter
                -> Table "RDB$DATABASE" as "Y RDB$DATABASE" Full Scan
 
Karol Bieniaszewski

ODP: [firebird-support] Unique Constraints and NULLs

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

If you do not need referential constraint based on this unique only you need to 
have uniques.
Then you can simply create expression index, e.g.:

CREATE UNIQUE INDEX IXAE_blah_C1C2 ON blach COMPYTED BY(COL1 || ‘_‘ || 
COALESCE(COL2));

Regards,
Karol Bieniaszewski

Re: [firebird-support] Read OAT value

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

Thank you very muchRegards,Karol Bieniaszewski
null

[firebird-support] Read OAT value

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

Hii know that reading form mon$ tables are quite expensive. Is then a different 
way to read oldest active transaction from the connected db client? Some 
context variable or something?Regards,Karol BieniaszewskiPozdrawiam,Karol 
Bieniaszewski

Re: [firebird-support] strange bahaviour

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

What are the exact types of both fields?Regards,Karol Bieniaszewski
null

Re: [firebird-support] Getting error: dial tcp: missing address

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

Hi,You must provide more details. What tools, connection string, exact error 
message without modification and error code ...Regards,Karol Bieniaszewski
null

ODP: [firebird-support] Re: Objects cached in FB memory

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

I do not suppose it is possible. But why do you need that info?

regards,
Karol Bieniaszewski

Re: [firebird-support] Help in optimizing big data query

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

HiIf you have wher clause i that ordered query,Think how you can do this on the 
paper. You have rows ordered you read first and interesting is not null, you 
must skip it and go to next. And if you must skip e.g 30 000 000 to read 1 000 
000 then you see what is going on. Always your indexes should satisfy your 
where clause. Above sample show something called selectivity. You must maximize 
selectivity of index used. E.g. if you have only value true and false in the 
field then if your half of the records are true and other half false then index 
can only slowdown query execution. If you have 1000 records with true and 
1000 with false index can speed up query looking for true and slowdown 
query looking for false.Sumarize, index slowdown common value.Regards,Karol 
Bieniaszewski
null

Re: [firebird-support] Help in optimizing big data query

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

If the plan include ORDER not SORT then it read data throught index.But if you 
are asking server about 1 000 000 records, what do you expect more from the 
server to do?And you use 2048 page buffers then all data is retrived from your 
HDD. To speed up you must:1. Change design to retrive limited number of fecords 
e.g. 1000.2. Increase page buffers to speedup second and next queries.3. Invest 
in faster HDD like m2 3500 MB/sRegards,Karol Bieniaszewski
null

Re: [firebird-support] firebird on android

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

HiLook at tracker.firebirdsql.org/browse/CORE-5888there is fixed Firebird build 
provided by Alexander Peshkov and my screenshot from Delphi deployment 
settings.You can see all files required to run FB embeded or direct connect to 
remote Firebird. To connect from Delphi more steaps required. I do not know 
from where you will try to connectRegards,Karol Bieniaszewski
null

Re: [firebird-support] Firebird 64 Bit Superserver

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

Hi>>We are using some Applications, which are not designed for 64 Bit 
WindowsAll 32 bit applications should work without problem on 64bit 
windows>>UDFs, there are not designed for 64 Bit FirebirdThis is bigger 
problem. I do not know what your udf functions are, but there are now plenty of 
built in functions in FB3 version which can replace many old udfRegards,Karol 
Bieniaszewski
null

Re: ODP: [firebird-support] Re: Issue with large table in FB 1.5 and 2.5

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

I resend my email - it do not reaach the yahoo group :-(
 
Hi,
 
you can try modify your logic by adding IMPORT_ID to that table
and then you can import data without need to delete in the same time.
Then you can delete data in some portions during a day.
and successively run
 
 
SELECT COUNT(*) FROM THAT_TABLE
 
 
to clear old record versions.
 
This changed logic require changes in your current selects where you must 
include most recent IMPORT_ID.
 
regards,
Karol Bieniaszewski
 

Re: [firebird-support] Default value in DDL not work

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

HiDefault value is used when you ommit field in the insert statement. What is 
your usage case?Regards,Karol Bieniaszewski
 Oryginalna wiadomość Od: "m_brahi...@yahoo.fr 
[firebird-support]"  Data: 05.06.2019  10:22  
(GMT+01:00) Do: firebird-support@yahoogroups.com Temat: [firebird-support] 
Default value in DDL not work 
 



  



  
  
  Hi all,I build tables under IBEXPERT and define some fields with default 
valueFIELD_NAME INTEGER DEFAULT 0I except 0 as value but has always NULLAny 
idea ?Thanks


 

Re: ODP: [firebird-support] Page cache size in Firebird 3.0.4

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

I resend my email - it do not reaach the yahoo group :-(
 
Hi
 
Yes you have missed config in your db itself
 
Database properties

ODS Version 12
Page size 16384
Pages 437728
Size on disk 6.68GB
>>>Page buffers 10240
Read only false
 
You have page buffers set inside your DB.
 
Default priority is
Page buffers set inside DB if set to 0 go to point 2
Page buffers set in databases.conf if not present go to point 3
DefaultDbCachePages set in firebird.conf
 
 
Regards,
Karol Bieniaszewski
 

Re: [firebird-support] Re: How to specify manual plan for this kind of query?

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

>> Really? I'd suppose the changed part should be:
>> HASH (CTE T T NATURAL, CTE K NATURAL))
>> Dmitry
 
No,
 
My oryginal plan generated by Firebird looks like this
PLAN (SORT (JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__NAME))), HASH (CTE 
T T NATURAL, CTE K INDEX (IXA_FNAMES_K__ID)))
 
and when i have added some condition it is changed to
 
PLAN (SORT (JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__NAME))), JOIN (CTE 
T T NATURAL, CTE K INDEX (IXA_FNAMES_K__ID)))
 
and i need to replace it to oryginal plan
 
Regards,
Karol Bieniaszewski

[firebird-support] How to specify manual plan for this kind of query?

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

Hi
 
i need to modify plan generated by Firebird and specify manual plan.
I use Firebird 3.
 
example on employee.fdb (sample from taken from 
README.common_table_expressions.txt)

WITH RECURSIVE
  DEPT_YEAR_BUDGET AS
  (
    SELECT B.FISCAL_YEAR, B.DEPT_NO, SUM(B.PROJECTED_BUDGET) AS BUDGET
      FROM PROJ_DEPT_BUDGET B
    GROUP BY B.FISCAL_YEAR, B.DEPT_NO
  ),
 
  DEPT_TREE AS 
  (
    SELECT D1.DEPT_NO, D1.HEAD_DEPT, D1.DEPARTMENT, CAST('' AS VARCHAR(255)) AS 
INDENT
      FROM DEPARTMENT D1
     WHERE HEAD_DEPT IS NULL
 
    UNION ALL
 
    SELECT D2.DEPT_NO, D2.HEAD_DEPT, D2.DEPARTMENT, H.INDENT || '  '
      FROM DEPARTMENT D2 JOIN DEPT_TREE H
        ON D2.HEAD_DEPT = H.DEPT_NO
  )
 
SELECT D3.DEPT_NO, 
 D3.INDENT || D3.DEPARTMENT AS DEPARTMENT,
 B_1993.BUDGET AS B_1993
 
  FROM DEPT_TREE D3
       LEFT JOIN DEPT_YEAR_BUDGET B_1993
    ON D3.DEPT_NO = B_1993.DEPT_NO AND B_1993.FISCAL_YEAR = 1993
 

 
it generate plan
PLAN JOIN (DEPT_TREE D1 INDEX (RDB$FOREIGN6), DEPT_TREE D2 INDEX 
(RDB$FOREIGN6), SORT (B_1993 B INDEX (RDB$FOREIGN18, RDB$PRIMARY17)))
 
but if i put this plan witgout any modification to the query 
it say: 
 
--
Dynamic SQL Error
SQL error code = -104
Invalid command
there is no alias or table named D1 at this scope level.
--
 
 
How to put manual plan for this query?
 
In my real situation query plan looks like
PLAN (SORT (JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__NAME))), JOIN (CTE 
T T NATURAL, CTE K INDEX (IXA_NAMES_K__ID)))
and i need to change it to 
PLAN (SORT (JOIN (CTE T T NATURAL, CTE K INDEX (IXA_NAMES_K__NAME))), HASH (CTE 
T T NATURAL, CTE K INDEX (IXA_NAMES_K__ID)))
 
regards,
Karol Bieniaszewski

Re: ODP: [firebird-support] Query that was very fast under the oldest 2.5 versions are very slow under 2.5.8 or 2.5.9

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

>>But this is not the problem, because 2.5.0 does this query very fast, with no 
>>special cache settings.Yes, i say the same in the answer ;-)"And about 
>>changed plan, it is not releated to this setting but this only increase 
>>visibility (of the issue) in your small cache settings"Regards,Karol 
>>Bieniaszewski
null

Re: ODP: [firebird-support] Query that was very fast under the oldest 2.5 versions are very slow under 2.5.8 or 2.5.9

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

>>I enabled in firebird.conf the parameter DefaultDbCachePages = 2048, >>but 
>>after that the performace was 1 minute slower.Yes, i am talking about this 
>>param you can also set it indyvidually for database. And your value is small. 
>>If your db pagesize is 16KB then cache in your case is only 32MB which is 
>>value of cache from windows 3.1 ;-) days? If yor page sise is 4KB than it is 
>>8MB...And about changed plan, it is not releated to this setting but this 
>>only increase visibility in your small cache settings.Regards,Karol 
>>Bieniaszewski
null

Re: ODP: [firebird-support] Query that was very fast under the oldest 2.5 versions are very slow under 2.5.8 or 2.5.9

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

Ok you use FB2.5. What is your server type superserver, classic or 
superclassic. I never use installer only zip kits then i do not know which 
server type is default.If you use superserver then default values are 
super-small and you do not benefit from cache. Increase page buffers. Without 
this, all your queries are so slow as your harddrive. As you know, RAM is 
magnitude faster.Regards,Karol Bieniaszewski
null

ODP: [firebird-support] Query that was very fast under the oldest 2.5 versions are very slow under 2.5.8 or 2.5.9

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

Do you use FB3 Super Server or Classic?
What value of PagePuffers and PageSize?
I see big reads but also big indexes reads but fetches are lower.

There is also one plan difference 
MOVI INDEX (MOVI_PROC) vs MOVI INDEX (MOVI_PROC, MOVI_FECH)

But i am intersted how this looks like at second run with big cache


Regards,
Karol Bieniaszewski

Re: [firebird-support] Query that was very fast under the oldest 2.5 versions are very slow under 2.5.8 or 2.5.9

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

HiShow the query plan for both server versionsRegards,Karol Bieniaszewski
null

Re: [firebird-support] Re: TempCacheLimit - Integer?

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

Thank you DmitryP.S. correction of description in firebird.conf is needed 
thenRegards,Karol Bieniaszewski
null

[firebird-support] TempCacheLimit - Integer?

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

Hi
 
is TempCacheLimit is really Integer or Int64?
Below info from config
 
#
# The maximum amount of the temporary space that can be cached
# in memory.
#
# For Classic servers, this setting is defaulted to 8 MB.
# Although it can be increased, the value applies to each client
# connection/server instance and thus consumes a lot of memory.
#
# Per-database configurable.
#
# Type: integer
#
#TempCacheLimit = 64M
 
TempCacheLimit = 800M
 
regards,
Karol Bieniaszewski

Re: [firebird-support] re: Can't get Firbird 3.x embedded to work in Delphi 10.3 update 1?

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

If i see good on my phone you have engine12.dll in the same dir as exe but it 
should be in plugins. Are you sure that did you copied whole structure from zip 
kit?Regards,Karol Bieniaszewski
null

Re: [firebird-support] Firebird 2.5 transaction counters

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

HiYou are in the half way (1031734200) as tr counter in FB2.5 is 32int.P.S. you 
have some long running transaction which block garbage collector and degrade 
performance. Look into mon$transactions for oldest one.Regards,Karol 
Bieniaszewski
null

Re: [firebird-support] re: Can't get Firbird 3.x embedded to work in Delphi 10.3 update 1?

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

Hiare you sure that you have all files in the same dir as your executable? Also 
are you sure that database is not opened in e.g. IDE in design time or some 
tool? Try rename its file and run executable from directory not from IDE.And 
put whole error message here.Regards,Karol Bieniaszewski
null

Re: [firebird-support] Can't get Firbird 3.x embedded to work in Delphi 10.3 update 1?

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

Hi
 
i do not know what was improved in 10.3.1 but connecting to FB3 embeded is 
simple
FDConnection1 params:
Database=C:\YourDB\yourdb.FDB
CharacterSet=WIN1250
User_Name=sysdba
DriverID=FB
 
also LoginPrompt=False
 
 
FDPhysFBDriverLink1 should point to fbclient.dll not engine12 and 
(Embedded=True/False does not change anything for me).
i have in config
Providers = Engine12
AuthServer = Srp
AuthClient = Srp, Legacy_Auth
UserManager = Srp
 
Remember also about bitness 32/64 and that Firebird files should be in the same 
dir as your exe with folder structure e.g /providers and /udf if you use
 
regards,
Karol Bieniaszewski

[firebird-support] Firebird 3 - gstat output about blobs

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

Hi
 
can someone describe me the maining about "Blobs" output of gstat and levels
 
Blobs: 100212, total length: 6378829, blob pages: 0 
        Level 0: 100212, Level 1: 0, Level 2: 0 
 
below is full sample
 
Z_COMPARE (383) 
    Primary pointer page: 901, Index root page: 902 
    Total formats: 1, used formats: 1 
    Average record length: 64.60, total records: 136628 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Average fragment length: 0.00, total fragments: 0, max fragments: 0 
    Average unpacked length: 82.00, compression ratio: 1.27 
    Pointer pages: 1, data page slots: 1496 
    Data pages: 1496, average fill: 86% 
    Primary pages: 885, secondary pages: 611, swept pages: 0 
    Empty pages: 6, full pages: 1488 
    Blobs: 100212, total length: 6378829, blob pages: 0 
        Level 0: 100212, Level 1: 0, Level 2: 0 
    Fill distribution: 
  0 - 19% = 6 
 20 - 39% = 0 
 40 - 59% = 2 
 60 - 79% = 878 
 80 - 99% = 610 
 
 
What this output about blobs mean
records are 136628 blobs are 10021, is this mean that some blobs are null?
blob pages: 0 - what this mean? Blob is stored in the same page as other fields?
What about levels 0, 1, 2
 
regards,
Karol Bieniaszewski
 
 

Re: [firebird-support] Help in optimizing big data query

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

HiAs you can see, your query do not use any index for filter records. You got 
indexed reads only for order by. The obvius question is what about creating 
index on e.g KODE_MESIN. Regards,Karol Bieniaszewski
null

ODP: [firebird-support] Start/Stopping service

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

>> "Error occured during OpenService"
>>>The specified service doens't exists as installed service (sorry I had to 
>>>translate from french)

Run it as Windows admin

Regards,
Karol Bieniaszewski 

Re: [firebird-support] Drop a column with dependencies

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

Drop dependencies or alter it to empty body if this is e.g stored 
procRegards,Karol Bieniaszewski
null

Re: [firebird-support] Firebird embedded configuration

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

Why you need it?Regards,Karol Bieniaszewski
null

ODP: [firebird-support] Install a DB Firebird on a web site

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

As your description is not precise i post few possibilities.

1.  Simply run regular Firebird  and connect to network by e.g. RDP over 
VPN or something like that.
2.  Implement application server e.g. by using DataSnap and use it in new 
places from e.g. smartphone
3.  If you have or need web server, and this web server is only one 
accesing your db, then you can simply use embeded Firebird server on any 
hosting.
I use it on cheap Windows hosting and connect many clients by web page.

This really depend on your situation, plans and needs

Regards,
Karol Bieniaszewski

Re: [firebird-support] ORDER BY too slow

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

Hiyes, slowdown is because of sort as engine must sort the table first and then 
retrieve 10 records.But you can try to speed this up by:SELECT  FIRST 10 SKIP 0 
 msg.OBJ_GUID AS "MSG_GUID", msg.PRTY, msg.TTL,  pst.OBJ_GUID AS "PST_GUID", 
pst.MSTB_DTSFROM  (Select * from MSGS m order by m.PRTY) msg  JOIN MSG_PSTS pst 
ON msg.OBJ_GUID = pst.MSG_GUID  JOIN MSG_USRS meu ON msg.OBJ_GUID = 
meu.MSG_GUID  JOIN USRS usr ON msg.USR_GUID = usr.OBJ_GUIDWHERE  meu.USR_GUID = 
'12A61B0FAE3046B6AEDEEDF6B4FE0E78'ORDER BY  msg.PRTY, pst.MSTB  Please include 
results: timing and fetching stats. I am interested self in the results. I 
suppose that this should be faster then your oryginal query.Regards,Karol 
Bieniaszewski
null

Re: [firebird-support] how Firebird uses the cores of the microprossesor?

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

HiIt is expected, as Firebird do not have parallelism on single connection. 
Then the best results is with high power core rather then multiple cores. 
Multiple cores are used by multiple connections. And in this case FB3 is the 
winner, as it spread jobs better then FB2.5.But in your case you should look 
more on that stored proc, as 15 minutes is long time. I have changed many sp 
into single query with cte, recursive cte or by using window or psql function 
in fb3Regards,Karol Bieniaszewski
null

Re: AW: AW: Re[2]: [firebird-support] Link system tables to trigger

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

I forgot to say that from performance POV you should do this only once in eg 
connection trigger and set context vars.Use then this vars in 
triggersRegards,Karol Bieniaszewski
null

Re: [firebird-support] Can I restrict sysdba to login from localhost?

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

Thank you for this interesting info. I have missed this param, i only know 
switches in restore.Regards,Karol Bieniaszewski
null

Re: [firebird-support] Can I restrict sysdba to login from localhost?

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

Hi>>No, SYSDBA is able to bypass them.Can i ask how and why?Regards,Karol 
Bieniaszewski
null

Re: AW: Re[2]: [firebird-support] Link system tables to trigger

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

Hi,you can do this by:Select * from mon$attachments a where 
a.mon$attachment_id=CURRENT_CONNECTIONRegards,Karol Bieniaszewski
null