I have some rather complex Views in SQL that have been working well for a couple of years. We upgraded the SQL from 2014 Std to 2019 Std, and now it takes several minutes to get the same data that took only a few seconds previously.
The View in SQL SMSS takes about 2sec to run on an unburdened server. I get around 1600 lines. If I have a gallery of just one or two items, each item in the gallery makes a call to the SQL view for data. Only a couple of items in the gallery, its no problem. However, if I have a gallery of 20 or even 50 items, what only took a few seconds before has been taking even 10 minutes to generate! What!?? All I did was upgrade SQL. I also upgraded the on-prem gateway to I think the June 2021 build. I even loaded the app, and saved/published it again to be sure everything was current. This basically has my app unusable, and I need some help ASAP! I have posts on the SQL forum, and they have been making recommendations about the View, but if it only takes 2 sec to generate a full result, I'm not sure that even halving the time to 1 sec is going to fix the problem here. I'm not talking about seconds, I'm talking about minutes. I feel there is something broke with PA, or connectors with the new SQL 2019.
I Check the monitor, and calls to the SQL connector are quite low, until I get several items in the gallery. I have seen calls take over 80000ms to complete. I just cant think this is directly an SQL issue. It seems to run quick when I go there in SMSS.
Thoughts??
