Speed up load times - Count queries bottleneck?

Speed up load times - Count queries bottleneck?

TronikTronik Posts: 108Questions: 24Answers: 0
edited March 26 in Editor

Hi,

I have a database with around 30000 rows, some left and mjoins.
Im trying to speed up the load times.
When using developer tools in the browser I've measured the serverside script (Editor lib) to about 3.5-4.5 seconds when loading initial or performing search.
It works but it would be really nice if I could reduce that time, in terms of user experience.
I use serverside and scroller with a buffer of 16

When troubleshooting I extracted the SQL query using ->debug(true), and the results in the database when running the query is much faster, 0.2seconds.
That is the "main query"
I have come to the conclusion that the COUNT queries performed by dt/editor is what is slowing down the performance, which means there is really not much I can do i terms of optimizing the db or exluding joins etc?

Is there anything I can do regaring these queries? Or anything else that will speed thing up?
Workaround or hacky solutions are fine also

Answers

  • allanallan Posts: 57,300Questions: 1Answers: 9,127 Site admin

    What database type are you using? e.g. MySQL, Postgres, etc?

    With server-side processing, three queries should be returned in the debug. If you run each against your database with "explain analyse" (or whatever your db variant uses for query metrics) what does it shod for the two count queries?

    Allan

  • TronikTronik Posts: 108Questions: 24Answers: 0
    edited April 1

    I use Mysql,

    When I look in the debug I see 20 queries are made (at least when searching the word "query" in the json)
    I ran a couple of them in mysql and they are all pretty fast, but I guess running all 20 of them surley adds up to a few seconds.

    Is datatables / editor also running a (count) query for each JOIN?
    I have 9 JOINS and 2 queries each would make 18, and then 2 for main table perhaps

  • allanallan Posts: 57,300Questions: 1Answers: 9,127 Site admin

    Wow 20!? Are you using SearchPanes as well, or have a whole lot of options or something? Perhaps you can show me both the controller code and the SQL statements?

    Allan

  • TronikTronik Posts: 108Questions: 24Answers: 0

    Yes sure, I've sent them in a message to you.
    Really appreciate your help

  • TronikTronik Posts: 108Questions: 24Answers: 0

    Removing searchpanes definately speeded things up, went down to approx 0,7 sec init load time

  • allanallan Posts: 57,300Questions: 1Answers: 9,127 Site admin

    Yes that would do it - in order to operate, SearchPanes needs a whole lot of queries in order to be able to display the options. You've got a lot of options as well, so there must be overhead for this. I'm not sure I really see much of an option for this - in PHp the queries are done sequentially, so we couldn't even run them in parallel like we could in Node.

    I'll have a chat with Sandy about any ideas we can come up with for this, but your best best might be to limit the SearchPanes to operate only on a few specific columns.

    Allan

  • TronikTronik Posts: 108Questions: 24Answers: 0

    Yes, I removed searchpanes and created a custom filter function instead.

Sign In or Register to comment.