VL in a Windows environment with MS SQL Server database

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
René Houba
Posts: 60
Joined: Thu Nov 26, 2015 7:03 am

VL in a Windows environment with MS SQL Server database

Post by René Houba » Wed Oct 26, 2016 2:29 am

Hi,

One of our customers is moving completely from an iSeries environment to a Windows environment.

They are facing some performance issues. Together with them we try to solve them step by step.

To get a good and exactable Windows environment they do all kind of tests so they know what to do in the Windows environment.

At the moment they test a file in their MS SQL Server database with 4.5 billion records.

A select with a key in the select statement:
SELECT FIELDS(*ALL) FROM_FILE(ABC) WITH_KEY(#FIELD)
Takes about 3 seconds.

The same select without the key:
SELECT FIELDS(*ALL) FROM_FILE(ABC)
Takes about 105 seconds.
 We think this takes so long, because MS SQL Server first creates an index, because we don’t use a key.

The same select_sql without the key:
SELECT-SQL FIELDS(*ALL) FROM_FILES(ABC)
Takes about 12 seconds.

Does anyone has tips related to performance problems like this?
All tips related to: moving from an iSeries database to a MS SQL Server database are welcome!!!

HamadSheikh
Posts: 27
Joined: Fri Mar 18, 2016 3:49 am
Location: USA
Contact:

Re: VL in a Windows environment with MS SQL Server database

Post by HamadSheikh » Wed Oct 26, 2016 3:02 am

SELECT_SQL is faster as it bypasses LANSA OAM's and gets direct access to the SQL table via the configured ODBC driver.

Converting from an IBMi server to a Windows SQL Server is a non-trivial exercise. You have to cater for platform specific coding as well as things like application performance, languages, data conversion/migration, LANSA licensing etc.

Note that there is considerable performance gain in keeping your SQL Server database local (on the same machine where LANSA environment is installed) as opposed to using separate servers.

It is also quite important to make sure that the Windows Server spec is as good (if not better than the IBMi spec). Generally, if you have a $100K IBMi server, then it would be incorrect to think that a $10K Windows server will be able to match it. I will highly recommend using a Solid State drive array (if possible) and purchasing as much RAM (memory) as you can to keep the SQL Server database happy.

Post Reply