Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Aug 2007
    Posts
    41
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Database Query Timeouts

    Hey there,

    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.

    Sincerely,
    Travis Walters

  • #2
    New to the CF scene
    Join Date
    Feb 2010
    Location
    Williamsburg, VA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Speeding this up depends on a few things..

    What is the data type of the field you are searching? (text, varchar..)

    Is it indexed?

    What type of a query are you doing to search? Something like "column like '%search-keyword%'" or full text search or ...?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •