usp_JIWA_Creditors_AgedBalancesSummary performance

Support for Microsoft SQL Server in the context of Jiwa installations.

Re: usp_JIWA_Creditors_AgedBalancesSummary performance

Postby pricerc » Thu Sep 08, 2022 3:47 pm

Aargh. Pushed some key combo and lost the reply I was working on... Seems Windows activated one of my keyboard "accessibility" features.

I made some changes in my VM which worked well, but did little on their server. I don't have remote access, so I've scheduled an on-site visit for next week. I will report back again afterwards.

Mike.Sheen wrote:I've downloaded and run these stored procs against a copy of a customer database I have - 1,988 records in CR_Main and 168,727 records in CR_Trans.

Running the stored procs with the parameters for all creditors, as at todays date - both ordered by AccountNo and Name and BOTH stored procs return in 3 seconds - 42 rows returned.


Your experience is what I would expect - neglible difference regardless of which way you run it.

I've got a test script set up so that I can choose between "one", "some", and "all" creditors just by un/commenting the right lines.

My "some" returns 105 records in about 10 seconds. It takes 3 minutes on their server.

As an aside, I've noticed that many of our customers' servers seem to run slower than a VM running on my desktop PC or laptop. I suspect it's because they don't listen to their consultants and have spinning rust in a RAID configuration instead of a mirrored SSDs. But I digress...

Mike.Sheen wrote:
So, It's not a general issue - it's something about the nature of your data or environment which is impacting things. I think you'll find the problem will simply go away after a while, once things grow a bit more and indexes and trees change.


The problem has been there pretty much since they upgraded to 7 (18 months back now). It's now just gotten bad enough that they want it fixed.

I notice your test returns just 42 records - the full set for me is 271. So while I think that you're probably right about it being something with this specific database, I'm not ready to rule out any possibilities. This is the same customer who bumped into the transaction detail performance problem in their creditor maintenance screen - they have some creditors with very large transaction sets. Their top 2 have 52,000 and 30,000 records respectively.

Mike.Sheen wrote:Limiting to just a smaller range of creditors would be enough to reveal where the bulk of the time is spent.


I picked just one to start with.

The two insert queries in the creditor-balance-as-at procedure are what take up all the time.

The most expensive part of both of those queries was the sort needed on the CR_TransAlloc version.

There are gains to be had at several layers -
1) the transaction period calculation; my customer only uses PeriodType == 2, so an inline TVF can replace the complex scalar function.
2) the multi-statement gain/loss scalar function could possibly benefit from conversion to an inline TVF (less of an issue on new SQL Server versions, but still worth pursuing)
3) the creditor balance-as-at procedure has four inline sub-queries that can be reduced to two "outer apply" blocks.
4) some initial testing suggests that the creditor balance-as-at may work well as a TVF when the dataset is big enough - the difference was immaterial on smaller sets.
5) a TVF for the creditor balance-as-at would allow the removal of the loop in the summary sproc.

Apart from adding an index for the TransAlloc version (which helped), I've not looked too closely at the indexes yet.


Of course, none of that explains why the order of execution (including or excluding "order by creditorid") would make the slightest difference to the performance of whatever "creditor balance as at" code is running inside the timing blocks.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: usp_JIWA_Creditors_AgedBalancesSummary performance

Postby SBarnes » Tue Sep 13, 2022 3:33 pm

I have seen this issue with aged debtors when there is a large number of debtors check if the report is using table variables and convert it to use temp tables and you could get a massive performance boost.

Table variables are good for performance on small amounts of data but seem to have issues when there are large volumes of data.

This link might be of some help https://www.sqlshack.com/when-to-use-te ... variables/
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1636
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 177

Re: usp_JIWA_Creditors_AgedBalancesSummary performance

Postby pricerc » Tue Sep 13, 2022 7:46 pm

SBarnes wrote:I have seen this issue with aged debtors when there is a large number of debtors check if the report is using table variables and convert it to use temp tables and you could get a massive performance boost.

Table variables are good for performance on small amounts of data but seem to have issues when there are large volumes of data.

This link might be of some help https://www.sqlshack.com/when-to-use-te ... variables/


Thanks Stuart.

I've had some success with that myself in a different context. A big one is that you can add an index to a temporary table.

I'll keep it mind when I'm looking at it on-site on Thursday.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: usp_JIWA_Creditors_AgedBalancesSummary performance

Postby pricerc » Thu Sep 15, 2022 1:05 pm

So I'm on site. I get last night's backup from the client and restore it to my laptop.

I stare deeply at the database comparison screen for way too long, looking for some subtle difference between that and my development version. Visual Studio's database comparison tool sucks.

I'm able to make some improvements. So then I compare my local copy with their live server. The changes don't seem like they should make a difference, but I load them up anyway, since they're fairly harmless, and I can easily roll them back.

Run my test script on their live server against a small data set. It takes more than twice as long on a small dataset on their server vs my laptop. Run a longer test - 2 minutes on their server, 7 seconds on my laptop.

More staring at the screen, this time looking at the SQL server settings.

Then I realise they're running SQL 2017 Standard, and I'm on SQL 2019 Developer.

I need lunch.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: usp_JIWA_Creditors_AgedBalancesSummary performance

Postby Mike.Sheen » Thu Sep 15, 2022 4:22 pm

pricerc wrote:Then I realise they're running SQL 2017 Standard, and I'm on SQL 2019 Developer.


You can try to make your 2019 behave like 2017 with the compatibility level - and on that, make sure their compatibility level for the database isn't set to something older than the server version. There is no reason for Jiwa databases to be set to run in a compatibility level below that native server version (well, there is an explanation for how that happens, but there is no reason to keep a compatibility level set to an older version).
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2465
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 758

Re: usp_JIWA_Creditors_AgedBalancesSummary performance

Postby pricerc » Thu Sep 15, 2022 6:24 pm

Mike.Sheen wrote:
pricerc wrote:Then I realise they're running SQL 2017 Standard, and I'm on SQL 2019 Developer.


You can try to make your 2019 behave like 2017 with the compatibility level - and on that, make sure their compatibility level for the database isn't set to something older than the server version. There is no reason for Jiwa databases to be set to run in a compatibility level below that native server version (well, there is an explanation for how that happens, but there is no reason to keep a compatibility level set to an older version).


So lunch was quite late, and I had other things to attend to while I was there. So didn't get to do further testing on-site. But at least I have a current copy of their database to work from.

There are a few scalar UDF's involved in the creditor balance-as-at queries. SQL 2019 contains a number of significant improvements to the performance of scalar UDFs. So it seems reasonable that some difference could be expected.

I'm not sure that tweaking the "compatibility" settings will affect that, since there is no syntax change.

Since they can, the customer is going to upgrade their SQL Server.

I am going to go the other way, and install a SQL 2017 standard instance on my laptop and try it on that.

I know you can mess about with compatibility levels, for both the version 2017 vs 2019 and edition Developer vs Standard, but it'll only take a few minutes and there'll be no wondering.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: usp_JIWA_Creditors_AgedBalancesSummary performance

Postby pricerc » Fri Sep 16, 2022 7:38 am

Right, so the short version.

I restored the customer's database into both a 2017 (standard) instance and into a 2019 (developer) instance.

Making no changes, in both cases, the performance is miserable - I aborted the queries after about 10 minutes, and they still had a long way to go.

Changing compatibility level in the 2019 instance, the query runs in 17 seconds.

Now, just to further demonstrate how weird SQL can be....

To make it easier to see progress, I thought I'd sort the query by AccountNo. The 'stock' query ordering by CreditorID, which "order by" I had removed completely.

I made this same simple change in both instances.

The query now runs in 40 seconds on the 2017 instance, and 23 seconds in the 2019 instance.

Go figure.

edit to add this matrix:





instancemodeorder bytime
20172017noneaborted after 10 minutes
20172017AccountNo40 seconds
20192017noneaborted after 10 minutes
20192017AccountNo24 seconds
20192019none17 seconds
20192019AccountNo23 seconds
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: usp_JIWA_Creditors_AgedBalancesSummary performance

Postby DannyC » Tue Oct 11, 2022 1:37 pm

Getting late to the party.

This information may already be known but I can explain
Changing compatibility level in the 2019 instance, the query runs in 17 seconds


The reason is parallelism, or actually lack thereof.
When a stored proc contains a scalar function it forces the whole stored procedure to be executed on a single thread i.e. serial processing. That's gonna hurt.

Ideally we'd want to farm out the grunt of processing to multi threads and get the results faster but the mere presence of the scalar function smashes that on its head as SQL (prior to 2019) can't multi-thread SPs which contain scalar functions.
(google it, there'd be scores of articles about it).

Now SQL Server 2019 has made vast improvements such that the limitation doesn't exist any more. Along with a whole heap of other improvements.
User avatar
DannyC
Senpai
Senpai
 
Posts: 643
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 31

Re: usp_JIWA_Creditors_AgedBalancesSummary performance

Postby pricerc » Tue Oct 11, 2022 8:16 pm

DannyC wrote:Getting late to the party.

This information may already be known but I can explain
Changing compatibility level in the 2019 instance, the query runs in 17 seconds


The reason is parallelism, or actually lack thereof.
When a stored proc contains a scalar function it forces the whole stored procedure to be executed on a single thread i.e. serial processing. That's gonna hurt.

Ideally we'd want to farm out the grunt of processing to multi threads and get the results faster but the mere presence of the scalar function smashes that on its head as SQL (prior to 2019) can't multi-thread SPs which contain scalar functions.
(google it, there'd be scores of articles about it).

Now SQL Server 2019 has made vast improvements such that the limitation doesn't exist any more. Along with a whole heap of other improvements.


If you've ever looked at the output from SQL Profiler (pre 2019), you can see that each statement in a scalar function gets its own record in the trace. In a previous life, I had to deal with some software from a company that thought they were being clever by trying to make SQL work like Oracle, and they had some huge scalar function for some Oracle-style date conversion. Tracking performance problems was a real pain, because 90% of the trace would be the lines from this one function, which for practical purposes, could have been trivially replaced with the SQL equivalent in their code, since all their dates used the same format, but the function supported about 30.

And scalar functions that do database lookups can be particularly nasty.

I've done single-statement table-valued versions of a few of the stock Jiwa functions and stored procedures over the years, and had good results. It's time-consuming, but I enjoy the intellectual challenge of it.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Previous

Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron