在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

产品搜索有两种显示模式：多视图单一检视。

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

）总记录和

（b）将记录从FROM记录到TO。

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

用户总是从当前结果集转到单一视图，因此我的想法是存储每个用户的当前结果集，而不必查询数据库再次获取（浪费a）总数的记录和（浪费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.

但是，我没有这个。

我无法缓存当前的结果集查询，因为它对每个用户（会话）是唯一的。

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

查询在CFC中的CFINVOKED方法内运行，我通过AJAX调用，所以整个查询运行，然后CFC和CFINVOKE方法被丢弃，所以我不能使用query或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.

所以我的想法是将当前结果集存储在会话范围中，这将随着用户运行（分页或全新的搜索）的每个新搜索而更新。存储的最大结果将是显示的结果数。

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.如果我有一个struture / array /对象像上面一样，我如何选择我需要的记录style number =如何访问结果集？我不能只是循环存储的查询（尝试了一会儿现在...）。

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

感谢您的帮助！

推荐答案

根据多少条记录，我要做的是将详细数据存储在应用程序作用域中，是关键。像：

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.

为了改进按需查询，您至少有两个in code选项：
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屋！