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