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

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: importing access to sql ce
    ... There is nothing built into SQL CE to load data from a CSV or XML file. ... importing access to sql ce ...
    (microsoft.public.sqlserver.ce)
  • RE: MS patch-scanner for Win-NT, 2K, IIS, SQL
    ... MS patch-scanner for Win-NT, 2K, IIS, SQL ... XML file from the following location - mssecure.xml Possible ... and on a NT 4 Server, but the scanner works fine on a W2K Server ...
    (Focus-Microsoft)
  • RE: MS patch-scanner for Win-NT, 2K, IIS, SQL
    ... MS patch-scanner for Win-NT, 2K, IIS, SQL ... seems so far that the patches it suggests are accurate. ... it the patch for advisory MS01-015 is not istalled, ... verison of the binary or the xml file at any of the URLs I've been given. ...
    (Focus-Microsoft)
  • Re: ADO.NET questions
    ... Sure, you can use XML to move data to and from SQL Server, but it's very ... >>> have a way to export rows from a few tables from one users computer to ... >>> email xml file to another user) ... >>> 5)Create SQLDataAdapter on target computer. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Looking for database design/model software
    ... > generate SQL, ... Since Dia ... I actually have a small PHP script that takes the input of ... feed it into another script that turns it an XML file for Dia. ...
    (RedHat)
Quantcast