While profiling a site (~200 modules) with XHPROF noted that preprocessQuery is eating about 500ms of page load time. After reading a while, it looks as though complex regular expressions are computationally expensive.

Implementation of processed query cache yielded a result of 50ms load time. The patch is easy and should have no side efects but for eating a bit of cache.

It would be nice if someone could test performance results on other installations to see if there really is any peformance gain.

CommentFileSizeAuthor
#1 database.inc_.patch2.6 KBdavid_garcia

Comments

david_garcia’s picture

david_garcia commented
Status: Active » Needs review
StatusFileSize
new database.inc_.patch2.6 KB

Following up....

I've been working on the code since the initial post and solved several issues. Now attach proper patch.

This needs more work and testing, I found an aproximate 400ms-700ms reduction in logged users page load.

Cache can be rerouted to whatever caching mechanism you like (wincache in this example):

$conf['cache_class_cache_sqlsrv'] = 'DrupalWinCache';

* What needs to be worked on:

- Get some real benchmarks
- See how big the query cache gets (about 1MB on my tests), maybe some sort of criteria could be used to decide which queries should be cached and which not. Maybe only SELECT queries or limit the length of queries that should be cached.

david_garcia’s picture

david_garcia commented
Issue summary: View changes

remove incomplete initially posted code for cleanup

damienmckenna’s picture

damienmckenna
Location TN, USA
commented
Assigned: david_garcia » Unassigned
Category: Task » Feature request
Issue summary: View changes
Status: Needs review » Needs work

If you want others to review your code, don't leave the "assigned" field set to your name :)

At a cursory glance, this needs to be updated to match the Drupal coding standards.

damienmckenna’s picture

damienmckenna
Location TN, USA
commented

This kind of caching can also result in problems if e.g. the data being queried was modified during the current execution cycle.

  • e045655 committed on 7.x-2.x 
    Issue #2070241: Implement query processing cache.
david_garcia’s picture

david_garcia commented
Version: 7.x-1.x-dev » 7.x-2.x-dev

Commited a preview of the cache mechanism in the experimental 7.x-2.x branch of the driver.

This caches query processing and other database schema information, not query results themselves.

The cache will only work if you are using Wincache and set:

$conf['cache_class_fastcache'] = 'DrupalWinCache';

I benchmarked this a little bit to see if there were any gains, with Apache Benchamark:

+24% pages per second on a local environment with all high level caching turned off.

david_garcia’s picture

david_garcia commented
Status: Needs work » Closed (fixed)

7.x-2.x and 8.x-2.x are tightly coupled with wincache based on a more refined - and tested - version of the original proposal.