Are you sure that this query looks like this?
For me such query use indexes in both cases left and inner.
Show full example with plans
Regards,
Karol Bieniaszewski
>> where have you run and got 0.078s?
Tested in flamerobin.
Now tested in ISQL – result: 0.003 sec
NAME
===
Amsterdam
Current memory = 37027264
Delta memory = 254320
Max memory = 37109808
Elapsed time= 0.003 sec
Buffers = 2048
Reads = 21
Writes = 0
Fetches =
>>Can you explain how?
I do not study your topic much and i can be wrong here. But maybe you got the
idea about what i am thinking about.
SELECT
(SELECT
PRE.ITEM_CONTENT_ID
FROM
FISHEYE_GALLERY_IMAGE_MAP PRE
WHERE
Hi.
This is not Firebird issue.
You must look into something between.
For me first run is:
Total execution time: 0.078s
Second buffered is:
Total execution time: 0.000s
3 seconds is really long time. There must be something involved between.
3 seconds on even 1RPM HDD is also long..
Regards,
There is nothing built in. Think about. You can connect to client 1 through RDP
and on the client 1 run another RDP to client 2 maschine, and from this
maschine another RDP to client 3….
Where you do connection than this is the client.
As say by others you must do outside logic self, and store
You can do this without window function also in FB<3, but less efective. Do
simple 2 subselects in your select.
Regards,
Karol Bieniaszewski
I see that you have only 1024 page buffers.
Do you use SuperServer or Classic?
If Superserver then increase it.
Regards,
Karol Bieniaszewski
You have 91345 versions but Max versions is 1. Then i do not think that
performance problem is here.
You have quite big fill 94%, that there is a chence that new page must be
allocated – but here still you have free slots.
You have gap between oldest active nad next transaction. It is not big
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.
Run gstat and look at info about this table.
Look also at page size of your database. It can be small in 1.5 version, if
yes, change it to bigger value especially 16K.
PS. 900,000 rows is quite small table also for Firebird 1.5
regards,
Karol Bieniaszewski
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.
> Then maybe some switch to restore process should be added?
>>You can always post a feature request in the tracker.
I will do, thanks
> Now this is nightmare for the end user.
>>I'd hardly call it a nightmare, and the alternative to use sources is
>>not much better: it just has different
>>set FIREBIRD_LOCK env variable at system level and reboot.
>>Be careful as wrong usage could damage database.
Thank you Vlad. Can you explain more about risk?
Regards,
Karol Bieniaszewski
Hi.
I have FB3 installed on drive P: on Windows 10
P:\Firebird3\firebird.exe -s FB3_3305
Firebird.conf
TempDirectories = P:\temp
System var
FIREBIRD_TMP = P:\temp
But i see that Firebird create some files on C: drive
Obraz Identyfikator PID Typ Nazwa dojścia
firebird.exe8836
Hi.
I have FB3 installed on drive P: on Windows 10
P:\Firebird3\firebird.exe -s FB3_3305
Firebird.conf
TempDirectories = P:\temp
System var
FIREBIRD_TMP = P:\temp
But i see that Firebird create some files on C: drive
How to prevent this action?
Regards,
Karol Bieniaszewski
[Non-text
Good points Mark.
Then maybe some switch to restore process should be added?
Now this is nightmare for the end user.
regards,
Karol Bieniaszewski
Hi.
There are 2 common ways
1. Create global temporary table
Do INSERT INTO T(DSTART, DEND) SELECT …
and then do simple join with this table
2. Use derived table e.g.
SELECT
DATENLOGGING.* …
FROM
(SELECT D.DSTART, D.DEND FROM TABLED) X
LEFT JOIN DATENLOGGING ON DATENLOGGING.DATUMZEIT>=X. DSTART
>>Recompiling stored
>>procedures, triggers, etc is always a good thing to do when upgrading.
>>Mark
Why it is not done automatically on restore if there is source present?
Pozdrawiam,
Karol Bieniaszewski
Hi.
For your particular case you do not need regex at all. Simple substring +
position.
Example:
Select
D.NAZWA
, SUBSTRING(D.NAZWA FROM POSITION('V', D.NAZWA)+1 FOR POSITION('p', D.NAZWA)-3)
AS V
, SUBSTRING(D.NAZWA FROM POSITION('p', D.NAZWA)+1 FOR POSITION(' ',
SUBSTRING(D.NAZWA FROM
Yes, this wideo is usefull and show where the problem is.
1. Something is not satisfy by ypour filter and go throught 1M rows when you
have this one additional record vs 21K.
2. First run go from disk – 59K reads from disk vs 2.5K.
Look about your where clause and also if your inner join catch
>>You completely misunderstood the announcement. The Firebird Butler is a
>>thing that we develop.
I am really interested if i am only one And because of this i am talking
about any example as a first steep.
>> Steam-like deployment platform for Butler services provided by Firebird
Ok,
Thank you to be involved into discussion.
But i am still oposite. Example do not required any unique description.
Example is not less/more than only shortcut to description. It is introduction
to description.
Description can talk about example or will be totally unreleated.
But example is
Hi.
I had never such issue.
But mayby setting FB service autostart to „delayed” will help you.
regards,
Karol Bieniaszewski
Hi.
I read many pages and still do not know about what it is all about.
I am guy who like differenet learning pattern.
Consider 2 patterns.
I)
1. Description
2. Example
II)
1. Example
2. Description
If you thing about then you will see that only second pattern is acceptable..
Example first
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
Is this from problematic time with connected clients?
If yes than i do no see problems.
You must provide more details about issue.
What exact comand is slow? What are the query plan and query sats?
Did you tested HDD isues?
…
Regards,
Karol Bieniaszewski
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.
Look at gstat output
You probably have very long version this particular record
Regards,
Karol Bieniaszewski
Hi.
When you gain slowdows
Run
gstat -h
and put here results.
Problem probably is because of some long running transaction.
Regards,
Karol Bieniaszewski
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
Look also at hex literals
https://firebirdsql.org/refdocs/langrefupd25-hexbinstrings.html
Pozdrawiam,
Karol Bieniaszewski
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]
>> May I ask why you are using the embedded version of Firebird for a web
>> application?
I suppose he use shared hosting and have not possibility to run full server..
I do this self many times.
Regards,
Karol Bieniaszewski
I suppose you do some wrong things like:
1. Wrong calling convention – should by „cdecl” not „stdcall”
2. You allocate strings locally and return it to the engine but you should
allocate it by „ib_util_malloc”.
3. Your declaration of udf is wrong.
4. You raise some exceptions inside your udf
…..
Hi,
did you checked this or is this your assumption?
Regards,Karol Bieniaszewski
null
Thank you once again.
Now it is much clear that sql standard have more functions and much useful.
I will wait for future improvements in Firebird. I stay longer with udfs for
now.
Regards,
Karol Bieniaszewski
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
Thank you very much Mark for detailed info but i have more questions.
I am really curious.
Is this sql standard concept that i must do this in this crap way?
SELECT substring('ab11c' similar '[[:ALPHA:]]+#"[0-9]+#"[[:ALPHA:]]+' escape
'#') FROM RDB$DATABASE
Also strange that i must consume
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
>> Yes but in the scenario you describe - all computers are on the same network.
No, you can have web server on e.g. shared hosting
and you can connect to it from every place. You can show web page on your
phone, tablet …
Regards,
Karol Bieniaszewski
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:
>>I trying to find a web host that will run a firebird server is very hard
Why? Do you hear about embeded Firebird?
I use FB embeded on the hosting environment without the problem
Regards,
Karol Bieniaszewski
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
As Mark say „Please provide a reproducible example.”
>> also like i said if i run this same query as a standalone outside the SP it
>> run fine ..
Single query is not the answer because in stored procedure you have loop with
multiple records
regards,
Karol Bieniaszewski
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.
Test on Firebird3 „SuperServer” and you will see if the problem is with
resources caching problem.
Regards,
Karol Bieniaszewski
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
>>The table "sto_depositos" (it means warehouses) is populated entering all the
>>warehouses manually.
>>It barely changes.
Then when it is changing you can have situation like this?
Transaction 1 start
Transaction 2 start
Tranaction 1 change sto_depositos
Transaction 2 manipulate data and
It depend how you table sto_depositos is populated and when you set activo=1?
Pozdrawiam,
Karol Bieniaszewski
Od: shg_siste...@yahoo.com.ar [firebird-support]
Wysłano: wtorek, 25 września 2018 16:49
Temat: [firebird-support] trigger not firing?
Hello! I use FB 2.5. I know that this its almos
about multithread: readonlyquery
ina single transaction
19.09.2018 13:26, Karol Bieniaszewski liviusliv...@poczta.onet.pl
[firebird-support] wrote:
> Is something like this safe?
Yes. But it has no performance difference from the case when everything
happen in
single thread.
--
WBR, SD.
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
transaction
18.09.2018 12:33, liviuslivius liviusliv...@poczta.onet.pl [firebird-support]
wrote:
> is this true? I read many times that this is not safe.
Starting from Firebird 2.5 (at least) every API call uses YEntry guard that
lock
entryMutext in corresponding attachment.
You can l
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,
Hi,
no, you must use separate connection fo this. fbclient.dll is thread safe, but
thinking about thread safe in a level of connection or transaction is not
releated here.
If something was changed in Fb3 i am realy interested.
Regards,Karol Bieniaszewski
null
Do you mean remote connection or local maybe embeded connection?
Regards,Karol Bieniaszewski
null
Hi,
1. Why not single Firebird database with all tables?
2. Why not recent Firebird which is FB3 not FB2.5?
Pozdrawiam,
Karol Bieniaszewski
Hi,
<
Hi,
thank you András, but this is what we do already. I mention this in my first
post."Currently we lock customer record and only one user can do
update/insert/delete operation at a time."
But this cause serialisation of operations, only one "user" at same time.This
cause that our server sleep
Hi,
quite interesting solution wihich minimize possibility of overlapping but do
not eliminate it.
Consider first sample about two transactions
What is the difference between normal two transactions and two autonomous
transactions?
Autonomous transaction 1 start
Autonomus transaction 2 start
Yes, it help me.
I can obtain this info on client side
And i can store this info into separate table to simply obtain on the server
side.
In Fb4 i see this will be simpler.
regards,
Karol Bieniaszewski
Od: hv...@users.sourceforge.net [firebird-support]
Wysłano: środa, 12 września 2018 18:19
Hi,
Solution is good only for dates and only in small range but considering date
with time or numbers you see that solution is realy limited.
As Tomasz say will be good to see „systematic solution”
And to check overlaping your sample is quite ok but is ineficient
|---R1---|
Hi,
but this require to have table with all dates. 365 rows per year per customer.
This is not possible solution. This sample with customers was only
simplification. But extend this sample to meeting time date with time. This
table will be too huge...
Regards,Karol Bieniaszewski
null
Hi,
is there a good way to prevent overlaping dates to be inserted to the table?
You know DATE_FROM, DATE_TO and you can have
2018-09-10 to 2018-09-20
and
2018-09-15 to 2018-09-22
they ovelap on 15,16,17,18,19 and 20
No my real sample but simple to understand is:
customer and meeting
Hi,
is there a good way to prevent overlaping dates to be inserted to the table?
You know DATE_FROM, DATE_TO and you can have
2018-09-10 to 2018-09-20
and
2018-09-15 to 2018-09-22
they ovelap on 15,16,17,18,19 and 20
No my real sample but simple to understand is:
customer and meeting
Hi,
how to identify that connection use WireCompression effectively?
I need to get that info:
1. from client application side
2. from server side
regards,
Karol Bieniaszewski
Hi,
>> Now I need to set a fields in the same table like this to track the
>>updates performed:
If you need to update same table then „before” update trigger is the best
choice.
If you use after and run there UPDATE command you must take into accout
recursive call.
Your update command run
Hi,
"As far as I remember, aggregate functions also do
not use indexes and will get progressively slower over time."
for min/max index is used if you have ascending for MIN and descending for MAX.
About topic itself. I always use numbering table and i raly on transaction
conflicts.You know if
Hi,
Use INSERT INTO SELECT ..
e.g.
INSERT INTO MAILSERVERS(NAME, SERVER, REQUIREAUTH, SMTPPORT)
SELECT A.IP_ADDRESS, A.IP_ADDRESS, 0, 25
FROM ip_addresses A
Look also at „MERGE” statement which is much more powerfull then simple insert
statement
Pozdrawiam,
Karol Bieniaszewski
Od:
Hi,
Change your table declaration to include separator e.g. line break.
(IP_Address CHAR(15),Myln char(1));
If you have windows style linebreak change myln to 2 chars.
External table is a fastest way, but you must define what do you mean in your
scenario as "large".
Regards,Karol Bieniaszewski
Hi,
may i ask what is the purpose of this hash?
Regards,Karol Bieniaszewski
null
Hi,
Personally, I like mailing groups.
The speed of getting answers is much greater than on regular web forums.
At the beginning I was skeptical but I have been using this form for many years
and I see more pros than cons of this form of communication.
And as someone rightly stated, one can
Hi,
i suppose you have name longer in your particular select statement. This can be
e.g. in aliasing a field
Select a.x as veryLongAliasedNameLongerThen31 from ...
But only you can tell in what exact place you got this error
Regards,Karol Bieniaszewski
null
>>I suggest you take this question to the firebird-devel mailing list as
>>it concerns an unreleased version. Questions on FB4 are off topic here.
>>Mark
Yes, but as local_time and local_timestamp was forwarded to FB3 it is important
to know if we need also Local_date as replacement to
: Mark Rotteveel m...@lawinegevaar.nl [firebird-support]
Sent: Tuesday, July 17, 2018 5:38 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] LOCALTIME and LOCALTIMESTAMP
On 2018-07-17 16:43, liviuslivius liviusliv...@poczta.onet.pl
[firebird-support] wrote:
>>> ANSI
Hi,
as this is forwarded to FB3 i ask on support group not devel.
Now we have CURRENT_TIME, CURRENT_TIMESTAMP which get time from server whitout
timezone.
Why you breake backward compatibility in FB4 (FB3 is still ok)?
In FB4 "old" FB3 identifiers
CURRENT_TIME will be LOCAL_TIME
Hi,
is this your real query? I ask, because below WHERE can be not intentionaly
written by OR
WHERE (
heir.iaccttreeid = 47
AND cast(tsi.tdt AS date) < '2018-04-01' )
OR(
heir.iaccttreeid <> 47
AND
Hi,
are you sure that you are the only one attachment to the database?Check this by
mon$attachments if present in Fb1.5...You can try to change database file name
if it is changed succesfully then you are the only one.
Try to add some delay after disconnect.
But your real cure is Firebird3.
Hi,
returning ... INTO ..
regards,
Karol BIeniaszewski
From: talorigo...@yahoo.co.uk [firebird-support]
Sent: Sunday, June 10, 2018 1:14 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] stored procedured with insert ... returning
When I try to create the following
I understand that data from real database are restricted.But what is the query
plan for my CTE?Do you have an index on column id_parent?
I am really interested about it in your environment i use it every day.
Regards,Karol Bieniaszewski
null
Hi,
can you show some sample?And what indexes have you created?Can you show plan
for both queries and real queries itself?What filter "where" are you using?
I am really interested because i have compared both queries with much more
populated tables and i have got better results time, fetches
Hi,
Two questions 1. Why do you remove all FK?2. Why do you still use such old
Firebird version?
Many things was fixed since FB1.5 and i suppose that only real fix to your
problem is using recent FB.
You can try disconnect after every FK.You can also try do select count(*) from
updated tables
Hi,
better is walk throught tree
--
WITH RECURSIVE
R_TREE AS
(
SELECT TT.ID, TT.ID AS TOP_PARENT
FROM TABLE1 TT
WHERE TT.PARENT_ID IS NULL
UNION ALL
SELECT TT.ID, RT.TOP_PARENT
FROM TABLE1 TT JOIN R_TREE RT ON RT.ID =
niu 2018-05-30 10:47:57 użytkownik Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support] napisał:
> 30.05.2018 10:45, liviuslivius liviusliv...@poczta.onet.pl [firebird-support]
> wrote:
> > it redirect info to the file but errors are not redirected - why?
>
>You forgot &
Hi,
how to redirect output of gbak to the file?
"%FB_DIR%\gbak" -service 127.0.0.1:service_mgr -c -v -BUFFERS 0 -STATISTICS TD
-user SYSDBA -password masterkey %FB_BACKUP%\security3.bak
%FB_RESTORE%\security3.fdb.restored 2>&1
>>As far as I can tell: no. I suggest you create a ticket to introduce
>>something like
>>ALTER TABLE ALTER EXTERNAL [FILE]
>>Mark
>>--
>>Mark Rotteveel
Thank you
created http://tracker.firebirdsql.org/browse/CORE-5834
regards,
Karol Bieniaszewski
Hi,
previously it was possible to change external table filename
UPDATE
RDB$RELATIONS r
SET r.RDB$EXTERNAL_FILE='c.txt'
WHERE
r.RDB$EXTERNAL_FILE='b.txt'
in FB3 it is prohibited.
Is there some DDL statment to alter such table only file name?
regards,
Karol Bieniaszewski
> the blob field is a ISC_QUADPtr
> ISC_QUAD = array [1..2] of Integer;
> ISC_QUADPtr = ^ISC_QUAD;
>
> --
> Norbert Saint Georges
> http://tetrasys.fi
Hi,
thank you
in the meantime i have found this:
https://github.com/FirebirdSQL/firebird/blob/master/doc/Using_OO_API.html
i do not know why
101 - 200 of 564 matches
Mail list logo
