Optimalize database queries when creating links

Morfeo21

Hi,
in my application I use my own router. In template I'm creating links like this:
href="{plink Product:, producerId => $producer->id}", so it goes to constructUrl method in my router and send query to DB:

SELECT `id`, `url` FROM `url` WHERE (`type` = 'producer' AND `key` = 18)

Everything works, but problem is that I have over 100 links on a single page, so that means over 100 queries for URLs. And the number of links will only raise :)

Do you guys have any tip on how to optimalize that? I have a thought to collect the IDs and instead of correct URL I'd send some hash and on the very end of script I'd ask database to return all the URLs in one query and replaced all hashes. But I have no idea if and where I can get all HTML code of generated template and replace the hashes.

Is it completle wrong and is there any simple solution for that?

Thanks in advance!

JakubJarabica

Currently I am solving the same problem.

I have custom router that gets some entity ID and lookups for current URL of that entity. When I create key ⇒ value array of id ⇒ url, it might get big(heavily increasing memory usage of script and transferring lots of data from DB on every request) while having only one SQL query(good part).

On the other hand sometimes I have single pages where pagination is disabled(valid use case, someone(Googlebot) wants to see all items on every page without infinite scrolling) and it creates hundred of SQL queries(same as you currently have).

I am thinking of some hybrid solution – pass some memcache/redis to router and lookup there just by ID(not once, but N times). Not sure how efficient will be querying various counts of items from cache rather than DB, as on the other hand it will cost me creating some application logic that will handle syncing between DB ↔ cache.

Thinking of this again – it will be nice to query DB/cache just once just for the keys I want on current request (like NDB/Notorm does with IN operator) – but dunno whether it's possible to lazy collect IDs in router and send just one query in the end(and possibly no query if whole content is wrapped in cache macro). This will be the best.

Any practical experiences are welcomed!

petr.pavel

@Morfeo21: How do you fetch $producer?
@JAM3SoN: Same answer. :-)

Let's take an example (syntax for NotORM):

$this->template->products = $db->products()->where('category', 1);

{foreach $products as $product}
  <a n:href="Producer: $product->producer->id">link</a>
  {* $product['producer_id'] would do but it wouldn't demonstrate my point *}
{/foreach}

Here, the query into producers will only be for id = producer_id values present in query result $products. Not all producers.

So you guys have to use this mechanism to query only for producers that you really need on the page.

If you need all of them and it's still too much, then use cache. Learning how to use Nette cache is worth the effort. You'll find many uses for it, it's golden.

JakubJarabica

That's exactly what I wrote about and fully understand that principle – but HOW do I use that “mechanism” in routing? :)

I want my routes to be like {plink Product: $productId}, but on the background to stack somehow Router::constructUrl calls, so they will be executed a) lazy to make use of cache macro b) to create just one query to DB/cache obtaining only needed rows. I know what I want in theory, but got no clue how to implement it(and whether it is possible).

petr.pavel

@JAM3SoN: What's “in routing”?
You pass id to your router, so the query/queries are actually executed outside the router, right? In a template or in a model. You don't have to query db in the router because you've already been given all information.

Please give me some code, so that we can get specific.