Hello folks,

We are starting the process of upgrading from an older version of MAS 90 which ran on Providex to 2023 Premium.

There are a number of custom apps that we've developed over the years to run with our MAS data and they all used the MAS ODBC connection.

One such application is a fairly simple (but awesome) ASP.NET web app on IIS. This web app helps our warehouse to determine whether or not to backorder items on their pick sheets.

We are able to connect to the new Sage SQL database using a 32 bit OBDC connection.

We've had to change the syntax slightly in the application, from the providex syntax to SQL. Most of the code is working fine.

However, the following query isn't working at all in our webapp:

SELECT Top 1 TransactionDate As LastReceivedDate, TransactionQty as LastReceivedQty from IM_ItemTransactionHistory where ItemCode = 'MY_SKU' and TransactionCode = 'PO' and TransactionQty > 0 order by TransactionDate Desc

In SQL Server Management Studio, this query takes 4 seconds to run.

The problem appears to be with IM_ItemTransactionHistory.

If we try to count the number of records in IM_ItemTransactionHistory in SSMS, it takes over 7 minutes to complete the record count. The number of records in this table is 16,521,611 

The old Providex ODBC connection has no problem returning results of the query in 3 seconds.

This is using a copy of last month's company data, with the keyfiles rebuilt, but not optimized. The data was migrated from version 4.5 to Sage 2018 Advanced, where it was converted. It was then  migrated from 2018 Advanced to Sage 2023 Premium (SLQ), where it was converted.

The Sage 2023 application runs separate from the MS SQL instance, but both servers are VMs on the same Hyper-V host.

Is there something we should be doing to optimize the data files? or does it sound like the data might not have survived the two upgrades?

Shouldn't SQL be faster and easier than Providex? 

Thanks in advance so much for any comments or suggestions. 

Best wishes, Kris