Speed up load times - Count queries bottleneck?
Speed up load times - Count queries bottleneck?
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
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
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
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
Yes sure, I've sent them in a message to you.
Really appreciate your help
Removing searchpanes definately speeded things up, went down to approx 0,7 sec init load time
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
Yes, I removed searchpanes and created a custom filter function instead.