Just curious how Suite CRM handles searching large datasets?
I currently have around 30 million records I need to import into some sort of CRM and have them be searchable. Currently I do this right in SQL Server but as I take on some new users I need something more user friendly. Suite CRM seems like a great fit if it can manage the data in a reasonable amount of time.
The data is quite simple. Names, addressed, and some values. In 90% of cases we would be searching on the name field with some wild cards.
The solution would be hosted on a local machine. i5-7600, 16GB of ram, 500GB SSD. Plan is to use SQL server but Im open to any database option if it means a faster solution.
I would say that SuiteCRM isn’t your main concern, but rather hardware and database optimization.
Your number of records is pretty huge, and your hardware isn’t that impressive.
I think you will need to test and get down to details (which queries are getting generated, do you have the correct indeces, etc.):
A few pointers:
in 7.11 (to be released next week) we add Elastic Search, which is external search, so you outsource the problem. This could work well for you, but I am not very familiar with this yet, so you’ll have to investigate.
CPU will be doing a lot of work
if you can access the expertise of a specialist database admin, they are normally able to do wonders for performance issues, especially if they are focused on a few things (like those name searches that you say will be 90% of the cases).
watch out not just for searches, but for other things where huge tables can be a problem. So when you check a detail view with a lot of subpanels, SuiteCRM will ask the database for quite a good number of queries. If they all relate to a huge table, trouble may ensue.
you might be better off if you can build a two-level data architecture, so that you don’t put 30 million records in the CRM, but rather keep a) an external big list where you just search in order to get a link or grab some email address, for example; and b) a CRM where you have the records that you’re actually, currently, doing business with, where you can add all the richness of a CRM’s information to your records, and not worry too much about performance. Then you should have a nice swift way of moving between the two: easily creating a CRM entry after searching the big list, etc.