KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.

SQL Queries Behavior #1545

Open Leon Katzke opened this topic on on Jul 30 - 6 comments

Leon Katzke commented on Jul 30

Hi,

Please advise how SQL queries behave in following scenarios:

  1. Do queries for unused filters are run each time a report is run?
  2. If yes is there a way to limit it?
  3. Do queries are killed when the page is refreshed? If not, how to kill them?
  4. Any information on SQL queries behavior will be appreciated

Thank you,

David Winterburn commented on Jul 31

Hi Leon,

Please clarify (with php code if possible):

  1. What is queries for unused filters?

  2. What do you mean by queries are killed? Did you mean database connection or something else?

Leon Katzke commented on Jul 31
  1. datastore for Select2 inputs package
  2. Yes does database connection are stopped when page is refreshed?
Keith Burke commented on Jul 31

On point 2, my understanding of MySQL interacting with PHP is that PHP makes the db request but MySQL finishes when it finishes. If you refresh the page, you could have two expensive queries running. One that won't go anywhere and one that will go to the page refresh.

On point 1, Do queries for unused filters are run each time a report is run?. Your queries will run the way you write them. Maybe set up a default value for Select2 to minimise the initial run.

For example, I use the DatePicker extensively. I set my defaults like below to set my start date 2 days ago and my end date two days into the future.

     protected function defaultParamValues()
    {       
            return array(
            "startDatePicker"=>date("Y-m-d 00:00:00", time() - 60 * 60 * 48),
            "endDatePicker"=>date("Y-m-d 23:59:59", time() + 60 * 60 * 48),
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
            "startDatePicker",
            "endDatePicker",
        );
    }

Alternatively, you can have a conditional statement in your PHP controller to check for a value in your Select2. If it's there set one SQL statement. If it's not, set a different one that doesn't use it.

David Winterburn commented on Aug 3

Hi Leon,

Regarding to your points:

  1. Any query inside the ->query() method in a report's setup is run each time a report's view is refreshed. If the query is used to populate an unused input it still needs to run to get datastore for the input. If your queries are expensive, please consider using the Cache package for faster refresh:

https://www.koolreport.com/docs/cache/overview/

  1. KoolReport keeps a pool of connections for its datasources and manages their creation, reuse. You shouldn't have to worry about this.

Let us know if you have any question. Thanks!

Leon Katzke commented on Aug 3

Hi David,

Thanks for your help. Cache package looks great, is there a way to use the cache only for filters?

David Winterburn commented on Aug 3

Hi Leon,

As far as I know Cache traits in Cache package is used per report so probably you can not isolate it for filters only. Please test it and let us know if you have any difficulty. Thanks!

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed

None