Performance and Field Collection I have a use case where I want to add multiple fields grouped together to an entity, field collection is perfect for this and I have it working well now this way. 

Field collection stores all field collection entities in one table in field_collection_item, so if I had another field collection that had a huge number of rows would it effect performance? If so then I would like to store these other fields groups somewhere else, maybe an entity reference so these fields are in their own table. How would I convert my field collection to entity reference. 

Can I duplicate the functionality of field collection with entity reference? As essentially an entity reference and field collection are the same. But if I had an entity reference it would be in it's own table that is less crowded.
Not exactly. Field Collection main purpose is to make a field that holds other fields so the data can be logically grouped. So, no, you can't really "convert" it to an entity reference field - they store different data. An entity reference points at another entity; so you'd need an entity to be a container for that data. You can either achieve this via Entity API, or use ECK. I suppose you could write a migration/batch script to port over the data, but I really don't see the point unless the entities were going to be viewed on their own page.

You shouldn't have any issue with the number of rows in the database, the issue will arise when it comes to operating with that data. If you have an entity that has 1500 field collection items, and you load them for display, yes, you will have very severe performance issues. Think of it like calling entity_load 1500 times.

This usually rears its ugly head when you try to make some Views to display field collection items. Pro tip there - disable automatic preview in the Views config.

Here are some resources that may help:


https://drupal.stackexchange.com/a/105397/57
https://www.computerminds.co.uk/drupal-code/you-should-use-entity-cache
https://blog.openlucius.com/en/blog/drupal-field-collection-vs-drupal-compound-fields


This thread also has some good history on a group of people looking for performance improvements with Field Collections: https://www.drupal.org/node/1268620

Edit:

Forgot to link to Entity Cache, which you can bolt on to Field Collection.

In the past, I had came up with two solutions to solve performance bottlenecks revolving around Field Collection.

In one case, I would prime the cache with a nightly cron run, render the field collection field into a variable and cache that variable. If the cache item exists, I pass that as the template variable. This bypasses loading and rendering the field and rendering the data (entity_load) for every user requesting the page.

This works, until your cache item grows to over 1MB in which case it can still cause MySQL to slow down. You may also want to look into adding Memcache or Redis on top as the caching backend to alleviate that. Ultimately, MySQL will be your bottleneck - so you should try to offset that work and cache as much as possible in a caching backend.

In another case, I wound up storing the data in Solr. When the node was preprocessed, I would query Solr for that item. I was also storing the rendered result in Solr, so that my result already contained the rendered HTML. This was the fastest solution for end users, but came with a bit of work to keep it up to date. This is also beneficial if you also have to search through that data elsewhere, since you'd already (read: should) be working with Solr in that case.