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] Column Types
On 27-4-2012 12:03, AngelBlaZe wrote: for example a decimal(10,5) column would have using the query above: type = 16 And subtype = 2 precision = 10 scale = 5 Scale is a negative value, not positive (so a DECIMAL(10,5) has scale -5 (you can interpret it as: move the decimal 5 positions to the left). some common types: typemap(8) = ddTypes.aInteger typemap(37) = ddTypes.aString typemap(7) = ddTypes.aSmallInt typemap(16) = ddTypes.aDecimal typemap(12) = ddTypes.aDate typemap(35) = ddTypes.aDateTime typemap(261) = ddTypes.aByte Table RDB$TYPES contains a mapping of ids to their type name (not just for field types btw). -- Mark Rotteveel
[firebird-support] ODBC/JDBC performance
This is an old problem. Many drivers do local cache-ing, others do their own SQL parsing (imagine the effort if you are joining two or more tables from different databases). Many years ago, my company wrote a driver that did this client-server thing (SQLRunner). We let the server interprets the SQL (Oracle has it's own variants just as Firebird and Interbase do) so you let the server worry about interpreting the syntax. The second problem is how to get data as the syntax for getting the data is by field when you get down to the final strokes - we cached as many rows for a get as possible with the largest TCP-IP buffer we could support and sent parts of an even bigger cache by dividing the 32k server cache (or less, depending on how many rows could fit into that) into 2k TCP blocks. The result beat the Borland equivalent for Interbase by huge factors (20 mins VS 24+ hours to get a million+ rows). Then ODBC came out (slow but industry accepted) - so the product was only used by a few folks - i.e. still born. Of course, you need access to multiple DBs (differing vendors perhaps) and the problem of doing a join between two or more databases was left to the programmer. There was (maybe still is) some code in the Firebird engine that could be expanded to make an EXTERNAL table into a foreign DB object - I had hoped to do something with that so the DB engine could do the join for you. Once in place, you could join data from Informix (still there?), Ingres (dead), Oracle (killing Sun) and Interbase/Firebird DBs - all located on different machines or (shudder) on the same machine. I could donate the code to the firebird development group if they want it.
Re: [firebird-support] Performance over VPN
Em 27/4/2012 15:11, Maury Markowitz escreveu: 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 I use RDP for connections of internet, it's the best performance I can get and no changes on my code. 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. Well I don't know the JDBC implementation, but I can assure you that the JOINS are done server side. One thing, could be possible that some library (hibernate or some other O/R mapping) are doing things behind the scenes ? see you !
