Reply
Member
Posts: 1
Registered: ‎11-22-2019

DB Structure for Large Scale SAAS CRM

We are working on an early stage CRM Platform and we are currently suffering from slow request and database overload on bussiness inteligence and reporting pages,

I don't think we can continue working doing live request on database on potentially over 200K Leads and show results on real time.

What are the best practices for scaling? do we absolutly need to precalculate nightly all the reports?

Any help will be apreciated,

Thank you

Member
Posts: 1
Registered: ‎08-12-2020

Re: DB Structure for Large Scale SAAS CRM

Look at implementing a star schema. It's a dimensional data model. This is typically used for BI and reporting because it's as fast as can be for response time.

The trade off is it is slower with inserts and the code doing the inserts has to be more complex than the code for inserts into a normalized data model.

As soon as you said business intelligence, I wanted you off of a normalized schema. An analyst running iterative queries based on the prior query's results to build some understanding of the market needs the fastest response time possible or they lose their train of thought (along with their patience!).

Before you go there, be sure your SQL is not crap SQL. Enable the slow_log and look at the explain plans for the top five slowest queries. Your problem could be as simple as a missing index.

Then, if your database is being updated constantly, check for wait lock contention. If the db responds fast to a well tuned, complex query in a non-production environment but shitty slow in production, it could be the mixed load.

A cheap way to remediate that is to set up a read only slave and run all your reports on that.

Member
Posts: 1
Registered: ‎10-01-2020

Re: DB Structure for Large Scale SAAS CRM

Hi, if you are just displaying data in real time upon search. Maybe you should look at something like elastic search or algolia

cinehub apk

Member
Posts: 1
Registered: ‎03-11-2021

Re: DB Structure for Large Scale SAAS CRM

There are a huge number of terms: analytics, data mining, data analysis, business intelligence and the difference between them is not always so obvious, even for people who are associated with it. Today it is difficult to talk about what Business Intelligence (BI) is in an accessible and understandable language. The topic is undoubtedly huge and cannot be covered with just one short article.

Best regards, project developer https://pipsli.net/en/