Re: Updating 1000s of records
Access or SQL? - Original Message - From: "Michael Lugassy" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Sunday, April 22, 2001 3:17 AM Subject: Updating 1000s of records > Our application is built like that so I'm reciving a daily .txt file with 1000s of lines. each line > with a unique number that is userID. userID that is on this text file means he visited our site > in the last 24 hours (complex, I know.. bear with me) > > I'm trying to find the best way to update 1000s rows of SQL 7 DB Table: USERS. > with this information so that Colum LASTVISITEDDATE will have now() value for each > person that was on that list. > > Any brighter ideas then > > loop list > UPDATE WHERE userID='x > end loop > > OR > > UPDATE WHERE userID='x' OR userID='y' OR userID='n...' > > > Thanks, > > Michael Lugassy > Interactive Music Ltd. > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Updating 1000s of records
SQL 7 - Original Message - From: "Brian Thornton" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Sunday, April 22, 2001 11:41 AM Subject: Re: Updating 1000s of records > Access or SQL? > - Original Message - > From: "Michael Lugassy" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Sunday, April 22, 2001 3:17 AM > Subject: Updating 1000s of records > > > > Our application is built like that so I'm reciving a daily .txt file with > 1000s of lines. each line > > with a unique number that is userID. userID that is on this text file > means he visited our site > > in the last 24 hours (complex, I know.. bear with me) > > > > I'm trying to find the best way to update 1000s rows of SQL 7 DB Table: > USERS. > > with this information so that Colum LASTVISITEDDATE will have now() value > for each > > person that was on that list. > > > > Any brighter ideas then > > > > loop list > > UPDATE WHERE userID='x > > end loop > > > > OR > > > > UPDATE WHERE userID='x' OR userID='y' OR userID='n...' > > > > > > Thanks, > > > > Michael Lugassy > > Interactive Music Ltd. > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Updating 1000s of records
you could add a SQL server scheduled task to bulk insert (see BULK INSERT command in SQL server docs) into a temporary table from your text file and then use the data in the temp table to update the user info. Just moves the processing from CF onto SQL server, which will definitely improve performance. Even if you run both the BULK INSERT and UPDATE commands from a CF template, it'll still improve your performance considerably (only two database connections and queries now). Mark At 12:10 PM 22/04/2001, you wrote: >SQL 7 > >- Original Message - >From: "Brian Thornton" <[EMAIL PROTECTED]> >To: "CF-Talk" <[EMAIL PROTECTED]> >Sent: Sunday, April 22, 2001 11:41 AM >Subject: Re: Updating 1000s of records > > > > Access or SQL? > > - Original Message - > > From: "Michael Lugassy" <[EMAIL PROTECTED]> > > To: "CF-Talk" <[EMAIL PROTECTED]> > > Sent: Sunday, April 22, 2001 3:17 AM > > Subject: Updating 1000s of records > > > > > > > Our application is built like that so I'm reciving a daily .txt file >with > > 1000s of lines. each line > > > with a unique number that is userID. userID that is on this text file > > means he visited our site > > > in the last 24 hours (complex, I know.. bear with me) > > > > > > I'm trying to find the best way to update 1000s rows of SQL 7 DB Table: > > USERS. > > > with this information so that Colum LASTVISITEDDATE will have now() >value > > for each > > > person that was on that list. > > > > > > Any brighter ideas then > > > > > > loop list > > > UPDATE WHERE userID='x > > > end loop > > > > > > OR > > > > > > UPDATE WHERE userID='x' OR userID='y' OR userID='n...' > > > > > > > > > Thanks, > > > > > > Michael Lugassy > > > Interactive Music Ltd. > > > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Updating 1000s of records
Isn't this information already stored under CGLOBALS as a client variable? -Original Message- From: Michael Lugassy [mailto:[EMAIL PROTECTED]] Sent: Sunday, 22 April 2001 20:18 To: CF-Talk Subject: Updating 1000s of records Our application is built like that so I'm reciving a daily .txt file with 1000s of lines. each line with a unique number that is userID. userID that is on this text file means he visited our site in the last 24 hours (complex, I know.. bear with me) I'm trying to find the best way to update 1000s rows of SQL 7 DB Table: USERS. with this information so that Colum LASTVISITEDDATE will have now() value for each person that was on that list. Any brighter ideas then .loop list UPDATE WHERE userID='x end loop OR UPDATE WHERE userID='x' OR userID='y' OR userID='n...' Thanks, Michael Lugassy Interactive Music Ltd. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Updating 1000s of records
Didn't quite understand what you mean. The .txt file was created by an external COM object on a remote server. I got the file to calculate stats on a diffrent machine too. - Original Message - From: "Peter Tilbrook" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Sunday, April 22, 2001 12:56 PM Subject: RE: Updating 1000s of records > Isn't this information already stored under CGLOBALS as a client variable? > > > -Original Message- > From: Michael Lugassy [mailto:[EMAIL PROTECTED]] > Sent: Sunday, 22 April 2001 20:18 > To: CF-Talk > Subject: Updating 1000s of records > > > Our application is built like that so I'm reciving a daily .txt file with > 1000s of lines. each line > with a unique number that is userID. userID that is on this text file means > he visited our site > in the last 24 hours (complex, I know.. bear with me) > > I'm trying to find the best way to update 1000s rows of SQL 7 DB Table: > USERS. > with this information so that Colum LASTVISITEDDATE will have now() value > for each > person that was on that list. > > Any brighter ideas then > > .loop list > UPDATE WHERE userID='x > end loop > > OR > > UPDATE WHERE userID='x' OR userID='y' OR userID='n...' > > > Thanks, > > Michael Lugassy > Interactive Music Ltd. > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
