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

behaves like this, perhaps from the dev team...? Mvh, Kjell Den 2020-05-01 kl. 09:53, skrev Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]: > > Well, the changed subselect didn't do the trick, but I think it speeds > up the query slightly, overall. > > Now,

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

ut 35 minutes before the slowdown, the I/O activity graph shows an increase in cache hits, but no apparent change in disk reads or disk writes. Studying the actual statements around that time, I fail to see any significant change in the statistics. The statistics do change significantly at the ti

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

Hi Set! Understood and I thank you for pointing it out. My last attempt was to put all the Uhant."ECO_ID" in a temp table and then use that to run the last part of the query. That avoids the unions, but adds some extra work to store the records in the temp table. All in all I suppose it's

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

Thanks Karol! I will consider explicit plan if my current rewrite of the query doesn't pan out. The table "TmpFKExportId" will always have the same number of records, but a different set (each chunk of the batch will load the same number of id:s, but of course different id values). Statistics

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

like that, to reduce query complexity. Kjell Den 2020-04-30 kl. 13:04, skrev Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]: > > Yes, but the transaction just wasn't there. > > I did let it run during the night. Here are FBMonitor diagrams from > about 1 hour

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

Yes, but the transaction just wasn't there. I did let it run during the night. Here are FBMonitor diagrams from about 1 hour before slowdown up to now: https://cdn.mise.se/FirebirdDiagrams.png The vertical line in each diagram marks the time of slowdown (at about 22:05 last night). The upper

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

Thanks, I tried firing up Gregor Kobler's FBMonitor (similar to the old but dead Sinatica Monitor). I found that OAT is 222085103 while OIT and OST (what's that?) is 261140768 and growing. I assume the OAT must have got stuck somehow quite a while back, since it's so far behind the others.

[firebird-support] SQL slower after N executions?

Hi, I have a batch job that among other things executes a certain SQL query to load some data. This query takes about 10-12 seconds the first ~320 times, but then suddenly takes ~45-50 seconds every time. Any idea what could be causing this? The query is rather long, see below. Firebird

Re: [firebird-support] How to inspect nbak state of a Firebird database

Excellent! Thanks! Feel free to anser that in StackOverflow too (link below). If not, I will do it myself after testing it and mark my own answer as the correct one. Regards, Kjell Den 2020-04-22 kl. 10:08, skrev 'Paul Beach' pbe...@mail.ibphoenix.com [firebird-support]: > > MON$TABLES ->

[firebird-support] How to inspect nbak state of a Firebird database

Is there a way in C# to inspect the nbak state of a Firebird database? The state in the database header would be set to |nbak_state_diff| or |nbak_state_normal|. But how do I actually read this value? https://stackoverflow.com/questions/61359915/how-to-inspect-nbak-state-of-a-firebird-database

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

Den 2020-03-08 kl. 23:16, skrev liviuslivius liviusliv...@poczta.onet.pl [firebird-support]: > Hi > > I do not know which rel notes have you read but this one is quite good > about > > https://firebirdsql.org/rlsnotesh/rnfb25-fbconf-fscache.html Yes, that's the one. A few things are unclear to

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

Den 2020-03-08 kl. 10:45, skrev 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

[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

[firebird-support] Can i change write mode "On the fly" and does it take effect right away?

Related to my preceding question: Can I execute "gfix -write sync" and "gfix -write async" while the database is in active use? I.e. while it has connections from a supersevrer process? If yes, will the changed write mode take effect immediately, or does it require a restart of the

Re: [firebird-support] Firebird begin backup and forced writes off, timestamp update delayed

Den 2020-01-24 kl. 09:08, skrev Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]: > > Hi, > > As posted here: > https://dba.stackexchange.com/questions/258124/firebird-begin-backup-and-forced-writes-off-timestamp-update-delayed > > >

[firebird-support] Firebird begin backup and forced writes off, timestamp update delayed

Hi, As posted here: https://dba.stackexchange.com/questions/258124/firebird-begin-backup-and-forced-writes-off-timestamp-update-delayed Feel free to reply here, at StackExchange, or both. I will make sure the StackExchange question gets updated to show any helpful info that appears here.

Re: [firebird-support] Isql exclusive mode?

Den 2020-01-23 kl. 11:32, skrev Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]: > 23.01.2020 08:32, Kjell Rilbe kjell.ri...@marknadsinformation.se > [firebird-support] wrote: >> Can anyone explain in which situations this happens, and in particular >> how I can

Re: [firebird-support] Isql exclusive mode?

Den 2020-01-23 kl. 08:32, skrev Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]: > I have a vague recollection that Firebird's isql utility will in some > situations make an exclusive connection to the database, meaning that > any other connection attempts will b

[firebird-support] Isql exclusive mode?

Hi, I have a vague recollection that Firebird's isql utility will in some situations make an exclusive connection to the database, meaning that any other connection attempts will be refused. I fail to find information about this. Can anyone explain in which situations this happens,

[firebird-support] Is it safe to risk multiple Firebird nbackup -L?

Hi, Please see my question here: https://dba.stackexchange.com/questions/257259/is-it-safe-to-risk-multiple-firebird-nbackup-l Quoting here, but wish you will reply at the site, if acceptable to you. I'm using Firebird 3.0 nbackup to lock + copy + unlock each night. If some steps fail

Re: [firebird-support] gfix -v -full sufficient?

Den 2020-01-11 kl. 15:05, skrev Ann Harrison aharri...@ibphoenix.com [firebird-support]: > > > On Jan 11, 2020, at 7:37 AM, Kjell Rilbe > kjell.ri...@marknadsinformation.se [firebird-support] > wrote: > > > > Does gfix -v -full detect all k

[firebird-support] gfix -v -full sufficient?

Hi, Does gfix -v -full detect all kinds of database corruption? Or are there corruptions that cannot be detected that way? I haven't seen any indications of anything wrong, I'm just trying to check as best I can after a migration. I have already tested a full gbak backup successfully,

[firebird-support] Safe to clean out C:\ProgramData\firebird folder when FB offline?

Hi, Is it safe to clean out the contents of the C:\ProgramData\firebird folder, i.e. wipe it, when the Firebird service (superserver) is not running? I understand that it contains lock tables etc. so should not be touched while FB is running. But it's not clear to me if it can be wiped

[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

Re: [firebird-support] How do I limit an SQL search list to 10 records?

Den 2019-12-12 kl. 22:36, skrev Clyde Eisenbeis cte...@gmail.com [firebird-support]: > Marcin, Minor tweak => works!  I also added DESC to retrieve the > newest records using the field key name (number is an auto increment). > ` > "SELECT FIRST " + stNumOfRecordsToRetrieve + " *

Re: [firebird-support] Fail to create external table

Den 2019-12-02 kl. 16:56, skrev Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]: > 02.12.2019 16:35, Kjell Rilbe kjell.ri...@marknadsinformation.se > [firebird-support] wrote: >> Any ideas? > In short: give up. Just give up. > In long: both client side and ser

[firebird-support] Fail to create external table

Hi, Using Firebird 3.0.4.33054 on WIndows (64 bit), I want to create an external table where the external file name contains Swedish letters. I would think this is a reasonable desire these days. After failing to get the character encoding right in FlameRobin, I thought I'd try isql.exe. So

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

Den 2019-11-28 kl. 17:30, skrev cte...@gmail.com [firebird-support]: > I have this: > > "SELECT fstName, fiKeyID FROM Members WHERE lower(fstName) LIKE > lower('%' || @p0 || '%')" > > Assume fstName includes "Richard" and "Rich".  If I search for "Rich" > it finds both names. > > What do I

[firebird-support] Plugin(?) for external tabels in CSV or Excel?

Hi, We work in .NET and C# and we often import and export Excel or CSV data. It would be really good to be able to select from and insert (append) directly into an external CSV file or Excel file. I am aware of the currently supported external table feature, but the format is rather

Re: [firebird-support] Array datatype?

Den 2019-08-29 kl. 07:17, skrev 'Check_Mail' check_m...@satron.de [firebird-support]: > > Okay, > > unfortunately there is no coherence between the weekday 0-6 and my > table-Field Montag, Dienstag etc. (developed historically) and the execute > statement works not too (not with "variable

Re: [firebird-support] SQL

Den 2019-08-05 kl. 13:20, skrev mohamed hamza medh...@hotmail.com [firebird-support]: > > Hi All, > >   I am new to sql,  I am coming  from  Xbase  system . >  I want to know if it 's  possible to execute query using last query > result? > > EXAMPLE > 1  select * from persons where state='CA' >

Re: [firebird-support] Performance of deleting records based on another table

Den 2019-08-17 kl. 12:51, skrev Joost van der Sluis jo...@cnoc.nl [firebird-support]: > > Hi all, > > I finally realized today that I do not know how to properly remove > records based on another table. > > Suppose I have two tables, one with data (called BIG), and one table > (SMALL) with only

Re: [firebird-support] Round gives inconsistent results for DOUBLE PRECISION expressions

Den 2019-07-29 kl. 05:49, skrev Geoff Worboys ge...@telesiscomputing.com.au [firebird-support]: > > Hi, > > Firebird v2.5 > > Can anyone explain why the following example gives inconsistent > results. The first two I consider correct (rounded up to 8.42), > the last four I consider

Re: [firebird-support] Help interpret plan

Den 2019-06-12 kl. 13:40, skrev Svein Erling Tysvær setys...@gmail.com [firebird-support]: > How many records are there in BUPD, are the other indexes in the plan > selective and can you show us the query? Just wondering if it can be > rewritten so that the query performs better (I assume the

[firebird-support] Help interpret plan

I got this plan for a query: PLAN SORT (   JOIN (     JOIN (   JOIN (     BUPD NATURAL,     UPD INDEX ("IX_PK_Uppdatering"),     SP INDEX ("IX_Uppgiftsspärr_TilläG2M")   ),   E INDEX ("IX_Epostadress_UppgiftLZH")     ),     F NATURAL   ) ) Would this mean that

Re: [firebird-support] Re: Drop table safe when other users are active?

Den 2019-05-26 kl. 13:28, skrev hv...@users.sourceforge.net [firebird-support]: > > > A few years ago we suffered Firebird corruption and got it fixed with > > the help of IB Surgeon. I was told at the time that dropping tables > > while other users were connected was the probable cause, and

Re: [firebird-support] Re: Drop table safe when other users are active?

Den 2019-05-26 kl. 13:28, skrev hv...@users.sourceforge.net [firebird-support]: > > > A few years ago we suffered Firebird corruption and got it fixed with > > the help of IB Surgeon. I was told at the time that dropping tables > > while other users were connected was the probable cause,

Re: [firebird-support] Drop table safe when other users are active?

in any non-exclusive connection and return a suitable error. If there's already an issue in the tracker about this, I'd appreciate a pointer to it so I at least would be able to follow it. Mvh, Kjell Den 2019-05-24 kl. 11:28, skrev Kjell Rilbe kjell.ri...@marknadsinformation.se [firebi

[firebird-support] Drop table safe when other users are active?

Hi, A few years ago we suffered Firebird corruption and got it fixed with the help of IB Surgeon. I was told at the time that dropping tables while other users were connected was the probable cause, and that this was a bug in FIrebird that had been around for a long time. I think this

Re: [firebird-support] Consecutive values from different fields

Den 2019-01-23 kl. 11:53, skrev 'Autoneer' myauton...@gmail.com [firebird-support]: > > Good day all > > I have a question if you can help me PLEASE. > > Using a Firebird SQL query is it possible to count the consecutive > number of matching values in different fields? I need to

[firebird-support] Firebird vs. PostgreSQL

Hi, We've been using Firebird "forever". For upcoming projects and also possible replacement in existing ones, we're considering PostgreSQL. What would you say is the pros and cons of Firebird vs. PostgreSQL? I'm aware that the answers may very well be different depending on application and

Re: [firebird-support] ISQL connects with any credentials?

Den 2018-09-17 kl. 16:04, skrev Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]: > > I just noticed that on my server I can connect to ANY database using ANY > credentials, even with user names that don't exist. And for existing > user name, it doesn't matter wha

[firebird-support] ISQL connects with any credentials?

I just noticed that on my server I can connect to ANY database using ANY credentials, even with user names that don't exist. And for existing user name, it doesn't matter what password I specify - I can still connect and select from e.g. rdb$relations. Why does this happen? Mvh, Kjell

[firebird-support] Transliteration problem with table locks?

Hi, Seems this message wasn't posted properly yesterday. Retrying. I've been using Firebird since a long time, successfully using quoted identifiers with Swedish characters åäö in them. I am at version 3.0.3 (on Windows Server 2016, 64 bit). I tried this code in C# using FirebirClient

[firebird-support] Transliteration problem with table locks?

Hi, I've been using Firebird since a long time, using quoted identifiers with Swedish characters åäö in them. I am now at version 3.0..3 (on Windows Server 2016, 64 bit). Now I tried this code in C# using FirebirClient 6.3.0.0: Dictionary locks = new Dictionary() {   { "KörningInstans",

Re: [firebird-support] CLI to firebird

Den 2018-08-01 kl. 10:44, skrev Mark Rotteveel m...@lawinegevaar.nl [firebird-support]: > > On 2018-08-01 09:14, Kjell Rilbe kjell.ri...@marknadsinformation.se > [firebird-support] wrote: > > The benefits, as I see it, are the object trees, command history, data > > grid, C

Re: [firebird-support] CLI to firebird

Den 2018-07-31 kl. 10:44, skrev trueriver river14ap...@gmail.com [firebird-support]: > I am used to creating tables and users and even databases by typing > SQL into a command line client. > > Forgive me if I am missing some clue in the documention but there I > have not found anything about

Re: [firebird-support] Database file modified shortly after NBACKUP -L

den 2018-04-07 10:37, skrev Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]: > > den 2018-04-06 18:08, skrev hv...@users.sourceforge.net > [firebird-support]: > > > > > For now, I've added, between lock and copy, a dummy isql script that > &g

Re: [firebird-support] Re: Database file modified shortly after NBACKUP -L

den 2018-04-06 18:08, skrev hv...@users.sourceforge.net [firebird-support]: > > > Thanks! As a workaround, I attempted gfix -write sync, but alas, it > will > > work only if no other attachments. We start the copy at midnight, when > > it's likely there won't be other connections, but can't be

Re: [firebird-support] Re: Database file modified shortly after NBACKUP -L

den 2018-04-06 10:53, skrev hv...@users.sourceforge.net [firebird-support]: > > > After testing a bit more, I notice that the locked database file has > its > > > timestamp updated when firebird.exe does a "FlushBuffersFile" > operation, > > which can occur several minutes after the NBACKUP -L

Re: [firebird-support] Re: Database file modified shortly after NBACKUP -L

Den 2018-04-05 09:13, skrev hv...@users.sourceforge.net [firebird-support]: > > ---In firebird-support@yahoogroups.com, wrote : > > ... > > > > It seems like the original database file is touched about a half to one > > minute after being locked with NBACKUP -L. > > > > Why is

Re: [firebird-support] Re: Database file modified shortly after NBACKUP -L

den 2018-04-06 07:56, skrev Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]: > den 2018-04-05 09:13, skrev hv...@users.sourceforge.net > [firebird-support]: > > > > ---In firebird-support@yahoogroups.com, <kjell.rilbe@...> wrote : > > >

Re: [firebird-support] Re: Database file modified shortly after NBACKUP -L

den 2018-04-05 09:13, skrev hv...@users.sourceforge.net [firebird-support]: > > ---In firebird-support@yahoogroups.com, wrote : > > ... > > > > It seems like the original database file is touched about a half to one > > minute after being locked with NBACKUP -L. > > > > Why is

Re: [firebird-support] Database file modified shortly after NBACKUP -L

AB Ulvsundavägen 106C 168 67 Bromma www.marknadsinformation.se <http://www.marknadsinformation.se> 08-514 905 90 den 2018-04-05 09:03, skrev Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]: > > Hi, > > I've been using FastCopy to make backup copies of a ~

[firebird-support] Database file modified shortly after NBACKUP -L

Hi, I've been using FastCopy to make backup copies of a ~180 Gbyte Firebird database, under NBACKUP-L state. This has been working fine for years with Firebird 2.5 and an older version of FastCopy. After migrating the database to a new server and upgrading Firebird to 3.0 and FastCopy to

[firebird-support] Page size vs. index depth?

Hi! I'm in the process of migrating a database from Firebird 2.5 to 3.0. Since this requires a backup restore cycle, I thought I'd take the opportunity to investigate an increased page size to achieve lower index depths. The database is ~170 Gbyte and a couple of tables contain ~300 million

[firebird-support] Firebird SQL 3.0 login from legacy tools?

Hi, New Windows 2016 server with Firebird 3 SuperServer x64 on port 3050. I'm trying to connect to this server from two legacy tools: Sinática Monitor and FlameRobin. I can successfully connect with Upscene Database Workbench and Firebird Maestro, but the old tools fail, saying "Your user

[firebird-support] Image based VM backup and Firebird?

Re: [firebird-support] embedded database with Chinese path

Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] skrev: > 03.10.2017 2:25, Hamish Moffatt ham...@risingsoftware.com [firebird-support] > wrote: >> Any ideas? > Give up. Firebird does not support unicode characters in database name > and path. Only ANSI. Forgive me for butting in,

Re: [firebird-support] Automation tools?

Re: [firebird-support] Automation tools?

Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] skrev: > 15.08.2017 15:36, Kjell Rilbe kjell.ri...@marknadsinformation.se > [firebird-support] wrote: >> We're in need of a tool where we can author sequences of SQL queries against >> FB and non-DB >> commands

[firebird-support] Automation tools?

Hi, We're in need of a tool where we can author sequences of SQL queries against FB and non-DB commands like file operations etc. I'd like to call them "batch runs" to perform recurring tasks. Do you know of such tools or what that kind of tool would be called in general? We need to be

Re: [firebird-support] Full join and indices

[firebird-support] Full join and indices

Hi, I just needed to do a full outer join between two very simple tables G and N, each with a single-column PK of identical type char(12). I noticed that the plan produces was: PLAN JOIN (N NATURAL, G NATURAL) I stopped the query after ~5 minutes. Then I thought I can do this calculation

Re: [firebird-support] Re: ODP: FB 3 issues with String from FB 2.54

Fabian Ernesto Chocron fabia...@itbizolutions.com.au [firebird-support] skrev: After playing with the problem for a while I noticed the definition of the field was Varchar (8192) charset ASCII collate ASCII, so that was probably causing the problem. I changed the domain settings to be Charset

Re: [firebird-support] FB 3 issues with String from FB 2.54

fabia...@itbizolutions.com.au [firebird-support] skrev: I have been trying to migrate from FB2.54 into FB 3 for a few weeks, and after hitting a string related error for some time i have got to the point where I do understand the issue, but I don't know how to solve it. The issue is pretty

Re: [firebird-support] Doubt in Firebird

lionel napoleon lionelnapol...@yahoo.com [firebird-support] skrev: I use firebird 2.5.6.I just wanted to know if there is a method by which i can speed an insert query to (insert about 5 records) into a table.Right now it is taking about 40 minutes.My front end is VBscript and i use a

Re: [firebird-support] How To Speed Up Update SQL

I've found that it often helps with an execute block when updates with complex join-like filter conditions apply. Something like this (untested): set term #; execute block as declare PK yourprimarykeycolumntype; begin for select MB.PRIMARYKEYCOLUMN from MY_BOOK MB inner join