Not sure what database you are using but 77k record parsing/insert sounds like a prime candidate for Stored Procedure processing - take it to the database and let the database server do its magic. CF is obviously not built for / handling that kind of processing.
Hmmm if memory serves, some databases also have the ability to do inserts directly from a CSV file. The details escape me for now [maybe something like "INSERT into TABLENAME (#fieldlist#) VALUES (SELECT * FROM '<PATH TO CSV FILE>')"] but your database may have specific help regarding this ability.
Another possibility is to treat the uploaded CSV file as an ODBC datasource (Microsoft Text Driver (*.txt, *.csv)). Again, your O/S is not mentioned, but if you're on windows, this would allow you to do the INSERT mentioned above treating the file as a second datasource - do a QoQ (do they let you do inserts?) and the O/S & ODBC driver handles the file access for you...
HTH
Aaron
----- Original Message -----
From: Pete Ruckelshaus - CFList
To: CF-Talk
Sent: Friday, May 21, 2004 12:36 AM
Subject: Text file problem
Hi,
I'm writing an application that allows our client support reps to import
customer files of a predefined format into our database. The CSR
uploads (form via CFFILE) the tab-delimited text file to the server, and
then I loop through the file contents and insert the data into the
database. So far, it has worked great.
Well, we're getting bigger clients and we just got a 77,000 record file
to import, and my import script died at about 52k records. In debugging
it, what I found was that I was bumping against the max JVM heap size
(we have it set at 512MB for all of our servers, and we came to this
number after a long and painful period of performance and reliability
testing of our application); if I bumped up the max heap size on my
development workstation, the import script worked fine. Unfortunately,
that's not an option for our production servers, and I also expect that
our import files will keep getting larger.
So, my thinking is to split the really big import file into a number of
smaller files, probably 40-50k records per tab-delimited file. However,
I'm trying to figure out what the most elegant way is to split these
big files into smaller ones, and do it without killing performance. Has
anyone done this?
Thanks,
Pete
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
