Speeding up code that copies database structure to XML

Question

I have a database structure filled with data that I'm trying to write out to an XML file. Here's a taste of the way the VB code is structured:

Dim xsi = XNamespace.Get("http://www.w3.org/2001/XMLSchema-instance")

Return _
    New XElement( _
        "providers", _
        New XAttribute(XNamespace.Xmlns + "xsi", xsi.NamespaceName), _
        New XAttribute(xsi + "noNamespaceSchemaLocation", "\schema\provider_schema.xsd"), _
        From p In context.providers _
        Select New XElement( _
            "provider", _
            New XElement("external_provider_id", p.external_provider_id), _
            New XElement( _
                "demographic", _
                New XElement("record_type_id", p.demographics.Single.record_type_id), _
                New XElement("provider_number", p.demographics.Single.provider_number), _
                ...
                New XElement("provider_type_id", p.demographics.Single.provider_type_id)), _
            New XElement( _
                "provider_specialties", _
                From s In p.provider_specialties _
                Select New XElement( _
                    "provider_specialty", _
                    New XElement("external_provider_specialty_id", s.external_provider_specialty_id), _
                    New XElement("record_type_id", s.record_type_id), _
                    New XElement("effective_date", s.effective_date.TruCareFormat), _
                    ...

The structure pulls in data from about 15 different tables, all tied back to the Provider table, some of which may go 2 or 3 levels down. There are currently about 40,000 providers, and the code is taking about 1.5 hours to complete.

I have two problems with this code as it stands currently:

1. I can't see or report any progress, because the processing is all tied to one giant LINQ query. Even if I can't make it any faster, I'd like to know how much longer I have to wait. My first version of this code had For loops instead of queries, so I could display progress, but it took a loot longer.
2. Obviously I'd like to speed this up considerably. It seems like there should be a simpler way to convert the data to an XML file, especially since the database structure was created from the XSD in the first place.

Any ideas on what I can do to improve this?

Answer 1

I would try to write a query (or maybe more queries) that gives you all the data and use this data to construct the xml document. Now your SQL query is bound to the structure of your xml document but there may be a more efficient query that returns the same data but is a lot faster.