Problem with XPA 4.7 and MSSQL performance #performance #mssql #xpa


Stefano Giusto
 

Hello,
I confirm that changing the transaction of batch task from none to before task prefix solves the multiple connection issues.
now we have to check our application logic to see if all these transactions does not have some concurrency drawback
  Stefano


Craig Martin
 



As we saw, you can disregard this. The xtp setting is available in 472 and it turned out it was transaction settings. Good luck, Stefano!

On Jun 26, 2021, at 10:31 AM, Craig Martin <craig@...> wrote:



Magic have an unreleased service pack that allows the IsXTPSupported feature to be turned off. I’m sure they’ll release this generally soon. Meantime you could always change the bytecode of the sql gateway to look for a non-existant IsXXXSupported value :-)



On Jun 26, 2021, at 3:23 AM, Stefano Giusto <sgiusto@...> wrote:

Hello,
we upgraded our client/server application (no web, no ria, no mobile) from 8.3 to XPA 4.7
most of the application works fine and performance seems good in online tasks
we notice a huge performance degradation in batch tasks.
we tried changing the number of connections or enabling SQL_OLDOLEDB but no luck
even simple batch tasks go very slow, mainly ones that insert or delete rows.
using profiling tools of mssql we noticed that xpa open and close connections to the server for each insert/delete operation.
a simple batch task in modify mode that deletes records using the 'force record delete' property runs about 1000 times slower that the same task in 8.3
this because xpa open a cursor to scan the table and then open and close a connection every time it need to delete a record
same if you scan a table and insert records in a second table with a link create
is there any approach to write tasks to mitigate this behaviour? best would be to have same performance without touching the code, but if we have to do refactoring, how to do that?
BR
  Stefano


Craig Martin
 



Magic have an unreleased service pack that allows the IsXTPSupported feature to be turned off. I’m sure they’ll release this generally soon. Meantime you could always change the bytecode of the sql gateway to look for a non-existant IsXXXSupported value :-)



On Jun 26, 2021, at 3:23 AM, Stefano Giusto <sgiusto@...> wrote:

Hello,
we upgraded our client/server application (no web, no ria, no mobile) from 8.3 to XPA 4.7
most of the application works fine and performance seems good in online tasks
we notice a huge performance degradation in batch tasks.
we tried changing the number of connections or enabling SQL_OLDOLEDB but no luck
even simple batch tasks go very slow, mainly ones that insert or delete rows.
using profiling tools of mssql we noticed that xpa open and close connections to the server for each insert/delete operation.
a simple batch task in modify mode that deletes records using the 'force record delete' property runs about 1000 times slower that the same task in 8.3
this because xpa open a cursor to scan the table and then open and close a connection every time it need to delete a record
same if you scan a table and insert records in a second table with a link create
is there any approach to write tasks to mitigate this behaviour? best would be to have same performance without touching the code, but if we have to do refactoring, how to do that?
BR
  Stefano


Stefano Giusto
 

Hello,
we upgraded our client/server application (no web, no ria, no mobile) from 8.3 to XPA 4.7
most of the application works fine and performance seems good in online tasks
we notice a huge performance degradation in batch tasks.
we tried changing the number of connections or enabling SQL_OLDOLEDB but no luck
even simple batch tasks go very slow, mainly ones that insert or delete rows.
using profiling tools of mssql we noticed that xpa open and close connections to the server for each insert/delete operation.
a simple batch task in modify mode that deletes records using the 'force record delete' property runs about 1000 times slower that the same task in 8.3
this because xpa open a cursor to scan the table and then open and close a connection every time it need to delete a record
same if you scan a table and insert records in a second table with a link create
is there any approach to write tasks to mitigate this behaviour? best would be to have same performance without touching the code, but if we have to do refactoring, how to do that?
BR
  Stefano