Buggish: Microsoft Teams Web Excel slow performance at 7500 records

I use Microsoft Teams Web Excel. There is an obvious performance issue at 7500+ records with about 15 columns that are mostly populated.

This spreadsheet is an audit history of objects that I have promoted since last year. The largest column is the object path which typically would be file paths as they would include server and folder paths. The other columns are simple strings at most 50 characters or date-time fields.

The biggest problem is the copy/paste. I will oftentimes get the error:

Retrieving data. Wait a few seconds and try to cut or copy again.

Before, waiting will sometimes work but now waiting does not seem to work at all. I do not know if waiting will ever work but I've tried pasting a few hours later and I will still get this message.

Copy/pasting rows now take time. Inserting new rows take time. If I keep my browser open overnight, sometimes it takes a minute or two just for the page to allow me to click on something.

Do I know specifically that this is due to the number of records? No. I am only assuming because it seems to be a trend I see with Microsoft products. Also there is weak correlations between the number of records and frequency of issues or length of delay.

Rebooting sometimes helped before. Now, most little tricks to improve performance no longer work.

Update (2021-09-02)

I split some old data to another sheet, and the performance almost seems immediately better. I moved about 2500 records out of the primary sheet. With around 5200 records, performance still has intermittent issues but much less frequent and faster but still noticeable.

GridView with multiple columns with the same name

Microsoft ASP.NET 4.5
Visual Studio 2013

Scenario: Joining to tables that have the same column names but different uses without using alias (because sometimes you don't have access to the data or query).

Solution: The first column will be the original column name. Each additional column will have an integer counter starting at 1 appended to the end of the column name.

Exampe: If the column name is XXXXXX, then the first column will be XXXXXX, the second column will be XXXXXX1, then XXXXXX2, etc, etc.

SharePoint 2013 DateTime and DateTime format sample

SharePoint 2013 DateTime and DateTime format sample:

2017-03-23


This was surprisingly hard to find for me.

Storing Approximate or Partial Dates in Database

One of the common issues that I have in programming is how to store questionable dates. There are all different forms of questionable dates, but I have primarily been focused on partial dates where I may or may not have the date. The most common property is the a person's birthday. After a few different ways of storing the date, I have found that having an additional column to store the certainty of the date works best for me thus far.

In MS SQL, I have an additional binary column. I will have two columns, DOB (date) and DOBAppr (binary(1)). DOBAppr contains the certainty of DOB. If DOBAppr is 0 then the date is known exactly. If 1, the day is uncertain. In the application, I will only display Month and Year if DOBAppr is 1. If 2, the month is uncertain. This is uncommon to have only the year and day but the possibility is there. One example is when someone does not explicitly share their DOB but has mentioned that they shared the same day (and you know how old they are). If 3, the day and month is unknown. So basically, I have set it up like a binary flag. 4 is unknown year, 5 is unknown year and date, 6 is unknown year and month, 7 is all unknown.

In the previous solution, I used to broken the date into multiple fields, day (tinyint), month (tinyint), and year (smallint). If the value was unknown, I would leave the field as null or use 0 if null is not allowed. Although slightly easier to build the tables, there was quite a bit of overhead to programming. There are other insignificant inefficiencies. This solution has always just bugged me because it was just not a perfect fit even though it worked sufficiently.

Recently, I have expanded on my application and found other uses to using a flags. I was trying to store data on music genres and one of the field is origins. Often in sources, I only get date ranges or decades. So by simply expanding my flags to include the 4th-bit for decades, 5th-bit for centuries, and 6th-bit for millenniums.

While on this expansion, there is also another flag that I could use in the future like approximation (ie circa, c, ca, circ, or cca). Quite similar to partial dates, but this would also mean that the value provided may be incorrect. For example for circa 2015, this could include 2014 or 2016 (or a wider range depending on the object). For partial dates, 2015 would have high confidence that it is correct (or at least a very small chance for error). Although I have no implemented this yet, the solution still gives this flexibility. I probably would use the last bit.

Coding: Name Distributions for 12k Semi-random Names

I wanted to create a table of people's names so that I have a set of data for testing purposes. I wanted the names to be somewhat more random than people I know and needed to find possible exceptions to my assumptions on names. As noted in my previous post, I have ran into quite a few exceptions and some were quite difficult to workaround (ie Muhammad ibn Musa al-Khwarizmi or Georges-Louis Leclerc, Comte de Buffon).

Before I get to some of the problems, I wanted to just post some data since it is just one of those things that I just like to do with my free time even though there may be no value to it. This is for my set of 12k names which I pulled from any lists of names that I could find (US presidents, popular scientists, soccer team members, veterans, etc.) with some diversity.

The top 10 first names are:

1. John - 40
2. James - 29
3. William - 21
4. Thomas - 17
5. Robert - 17
6. Michael - 15
7. David - 14
8. George - 12
9. Mark - 12
10. Richard - 12

The top 4 last names are:

1. Brown - 9
2. Johnson - 8
3. Smith - 5
4. Stewart -5

My random data search is quite dominated by males. The top female name is Susan (8). Just off the top of my head, I think at least 95% of the names are male. I will try to focus on more female dominated industries. I stop at 4 for last names because there were too few overlaps in last names.

I also did a direct aggregate of names, so names with different spellings or abbreviations would have a lower count. I have thought about creating some sort of normalized table for names (ie John, Jonathan, Johnny, Johny, etc.).

As for unique spellings:

First names - 655

Last names - 1055

Another small issue with the names are the use of accent marks. On the traditional US keyboards, accents are not easily accessible so accents may be left out (ie Zoe vs Zoë). These are counted separately in the SQL aggregates.

One of the biggest problems to this count is Asian names especially Chinese ones. Most Asian names are family names first. Although almost all Chinese family names are a single characters, there are rare exceptions to this rule. Unfortunately, I cannot read the names. Even if I assume that whatever I find are single characters, I am not exactly sure how to manage surname first whether I should enter them into the last name field because that is traditionally the family names for Americans (which most systems are based off of) and because names are split so that formalities can be added to American traditions or keep the literal that it is the first part of the name then.

This does make a difference if I were to create metrics similar to what I have above. It will be more important to keep the list as first names as opposed to given names and last names as opposed to family names. This becomes even more complicated as I read that some places like Iceland and India have other traditions to their names where there is no "family" names similar to American or Asian cultures. There are some that include the location, parent's, or parents' names. 

Or even ancient times where people only had a single name. How would I enter Alexander the Great? Given that I keep 'von' and 'de' in the last names, the most logical method is to have 'the Great' as the last name.

Also some people changed their names or inherits new names. I did not have a method for this except to just keep the first name that they had (or at least I think it was the first one). In the future, I will likely have to create an alias table to track people with multiple names.

So there came to be a lot of work to dealing with names than I had originally planned. And this shows how software planning could easily be thrown out the window. What most would probably estimate to be only a couple hours could turn into days because the architecture may change thus rippling other changes.