0

Background

I have a query that returns 140000+ results and I need to perform more calculations on the result before displaying them to the user. This has caused a huge wait time (left it to run for 1hr and 30 mins and it still wasn't finished).

Problem

The wait time is huge and needs to be reduced. I can happily load all the data (140000+ results) and run a lesser calculation. This is no use if I can't run the full calculation.

Possible Solution

I've thought about this and I think if I was able to limit the results returned (say 1000 results) I would be able to run the full calculation with no problems.

Question

Can I limit the amount of results back (say results 0-999) run my calculation on them and then load the next bit (results 1000-1999) and run the calc again?

2
  • 2
    Sounds like a good candidate for the Drupal Batch API
    – Beebee
    Sep 3, 2015 at 15:43
  • Depending on the exact process (querying external, data constantly being updated, etc.) it may also be a good candidate for Cron processes or Ultimate Cron
    – sareed
    Sep 3, 2015 at 15:48

1 Answer 1

1

According to https://www.drupal.org/node/1848368

Queries may also be restricted to a certain subset of the records found. In general this is known as a "range query". In MySQL, this is implemented using the LIMIT clause. To limit the range of a query, use the range() method, which takes two arguments: the first specifies offset and the second specifies limit

In most cases we want "the first n records". To do that, pass 0 as the first argument and n as the second.

<?php
// Limit the result to 10 records
// where 0 is offset and 10 is limit
$query->range(0, 10);
?>

The following example will instruct the result set to start at the 6th record found (the count starts at 0) rather than the first, and to return only 10 records.

<?php
$query->range(5, 10);
?>

Calling the range() method a second time will overwrite previous values. Calling it with no parameters will remove all range restrictions on the query.

1
  • This is exactly what I'm looking for but i need some way for the user to pick the limits and offset. Can this be done when building a view?
    – Joe Lloyd
    Sep 3, 2015 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.