Database Query Timeout

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

 

Can you explain how you are searching. Is the user selecting pre-defined fields, or is it completely user entered where you're trying to match text within a column?

 

Hey there,

I was trying to do something like this:

<cfquery datasource="#dsnName#" name="PHOTO_LIST" timeout="#searchTimeout#">
SELECT PRODUCT_ID AS PRODUCT_PHOTO_ID FROM (
  SELECT TOP #selectRows# PRODUCT_ID FROM (
    SELECT TOP #innerSelectRows# PRODUCT.PRODUCT_ID
    FROM PRODUCT
            
    LEFT JOIN ASSIGN_SUBCAT_PRODUCT ON ASSIGN_SUBCAT_PRODUCT.PRODUCT_ID = PRODUCT.PRODUCT_ID
    LEFT JOIN PRODUCT_SUBCATEGORY ON PRODUCT_SUBCATEGORY.PRODUCT_SUBCATEGORY_ID = ASSIGN_SUBCAT_PRODUCT.PRODUCT_SUBCATEGORY_ID
    LEFT JOIN PRODUCT_CATEGORY ON PRODUCT_SUBCATEGORY.PRODUCT_CATEGORY_ID = PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID        
            
    WHERE PRODUCT_VALIDATED = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="1">
        
    <cfif #URL.SEARCH_FOR# NEQ "">
    AND (PRODUCT.PRODUCT_NAME LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#URL.SEARCH_FOR#%">
    OR PRODUCT.PRODUCT_DESCRIPTION LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#URL.SEARCH_FOR#%">
    OR PRODUCT.PRODUCT_KEYWORD1 LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#URL.SEARCH_FOR#%">
    OR PRODUCT.PRODUCT_KEYWORD2 LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#URL.SEARCH_FOR#%">
    OR PRODUCT.PRODUCT_KEYWORD3 LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#URL.SEARCH_FOR#%">
    OR PRODUCT.PRODUCT_KEYWORD4 LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#URL.SEARCH_FOR#%">)
    </cfif>
    
    ORDER BY PRODUCT.PRODUCT_ID ASC) AS FOO
  ORDER BY PRODUCT_ID DESC) AS BAR
ORDER BY PRODUCT_ID ASC
</cfquery>

Code (markup):

To answer your question, the user can select pre-defined categories and subcategories to narrow the search results. This is extremely efficient because I have keys setup in the database to handle this. The part that is inefficient is the SEARCH_FOR part of the query.

I did a little research earlier today after I wrote my post and found that SQL server has full-text searching. I am going to see if I can get that working and see if the query is more efficient.

Sincerely,
Travis Walters

 

Hey there,

From what I have seen with full-text searching, searches are much faster now especially on subsequent calls using the same query parameters.

The only concern I have is the first time an item is looked up, the user may navigate away from the webpage because it takes awhile to load in some cases. I suppose I could show one of those AJAX wheels spinning in the middle of the page that says please wait while the search completes.

<cfquery datasource="#dsnName#">
SELECT TOP 1000 PRODUCT.PRODUCT_ID
FROM PRODUCT
WHERE 1=1

<cfif #URL.SEARCH_FOR# NEQ "">
AND (CONTAINS(PRODUCT.PRODUCT_NAME, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">)
OR CONTAINS(PRODUCT.PRODUCT_DESCRIPTION, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">)
OR CONTAINS(PRODUCT.PRODUCT_KEYWORD1, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">)
OR CONTAINS(PRODUCT.PRODUCT_KEYWORD2, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">)
OR CONTAINS(PRODUCT.PRODUCT_KEYWORD3, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">)
OR CONTAINS(PRODUCT.PRODUCT_KEYWORD4, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">))
</cfif>
            
</cfquery>

Code (markup):

Would love to hear from others on how they expediated full-text searches?

Sincerely,
Travis Walters

 

Hey there,

Consider this matter resolved.

The solution was to use "full-text searching" in MS SQL Server. This reduced my execution time by 3 to 5 minutes in some cases. Now it only takes about 10 seconds maximum to search for a product while other product results return instantaneously! Best part though is subsequent searches are instant.

Sincerely,
Travis Walters