Hi
First change group from yahoo to google group.
And without whole reproducible script no one can help i suppose
Regards,
Karol Bieniaszewski
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
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
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
I do not suppose that this is Firebird message.
This looks like client program message.
regards,
Karol Bieniaszewski
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
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
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
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
HiSimply replace security3.fdb by ine from the zip install and initialize
sysdbaRegards,Karol Bieniaszewski
null
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) || ';',
"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
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
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
Now possibility:
Exctract + cast
Substring + cast
But you can vote on something more usefull:
http://tracker.firebirdsql.org/browse/CORE-5623
regards,
Karol Bieniaszewski
>> 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
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
>>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
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
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
Did you reconnect after grant?Regards,Karol Bieniaszewski
null
>>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
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
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
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
>>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
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
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
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
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
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
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
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
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
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
Hi,
I do not suppose it is possible. But why do you need that info?
regards,
Karol Bieniaszewski
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
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
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
>> "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
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
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
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
1 - 100 of 519 matches
Mail list logo
