FTP File - Parse - Load SQL Server?

[rexxrally]

What's the most efficient (ie, fastest processing) way to take a comma-delimited text file from an FTP site out on the Net, parse it into its individual fields and load it into a SQL Server table on the local website?

I need to set this up for an end-user who doesn't know how to transfer files, and doesn't know how to work in SQL Server itself.

We have a website, made up of ASP pages powered by Java Script and VB Script. We were thinking of letting the scripting languages do the work, but 4500 records were taking over 5 minutes! (Way too slow).

So, our next thought is a VB DLL, using ADO to load the tables, and being called from the ASP pages. But, never having written a DLL to be accessed by VB Script, I'm not sure how fast this will be.

Can someone suggest a fast, automated way to do this?

TIA.
Dave

[Cakkie]

Running a DLL from VB Script (or JavaScript) will be about the same as running it from a program. The key point is to make as few calls to the dll as possible (cause the DLL will run out of process, and that makes calls very expensive). Once the DLL has control of the situation (and starts retrieving and parsing the file), it will be running at full speed.
I once did a test (actually to test the difference between a VB6 and a VB7 dll) where I inserted 10000 records into a SQL server tabel, and then deleted them one by one.
When I did it completely in ASP, it took me about 7 minutes, when I gave control to the DLL, it only took me about 70 seconds (for those interesed, the VB7 dll only took 40 seconds). You can seen that the difference is quite large, so the chooce should be easy to make.

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[carymartin]

Actually the fastest way do to so is with a multi-threaded application. That way you take advantage of all of the bandwidth available and reduce wait time while processing.

[Cakkie]

Ok, I agree, but how exactly are you planning on doing miltithreading in VB?

Tom Cannaerts
slisse@planetinternet.be.remove.this.bit
Moderator on http://www.vbcodelibrary.co.uk/board

A bottomless pit, I'm sure it came with the place, who would dig one on purpose?

[carymartin]

I wouldn't. I use C++ to do that. If you want to build a VB App. You can either build the Threading in a C++ component, or have someone else do it for you. ( Like Me :o) )

Anyway, you know how microsoft says that VB .net is going to revolutionize the world. Maybe it can be done easier with .Net VB.

There are articles on how to perform Threading in VB 5.0 and 6.0, but I have heard that they are prone to crashing.

Let me know if I can help.