Sync time problem

Hello-

I’m really frustrated because I already deleted most of the virtual columns and left with REF_ROWS (average 2 ref_rows per table). Can someone help me?

Here what happens on the app.

  1. Action - Add new rows to purchase_order_items_history
  2. Edited some data on the purchase_order_items*

Attached herewith is the snip of my performance analyzer

It doesn’t appear that your Sync time is the problem so removing Virtual Columns is not going to help. It looks like there is something significantly slowing down your Purchase Order processing.

If you tap on the binoculars, you can drill in deeper to see more precisely what is taking up the time.

More than likely, you have selection expressions (SELECT(), FILTER(), etc) that are performing inefficient table scans.


EDIT: OH, and by the way, it seems you may not have “Delayed Sync” on. With it off, a full Sync is performed after EVERY change. This may be why it appears its a Sync time problem. Try turning it on and see what the performance is like then. You still need to look at the Purchase Order processing because it is WAY to slow!

1 Like

Hello-

I’m trying to check the deep down details in the “binoculars” but I can’t understand it, here it what looks like:

Delayed Sync is also turned on. By the way, I don’t have any data on the tables as I’m still developing it that’s why I’m worried about the sync time.

Ok, I, and I’m sure others, will help you get it figured out.

In your Image there is a row “(internal) DataLayer_UpdateTableRows” showing to have taken 49.52… seconds. It doesn’t seem like your image is showing all of the rows underneath that. Could you re-post an image with more of that information?

I’m sorry but that are the only details on my Performance Analyzer. When i expand the row “(internal) DataLayer_UpdateTableRows”, all I can see are:

[internal] DataLayer_GetAccessTokenForUser
[internal] XlsxSpreadsheetData_Initialize
[internal] ExcelTableAnalyzer_Analyzer

That’s why I can’t also figure it out what part of the process slows down the sync time.

So your Excel workbook only contains worksheets that only have header rows? No data in any worksheet in the workbook?

Yes, I only have headers. (before I executed the add row and edit row.). But most of my tables are connected, some tables have 2 ref_rows, some 4 ref_rows.

Upon checking my excel file size is 8MB, that’s weird.

An AppSheet best-practice is to delete all blank rows and columns from your worksheets. Try that and see if it helps.

2 Likes

Hey Steve.

Yes, it works! From 8MB down to 41KB and the sync time now is approximately 2 secs for adding an entry on the table. Sharing here how I did it by using VB Code.

Sub DeleteEmptyCells()

For Each sh In ActiveWorkbook.Worksheets

sh.Activate
sh.Rows("15:1048576").RowHeight = 10
sh.Rows("15:1048576").EntireRow.Delete
ActiveWorkbook.Save
MsgBox "Sheet " & sh.Name & " has " & ActiveCell.SpecialCells(xlLastCell).Row & " rows"
sh.Rows("1:1048576").RowHeight = 15
sh.Range("A1").Select

Next sh
End Sub

And btw Steve, if I have already have 50, 000 rows. Will it slow down the sync time again?

3 Likes

Wow!! That’s awesome! I wasn’t convinced it was still a limitation as I have let some new sheets keep the extra cells and it didn’t seem to affect the times at all - even when removed.

But now we know! Thanks for following up and posting your results!!

2 Likes

Yep: as your data set grows, so will the sync time. The sync time will grow even faster (much faster!) if you have complex virtual column app formulas.

1 Like

Thanks Steve will check this one! :slight_smile:

2 Likes