HiSimply replace security3.fdb by ine from the zip install and initialize
sysdbaRegards,Karol Bieniaszewski
null
"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
I do not remember from memory but look for "netsh" commandRegards,Karol
Bieniaszewski
null
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
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.
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
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
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
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
HiWithout analysis i can only advice to create descending index.Create
descending index ixd_mytable__timestamp on mytable(timestamp)Regards,Karol
Bieniaszewski
null
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
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
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
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
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
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
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
Did you reconnect after grant?Regards,Karol Bieniaszewski
null
Show the query plan.And what abput the second advice about temporary table
istead of multiple or?Regards,Karol Bieniaszewski
null
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
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
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
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
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
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
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
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
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
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
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
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
>>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
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
>>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
>> 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
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
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
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,
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
Thank you very muchRegards,Karol Bieniaszewski
null
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
What are the exact types of both fields?Regards,Karol Bieniaszewski
null
Hi,You must provide more details. What tools, connection string, exact error
message without modification and error code ...Regards,Karol Bieniaszewski
null
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
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
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
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
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(*)
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:
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.
>> 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
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
>>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
>>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
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
HiShow the query plan for both server versionsRegards,Karol Bieniaszewski
null
Thank you DmitryP.S. correction of description in firebird.conf is needed
thenRegards,Karol Bieniaszewski
null
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
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
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
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
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
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
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
Drop dependencies or alter it to empty body if this is e.g stored
procRegards,Karol Bieniaszewski
null
Why you need it?Regards,Karol Bieniaszewski
null
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)
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
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
Thank you for this interesting info. I have missed this param, i only know
switches in restore.Regards,Karol Bieniaszewski
null
Hi>>No, SYSDBA is able to bypass them.Can i ask how and why?Regards,Karol
Bieniaszewski
null
Hi,you can do this by:Select * from mon$attachments a where
a.mon$attachment_id=CURRENT_CONNECTIONRegards,Karol Bieniaszewski
null
This question was to Dmitry.But i ask it wrongly
Why this is system variable at all?Why this is not a memory mapped file or
something like that?
Fb config was wrong in my previous question, because it is not possible to do
this with config based setting.This must be global think.
Regards,Karol
Hi.
Why this is system variable at all?Why this is not a in firebird.conf
Regards,Karol Bieniaszewski
null
Hi.
To extend this answer i can only say that you have long running transaction.
Look at your last active and next. Go to mon$transaction and look which
transaction is so old
Regards,Karol Bieniaszewski
null
16 KB page size is ok and is maximal for FB1.5 to FB3.
During problem time run gfix -h and show us the results and also gstat results
for that tabe and indexes on it.
P.S. to recreate index you do not need to set it inactive. You can activate
already active index. But this operation can be done
Hi.
I have limited knowledge in this matter then maybe someone correct me.
Firebird work with record formats. When you add new field, firebird create only
new record format for the table and do nothing with data stored. When you
update some record and it is in old format it is then reformatted.
Hi.
If you install Firebird then yes, but if you think generally then no. You can
install FB service at any name e.g "myFB"
Regards,Karol Bieniaszewski
null
Hi
Also look at application logic. When invoice is commited before or after
print?What is your transaction setup? Do you depend on some automatic
commit/rollback or you call commit explicity? Do you use commit retaining?
Regards,Karol Bieniaszewski
null
Hi.
I suppose you have missed previous point
athttps://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-compat.html
Look
forhttps://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-compat-initsec.html
Regards,Karol Bieniaszewski
Oryginalna
Hi,
i have read on http://firebirdsql.org/
article "23 More Ways To Speed Up Firebird".
Can you explain me point "12. Don't forget to enable file cache with big
Firebird cache page".
I can imagine difference only in 2 situations.
1. When all attachments are disconnected and connected
If you try something new better use newest snapshot. There are some fixes
Regards,Karol Bieniaszewski
null
Which plugin do you use, srp or legacy?
Regards,Karol Bieniaszewski
null
Stackoverflow.com ;-)
but do you think that it's time to upgrade?
Regards,Karol Bieniaszewski
Oryginalna wiadomość Od: "Robert Tulloch tult...@hughes.net
[firebird-support]" Data: 12.12.2018 21:34
(GMT+01:00) Do: firebird-support@yahoogroups.com Temat: Re: [firebird-support]
Hi,
did you checked this or is this your assumption?
Regards,Karol Bieniaszewski
null
Hi,
first you can share your query and query plan for it, and what you have changed
and how your modified query plan looks like.
Also check if you have problem with your query under newest snapshot as it
contain recent optimizer fixes
Regards,Karol Bieniaszewski
null
Hi,
Firebird 3
can someone tell me how to work with substring similar?
SELECT substring('abc' similar 'a' escape '#') FROM RDB$DATABASE
raise an error "Invalid SIMILAR TO pattern"
SELECT substring('ab11c' similar '[0-9]+' escape '#') FROM RDB$DATABASE
raise an error "Invalid SIMILAR TO
To extense answer.Firebird Server use max what you set in firebird.confAnd for
Superserver it can use MB5×YourDBPageSize/(1024×1024)and add to this your
sort buffer settings.Remember that database can override your default 5
pagesRegards,Karol Bieniaszewski
null
Please define local/remote here.For mee, it looks like this.On shared hosting i
have web server e.g IIS. This web server operate locally with embeded Firebird
server. Remote are only connections from clients to those web server. All is
working for me without any problems.Regards,Karol
Probably difference in query plans.Show query plan for both server
versionsRegards,Karol Bieniaszewski
Oryginalna wiadomość Od: "André Knappstein
knappst...@beta-eigenheim.de [firebird-support]"
Data: 12.11.2018 12:01 (GMT+01:00) Do:
firebird-support@yahoogroups.com Temat:
For me highest priority are grouping sets and pivot.If we are talking about
triggers/procedures. For me there is one big disadvantage about string
processing.Why we can not use indexed access to string? This should be
simplified.E.g. declare variable A char(50)A[4] = 'x'A[7] = A[8]I
Wow is this true? If yes in my opinion going to PG is a mistake.Regards,Karol
Bieniaszewski
null
Your last query update only one record then i cannot imagine how do you measure
this.But i have answered your question based on information provided. CPU can
be used in stricte calculations like a*b or e.g sort operations. To see simple
what do CPU in particullar database operation and if you
Hi,your update query is disk intensive operation not CPU operation. CPU have
near to nothing here to compute.Regards,Karol Bieniaszewski
null
Change your matching as it contain only one of your fields but as you say you
have composite uniqueRegards,Karol Bieniaszewski
null
Hi,which Firebird version? I never hear about such strange issue. How do you
checked this? Start new transaction and select current_timestamp from
rdb$databaseWhat is the result?Regards,Karol Bieniaszewski
Oryginalna wiadomość Od: "elifla...@gmail.com
[firebird-support]" Data:
Also, as you use superclassic you have separate cache per connection. It can be
also problem here.
Testing on Firebird3 will bring you hint...
Regards,Karol Bieniaszewski
null
Hi,
more detais needed.Do all with same connection
1. Connect to db2. Run query without where clause
SELECT
A.cd_xxx, A.dt_1, A.dt_2, A.dt_3, A.dt_4, A.dt_5, A.dt_index
FROM DM251 A
3. Run query
SELECT
A.cd_xxx, A.dt_1, A.dt_2, A.dt_3, A.dt_4, A.dt_5, A.dt_index
FROM DM251 A
WHERE
Hi,
first, this was not good concept to use -mend, as you can loos data. If you
have a copy before this operation bring it back and run gfix without it.
If this is realy index problem you can fix it with simple command Alter index
indexName active;
This will rebuild your index
Regards,Karol
Hi Dimitry,
is this true? I read many times that this is not safe. I remember that i read
this also on this group and noone say something different. Especially one
thread read some portion of data when another try to retrive different portion
i know that there is statement id, transaction id,
1 - 100 of 293 matches
Mail list logo
