Re: [firebird-support] Confused about delta files [SOLVED]
On 2014-01-17, at 2:33 PM, Ann Harrison wrote: > > Gbak is a backup/restore program that produces an architecture agnostic > backup file. The backup file contains a logical representation of the format > and contents of the database. Nbackup is a fast physical backup that > supports incremental backups. Nbackup is not a database transport > mechanism. If you found documentation that suggests using Nbackup as a > transport, the Firebird Documentation group should know about it, because > neither the database file nor the delta is architecture independent. No, sorry, I meant to say that every reference I have ever found has suggested the "correct" way to move a FB database from one machine to another is to simply copy the FDB file while the server is down. However, in my case, the presence of the failed nbackup meant that the delta file it left behind also had to be copied. I'm actually surprised that simply copying both files actually solved the problem, but I suppose it did so because the original FDB had some sort of pointer to the existence of the delta. This is definitely *not* something that the documentation covers. Actually, from your post it would seem that the whole concept of simply coping the FDB is a bad idea anyway, and the proper solution would be to always use gbak?
Re: [firebird-support] Confused about delta files [SOLVED]
On 2014-01-16, at 8:30 PM, Paul Vinkenoog wrote: > In general, moving Firebird database files is a definite no-no. You copy or > move Firebird databases by gbak'ing them and restoring them at the new > location. Now *that's* interesting, because this has been the most suggested method I have seen. In fact, I only learned about the nbackup method long after having done two moves "the other way", which was the recommended method from the vendor. I think maybe it would be a good idea to put this into the vendor's dox as well.
Re: [firebird-support] Confused about delta files [SOLVED]
On 2014-01-16, at 10:11 AM, Paul Vinkenoog wrote: > > What exactly are you missing in the documentation? Notice that delta > files are normally very short-lived. That's the issue right there. As it is clearly possible that these are not *always* short lived, this should be mentioned along with suggestions on what to do. More specifically, there should be a mention in the sections on moving the databases that state that if a delta file is present, it must be moved as well. > I daresay that if you had read the nbackup manual from beginning to end, I did. I saw no mention of copying FDB files - which I wouldn't expect. Did I miss something? > you could have solved this problem yourself. Well, I did... > If you want to propose changes to a manual, the preferred way is to > submit them to the firebird-docs list (to subscribe, mail to > firebird-docs-requ...@lists.sourceforge.net) Done.
Re: [firebird-support] Confused about delta files [SOLVED]
Whoa, this is interesting... My first attempt to move the DB was to simply copy over the FDB. That didn't work, so that's when I went down the nbackup rabbit hole. As it turns out, nbackup is the problem, not the solution... When I tried to re-attach the FDB file on the new server I got a "file not found" error. But looking in some of the logging files I found... Reason: I/O error for file CreateFile (open) "D:\FISHBOWL\DATABASE\DATA\ASSOLAR.FDB.delta" Error while trying to open file null A. Notice *which* file it can't find? Well then, perhaps if I copy BOTH files over... and it works! So basically it seems that the FDB file contains some sort of information that points to the delta. When you attempt to connect to a FDB in this state, it also looks for that delta file, and if it's not there, you get "file not found". Makes sense in retrospect! I then used isql to ALTER DATABASE END BACKUP. This took about 15 seconds to run (impressive!), and successfully merged the delta back into the FDB. Everything is back to normal. Yay! This is definitely something that should be mentioned in the documentation! Is there a method for me to do the documentation change? I'm pretty good at that sort of thing. p.s. Now that I'm running, does anyone have any suggestions on any post-move maintenance I might want to do?
Re: [firebird-support] nbackup questions
On 2014-01-15, at 8:23 PM, Paul Vinkenoog wrote: > > I think you should contact Fishbowl Inventory support to clear this up. So a little Google-fu was all that took. Now armed with the credentials, what would be the next steps at this point? I suspect the next step is to END BACKUP. However, I wish to tread carefully... I don't have a backup newer than last May, the existing FDB is not a usable file as far as the new machine is concerned, etc. So, what exactly happens when you do the END BACKUP? Does the server have to fold the delta into the original FDB? If so, I suspect this is a long-running task given the size of the file? Or is this a much simpler task, simply renaming the files or such? If I first copy the original FDB and delta, if something goes wrong during END BACKUP will those files be useful to me? It does not appear the original FDB is a working database (at least I cannot connect to it), and I suspect the name "delta" is accurate so it is not a usable file by itself either. This seems to suggest (I know I know, ASSuME) that simply copying these files to offline storage gets me basically nothing?
Re: [firebird-support] nbackup questions
On 2014-01-15, at 8:23 PM, Paul Vinkenoog wrote: > > I think you should contact Fishbowl Inventory support to clear this up. Indeed; they currently have a multi-hour phone wait time. > Most keep it at "masterkey" though. In your case, I assume that the > Fishbowl software has generated a secure SYSDBA password. Nice of them to record it...
Re: [firebird-support] nbackup questions
On 2014-01-15, at 5:40 PM, wrote: > > This is too strong statement. To verify it we need to know physical backup > state of the database. gstat -h will show it (and it not requires admin > privileges). I am able to run this on the original server, are there some additional switches you would like? Here is what I got with -h, and I see that "force write, backup lock" at the bottom, which I suspect is interesting... Database "../data/company.fdb" Database header page information: Flags 0 Checksum12345 Generation 753393 Page size 4096 ODS version 11.2 Oldest transaction 734639 Oldest active 734640 Oldest snapshot 734640 Next transaction734721 Bumped transaction 1 Sequence number 0 Next attachment ID 18032 Implementation ID 26 Shadow count0 Page buffers0 Next header page0 Database dialect3 Creation date Sep 1, 2012 10:22:08 Attributes force write, backup lock Variable header data: Database backup GUID: {407742FD-11DE-43CD-60B7-CBCCC1AA866E} Sweep interval: 2 *END*
Re: [firebird-support] RE: nbackup questions
On 2014-01-15, at 6:31 PM, Paul Vinkenoog wrote: > > "The date of the FDB is May 2nd, 2013. The date of the delta is today." > > ...combined with his later statement that "the system's Scheduler > function, without any warning or errors, stopped making these nbackups > last May". Indeed. Perhaps file sizes may be of interest. The original FDB is ~68MB, and holds data for about three years. The FDB.delta is 70MB and holds data for 8 months. I suspect the implication here is that we've touched so many rows of the original db that practically everything had to be duplicated, and that we've added about 2MB of new material since then.
Re: [firebird-support] nbackup questions
On 2014-01-15, at 4:29 PM, Paul Vinkenoog wrote: > > Why don't you contact the administrator of the system in question? After > all, *someone* should know the SYSDBA password, shouldn't they? I don't believe so. The installer process that created the DB, which comes from Fishbowl, not Firebird, never asked for a SYSDBA password, and did not report one. Some of the dox suggest the Firebird installer will leave behind a file saying what the password was randomly set to. No file of this type appears to be on my system.
Re: [firebird-support] nbackup questions
On 2014-01-15, at 2:43 PM, Alexandre Benson Smith wrote: > > Take a look on nbackup (http://www.firebirdsql.org/manual/nbackup.html) > manual so you could try to merge it back manually. But wait... what about... ALTER DATABASE END BACKUP If I am reading it correctly, this appears to do the same thing as nbackup -N. Is that correct?
Re: [firebird-support] nbackup questions
On 2014-01-15, at 2:43 PM, Alexandre Benson Smith wrote: > > Take a look on nbackup (http://www.firebirdsql.org/manual/nbackup.html) > manual so you could try to merge it back manually. Indeed, but that brings me back to the actual problem... I do not know the password for SYSDBA, and the only registered user does not have enough privs to run nbackup. So, how do I retrieve the SYSDBA pass?
Re: [firebird-support] nbackup questions
On 2014-01-15, at 2:02 PM, Hugo Eyng wrote: > > I am not sure if i understood your question. > > You want to move the "fdb" file to another computer? That is correct. For reasons that I do not understand, the simple method of simply copying the FDB from one machine to another does not work. When I try to attach to that database, it reports "file not found", which I assume is a spurious error code. But there already is a small (example) database file on that machine that I *can* attach to. I can also restore into it. So if I can get nbackup to work, that provides a way to move the data over.
[firebird-support] nbackup questions
I am a admin of the Fishbowl Inventory (FBI) system for our company, which is a Java app on top of Firebird. I need to move the system off an unstable machine. FBI does not allow me to simply replace the .FDB files, for reasons that have not been explained (error codes that make no sense). I experimented with using the system's automated nbackups to replace the information in an existing .FDB, and that works. *phew* The problem is that the system's Scheduler function, without any warning or errors, stopped making these nbackups last May. So I need to make an nbackup manually. So I tried... nbackup -U gone -P fishing -B 0 ../data/EXAMPLE.FDB ../data/ex.bak However, this results in the error, "You must be either SYSDBA or owner of the database". The thing is, the user "gone" is the only one that appears in SHOW USER in isql, so how could it be the case that they are not the owner? Can one assign ownership to a non-existant user? Might it be assigned to SYSDBA? Moving on I tried the same command, but this time with SYSDBA and masterkey. This results in another error, "Your user name and password are not defined." I assume this is because "masterkey" is no longer used, but the installer process does not ask for a password. The dox suggest that the password might be randomly assigned in this case, and then stored in a file, but I cannot find the file it refers to. Can anyone offer suggestions on how to solve this?
[firebird-support] Right way to get number of rows?
I'm trying to retrive the number of rows from a query passed in via ODBC. Not much to it... if (!SQL_SUCCEEDED(SQLRowCount(odbcStmt, (SQLLEN *)&rowCount))) { rowCount = 0; } This works for most providers, but returns -1 on Firebird. Is there another call I should used instead?
Re: [firebird-support] FB-OSX example DB won't open under isql?
On 2012-06-16, at 5:00 PM, Helen Borrie wrote: > The name of the database is "employee.fdb" not "employees.fdb". Which doesn't work either. Same error. The error remains the same no matter what you try to open: Statement failed, SQLSTATE = HY000 Can't access lock files' directory /tmp/firebird
[firebird-support] FB-OSX example DB won't open under isql?
I'm testing my FB install using isql and the employee.fdb database... Maurys-MacBook-Air-2:bin maury$ ./isql localhost:/Library/Frameworks/Firebird.framework/Resources/examples/empbuild/employees.fdb Statement failed, SQLSTATE = HY000 Can't access lock files' directory /tmp/firebird Use CONNECT or CREATE DATABASE to specify a database Is this expected behaviour? It's not mentioned in the readmes or guides I've looked at.
[firebird-support] libgds.so being called from ODBC driver
I've been muddling my way through a build of the ODBC driver on OSX. After some work I was able to find and build all the required prerequisite files, change some paths, move a few things around, and presto, one Mac driver. However, attempting to connect using it fails with: dlopen(libfbclient.so, 2): image not found2012-06-12 09:10:20.845 ODBCQueryTool[6180:7e17] SQL Error State:08004, Native Error Code: 1FC78, ODBC Error: [ODBC Firebird Driver]Unable to connect to data source: library 'libgds.so' failed to load Looking around, it seems that this is normally solved by dropping symlinks into /usr/iib, so I did this using the libfbclient.dylib. But this didn't work, same error. Suggestions?
Re: [firebird-support] Simple query interface for the Mac
On 2012-06-06, at 11:22 AM, mariuz wrote: > you can use isql from unixodbc > I have installed it on ubuntu linux so it should be possible to work on > macosx too > http://www.firebirdnews.org/?p=1324 I found an installer-based version of this, which appears to have installed a DSN editor and iQSL-Viewer in my Utilities folder. These appear to be generic versions of the OpenLink iODBC versions? It appears iSQL is a version of iODBC from the OpenLink package? It does not appear to see my DNSs, so I suppose I have to set up the JDBC side of things. Can anyone point me to a classpath that it might accept? I don't seem to have anything on my drive that's suitable.
[firebird-support] Simple query interface for the Mac
Can someone suggest a simple ODBC-based text-based query interface for the Mac? I'm using RazorSQL, but it's based on JDBC and its more than it should be.
[firebird-support] Moving from 2 to 2.5?
I would like to test the transition from 2.x to 2.5 on a backup machine we have. Am I correct in believing I can simply move the data files over and point the 2.5 server at them? The only note I found on this was this: http://www.firebirdnews.org/?p=5027 Which appears to detail problems in one particular file. But I can't tell from context if this is a general issue or applies only to the USERs.
[firebird-support] A different solution
Well it doesn't seem like I'm going to get ADODB working in Excel, so I'm looking for other solutions. MSQuery *does* work, as do other tools using ODBC. So (realizing this is really an Excel question) does anyone have some advice for using any of Excel's other data tools? I've never used them before. All I really need to do is have all of the queries (there's a dozen or so) update when the book is opened, and then run some code on the data when the updates are complete.
Re: [firebird-support] Calling Firebird from Excel?
Oh, I think this might be in. According to some forum posts over on MS "Live" (love that name!) ADODB does not run under 64-bit. Sweet! Chime in if you're using ADO on 64-bit Excel, this post might just be wrong. According to the same post, there are other technologies I might have to use, but I'm not entirely clear whether or not these can be used from within Excel. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Calling Firebird from Excel?
On 2012-05-03, at 12:56 PM, Bart Smissaert wrote: >> Yes, I have tried calling the DSN from the connection string in VBA > > Can you show that exact connection string? conn.Open "DSN=Fishbowl;", "GONE", "FISHING" Wait wait wait… is there a possibility that I'm using the wrong ADO inside Excel? Is there a separate 64-bit version that I should be including? ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Calling Firebird from Excel?
On 2012-05-03, at 12:49 PM, Leyne, Sean wrote: > Let's start by setting up an ODBC DSN -- we will use that to ensure that the > basic connectivity is working. > > When you press the "Test" button on the ODBC DSN want happens? It works perfectly. And that DNS also works perfectly in iSQL and many other SQL-front-end programs. And one of those working programs is MSQuery, which happily dumps the expected data into Excel. But when I call that same DNS from ADODB in VBA, it fails. Yes, I have tried calling the DSN from the connection string in VBA. That fails too.
Re: [firebird-support] Calling Firebird from Excel?
On 2012-05-03, at 11:59 AM, Bart Smissaert wrote: >> The driver box says "IscDbc". > > That is not a file name as far as I know. > The file I have is C:\WINDOWS\system32\OdbcJdbc.dll So I went to the System32 folder… but are we not talking about 64bit stuff? Do they put 64bit stuff in System32?! Looking in the folder I see a range of Odbc___.dll files. The ones of interest appear to be OdbcFb.dll, .lib and .pdb There is also a CHM. It does not contain any reference to OdbcFb.dll, but it does refer to IscDbc.dll and OdbcJdbc.dll, neither of which exist on my system. The file also refers to itself as OdbcJdbc.chm, when that is not its actual name. So I suspect that the driver was recently renamed from OdbcJdbc.dll to OdbcFb.dll However, I also strongly suspect that none of this would have any effect on the problem. In particular, the help file has a number of connection string examples, but all of them are the same as what we are attempting here, with no luck. The file that supplied the driver can be found here: http://www.firebirdsql.org/en/odbc-driver/ ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Calling Firebird from Excel?
On 2012-05-03, at 11:51 AM, Bart Smissaert wrote: > Here are various ODBC connection strings for Firebird: > http://www.connectionstrings.com/firebird No joy. I tried them all, they all fail with the same useless error message.
Re: [firebird-support] Calling Firebird from Excel?
On 2012-05-03, at 11:15 AM, Michael Möhle wrote: > It looks you may have a different driver than me. > What does it say in the Driver box under System DSN, select the > Firebird/Interbase driver and then Confirgure? > I have there lscDbc and the file is ODBCJDNC.dll > Different driver may mean different connection string needed. The driver box says "IscDbc". It's a combo, but there's nothing else in it. I do not have that file, where is it being referred to? The file I have is fbclient64.dll. That is referred to in the Client line of the Configure… screen. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Calling Firebird from Excel?
On 2012-05-03, at 11:34 AM, Thomas Steinmaurer wrote: > Open Windows Task Manager. On Windows 64-bit. If you see a "*32" behind > the process name, then it is 32-bit, otherwise 64-bit. It is 64 bit. That *suggests* that I'm using the correct driver, installed from Firebird_ODBC_2.0.0.150_x64.exe What is interesting, however, is that the error message implied it was trying to contact the 32bit version of some dll.
Re: [firebird-support] Calling Firebird from Excel?
On 2012-05-03, at 11:15 AM, Michael Möhle wrote: > Following Versions must be installed: > > - Excel 64 Bit: > > - Excel 32 Bit: > Hey Michael, is there an easy way to see which version of Excel I'm running? I think this may be the issue...
Re: [firebird-support] Calling Firebird from Excel?
On 2012-05-03, at 9:41 AM, Maury Markowitz wrote: > On 2012-05-02, at 6:32 PM, Bart Smissaert wrote: >> What is your ODBC driver? > > I don't know, it came with the Fishbowl system. Is there a way to get more > information? Ok, I found it: Firebird/Interbase® driver, 2.00.00.150, IBPhoenix Inc., ODBCFB.DLL, 11/4/2010 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Calling Firebird from Excel?
By the way, I don't know if this is important or not, I can access the DB fine through MSQuery and the DSN I set up. From within the same Excel instance.
Re: [firebird-support] Calling Firebird from Excel?
On 2012-05-02, at 6:32 PM, Bart Smissaert wrote: > What is your ODBC driver? I don't know, it came with the Fishbowl system. Is there a way to get more information? > strConn = "Driver={Firebird/InterBase(r) driver};" & _ >"Dbname=c:\Program Files\Fishbowl\database\data\AS SOLAR.FDB;" > > OpenDBConnection strConn, "XXX", "YYY" Ahhh, progress, but not for the reason you think! I used the string above and it did not work - same "Automation Error". So then I started suspecting that perhaps this was due to permissions. So I opened the same code under an admin account. It still doesn't work, but at least now I have more information! [ODBC Firebird Driver]Unable to connect to data source: library 'gds32.dll' failed to load. I have noticed one difference between the ODBC manager setup and this connection string: the ODBC setup includes a "client" line. However, adding… Client=c:\Program Files\Fishbowl\odbc\fbclient64.dll; to the connection string didn't improve matters. I *suspect* this has something to do with 32/64 bit issues, or a mis-registered DLL. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Calling Firebird from Excel?
On 2012-05-02, at 5:59 PM, Bart Smissaert wrote: > Post your code and tell where you get the error. > Should be something simple Yeah, normally is simple. Just wish the error was more descriptive! Const cnn = "Driver={Firebird/InterBase(r) driver};Dbname=c:\Program Files\Fishbowl\database\data\AS SOLAR.FDB;UID=XXX;PWD=YYY;" Public conn As ADODB.Connection Sub OpenDBConnection() If conn Is Nothing Then Set conn = New ADODB.Connection End If If conn.State = 0 Then conn.Open cnn End If End Sub Happens right on the conn.open. I assume it's a problem in the cnn string, but what? I tried {}'s around the dbname, no luck. The connection to the DB works when I use ODBC. I have a DSN pointed to it as well, but when I try to use the DSN based cnn it doesn't work and gives me two errors :-) Const cnn = "ODBC;DSN=Fishbowl;UID=XXX;PWD=YYY;"
Re: [firebird-support] Calling Firebird from Excel?
On 2012-05-01, at 7:03 PM, Bart Smissaert wrote: > This is some simple VBA code that demonstrates how this can be done. Thanks Bart, that did help. Sadly, I'm getting the dreaded "Automation Error". Likely a missing semicolon...
[firebird-support] Calling Firebird from Excel?
In a former life I was a SQLServer+Excel guru, but today I am merely the noob. Does anyone have some sample VBA of calling Firebird via ADODB from Excel, or Office in general? I'm sure I can get up to speed quickly, but that first step...
Re: [firebird-support] Performance over VPN
Well one way or the other the latency of the link does seem to be the most important factor in the problem I'm seeing. I have three ways to get at the data: 1) log into the remote server using RDP and run the queries locally. even the most complex ones complete in under a second 2) use JDBC over a Bell link with about 75 to 80 ms of delay. most queries take about 1 second, and the bad ones up to 20 3) use JDBC over a Rogers link with 110 to 125 ms of delay. most queries take about 4 seconds, and bad ones take minutes. This is in spite of this link having 6 times the raw throughput. What's interesting is that there does seem to be some relationship between the number of joins and the performance. This makes me *suspect* that the driver is pulling tables to the local client and doing local JOINs.
Re: [firebird-support] Performance over VPN
On 2012-04-26, at 5:36 PM, Ann Harrison wrote: > Actually that's not true for selects, though the client interface looks that > way. The remote protocol packs a buffer with data until it reaches a size or > the client calls for the next packet. I'm confused then... is the buffer very small or is the client asking for results too quickly or something? The client is definitely doing *something* on the outbound channel, and it does appear to be effecting performance, dramatically.
Re: [firebird-support] Performance over VPN
On 2012-04-26, at 8:20 AM, Svein Erling Tysvær wrote: > Exactly what is the definition of your fields? I can't figure out an easy way to cut-n-paste, but generally there isn't a single column larger than VARCHAR(100). It's an extremely simple database with very little data. > And what does your select look like (just out of curiosity)? select * from part - there's about 450 rows and maybe 25 columns of int and varchar I think the other poster's hit it on the head - if the ODBC interface really does require the client-side to request result sets one row at a time, that is clearly the problem I am facing.
[firebird-support] Performance over VPN
I'm trying to use ODBC to get at a Firebird database for the first time. The db is on the other end of a VPN link, but the pings are around 75 ms in the bad cases and the throughput is normally in the Mbps. The database in question is only 50 MB on disk and the tables I'm talking to are generally only a few hundreds rows and maybe 20 columns nothing but char, int and float. It's all very small and light! Using RazorSQL as a UI on both the host machine and the client, I have been testing the performance of the query interface. My test queries have a few simple fkey joins, queries strings about 100 to 200 chars at the most, and the returned data is maybe 10 k or so. Running on the server, over the VPN and using RDP, the simple queries I run complete almost instantly, in hundredths of a second. Ok, works for me. Running on my client, over the VPN using ODBC directly, the exact same query takes about three seconds. What the?! More complex queries against *slightly* larger tables with a few more joins continue to complete instantly on the server, while taking between 1 and two minutes to complete when run on the client! Moreover, I can see that during the delays, the *outbound* link from the client is clicking over about 1 kbps for the entire period. It *appears* that the client is dribbling out information that the server is waiting on, although I can't imagine what it is. Does anyone have any idea why this is happening? I'm literally changing nothing but the location of the client. SQL should not care. Yet it clearly does.