Current time: 01-22-2021, 11:58 PM Hello There, Guest! (LoginRegister)

Post Reply 
 
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database Query Search Time
09-20-2010, 04:39 AM (This post was last modified: 09-20-2010 04:40 AM by green-watch.org.)
Post: #49
RE: Database Query Search Time
Hey Guys,

I have taken a step back from the SOLR implementation to work on the scripts leading up to the indexing of the products. These scripts include imported zip files from Google, unzipping those files and creating product csv files, importing the csv files into a database table, processing that information into the main product table, and then calculating the greenness of the products. My ultimate goal is to get all of these scripts working in such a way that products are updated at least once a week.

If anyone has to import anything containing millions and millions of records, you may be better off doing a bulk insert through a csv file like this:

Code:
<cfquery datasource="#dsnName#">
BULK INSERT PRODUCT_GOOGLE
FROM 'F:\products\feeds\unzipped\testCSV.csv'
WITH
(
FIELDTERMINATOR='#Chr(9)#',
ROWTERMINATOR='\n'
)
</cfquery>

On another, note my greenness script seems to be getting slower as more and more products are updated. I think this is due to this:

Code:
<cfquery datasource="#dsnName#" maxrows="1">
UPDATE PRODUCT_GREENNESS
SET PRODUCT_GREENNESS.PRODUCT_HTML_PENALTY_ENABLED = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="#invalidCharsFound#">,
PRODUCT_GREENNESS.PRODUCT_GREENNESS_MODIFIER = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="#PRODUCT_GREENNESS#">,
PRODUCT_GREENNESS.PRODUCT_GREENNESS_UPDATED = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="1">,
PRODUCT_GREENNESS.PRODUCT_DESCRIPTION_LENGTH =
<cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="#Len(PRODUCT_INFO.PRODUCT_DESCRIPTION)#">
WHERE PRODUCT_GREENNESS.PRODUCT_ID = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="#PRODUCT_ID#">
</cfquery>

PRODUCT_GREENNESS is a view for my PRODUCT table that displays rows when GREENNESS_UPDATED equals 0. Changes in the PRODUCT_GREENNESS table reflect changes in the PRODUCT table. Since the record I want to edit gets farther from the top as the script executes, it is taking longer each time.

Is there anyway with SQL that I can say update where this = this and start at some row number for searching? By the way, I am using MS SQL Server 2005 as my database.

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 - 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
RE: Database Query Search Time - green-watch.org - 09-20-2010 04:39 AM

Forum Jump:


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