Re: speeding up a file conversion from text file to XML format

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks Steve,
I will give that a go when i get in the office and keep you posted.

"Steve Gerrard" wrote:

Peter Newman wrote:
HI, Im usning vb.net ( 2005 ) & SQL Server 2005. I have been asked
to try and improve the importing of files into SQL for old legacy
system. The file are submitted to the company by FTP, decrypted. (
sample file below )

I have written a module to convert this file into an XML file which
imports to the SQL very quickly. The problem I have come up with is
generating the XML File, when files are received in with inexcess of
4000 data records ( data recorded marked for with *** for
identification only in the sample ). The client is online waiting for
a report, which is not generated till the file has been imported to
the SQL. In testing with a file of 4000 records it is taking 2
minutes + to generate the xml file and 4 seconds to import it. I need
to find a way to reduce the time it takes to generate the XML style
file or come up with an alternative. The option of upgrading the
clients software to submit XML files is not an option, so the problem
remains in-house. The XML file is generated on the FTP server itself
so I cant even blame a slow network. Any suggestions are most welcome


That's a pretty big sample. :)

A couple of thoughts occur to me:

It seems like most of the activity is adding the Transaction XmlElement 4000
times. Couldn't you define the Transaction element, and its attributes, just one
time, and then use cloneNode (or something similar) to copy it, changing the
attributes as needed each time?

It might help to read the incoming text file into an array, and process it from
there, instead of pausing to read the next line as you add each transaction. Or
it might make no difference, but it might be worth checking on it.

The final thought is that given that you have a tightly defined xml structure to
produce, you might consider just creating it yourself using strings, instead of
using the DOM. There is bound to be a lot of extra checking of things going on
in there. Building the strings yourself is not so difficult; it is just a lot of
concatenating of "<elemname>", "attribname=" + attribvalue + ",", "</elemname>",
etc. Might be worth it.



.

Relevant Pages

  • Re: speeding up a file conversion from text file to XML format
    ... to try and improve the importing of files into SQL for old legacy ... The file are submitted to the company by FTP, ... I have written a module to convert this file into an XML file which ... imports to the SQL very quickly. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Importing data into MS SQL 2000 from XML file
    ... Re: Re: Importing data into MS SQL 2000 from XML file ... If I remove the xmlns entry from the XML data file the import runs fine, ... understanding, Understanding is not wisdom." ...
    (comp.databases.ms-sqlserver)
  • Re: ImportXML and row order (Access 2002)
    ... >I am attempting to import a table into Access from an XML file. ... The schema and data are importing correctly either way. ... it's definitely blocks of rows shifted. ... > prefer not to have to programmatically resort the table after ...
    (microsoft.public.access.externaldata)
  • MySQL Insert Unicode Problem
    ... I'm trying to insert some data from an XML file into MySQL. ... while importing one of the files, ... Traceback: ...
    (comp.lang.python)
  • ExportXML File Format Problem
    ... I'm generating an XML file to be imported into an accounting program using a ... Payment and Tax sections inside of the Transaction section where they belong. ...
    (microsoft.public.access.externaldata)