Pull SQL query per page, and not all pages

[p38]

Hi,

If a list has say 5000 records, is there any way to tell fabrik to pull a query as each page is selected, rather than all 5000 records? This takes a large amount of time, and if the records go to 50,000, this becomes un-useable.

I know there is a max limit, but this message pops up each time and is confusing to users.

The ideal would be is pull the query as each page is requested.

Or are there better ways of improving the response display time of large datasets?

Paul

 

[rob]

Hi Paul

Fabrik will limit the query to the number of records shown per page.
If you look in components/com_fabrik/models/list.php the method getData() line 528 (in the latest github code) you see:

$fabrikDb->setQuery($query, $this->limitStart, $this->limitLength);

or if you are using the merge data option, its slightly different in that we have to do a rather complex query across all the possible join to get an array of potential primary key values which we then chop up to produce a limit for the main query. You can see this in the same file in the _buildQuery() method in lines 1584 to 1749. However as we are only querying primary and foreign keys in this query we limit the exposure to a long query. Obviously there is a play off here as we have to take into account all possible join scenarios for building this query.

If this is your scenario and things are slowing down then a potential way round is to build the query in a mySQL view and then create a Fabrk list which points at the view.

Was there a specific scenario that led you to think that we didn't limit the query?

-Rob

 

[p38]

Hi rob,

Perhaps it has to do with the dropdown header filters that is slowing the query down which I am using?

The initial loading of the list caused me to think it was loading all 4000 records first as this time can be up to 60-90 seconds.

It is not our server, as a standard select * query via phpadmin takes about 5 seconds.

So I was wondering, that in order to populate the dropdown lists in the column headers, do you not need to iterate through all records to find distinct items?

or are you running a separate distinct query of each dropdown list?

One the list is displayed for the first time, page navigation speeds are acceptable (3 seconds) to display 100 records per page..

regards

Paul

 

[rob]

It depends what method you are using for the dropdown filters, they can significantly slow down render times depending on what you are displaying for which element type.

Generally I would use a dropdown filter for something like radio buttons, checkboxes dropdowns, database joins and set the 'Filter data' option to 'show all'.
This will populate the data with the specified sub options and is quick.

If you set 'Filter data' to 'show recorded data' then we have to do a

'select distinct(foo) from bar (any applied joiins) + (any applied filter)'

query limited on the value you set in Fabrik's global config for 'Lists->Filter List Max'

We do try to add the correct db indexes to fields which are set to be filters. but obviously that is slower than just picking up the options

There is also the issue that the browser tends to slow up a bit when creating very long select lists.

A course of action I would do is check those filters and set 'Filter data' to 'show all'
If that is still not quick enough then set the filters to autocompletes. This will mean the page is loaded without the need to populate the filter

 

[rob]

This thread got me thinking (oho I hear people say!)....
I've had a go at doing some callback caching of certain methods - such as populating the filters, retrieving auto complete data, and the fabrik magic we do with the raw sql data set.
The work is current in a branch at github called caching
https://github.com/Fabrik/fabrik/tree/caching

on some larger lists (e.g. 100 records, with related data links and a couple of joints) I saw a 1.5 second improvement in render times.

Currently the cahce is likely to be removed too easily and too widely, there's no distinction between cached files for list A over list B, but it might still be worth you trying out to see if it helps

-Rob

 

[p38]

I'll give it a bash thanks Rob.

My database is running at 85,000 records, so when a user clicks on his list (each user has about 4000 records), the pre filter on username kicks in.

The first delay is here, as it needs to pull a query for the user list.

So the user waits sometimes up to 90 secs for teh first page to render.

So any way of improving render times will be appreciated.

regards

Paul.