What is the best approach for the following situation?

[sales2010]

Hello,

Here is my situation:

7 lists. 3 lists have over 1.5 milion rows each. 6 tables are joined to a main list. In main list i need to have (beside other filters like Company name and SSN) a filter for City.

The problem is that even i use Exact match for City, i will wait way too long for a result, and sometime i got the message "Out of memory" . That because for some searching terms, the result could contain over 300.000 rows.

What can i do to properly use a filter for City element?

Thank you

 

[jfquestiaux]

I guess with that many records and that many joins on the table, you can expect some delay in the processing.

What I would do is increase the ressources of the server to the max (add CPUs and memory) and maybe consider accessing your list through a search form where you set the city. On submit, this form will filter your list and display only the result for this city. That **may** speed things up a little.

 

[sales2010]

Thank you, but i'm still waiting too much to load the page even using a search form. The website is hosted on a dedicated server with a quad core cpu an 4gb of ram..

 

[troester]

Which elements do you need in list view? Only from the main list or all joined ones?

There's a (new) "List view settings" option: "Include in list view"
Default is yes, maybe setting all elements you don't display in list view to NO will help.

Or copy you list, in the copied list delete all joins you don't need in list view, delete/unpublish all other elements not shown and define custom add/edit/view links pointing to the original form (and a redirect form plugin redirecting to the reduced list).

 

[sales2010]

Hello,

The filtered element belong to main list. In list view i have only two elements included in list query (i suppose this is the option you said about it), all other information are available in details view.

The problem is that in details view, all info from all joined lists should be visible. Think of a search for a company in Golden Pages. The search result will display few information with a link to details.

I will try your idea, troester, thank you.

 

[troester]

If your list view only contains 2 elements from one database table (nothing joined) I would create a new list, pointing to this table (so no need to copy joins and joined groups and delete afterwards).
Then unpublish elements you don't need and add the custom links in the list settings.

 

[troester]

If your list view only contains 2 elements from one database table (nothing joined) I would create a new list, pointing to this table (so no need to copy joins and joined groups and delete afterwards).
Then unpublish elements you don't need and add the custom links in the list settings.

 

[cheesegrits]

I'd need to see the page, with both J! and Fabrik debug enabled, so I can see the actual query being generated, and the profile information so I can see where Fabrik is spinning it's wheels.

-- hugh