One of the packages that I wrote is installed at about 140 locations. Until recently, less than 5% were running SQL. So I did a marketing push and we are growing that number by converting several sites from embedded to SQL.

This is important, because the coding for the program is entirely for embedded with a couple of exceptions where I have dual program code (one for embedded, one for sql).

One of the larger pieces is a statistical calculation piece which is present in at least 3 different sections of the program and each of these sections in extremely large and extremely complex (approximately 4500 lines of code in one section).

Most of this runs behind the scenes as an unattended Server Task, but sometimes the user may elect (or may need) to run this large process during their session.

Recently, I converted our largest client from embedded to SQL and yesterday they went to run this process which they run once a month. They thought that something was broken because it ran too long. I obtained a copy of their data and can validate on my development computer the very same result. Here are the specifics


DF18.2
MS SQL Express 14
current MS SQL driver
Process run directly on the server where the data and SQL are located.


Process run to cover 9 mths (Apr 1 - Dec 31)

Embedded: ~25 minutes
SQL: ~3.5 hours


In all of my prior testing, the speed between Embedded and SQL seemed to be very similar, so I am shocked to see such a performance drop. Obviously rewriting these processes to utilize SQL calls would likely help bring this performance back in line, but the process of writing a current 4500 line procedure into SQL is a daunting challenge to say the least.

Any ideas, thoughts or tips will be appreciated.

TIA

Mike