Welcome Guest, Not a member yet? Register   Sign In
Possibly not so simple question about the Database Caching Class.
#1

[eluser]SpYk3[/eluser]
I'll try to explain this as simple as possible. The question, I'll prose at the end as the filler is important.

Let me first say, I've used CI for years and love it. If I'm ever doing anything in PHP, I see no reason not to use it and it's very expandable. So much so, I have an "Extension" to the "Load" class that allows for the following:

Code:
$this->load->master('pageBody');

As you might guys, this loads a header and footer automajically with whatever "view" I pull loaded in the <body>. It's a very handy feature. If you like the idea, I think I posted the code somewhere on here a long time ago, look around, it's really easy to do.

That being said, I also have 2 "backbone" modals that are of GREAT importance to the whole CRM (the "site" I manage for this company's Customer Service Reps). 1 Backbone is loaded on virtually every single page/widget (widget being smaller views of specific info loaded into one larger one). The second "backbone" is only loaded on specific views and "feeds" off of the first. The second one can never be "cached" as it may be updated at any moment, whereas the first simply pulls "customer links" (identities, since we don't make customers log in, we use things like past email provided on an order or phone number, etc...).

So I've looked into using Database Caching manually:

Code:
$this->db->cache_on(); $this->load->master('page'); $this->db->cache_off();

However, I can't tell if i'm using it correctly or if it is even making a difference? My natural assumption was that, the first "Backbone" piece would "cache" after it's first call (usually on header widget), and thus not hit Database during loading of following widgets on page. But as I stated, I'm not sure it quite functions as I expect and I really can't tell whether it's doing anything or not. Our "server" is currently undergoing a lot of changes and people are constantly hitting the DB with this "call", thus it's really hard for me to filter db logs to check.

Can someone help me better understand the caching class and how I might make better use of it in my situation?
#2

[eluser]Alucemet[/eluser]
You will not likely see any performance increase using DB caching on your dev environment, and you will not likely see any performance increase for simple queries on tables that are indexed and have a small amount of rows. If you use some memory usage tests, you will see slightly less memory used when retrieving the cached data. On the production environment you can save some memory and have faster pageloads if used right, but keep in mind that caching queries means the query can't be dynamic, which in my opinion makes the benefits of caching almost useless in many cases.
#3

[eluser]SpYk3[/eluser]
Yeah, non "dynamic" is why I can't use it on "backbone #2", however, backbone one only changes whenever the URI Query String Changes, thus page is loaded anew and thus caching would start over again, as I have written above. Or at least that's how I think it's working?

However, using it on Backbone #1 would be extremely useful as it makes a VERY long mysql query that involves several "UNION" pieces, thus the reason I'd really like to have it cache per page load. As it stands, each widget (the main page has 7 widgets!) must run this same query, and this can sometimes make page loads take as long as 33 seconds. I'm really trying to get the page load time down as much as possible, and if I can reuse BackBone #1's results without needing to remake the call or add some crazy "if" statement to each and every widget it would be quite wonderful and I'm sure it would make quite a difference in load time. Though as you've pointed out, I've yet to implement it in production as I wasn't sure it was working or if I'm even using it correctly.

I guess the big question here is, what should I expect from the Database Caching Class? Am I interpreting its use correctly?
#4

[eluser]Alucemet[/eluser]
Db caching simply stores the result in a file, which is accessed quicker on subsequent page loads. So it sounds like you are using it correctly, but my concern would be your database design, proper indexing, and optimized queries. It's hard to imagine that you would have a 33 second query and there not be something that could be improved there.
#5

[eluser]SpYk3[/eluser]
Total page load on the largest page is 33 seconds. It has to make the same exact DB call 7 times on that page. The problem is, I have 0 control on DB design. It was implemented years before I started here, and while I do have a future plan to rebuild it, the boss isn't ready for such a major change. As it stands, the main Backbone piece hitting it is querying several different tables for all possible "linked" references containing the same emails and phone numbers from the same customer. So of course the SQL statement is quite long, but as I've worked with it for 8 months now, I have it as short as it can possibly be (still about 748+- chars). This setup was initialized, as I said, long ago when the boss decided he didn't want to make customers "log in", but still wanted to have quick access to anything about them based on previous orders, quotes, and etcetera.

Believe you, me, if I could rewrite this DB now, I totally would, but that is out of my hands, and as most of you probably see in your own work, I have to "make it work" with what is provided. The only thing I have "total" control over is the site itself. Even in that, I don't get total server control, as he has many other things running on the server and has it all tied together (hoping to move away from this soon), so, i'm limited to just the PHP at the end of the day.
#6

[eluser]CroNiX[/eluser]
Have you implemented a slow query log to see where the bottlenecks are for queries taking a long time? Adding indexes on all fields that you
1) Search on (WHERE)
2) ORDER BY
3) JOIN on
is pretty trivial and can tremendously speed things up.
#7

[eluser]SpYk3[/eluser]
LoL, how do you think I got it as condensed as I have? I know 700+ chars doesn't sound small, but what I walked into was originally about a 1800+ char statement!
#8

[eluser]CroNiX[/eluser]
I wouldn't know; you didn't say.




Theme © iAndrew 2016 - Forum software by © MyBB