StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSegmented Data Load: Table records to xml
text
Body
copied!<p>I have a sequence of sql queries that result in very large datasets that I have to query against a database and write them to files. I have about 80 queries and each one produces somewhere between 1000 records to 10,000,000 records. I cannot change the queries themselves. What I'm trying to do is read 500,000 records at a time for each query and write to a file. Here's what I have so far</p> <pre><code>void WriteXml(string tableName, string queryString) { int pageSize = 500000; int currentIndex = 0; using ( SqlConnection connection = new SqlConnection(CONNECTION_STRING)) { using (SqlCommand command = new SqlCommand(queryString, connection)) { try { connection.Open(); SqlDataAdapter dataAdapter = new SqlDataAdapter(command); int rowsRead = 0, count = 0, index = 0; do { DataSet dataSet = new DataSet("SomeDatasetName"); rowsRead = dataAdapter.Fill(dataSet, currentIndex, pageSize, tableName); currentIndex += rowsRead; if (dataSet.Tables.Count > 0 && rowsRead > 0) { dataSet.Tables[0].WriteXml(string.Format(@"OutputXml\{0}_{1}.xml", tableName, index++), XmlWriteMode.WriteSchema); } } while (rowsRead > 0); } catch (Exception e) { Log(e); } } } } </code></pre> <p>This works but it's very very slow. I'm pretty sure I'm doing something wrong here because when I run it, the application hogs up most of my memory (I have 6GB) and it takes for ever to run. I started it last night and it is still running. I understand I'm dealing with a lot a records but I don't think it's something that would take so many hours to run.</p> <p>Is this the right way to do paged/segmented data read from a database? Is there any way this method could be optimized or is there any other way I can approach this?</p> <p>Do let me know if I'm not clear on anything and I'll try to provide clarification.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload