在 Coldfusion 中存储和访问活动查询结果集与重新查询数据库是一个好主意吗? [英] Is it a good idea to store and access an active query resultset in Coldfusion vs re-quering the database?

问题描述

我有一个使用 Coldfusion8 和 MySQL 5.0.88 的产品搜索引擎

I have a product search engine using Coldfusion8 and MySQL 5.0.88

产品搜索有两种显示模式:Multiple View和Single View.

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

Multiple 显示基本记录信息，Single 需要从数据库中轮询其他数据.

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) 总记录和
(b) 记录 FROM 到 TO.

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

用户总是从他当前的结果集中转到单一视图，所以我的想法是为每个用户存储当前的结果集，而不必再次查询数据库来获取(浪费一个)总数记录和(浪费 b)我之前已经查询过的单个记录，然后获取单个视图仍然需要的详细信息.

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).

查询在我通过 AJAX 调用的 CFC 内的 CFINVOKED 方法中运行，因此整个查询运行，然后 CFC 和 CFINVOKE 方法被丢弃，因此我不能使用查询或 variables.cfc_storage 的查询.

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.

所以我的想法是将当前结果集存储在 Session 范围内，它将随着用户运行的每次新搜索(分页或全新搜索)而更新.存储的最大结果将是显示的结果数.

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
    ...

每次用户进行新搜索时，这都会被覆盖.但是，如果用户想查看其中一项的详细信息，我不需要查询(记录总数和获取一条记录)是否可以从我的临时存储中访问我需要的记录.这样，我将节省两次数据库行程，每次都值得 2031 执行时间来获取我之前已经提取的数据.

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.

折衷方案是每个用户在 Session.scope 中拥有最多 48 个结果(每页的最大项目数)的结果集.

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

我的问题:
1. 这是可行的还是我应该重新查询数据库?
2.如果我有一个像上面这样的结构/数组/对象，我如何通过样式编号从中挑选出我需要的记录=我如何访问结果集?我不能只循环存储的查询(现在尝试了一段时间......).

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...).

感谢您的帮助！

推荐答案

根据记录的数量，我要做的是将详细数据存储在应用程序范围内，作为以 ID 为键的结构.比如:

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

那么您实际上只需要按需查询项目的 ID.

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

为了改进按需"查询，您至少有两个代码中"选项:1.查询的查询，您查询整个项目集合一次，然后从中查询您需要的数据.2. Verity 或 SOLR 索引所有内容，然后您只需在刷新搜索集合时查询所有内容.这将比为每个查询执行所有连接要快 吨.

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.

这篇关于在 Coldfusion 中存储和访问活动查询结果集与重新查询数据库是一个好主意吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！