Performing 500,000 inserts on 1 request

[Ryan Bergman]

HI, I need to take a CSV file and stick it in a MS sql server 2000 database.
The file has 4 column and is over 500,000 lines long. I Got a nifty parser
that uses java out of the code exmaples that came with CF. I simply added a UDF
with a stored proc that inserts the params into the database. Now without
invokeing the UDF the java/cf can parse the entire 500,000 lines in about 10
seconds, add in the query and it still not bad, manageing about 1000 records
per second...that is up untill its over 400,000 records, then it starts to get
slower and slower untill it just stops at about 449,000 aaag!

Im pretty sure the reason is because CF is caching the statusCode and
ExecutionTime for every single query (if you run with debugging on the server
you will end up with all the queries outputed at the bottom). Is there anyway
to suppress this completely so cold fusion just forgets that it ever did the
query at all?

Ive got the wholw thing fairly silenced and its flushing the cache ever 1000
lines along with a count of the row its on. Im not sure how to get it to run
much faster without just ditching cf entirely and doing it all in java or a DTS.

[rmorgan]

Got a question. Is this import of records a common occurance? or is it
occasionally? I find it a heck of alot simpler and faster to just import the
records into the database manualy or with a frontend, depending on the sql
server. Using CF in this aspect seems to me like a waste of time and overhead,
not to mention another place for something to go wrong in such a simple
process. Unless the goal is for the end users to do this themselves. just my 2
cents.

[TagTurner]

I doubt it keeps coldfusion from recording the information but you should
certainly try disabling the output (see attached code) to see if that helps.
Just throw cfsetting above the beginning of your template to ensure it doesnt
render the debug information. Sorry I can't be of more help on this but I know
if it were me I would be putting my eggs in the DTS route as its going to be a
heck of a lot faster.

Also do you have access to the server? Try monitoring memory use as the
template processes? One thing I might look at is the JVM heap which seems to
do much better when you dedicate a set amount of memory to CF that is to say
make the minimum heap and max heap the same. I have done this in production
enviornments where CF was on its own machine but this could be detrimental to
performance if your running other services on the box with CF (provided you
give CF to much memory). As I recall when I did this last I gave CF to much
memory and it jacked up the service and it wouldnt load anymore, I believe the
limit was 1.8 gb but that might have only been for 6.1.

See: [url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_19359[/url]

I hope this helps but I get the feeling the second suggestion is the only one
likely to effect it in any respect.

<cfsetting showdebugoutput="no">

[Ryan Bergman]

To answer the first reply it is a common occurance unfortunatly. And the reason
I wanted to keep it in CF rather than writeing a DTS is because I needed to do
several other file manipulation processes that needed to talk to the
surrounding application.

I've found a solution though. apparently you can create a linkserver out of a
directory and then interact with text files in that directory as tables. Its
wicked fast and I can put the commands in the stored proc and then just execute
from CF.

The tutorial I found was here:
[url]http://www.users.drew.edu/skass/sql/TextDriver.htm[/url]

it doesnt mention this but I needed to modify the permissions of my link
server like this:
EXEC sp_addlinkedsrvlogin 'txtsrv',
'false',NULL,'ADMIN',NULL