Primary site has approximately 100 grid fields. Duplicating the first site with MSM, that makes 200 exp_channel_grid_field_[id] tables. With setting up 4 sites total, that means we will be dealing with over 400 grid tables. This is far from ideal, and will there be any impact in performance having this many tables?
No, you should be fine. It will be a pain in the ass to manually manipulate the schema (using a GUI tool like MySQL Workbench) since you will be looking at a list of hundreds of tables, but there is no theoretical limit if your table engine is MyISAM. InnoDB has a theoretical limit of 4 billion tables.
So you're fine. The only possible overhead would be if you were querying the
information_schema table, which stores meta data about the schema, it's tables, and it's table's columns. So it's only overhead if you have billions of tables and need to look up the meta on lots of tables (so querying a table with billions of rows).
When you (EE) queries a table, it's called out by name, and I'm pretty sure MySQL seeks the table data directly and doesn't need to do a tech-debt heavy look-up. I could be wrong, but MySQL schemas (in relation to table count) scale vertically with very little issues in my experience.
I might not be exactly right (probably some engine overhead I'm not keyed in to), but EE expanding vertically in the schema versus expanding horizontally inside of a table (think about the
exp_channel_data table, that adds columns for every new custon field) is much smarter.
That said; be aware about the theoretical limit on table columns when you add new fields. Every new custom field you add creates a column in the
exp_channel_data table, and there is a much closer limit. It's something like 4096 columns in theory, but less depending on column type. I'll link some resources.