Current time: 05-26-2020, 06:02 PM Hello There, Guest! (LoginRegister)

Post Reply 
 
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database Query Search Time
08-17-2010, 11:55 AM
Post: #26
RE: Database Query Search Time
Hey Guys,

If anyone is working with MS SQL Server, I recommend creating a view like this:

Code:
SELECT     PRODUCT_ID, PRODUCT_INDEXED
FROM         dbo.PRODUCT
WHERE     (PRODUCT_INDEXED = 0)

For faster execution, the PRODUCT_LIST cfquery can be updated to this:

Code:
<cfquery datasource="#dsnName#" name="PRODUCT_LIST">
SELECT PRODUCT.PRODUCT_ID, PRODUCT.PRODUCT_PRIORITY_ID, PRODUCT.BUSINESS_ID, PRODUCT.PRODUCT_NAME, PRODUCT.PRODUCT_PRICE,
PRODUCT.PRODUCT_DESCRIPTION, PRODUCT.PRODUCT_PHOTO, PRODUCT.PRODUCT_KEYWORD1, PRODUCT.PRODUCT_KEYWORD2, PRODUCT.PRODUCT_KEYWORD3, PRODUCT.PRODUCT_KEYWORD4, PRODUCT.PRODUCT_GOOGLE_IMAGE_URL, PRODUCT.PRODUCT_VALIDATED
FROM PRODUCT
WHERE PRODUCT.PRODUCT_ID IN (SELECT TOP 5000 PRODUCT_ID_COLLECTION.PRODUCT_ID FROM PRODUCT_ID_COLLECTION)
</cfquery>

The change above allows the products that need indexed to be gathered more quickly. Without this change, performance degrades as more and more products are indexed.

To optimize a collection as suggested above, the following code can be used:

Code:
<cfcollection action="optimize" collection="#collectionName#">

I think performance suffers a little as the collection grows larger as well. Not sure if anyone else has experienced this while indexing?

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 - 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 - green-watch.org - 08-17-2010 11:55 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)