Caching/searching large datasets

Communities ColdBox HMVC
1

Hey all,
so we have a search page with anywhere from 5-15 different potential filter fields, such as start date and end date, project type, state, etc. For one project type there will be over 1 million records to search through. Right now we have one big query that is being run with all the search criteria in the where clause of the query surrounded by cfif stmts. I am trying to find the best way to speed up the query (which joins about 4 tables) using Coldbox 2.3.6, or just coldfusion. we are only displaying a max of 1000 records from that recordset and displaying the data in a jqgrid.
I thought about caching the main query, then running query of query on that cached query to filter the data (will this suck up too much memory). I have discussed creating a subquery in the where clause to filter down the records to the 1000 max. Friends said use verity/solr search collections to helps. Also, trying to just fix the current query.
I wanted to get some feedback to see if there was any caching features that would work in coldbox to help me, or any suggestions on the best approach to run a big query that must scan over a million records and return back a recordset no bigger than a 1000 rows…and i need to work fast, or faster than 1 minute :slight_smile:

I know I have not presented the query, but I am really looking for info on how others have used either coldbox, oracle, or just coldfusion to deal with search large data.

Any thought would be greatly appreciated, just not “you should upgrade coldbox”, I know that, but it can’t be done right now no matter how much we need to.

thanks
Dan

2

Dan,

If you are using Oracle I would first recommend that you create a materialized view of your data so you can have that provided by Oracle. That’s the first thing I would do. This will speed things up as a materialized view lives in the DB.

Second, I would create a stored procedure in Oracle to further accelerate the query and have the explain plans optimized by the DB.

Then I would execute the stored procedure from ColdFusion.

Again, without knowing more about it, I would rely on that approach first.

Luis F. Majano
CEO
Ortus Solutions, Corp
www.ortussolutions.com

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

Social: twitter.com/lmajano facebook.com/lmajano

3

Thanks Luis, i was not sure of a good route, but thought a stored proc might work best…my co-worker thought maybe adding a rownum < 1000 would speed things up since we are not going to show anymore than a 1000 records for our search results.
i’ll pass this info on to my coworkers.

thanks
Dan