SIGN UP

Huge data sets and server side processing

By: Luke M | Asked: 11/29/2022
Forums › Category: General questions › Huge data sets and server side processing
0 Vote Up Vote Down
Luke M asked 2 years ago

Anyone tried to implement editable views / tables with server side processing ? WpDataTables offers this functionality only for Gravity Forms tables. I think DB structure is pretty much the same in both Gravity and Formidable so it should be possible... 

Bobby Clapp replied 2 years ago

No, but I desire this as well. If my full time job was Formidable Forms/web design, I'd have it figured out, but that isn't the case. 🙂

3 Answers
0 Vote Up Vote Down
Victor Font answered 2 years ago
Formidable offers front end editing natively without additional plugins. https://formidableforms.com/knowledgebase/set-up-front-end-editing/
Luke M replied 2 years ago

<p>Does it have something to do with server side processing ? I doubt. I'm talking about hundreds of forms, hundreds of fields each and millions of entries in total. Handling such database is rather tricky as Bobby Clapp said in another post. I must agree with him 100%. Therefore I asked if any of you guys tried non formidable, custom soultion to be able to manage so big data sets. I use WpDataTables server side processing with sql tables / views created from formidable data (Left Joins). Works fine but only for data presentation. No extra features like real time filtering / searching and editing. This of course for testing purpose only but I wish there`d be a fully functional server side processing solution available for formidable forms. Until now I tried formidable views, datatables (Chris, Bobby, Walter ideas) and wpdatabales (sql tables and formidable integration) and neither soultion is efficient enough. Cheers</p>

Victor Font replied 2 years ago

Front-end editing is Ajax based. Ajax processes data server side. Truthfully, for projects this large, I write my own handlers. Also, when necessary I use jQuery DataTables which is the underlying toolset for WPDataTables. This means you can use their Editor.

Nick AmisNick Amis replied 2 years ago

I’ve implanted a solution using wpDataTables with server side processing to put data from Views that I built on FF tables. I added a link to my wpDataTable views that takes the person to another page passing the Entry ID where the form displays it for editing.

Works a treat.

Nick AmisNick Amis replied 2 years ago

I’ve implanted a solution using wpDataTables with server side processing to put data from Views that I built on FF tables. I added a link to my wpDataTable views that takes the person to another page passing the Entry ID where the form displays it for editing.

Works a treat.

Victor Font replied 2 years ago

Great job!

0 Vote Up Vote Down
Rob LeVineRob LeVine answered 2 years ago
I'm not sure if this will help and even though it might get me blacklisted, I feel it's fair to pass it on as I've recently been down that road ("large" data, WPDatatables and server-side processing) and it wasn't a fun commute. After weeks of duking it out with WPDatatables, it finally became clear to me, that while their claim that they can handle 100,000 rows is technically correct, it's outrageously misleading because it's only true if your data is static, e.g., you import 100K rows into a table and the data never changes.  Personally, I've never had the call to use anything other than dynamic data because organizations are always entering new data as more people join, signup, etc.   Worse yet, I found that anything more than 1,999 live rows caused WPDatatables itself to fall over and die in its own admin pages within WP.  I even reproduced it on WPDatatables own sandbox.  Another added bonus was that if you bumped up to 2,000 rows, WPDatatables automatically turns serverside processing on, even if you tick it off in the admin UI.  As Victor alluded to in his message, I also have gone back to native Datatables to do anything with rows beyond 1,999, which by the way, is a REALLY small amount of data when you're talking database work. It's a shame because WPDatatables is a nice interface that allows you to easily create really nice, feature-rich tables with great searching and filtering and to do that yourself in native datatables is rather difficult.  But if you have "big" data and it's live data, WPDatatables is unusable.  For me, the solution was to create a query that's processed on the server side only at the first display of the table and then, once all the data is in the front end, the filtering and searching is done there, i.e., no more server-side, because that would mean suffering the query execution time over and over again.  
Victor Font replied 2 years ago

Great answer Rob! You can stay on the good list. 🙂

Bobby Clapp replied 2 years ago

Is this query generic (Rob/Victor) in that it can grab a form and populate all field data from within that form or is it custom? Asking for friends... haha

Rob LeVineRob LeVine replied 2 years ago

In a word "no". In two words, "hell no". Unfortunately, the query that I used was inextricably tied to the field ids. I felt unclean even creating it. An abbreviated version of the query looks like this.

https://paste2.org/dHHcEntF

Answering for a friend...

Luke M replied 2 years ago

<p>Rob thanks for sharing your experience. As for WpDataTables I didn`t get that deep but I encountered similar issues and decided to abandon further development. Now I use native Formidable views and DataTables in my apps. This of course, requires proper design in terms of the amount of data retrieved on page load and generally those stored in the database.</p>

Bobby Clapp replied 2 years ago

I don't have export functionality in my solution, but I've sort of resorted to divs in css grid as a "table" and applied some custom jQuery to handle live search features per div row.

Victor Font replied 2 years ago

When a system grows to "hundreds of forms, hundreds of fields each and millions of entries in total", this is what I would classify as an enterprise implementation. Enterprise applications require a completely different design philosophy than standard websites. It usually includes incorporating multiple enterprise systems such as HR, finance, supply chain, marketing, and sales into a single Operational Data Store (ODS) for executive and compliance reporting as well as "at a glance" business health dashboards. Each data element must map to a single source of truth. It also implies SSO security using Active Directory and workflow driven by HR systems. Enterprise systems like this is our strength. Building systems like this can often require 7-figure plus budgets.

An ODS can be built with Formidable as a data engine provided you have the infrastructure to support high availability and load balancing. A typical ODS is built with a star schema design. This means flat tables and views. The WordPress/Formidable schema is not suitable for an ODS because each data entry element resides in its own table row. To create a view with hundreds of fields requires an extremely complex SQL query consisting of left joins for each data element. It's much easier and performant to write data to a flat table that includes audit capability when the entry is created and run views off of the flat table.

In a true enterprise system, I would probably recommend Oracle or SQL Server as the ODS if the client has a budget of $20M or more. It can certainly be done with any of the MySQL variants as well. It's probably a good idea to consult with Percona on an enterprise design.

Bobby Clapp replied 2 years ago

That is all absolutely valid and I would agree with you Victor. The question here, I believe, is very specific to datatables and "slowness". This is going to start happening within the thousands without server-side querying on most server hosts I expect.

Victor Font replied 2 years ago

It will Bobby. With this much data, the system is going to need a lot of horsepower for any server side processing with datatables. It will work, but without a redesign, the performance is going to suffer.

Rob LeVineRob LeVine replied 2 years ago

Since I'm in the midst of working on a project that has the potential to have a table with 25K+ entries in a table (honestly, it's impossible to even guesstimate at this point), in the opinion of both of you sage gentlemen, what is your opinion on if we should even proceed with trying to implement this in FF? If FF is OK, what sort of "backend" would you say we'd need?

Bobby Clapp replied 2 years ago

I think it can do 25k rows purely from a PHP/MySQL standpoint, but if datatables is the plan for a reporting feature, then you'll have to go the custom server-side query route I'm guessing. It certainly sounds like Victor has more knowledge in this space (large data sets) than I do, so his comment would be more valuable to you.

Victor Font replied 2 years ago

I agree that 25k records is fine for a standard WordPress/Formidable implementation. Server side helps, but don't forget you can also use SQL views in datatables and Views.

Rob LeVineRob LeVine replied 2 years ago

OK, thanks. I'm all over the SQL views and have been using that successfully. I just want to make sure I'm not wasting my time developing something that could burst at the seams once it's out into the real world.

Luke M replied 2 years ago

Rob I run about 15 FF environments on single dedicated server from OVH. One of them has 10+ useres (kiosk mode devices) who collect production data and another 15+ who interacts with them. Total no. of entries before cleanup was 500k+, now ca. 300k . Some of the tables have 30k+ entries after cleanup. Views have often 30+ columns. This single WP has 200+ forms, 300+ pages, 40 addons, avada theme (slow), 200 code snippets. We run dozesn of API actions and use bulk operations on frontend. The server gets bored... I think the project has been under FF development since 2019 (FF v.2).

Rob LeVineRob LeVine replied 2 years ago

Good info, thanks!

Rob LeVineRob LeVine replied 2 years ago

Since my project is in its infancy, I'm wondering if the three of you would be willing to share the details on what you use for a server, specifically the host name, the server type (dedicate, shared, VPS, etc) and the PHP memory limit you have set (and any other details you'd like to provide that might be helpful).

Victor Font replied 2 years ago

I run a dedicated server that I manage myself.

Bobby Clapp replied 2 years ago

The only thing I've found for shared hosting is they might limit PHP processes and things like that that could affect performance. Unfortunately you do have to scour for limitations.

Rob LeVineRob LeVine replied 2 years ago

Since I'm woefully ignorant about servers (somewhat intentionally until now) do you all think the following will work for a high-volume site? https://www.knownhost.com/managed-vps . Which level would you pick? We're currently at "Standard" and I guess we'll need to move up.

Bobby Clapp replied 2 years ago

I would think that what you have would be sufficient.

Victor Font replied 2 years ago

Rob,

It's good to plan for scalability, but I'd wait to invest in scaling until you can see some solid usage reports, like CPU, Memory, and bandwidth. I don't like metered bandwidth as a rule, but that's what's offered today with VPSs. Bandwidth is a critical dimension for growth. If you continue to scale as the client grows, plan for a dedicated server at some point. Bigger than that and you'll need your own data center.

0 Vote Up Vote Down
Rob LeVineRob LeVine answered 2 years ago
@Bobby Clapp - I had some extra time and finally wrote a code snippet that generates the long/confusing SQL query for a given list of field ids and column names (you can create the list manually or query frm_fields to get all the fields for a given form).  Check out the tutorial (it's only slightly tested).  https://connect.formidableforms.com/user-tutorials/form-data-extraction-sql-generator/

Making the Best WordPress Plugin even better - Together

Take on bigger projects with confidence knowing you have access to an entire community of Formidable Experts and Professionals who have your back when the going gets tough. You got this!
Join the community
crossarrow-right