Outside
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:kpeterson@;sehinc.com]
> Sent: Monday, October 21, 2002 3:03 PM
> To: CF-Talk
> Subject: RE: Really big file manipulation
>
> When you are looping through and doing your inserts is the loop inside
the
> cfquery tag or outside?
>
> Kore Peterson
> Database Development Specialist
> SEH - Minneapolis
> 612.758.6739
>
>
>
>
>
> "Bill
> Henderson" To: CF-Talk <cf-
> [EMAIL PROTECTED]>
> <cf-talk@uutil cc:
> ity.com> Subject: RE: Really big
file
> manipulation
>
> 10/21/2002
> 04:32 PM
> Please respond
> to cf-talk
>
>
>
>
>
>
> As far as I can tell, if I am relying on SQL (as opposed to CF) for
the
> bulk of the work, the insert into the temp table seems to be the hog.
I
> am using cfflush to send progress reports to the browser, and when it
> hits the inserts it starts to bog down. The memory spike takes place
> somewhere between the 6,000th and 12,000th record, and stays spiked
> until I restart the MSSQLSERVER service.
>
> Bill Henderson
> [EMAIL PROTECTED]
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:kpeterson@;sehinc.com]
> > Sent: Monday, October 21, 2002 1:52 PM
> > To: CF-Talk
> > Subject: RE: Really big file manipulation
> >
> > Do you know which phase of the process is eating the memory?
> >
> > Kore Peterson
> > Database Development Specialist
> > SEH - Minneapolis
> > 612.758.6739
> >
> >
> >
> >
> >
> > "Bill
> > Henderson" To: CF-Talk <cf-
> > [EMAIL PROTECTED]>
> > <cf-talk@uutil cc:
> > ity.com> Subject: RE: Really big
> file
> > manipulation
> >
> > 10/21/2002
> > 03:44 PM
> > Please respond
> > to cf-talk
> >
> >
> >
> >
> >
> >
> > Tried these - I thought not committing was the problem at first, but
> > there was no appreciable change. Same with the nologged bulk, this
> > lightened the load, but not much. And I always use queryparams.
> >
> > Bill Henderson
> > [EMAIL PROTECTED]
> >
> >
> >
> > > -----Original Message-----
> > > From: [EMAIL PROTECTED] [mailto:kpeterson@;sehinc.com]
> > > Sent: Monday, October 21, 2002 1:25 PM
> > > To: CF-Talk
> > > Subject: Re: Really big file manipulation
> > >
> > > Your SQL server memory is mostly likely climbing trying to keep
> track
> > of
> > > rollback information.
> > >
> > > Try using a nologged bulk copy when you create and load the temp
> > table.
> > >
> > > If possible use update select or insert select for moving the
data.
> > >
> > > Commit your transaction(s) more often if possible.
> > >
> > > Use cfqueryparm for all changing values (variables) in your SQL
> > > statments.
> > >
> > >
> > > Kore Peterson
> > > Database Development Specialist
> > > SEH - Minneapolis
> > > 612.758.6739
> > >
> > >
> > >
> > >
> > >
> > > "Randell B
> > > Adkins" To: CF-Talk <cf-
> > > [EMAIL PROTECTED]>
> > > <[EMAIL PROTECTED] cc:
> > > OV> Subject: Re: Really
big
> > file
> > > manipulation
> > >
> > > 10/21/2002
> > > 01:25 PM
> > > Please respond
> > > to cf-talk
> > >
> > >
> > >
> > >
> > >
> > >
> > > I at one time had a 500,000 byte file to import 5 days a week.
> > > But that had to go to one table.
> > >
> > > Sorry my recommendations is on MSSQL DTS.
> > >
> > >
> > >
> > >
> > > >>> [EMAIL PROTECTED] 10/21/02 02:23PM >>>
> > > Hey all -
> > >
> > > I have got a 76,000+ line data file (ZipUSA comma delim file) that
I
> > > need to break into smaller tables in my DB. This needs to happen
at
> > > least monthly, so I wrote a cfm template to automate the process.
> The
> > > problem I am having is speed and server load. I have tried using
the
> > > following methods, and am wondering if anybody has any suggestions
> > > (apart from using SQL's DTS) Platform info: CF 5 on Win2k, IIS 5
> > > writing
> > > to MSSQL 2000
> > >
> > > The tables I am putting everything into are a state table, county
> > > table
> > > w/state reference, city table w/county reference,
location/district
> > > table w/city reference and zip table w/district reference
> > >
> > > 1) Create temp SQL table, loop over file, write to temp table,
query
> > > temp table, loop over query, and using conditional logic - break
> into
> > > table structure defined above, and drop temp table. This is the
> > > shortest
> > > method taking 20 minutes or so. The problem is that my SQL
service's
> > > memory usage spirals out of control and when the template is
> finished,
> > > it never returns to normal. I have to restart the service to right
> it.
> > >
> > > 2) Same as above, but converts the data file into a CF
function-made
> > > query and then do a query of query. (This is necessary to re-order
> > > data
> > > for break-down step) This one takes forever (an hour on the
slowest
> > > run)
> > > and both the CF services and the SQL service take over the
machine.
> > >
> > > 3) Read the data file, write a new one, then read that data file
and
> > > loop over the lines, breaking it into the table structure above.
> This
> > > one also takes forever.
> > >
> > > Any suggestions at all on how to streamline the process would be
> > > really
> > > helpful, Thanks in advance
> > >
> > > Bill Henderson
> > > [EMAIL PROTECTED]
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
