Does anyone have experience of extracting data via proxy table from Interbase. SQLA version is 10.0.1.4310 IB is XE 10.0.2 474. IB is on one server, SQLA on another both pretty fast, network good. Can't change the architecture at this stage, Am using a 64-bit EasySoft IB ODBC driver installed on the SQLA machine (The IB provided ODBC driver wouldn't work). OS is 2008 Server R2 in both cases. It connects (the Remote Server & Proxy Table definitions produced by the Sybase Central have to be tweaked) but the performance is dire - a few hundred (small) rows per minute is typical & I've got giga bytes of stuff to shift. A similar setup with MS SQL Server on same hardware was pretty instantaneous. IB performance on its own machine seems good. I've tried the obvious settings in the ODBC driver - ReadOnly, Isolation Level's etc but makes no difference. It's not the inserts that are slow, writing out to disk from SQLA ISQL is just as slow. I realise this is more an IB question, but wondered if anyone had any past experience with shifting from IB to SQLA. UPDATE Allocating more RAM to the IB cache & a re-boot seem to have helped a lot - quite why, I'm not clear but I'm not arguing :) Thanks everyone for your help and suggestions. |
BTW - I suffer from complete ignorance when it comes to configuring IB - so any suggestions welcome!
Two suggestions: Set the cis_option option to 7 to see what SQL Anywhere sees, and turn on the ODBC trace to see what ODBC sees. Somewhere in resulting torrent may lurk a clue.
...and in case these valuable suggestions don't lead to a clue they will at least give a reason why the data exchange will be even slower...:)
Thanks Breck - I'd forgotten about that option - I'll see what it shows.
Do keep in mind that SQL Anywhere has even less knowledge about how IB works than you do so the trace should show very generic behavior. Also, any given ODBC driver implementations can very wildly, you may want to also use the IB equivalent of "request logging" to see what the CIS generated SQL gets transformed into. Good luck Justin. Let us know how that turns out.
I have worked with EasySoft on performance related issues. My suggestion is to make sure you have the latest ODBC driver from EasySoft. Also, report the ODBC performance issue to them. They are a pretty responsive group. They'll also make sure you are running the latest driver.