Databases
StackOverflow2013
Postsdbo
POWhy is this code slowing down?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy is this code slowing down?
    primarykey
    data
    text
    <p>I'm currently in the process of converting a number of Access databases to Xml files. I have done this before and I still have the code from a previous project. However, this code will not let me structure the xml as I please which is what I need to do this time around. I'm using <code>XDocument</code> with <code>for</code>-loops to achieve this but it gets incredibly slow after a couple of 1000 rows of data.</p> <p>Reading in on how XDocument works tells me that <code>XElement.Add</code> actually copies the entire xml-code and adds the new element as it pastes everything back into the file. If this is true then that's probably where the problem lies.</p> <p>This is the part that reads and writes the data from Access to Xml, take a look and see if there's any way of saving it. Converting a database with 27 columns and 12 256 rows takes almost 30 minutes while a smaller one with a mere 500 rows takes roughly 5 seconds.</p> <pre><code>private void ReadWrite(string file) { using (_Connection = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Mode=12;Data Source={0}", pathAccess))) { _Connection.Open(); //Gives me values from the AccessDB: tableName, columnName, colCount, rowCount and listOfTimeStamps. GetValues(pathAccess); XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", "true"), new XElement(tableName)); for (int rowInt = 0; rowInt &lt; rowCount; rowInt++) { XElement item = new XElement("Item", new XAttribute("Time", listOfTimestamps[rowInt].ToString().Replace(" ", "_"))); doc.Root.Add(item); //colCount"-1" prevents the timestamp from beeing written again. for (int colInt = 0; colInt &lt; colCount - 1; colInt++) { using (OleDbCommand cmnd = new OleDbCommand(string.Format("SELECT {0} FROM {1} Where TimeStamp = #{2}#", columnName[colInt] , tableName, listOfTimestamps[rowInt]), _Connection)) { XElement value = new XElement(columnName[colInt], cmnd.ExecuteScalar().ToString()); item.Add(value); } } //Updates progressbar backgroundWorker1.ReportProgress(rowInt); } backgroundWorker1.ReportProgress(0); doc.Save(file); } } </code></pre> <p>This is this code from my old converter. This code is pretty unaffected by the size of the database, the 12 556 database only takes a second to convert. Could there possably be a way to merge these two?</p> <pre><code>public void ReadWrite2(string file) { DataSet dataSet = new DataSet(); using (_Connection = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Mode=12;Data Source={0}", file))) { _Connection.Open(); DataTable schemaTable = _Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (DataRow dataTableRow in schemaTable.Rows) { string tableName = dataTableRow["Table_Name"].ToString(); DataTable dataTable = dataSet.Tables.Add(tableName); using (OleDbCommand readRows = new OleDbCommand("SELECT * from " + tableName, _Connection)) { OleDbDataAdapter adapter = new OleDbDataAdapter(readRows); adapter.Fill(dataTable); } } } dataSet.WriteXml(file.Replace(".mdb", ".xml")); } </code></pre> <p><strong>EDIT:</strong> Just to clarify, the application slows down as it's executed. As in the first 500 takes 5 seconds no matter how big the database is.</p> <p><strong>UPDATE:</strong> Okay so I've come back after the weekend now and I made a small adjustment in the code to seperate the reading and the writing by filling a jagged array with the values in one loop and writing them in another. This has proven my theory wrong and it's infact the reading that takes so much time. Any ideas on how to fill an array with the values without hitting the database inside the loop?</p> <p><strong>UPDATE2:</strong> This is the end result after switching to a <code>DataReader.Read()</code>-loop and collecting all the data right away.</p> <pre><code>public void ReadWrite3(string Save, string Load) { using (_Connection = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Mode=12;Data Source={0}", Load))) { _Connection.Open(); GetValues(_Connection); _Command = new OleDbCommand(String.Format("SELECT {0} FROM {1}", strColumns, tables), _Connection); XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", "true"), new XElement("plmslog", new XAttribute("machineid", root))); using (_DataReader = _Command.ExecuteReader()) { for (int rowInt = 0; _DataReader.Read(); rowInt++ ) { for (int logInt = 0; logInt &lt; colCount; logInt++) { XElement log = new XElement("log"); doc.Root.Add(log); elementValues = updateElementValues(rowInt, logInt); for (int valInt = 0; valInt &lt; elements.Length; valInt++) { XElement value = new XElement(elements[valInt], elementValues[valInt]); log.Add(value); } } } } doc.Save(Save); } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USHjalmar Z
    1. USHjalmar Z
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy is this code slowing down?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhy is this code slowing down?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhy is this code slowing down?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHave you tried profiling your code to see where the bottleneck is?
      singulars
      1. POWhy is this code slowing down?
      1. USChrisBint
    2. CO@ChrisBint No I haven't. Could I go about doing that without any 3rd-party tool or software?
      singulars
      1. POWhy is this code slowing down?
      1. USHjalmar Z
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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