Originally posted by: AndrewBullock

Hi there

I've recently upgraded my fairly large LP OPL optimisation from my 32 bit Vista Core 2 Duo P9700 development laptop to a VMWare 64 bit Windows Server 2008 R2 XEON X5570. Each of them has CPLEX, the IDE and also SQL Server 2008 running on them. With identical problem / database / setup, as far as is possible, the laptop runs 3 to 4 times faster on writing approx 100,000 rows of data to a simple table in the SQL Server database using:

DBConnection Database("oledb", "//DBName/");

Test_Output to DBUpdate(Database, "INSERT INTO Test_Table VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

My laptop is a piece of old junk - it shouldn't be running faster than the server.

Any ideas what to look out for?

I've tried adjusting "Database update batch size" in the settings, but the default is the fastest.

Is it possible to adjust the parameters for the actual OLEDB connection with OPL / CPLEX?

Are there any known issues with the OLEDB drivers on 64 bit?

Any suggestions gratefully received.

Thanks

Andrew

Originally posted by: AndrewBullock

I ran a further test and it's 6 times slower on the server than the dodgy laptop!

Doing some investigation I found:

 - ILOG CPLEX uses an old version of the OLEDB driver (2000 rather than 2005 or 2008) on both laptop and server (using this article: http://www.mssqltips.com/sqlservertip/2198/determine-which-version-of-sql-server-data-access-driver-is-used-by-an-application/ )

 - OLEDB goes over TCP/IP rather than shared memory even though everything is on the single machine

Perhaps the local loopback for TCP/IP is not as direct on a virtual server than a real machine??

Any ideas?

Originally posted by: AndrewBullock

I ran a series of profiles on the database access and identified that the problem was with the handling of transactions by the combination of CPLEX and OLEDB. On the laptop it issues "set implicit_transactions on" before all of the inserts and does a "IF @@TRANCOUNT > 0 COMMIT TRAN" at the end. The server does neither of these. I've raised a PMR on this.

One point to note is that as a workaround I stopped using:

DBConnection My_Database("oledb", "//Database_Name/");

and changed to:

DBConnection My_Database("odbc", "My_DSN//");

where the DSN was ODBC using the "SQL Server Native Client 10.0" driver.

This improved write performance 14 fold.

Unfortunately the CPLEX ODBC code still doesn't work properly and it corrupts the data. I've raised a PMR on this. In v12.5 it would write strings after the first row as blanks. Now in v12.6 it writes every row, but when the length of the string changes (it's a varchar) it uses the length from the previous writes and either truncates the string or else adds garbage characters to the end. Hopefully this will be patched in a few days.