cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adamstadnick
New Member

Filter and Search to on-premises SQL extremely slow, not delegating properly

I have a fairly basic PowerApp I have put together that runs queries to our on-premises SQL server. The connector appears to be set up properly as I can pull results, but even though my entry should be fully delegated, the app behaves as if it can't delegate - only partial results come back, and it's extremely slow besides - ten to fifteen seconds to run a search. Doing a similar query on our SQL server comes back so quickly that it registers as 0.00s in SSMS so I don't think it's our server - even though this is running on each character typed, we've run much more complex queries simultaneously and never had an issue.

 

Here is an example:

 

Filter ('[dbo].[customer]',TextSearchBox1.Text in customer_name, TextSearchBox1 in customer_id)

 

We need end users to be able to search both fields as sometimes they will only know one of the two possibilities. However, even redoing this as a single query (removing one of the searches) doesn't seem to make much difference, I still get only partial results and poor performance - at best the search will begin to load answers quickly, then 'stutter' as newly typed characters reset the search and different results come in. Often the result you want will come up early on and then disappear, never to come back again.

 

I have tried both Search and Filter in this query but get similar performance either way.

 

I am brand new to PowerApps. Where do I start with this?

6 REPLIES 6
v-monli-msft
Community Support
Community Support

Hi Adam,

 

I haven't seen any similar issue before and I cannot reproduce your issue. As my on-premises SQL with PowerApps works great. Did this slow performance only happen when Filter and Search? If we write data to SQL with PowerApps, will we get the same result?

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

We aren't writing data to the database, this is read only. Changes are pushed to a Sharepoint list, and that appears to work with excellent performance.

 

How can I troubleshoot this?

 

After further testing it appears that the gateway service is consuming a great deal of CPU power every time I run a search or filter, spiking up to 90+% every time I run any of these queries. We have the gateway installed on a separate server from our SQL server. I feel that this must be related somehow.

 

Here is one example. I have included the ability to filter an input text box against the customer_no field. If I search for '102' I get three results of customers whose numbers start with 102, but the actual customer record with that number does not appear. I have tried running this against both a numeric and string version of the field with no change. If I erase the field and type in a different number I get results that don't appear to make any sense - although each result contains the numbers 1, 0, and 3, for example, none of them will have them all together. If I close the app and retry it returns a different set of results. It appears to be a different set of results every time, however I do sometimes see the same incorrect results across multiple attempts - there are half a dozen records that appear over and over.

 

I'm having a hard time figuring out what the problem is since it's so random.

Delid4ve
Impactful Individual
Impactful Individual

Did you ever get to the bottom of this? I have exactly the same issue.

I have the exact same issue, anyone else seeing this?  We have a sql server database on prem and the connections to filter / search are horribly slow, like 15 seconds and the table it is hitting has 10 records in it, makes no sense.  

Delid4ve
Impactful Individual
Impactful Individual

Over the last year ive learnt that the less you can do externally with powerapps the better.

 

Ive resorted to pulling all data into the app via collections as soon as possible (the apps im working on rely on specific filters so i dont need to query live data regulary), and offloading it to flow to do the legwork when writing rather than from powerapps.

 

Having said that, when im collecting at the start of one of my apps, im querying 10-12 tables each with over 30k records in each and it loads the data in 13 seconds from the ios app (pulling between 1 and 50 rows out of each table), the only big load time i have now is a timer hack to iterate through records to get around the 'in' operator not being delegable (but it works well).

 

The gateway definately has issues though:

it never restarts after a server reboot/power failure

write performance also seems slow hence why i dont write directly from powerapps.

 

Ive not experienced any major delays since my last post, since then I have moved the gateway back to the SQL server VM (was originally running on a seperate windows 7 box) though and assigned extra resources.

Interesting idea on leveraging collections.  Maybe I'll wire this up on the app load and then apply my filters on the collection instead, I'll have to test that theory out so thanks for the tip.

 

I also found this article talking about leveraging collections as a method to get past the 500 record limit.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (46,073)