Jump to content
  • 0
NickO

Summary Table

Question

I have a Datapage which is sifting through data in a fairly large view (4mil rows and growing). The simplified version is that the view allows me to search for "content" that meets a combination of about 15 different criteria, so no real way to shrink it. The Datapage in question though, is concerned only with summary level data. I.E. how many of each criteria are met by the content.

For example:

I have a single content, Content A. That content is linked to 3 items in criteria1, 2 items in criteria2 and 0 items in criteria3

So my view looks like:

image.png.d026e4603358d1b36ec19b7be1d66a45.png

But my datapage looks like:

image.png.b8747cfe71767e716bc0b6cbefaa8294.png

 

With 4 million rows, 500 Content and 15 different criteria, the datapage itself obviously takes a while to chew through the data and load, filter, sort, etc. Is there any way to create a table or view that will have the summary level data for this datapage so I don't have to calculate it all on the fly every time a user interacts?

Share this post


Link to post
Share on other sites

6 answers to this question

Recommended Posts

  • 0

Hi @NickO,

For this process, I suggest that you use the Pivot DataPage to only get the summarize/group report and use the Aggregate function for the calculation and the output will depend on your configuration. You can check this documentation for more information:  

https://howto.caspio.com/datapages/reports/pivot-table/

I hope this helps :)

~WatashiwaJin~

Share this post


Link to post
Share on other sites
  • 0

Not sure if I understood your question correctly.

 

If you have a Contents table, you may use that as a DataSource to a Tabular Report DataPage, then create 15 Calculated Fields with this syntax:

SELECT COUNT(Criteria1)
FROM _v_ViewName
WHERE ContentID = target.[@field:ContentID]

 

Repeat as needed for Criteria2 through 15

Share this post


Link to post
Share on other sites
  • 0

Thanks DefinitelyNot, you are understanding. You describe what I currently do to get those values. The problem is two-fold. 1) There are 15 of those queries for each of the 400 rows of the contents table and 2) Those queries are happening against about 4mil records currently in the view. This leads to a significant performance drag. It is light years better than the pivot table performance to be sure, but it is a turn off for users.

My real problem is that the data they are looking for really could be any combination of those 15 different criteria so I can't step them through it in any way to reduce the # of queries in any given datapage or the number of records in the view.

Share this post


Link to post
Share on other sites
  • 0
On 8/16/2019 at 6:43 AM, NickO said:

It is light years better than the pivot table performance to be sure, but it is a turn off for users.

Hi @NickO, I agree that doing 15 Calculated Fields is better in performance the more complex Pivot Table. To my knowledge, I believe this is the most optimized solution we can implement. You just have a big amount of data.

 

===

You may want to consider "caching" your results by creating 15 integer fields (corresponding to each of your criteria) on your table, then populate it either by Tasks/Triggered Actions.

 

Sorry for the late revert. Do feel free to tag/mention me with the "@" sign so I get notified when you respond.

Share this post


Link to post
Share on other sites
  • 0

@DefinitelyNot31337, thanks for circling back. While it would be great to be able to cache results, they are calculated on the fly based on user provided criteria. They are also queries against a View, so I am not sure that a calculated field in that view would be allowed even if I could leverage that method. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...