External List performance is poor in Office 365

[Pieter Veenstra]

For one of our customer we have implemented a BCS based solution in Office 365. All is working however the external lists are very slow. ( somethign like 20 seconds to collect infromation);

Does anybody else have any experience with BCS in Office 365 connecting to an on-premises SQL database?

Miscrosoft support has been involved and they say that this is expected behaviour.

Keypoints from Microsoft:

• SharePoint On-Premises environments are dedicated environments where as SharePoint Online is a shared environment, hence it would be slow as compared to SharePoint On-Premise.
• Since the SharePoint Online List has to fetch data from the External Database, it would take some time for it to fetch the data & save the changes to the External Database.

If point 1 was true then I would expct to see a huge varation during the day/night. 

If point 2 was true then I would expect that nobody would implement BCS. I can understand that it would take some time to collect information but not 20 seconds or more. We found that all CRUD operations are slow.

[#2]

Hi Pieter,

have not used BCS for a long long time cause of timeing issues. better would be to create a seperate app/webservice. and include in SharePoint Framework 🙂

[#3]

I can second this! And been searching for solution for years.

We arent even doing CRUD, just read-only for tagging. It is virtually unusable. I've tried all kinds of paging and filters and search criteria to try and speed things up. Our search results only return only the first 25 items that meet the criteria, and it takes approximately 20 to 30 seconds each query.

In our case we are doing a BCS to ODATA connection with credentials in the Secure Store.

[#4]

Hi @Brent Ellis,

That is exactly what we are doing. 

[#5]

I wrote up the details on my blog and a uservoice

https://veenstra.me.uk/2017/03/07/office-365-bcs-performance-issues-dont-use-it/

https://sharepoint.uservoice.com/forums/330318-sharepoint-administration/suggestions/18550603-bcs-on...

[#6]

Several years ago, I used BCS with a web service to get data and I don't remember it being that bad, it was not snappy but I seem to recall getting several thousand records in a few seconds. You may want to reach out to @Brett Lonsdale at Lightning Tools to see if they have any suggestions.

[#7]

Hi @Dean_Gross, was this on-premises? For us it works fine in an on-premises environment but in Offic e365 it is very poor

[#8]

No, it was in SPOnline, the webservice was running in an on-premises server

[#9]

Hi @Dean_Gross,

Do you remember the size of your tables? We now found that tables that are narrow ( 2-3 columns seem to work ok, not great but ok). Wider tables seem to be very slow.

[#10]

I think it was 8 or 10 fields,