ODBC read on 2016 server is very slow

[mikeopolo]

Hi all, I hope someone can help me.

We have developed a VFP app to read accounting files using the vendor's ODBC driver. There is no support for this driver now . The vendor's files use a Faircom c-tree database.

A client wishes to migrate to 2016 server. In testing, we found that our app works fine in itself, but reading data from the vendor's files via the ODBC driver is VERY slow - 8 to 10 times slower. Every effort has been taken to ensure that the environment is the same, except for the server.

We use DSN-less connections to the vendor's files.

In testing, a 15,000 record file with ~8 fields takes >2 minutes to read, compared to <10 seconds on the old server. If I query one field, not the whole table, this reduces to about 35 seconds. Complex queries take longer. Our record counter increments slowly by 100's.

Further, I created a data (MS)query in Excel (2010) using a (32-bit) DSN to the same file, and the whole table was read in <10 seconds.

I have run both tests (Excel and our program) with ODBC trace turned on. The trace files are very different (Excel 750k, our program 7MB+), but the trace program on the old server running our program is the same 7MB.

Can anyone suggest:
- directions I should be looking?
- ways to improve the performance?
- ways to find the bottleneck?
- any resources that might help?
- any explanations (deprecations, limitations, etc.)

THe client's IT support wish to migrate very soon, I am at a loss.

 

[atlopes]

Mike,

I'll start by saying I'm a little baffled by the size of the trace files of your test program. I would assume it would be a single SQL statement, or something close to that, but that is not obviously the case. How exactly are you reading the data? Can you post here the VFP code you're using?

 

[bborissov]

First use the latest SQL Server ODBC driver.
Do not use SQL SERVER driver that comes with Windows it is for SQL Server 2000.
Install some of SQL Server Native Clients (better 11) or ODBC Driver 13 for SQL Server.

Also in Control Panel->Administrative tools->ODBC Data Sources (32 bit) go to Tracing page and STOP tracing if it is run.


Borislav Borissov
VFP9 SP2, SQL Server

 

[atlopes]

Borislav, I don't think that MS SQL Server is involved in the issue. If I understood correctly, Mike's application connects to a Faircom server.

 

[GriffMG]

Is your VFP app running on the server or from a workstation?

If it is the latter, I would try it on the server itself.

If you can read the database in Excel, you might have found your workaround, read all the tables into Excel, then drop them into something
easy to read in VFP (like dbfs).



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

Aside from the trace log size, did you look inside it what differs? Even if you expect to understand nothing from it, comparing the content could easily shed a light on what Excel does differently, even when you programmed essentially the same query within VBA code and VFP code.

Anyway, to rule out it is VFP vs Excel you'd need to repeat the Excel using the driver, did you testrun the Excel version on the old server (is that still available?).

Besides that, does the situation differ? Assumed you started with a fresh empty or even non existing tracefile and did the same things from the VFP side, what really differs? Server OS version? c-tree database version? Network?

And as Griff already said, To exclude network issues, did you try to install the VFP program compiled at the server?

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[mikeopolo]

Hi, thanks to you all for your comments and suggestions.

The programs are all running on a server.

The Excel version runs as expected (ie fast) on the old server.

I've looked at the ODBC trace files and can see a completely different structure in the vendor's driver calls, but since I have no control over how the calls to the driver are made, this is an academic exercise only. I've turned off trace since, but with or without trace the performance is the same.

The client uses dozens of our custom reports across >100 accounting files, and it would be a huge amount of work to convert the data extraction process to read via Excel.In addition, there are possibly issues in working with Excel 2016 (Excel 2010 is installed only for comparative test purposes on the new server).

The basic read command used is SQLEXEC(lnConn, lcCmd, lcCursor), where lnconn is a statement handle, lcCmd is a Select * from <whatever tablename>, and lcCursor is the name of the output cursor. The DSN-less connection is typically created by SQLSTRINGCONNECT(lcConnStr, .T.), where lcConnStr is the connection string.

Regards
Mike

 

[Olaf Doschke]

When you read in full tables, then

a) how large are the resultsets?
b) is that really necessary?
c) are you sure Excel reads all rows, too and doesn't limit it, perhaps due to row number limitation of sheets?

Besides saying the Excel version runs fast on the old server you didn't clear up whether you ran the VFP version serverside, too, or just remote, network bandwidth, etc.

If you read a huge result through a 1Gbit network bandwidth and you get this for yourself alone, no shared load in a corporate network, then this still is just 0.125 GB/s and bus speeds of RAM, ie local access through Shared Memory has magnitudes of a few times 10 GB/s. Even 10Gbit networks are still just 1.25 GB/s. And yes, a local connection with Shared Memory also only gets the high spoeed, wehn data is cached in memory, but it could be, otherwise you get SSD speed, RAID speed, something that still is a magnitude better than Network speed even of high speed networks.

The network always is the bottleneck.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name