I have a database with about 2.5 million products in one table. By the time all the products are imported, I will have about 60 million products.
Let's say a customer searches products for "tires" and let's say there are 150,000 entries for tire products. I am retrieving the TOP 1000 tire results to speed up execution time. This works great especially when there are 1000 tire products near the beginning of the table entries.
If I have 400 tire results and want the TOP 1000, the query will search the entire database which can take some time. Execution is not even close to where it should be in this case.
I have a few questions:
#1 - Is there a way to set a time out in a query so that a query searches the database for so many seconds and then returns with whatever results it finds? The problem I see with this is if all the "tire" results are near the end of the database table, then results would not return because the query would never get to that point in the search.
#2 - I am using MS SQL Server 2005 and Coldfusion. Is there a way to make text searches more efficient? There has to be something I am missing?
Thanks in advance for any advice and suggestions.