Jump to content


Redundant database queries

database performance crazy idea

  • Please log in to reply
4 replies to this topic

#1 saulimus

saulimus

    Advanced Member

  • Members
  • PipPipPip
  • 40 posts

Posted 18 October 2012 - 10:57 AM

Hi,
I was just profiling my CodeIgniter app and noticed that GroceryCrud queries the database many times for the table set with set_table():

SHOW COLUMNS FROM `some_table` (6 * 0.0150s = 0.09s)
DESCRIBE `some_table` (5 * 0.0091s = 0.0455s)
Total time taken: 0.1355s

You could get better performance if you only queried once and saved the results...
Another method would be to provide a function that could be used to give table info without querying the server. Or... how about providing a way to cache the table schema somehow; then the developer would simply update the cache each time he changes something in the database. (Maybe just daydreaming here... :))

#2 web-johnny

web-johnny

    grocery CRUD Author

  • Administrators
  • 1,150 posts
  • LocationLondon

Posted 18 October 2012 - 07:23 PM

Hello saulimus
Actually this is a good question and I am happy to answer as it is very similar to other questions like "The queries need more optimizing... ." e.t.c . The thing is that grocery CRUD version 1 was designed before 2 years and for codeigniter version 1.7.x and the actual core system didn't changed until then. I am just telling that because the first actual plan was for the developer to create his own model for each CRUD by extending the Model of grocery CRUD. However I didn't realize that no-one use the:
$crud->set_model();
method of grocery CRUD. For more you can check an example at http://www.grocerycr...ample-included/ . The problem with the set_model is that it is not so "readable" as the user expect. So I tried to improved the queries as much as possible to not use the set_model anymore. But this is not the case if you want to optimize your queries. If you want to optimize as much as you can the queries you have to do it with the custom model and with the set_model method. So your CRUD will be as simple as before with one more method. For example:
$crud->set_model('GCRUD_Some_Table');
So there you can add as many rules as you like as it is only one file to actual change and without actually changing the Core code of grocery CRUD. You can even have caching rules. For example you can cache the data and the total number of records and delete them after an insert/update or delete. Simple idea right? ;) You can add it to your custom model as well.

Lastly the caching idea is not a dream. I am designing grocery CRUD 2 with much more powerful tools and with best practices at coding to be available for all the frameworks and much more extendable for caching. It will be really late though as I started again from scratch and I don't even know if I will have the time to finish it. Yea tough I know but it will be just an awesome, crazy new idea for all the frameworks.

So yes for now I am trying to do my best to optimize the queries but still it need to have a bit of different architecture to be even more optimized.

Cheers
Johnny
Posted Image

#3 xxaxxo

xxaxxo

    grocery CRUD Expert

  • Members
  • PipPipPip
  • 71 posts
  • LocationBulgaria

Posted 19 October 2012 - 05:21 AM

Great news, thanks again johnny, I have a question about grocery CRUD 2 ... what do you mean available for all the frameworks? Do you consider making it PSR-0 and available as a composer package (this would be nice).

#4 web-johnny

web-johnny

    grocery CRUD Author

  • Administrators
  • 1,150 posts
  • LocationLondon

Posted 19 October 2012 - 07:24 AM

Well there are actually two things in my mind. Probably I will make it as a composer package with the help of packagist ( https://packagist.org/ ) or to create my own builder with bash commands. I think the composer is better cause it will make our life just easier... for everyone. Well I will think about it, not decided yet.
Posted Image

#5 xxaxxo

xxaxxo

    grocery CRUD Expert

  • Members
  • PipPipPip
  • 71 posts
  • LocationBulgaria

Posted 20 October 2012 - 05:38 AM

great , if you need any help with coding drop me a line , I'm following you on github also ...





Also tagged with one or more of these keywords: database, performance, crazy idea

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users