0

I have a product search engine using Coldfusion8 and MySQL 5.0.88

The product search has two display modes: Multiple View and Single View.

Multiple displays basic record info, Single requires additional data to be polled from the database.

Right now a user does a search and I'm polling the database for

(a) total records and
(b) records FROM to TO.

The user always goes to Single view from his current resultset, so my idea was to store the current resultset for each user and not have to query the database again to get (waste a) overall number of records and (waste b) a the single record I already queried before AND then getting the detail information I still need for the Single view.

However, I'm getting nowhere with this.

I cannot cache the current resultset-query, because it's unique to each user(session).

The queries are running inside a CFINVOKED method inside a CFC I'm calling through AJAX, so the whole query runs and afterwards the CFC and CFINVOKE method are discarded, so I can't use query of query or variables.cfc_storage.

So my idea was to store the current resultset in the Session scope, which will be updated with every new search, the user runs (either pagination or completely new search). The maximum results stored will be the number of results displayed.

I can store the query allright, using:

 <cfset Session.resultset = query_name>

This stores the whole query with results, like so: 

query
CACHED: false 
EXECUTIONTIME: 2031 
SQL: SELECT a.*, p.ek, p.vk, p.x, p.y
    FROM arts a
        LEFT JOIN p ON 
        ...
        LEFT JOIN f ON 
        ... 
        WHERE a.aktiv = "ja"
        AND 
        ... 20 conditions ...

SQLPARAMETERS: [array]
1) ... 20+ parameters

RESULTSET: 
 [Record # 1] 
    a: true
    style: 402
    price: 2.3
    currency: CHF
    ...
 [Record # 2] 
    a: true
    style: 402abc
    ...

This would be overwritten every time a user does a new search. However, if a user wants to see the details of one of these items, I don't need to query (total number of records & get one record) if I can access the record I need from my temp storage. This way I would save two database trips worth 2031 execution time each to get data which I already pulled before.

The tradeoff would be every user having a resultset of up to 48 results (max number of items per page) in Session.scope.

My questions:
1. Is this feasable or should I requery the database?
2. If I have a struture/array/object like a the above, how do I pick the record I need out of it by style number = how do I access the resultset? I can't just loop over the stored query (tried this for a while now...).

Thanks for help!

frequent
  • 27,643
  • 59
  • 181
  • 333

3 Answers3

4

KISS rule. Just re-query the database unless you find the performance is really an issue. With the correct index, it should scales pretty well. When the it is an issue, you can simply add query cache there.

QoQ would introduce overhead (on the CF side, memory & computation), and might return stale data (where the query in session is older than the one on DB). I only use QoQ when the same query is used on the same view, but not throughout a Session time span.

Henry
  • 32,689
  • 19
  • 120
  • 221
  • 1
    Agree. Keep it simple unless the single queries are actually a performance issue. Use CF's built-in query caching and parameter binding (cfqueryparam) on the id. – Jared Beck Jul 13 '12 at 19:38
  • I like KISS... right now I'm re-querying, too (and skipping the total no of records query). So far it's ok, but queries I shouldn't need, I shouldn't do. – frequent Jul 13 '12 at 21:21
2

Feasible? Yes, depending on how many users and how much data this stores in memory, it's probably much better than going to the DB again.

It seems like the best way to get the single record you want is a query of query. In CF you can create another query that uses an existing query as it's data source. It would look like this:

<cfquery name="subQuery" dbtype="query">
  SELECT *
  FROM  Session.resultset
  WHERE style = #SelectedStyleVariable#
</cfquery>

note that if you are using CFBuilder, it will probably scream Error at you for not having a datasource, this is a bug in CFBuilder, you are not required to have a datasource if your DBType is "query"

invertedSpear
  • 10,864
  • 5
  • 39
  • 77
  • I tried using QoQ, but since my initial query is inside a function invoked with cfinvoke I'm always starting from scrath and cannot query the previous query since it was discarded "along with the cfinvoke". That's why I wondered about storing query results in Session scope – frequent Jul 13 '12 at 21:23
1

Depending on how many records, what I would do is have the detail data stored in application scope as a structure where the ID is the key. Something like:

APPLICATION.products[product_id].product_name
                                .product_price
                                .product_attribute

Then you would really only need to query for the ID of the item on demand.

And to improve the "on demand" query, you have at least two "in code" options: 1. A query of query, where you query the entire collection of items once, and then query from that for the data you need. 2. Verity or SOLR to index everything and then you'd only have to query for everything when refreshing your search collection. That would be tons faster than doing all the joins for every single query.

Sharondio
  • 2,605
  • 13
  • 16
  • I never really looked at Verity and Solr (yet). I'm happy I indexed my way through the database and it's more or less working. I'm not sure I want to load the data into application scope. Just too many records. I was thinking every user having up to 24 records in his Sessions."evoked set". When the user does a search I store 24. New searches or pagination replace 24. Hitting details uses x from 24 to load the detail view. I will give this a try using your syntax. So far thanks! – frequent Jul 13 '12 at 21:26