Current time: 08-13-2020, 10:55 AM Hello There, Guest! (LoginRegister)

Post Reply 
 
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database Query Search Time
07-04-2010, 01:10 PM
Post: #4
RE: Database Query Search Time
Hey Guys,

I am using MS SQL Server 2005 as my DBMS.

You can see the structure for this table here:
http://www.green-watch.org/temp/ProductT...ucture.png

I created a view to run queries against. That structure is here:
http://www.green-watch.org/temp/ProductTableView.png

On the PRODUCT table, I only have the PRODUCT_ID as the primary index.

On the PRODUCT_GREENEST_VIEW table, I have the following indexes:
PRODUCT_PRIORITY_ID (clustered)
BUSINESS_ID (non-unique, non-clustered)
PRODUCT_ID (unique, non-clustered)

As far as the query goes, here is part of the code:

Code:
<cfif #timeoutOccurred# EQ 0>

  <cftry>

    <cfset innerSelectRows = #currentPage# * 10>

    <cfif #currentPage# EQ #maxPage#>
      <cfset selectRows = #MAX_PRODUCT# - (#innerSelectRows#-10)>
    <cfelse>
      <cfset selectRows = 10>
    </cfif>
        
    <cfquery datasource="#dsnName#" name="PRODUCT_LIST" timeout="#searchTimeout#">
    SELECT * FROM (
        SELECT TOP #selectRows# * FROM (
            SELECT DISTINCT TOP #innerSelectRows# #URL.TABLE_NAME#.PRODUCT_ID, #URL.TABLE_NAME#.PRODUCT_PRICE, #URL.TABLE_NAME#.PRODUCT_PRIORITY_ID
            FROM #URL.TABLE_NAME#
            
            LEFT JOIN ASSIGN_SUBCAT_PRODUCT ON ASSIGN_SUBCAT_PRODUCT.PRODUCT_ID = #URL.TABLE_NAME#.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 #URL.TABLE_NAME#.PRODUCT_VALIDATED = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="1">
    
            <cfif #URL.businessID# NEQ -1>
            AND #URL.TABLE_NAME#.BUSINESS_ID = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="#URL.businessID#">
            </cfif>
    
            <cfif #URL.MIN_PRICE# NEQ -1>
            AND #URL.TABLE_NAME#.PRODUCT_PRICE >= <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="#URL.MIN_PRICE#">
            </cfif>
            
            <cfif #URL.MAX_PRICE# NEQ -1>
            AND #URL.TABLE_NAME#.PRODUCT_PRICE <= <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="#URL.MAX_PRICE#">
            </cfif>

            <cfif #URL.SEARCH_FOR# NEQ "">
            AND (CONTAINS(#URL.TABLE_NAME#.PRODUCT_NAME, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">)
            OR CONTAINS(#URL.TABLE_NAME#.PRODUCT_DESCRIPTION, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">)
            OR CONTAINS(#URL.TABLE_NAME#.PRODUCT_KEYWORD1, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">)
            OR CONTAINS(#URL.TABLE_NAME#.PRODUCT_KEYWORD2, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">)
            OR CONTAINS(#URL.TABLE_NAME#.PRODUCT_KEYWORD3, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">)
            OR CONTAINS(#URL.TABLE_NAME#.PRODUCT_KEYWORD4, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#URL.SEARCH_FOR#">))
            </cfif>
    
            <cfif #URL.CAT_ID# NEQ -1>
            AND PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID IN (<cfqueryparam value="#URL.CAT_ID#" list="true" cfsqltype="CF_SQL_NUMERIC">)
            </cfif>
        
            <cfif #URL.SUBCAT_ID# NEQ -1>
            AND PRODUCT_SUBCATEGORY.PRODUCT_SUBCATEGORY_ID IN (<cfqueryparam value="#URL.SUBCAT_ID#" list="true" cfsqltype="CF_SQL_NUMERIC">)
            </cfif>
            
            AND (#URL.TABLE_NAME#.PRODUCT_PHOTO IS NOT NULL OR #URL.TABLE_NAME#.PRODUCT_GOOGLE_IMAGE_URL IS NOT NULL)
        
            ORDER BY #URL.ORDER_BY2#) AS FOO
        ORDER BY #URL.ORDER_BY3#) AS BAR
    ORDER BY #URL.ORDER_BY2#
    </cfquery>
    
    <cfcatch>

      <cfset maxPage = 0>
      <cfset URL.END_ROW = 0>
      <cfset timeoutOccurred = 1>
    
    </cfcatch>
    
  </cftry>
  
</cfif>

There is code before this that gets the max rows, sets the correct variables, checks input against sql injection, etc.

I am using a full-text index for the SEARCH_FOR field.

I am not familiar with SOLR. I am on a Windows Server currently.

Sincerely,
Travis Walters
Find all posts by this user
Quote this message in a reply
Post Reply 


Messages In This Thread
RE: Database Query Search Time - pmeenan - 07-04-2010, 12:03 PM
RE: Database Query Search Time - green-watch.org - 07-04-2010 01:10 PM
RE: Database Query Search Time - pmeenan - 07-05-2010, 04:35 AM
RE: Database Query Search Time - sajal - 07-05-2010, 05:03 AM
RE: Database Query Search Time - pmeenan - 07-08-2010, 08:47 PM
RE: Database Query Search Time - sajal - 08-13-2010, 10:06 PM
RE: Database Query Search Time - sajal - 08-14-2010, 03:38 AM
RE: Database Query Search Time - sajal - 08-14-2010, 06:50 AM
RE: Database Query Search Time - pmeenan - 08-16-2010, 02:55 AM
RE: Database Query Search Time - sajal - 08-17-2010, 01:14 AM
RE: Database Query Search Time - pmeenan - 08-23-2010, 01:22 AM
RE: Database Query Search Time - pmeenan - 08-23-2010, 08:45 PM
RE: Database Query Search Time - ringman57 - 08-24-2010, 11:52 PM
RE: Database Query Search Time - ringman57 - 08-25-2010, 02:54 AM

Forum Jump:


User(s) browsing this thread: 1 Guest(s)