Databases
StackOverflow2013
Postsdbo
POSql Server 2008 Tuning with large transactions (700k+ rows/transaction)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSql Server 2008 Tuning with large transactions (700k+ rows/transaction)
    primarykey
    data
    text
    <p>So, I'm working on a database that I will be adding to my future projects as sort of a supporting db, but I'm having a bit of an issue with it, especially the logs.</p> <p>The database basically needs to be updated once a month. The main table has to be purged and then refilled off of a CSV file. The problem is that Sql Server will generate a log for it which is MEGA big. I was successful in filling it up once, but wanted to test the whole process by purging it and then refilling it.</p> <p>That's when I get an error that the log file is filled up. It jumps from 88MB (after shrinking via maintenance plan) to 248MB and then stops the process altogether and never completes.</p> <p>I've capped it's growth at 256MB, incrementing by 16MB, which is why it failed, but in reality I don't need it to log anything at all. Is there a way to just completely bypass logging on any query being run against the database?</p> <p>Thanks for any responses in advance!</p> <p><strong>EDIT:</strong> Per the suggestions of @mattmc3 I've implemented SqlBulkCopy for the whole procedure. It works AMAZING, except, my loop is somehow crashing on the very last remaining chunk that needs to be inserted. I'm not too sure where I'm going wrong, heck I don't even know if this is a proper loop, so I'd appreciate some help on it.</p> <p>I do know that its an issue with the very last GetDataTable or SetSqlBulkCopy calls. I'm trying to insert 788189 rows, 788000 get in and the remaining 189 are crashing...</p> <pre><code>string[] Rows; using (StreamReader Reader = new StreamReader("C:/?.csv")) { Rows = Reader.ReadToEnd().TrimEnd().Split(new char[1] { '\n' }, StringSplitOptions.RemoveEmptyEntries); }; int RowsInserted = 0; using (SqlConnection Connection = new SqlConnection("")) { Connection.Open(); DataTable Table = null; while ((RowsInserted &lt; Rows.Length) &amp;&amp; ((Rows.Length - RowsInserted) &gt;= 1000)) { Table = GetDataTable(Rows.Skip(RowsInserted).Take(1000).ToArray()); SetSqlBulkCopy(Table, Connection); RowsInserted += 1000; }; Table = GetDataTable(Rows.Skip(RowsInserted).ToArray()); SetSqlBulkCopy(Table, Connection); Connection.Close(); }; static DataTable GetDataTable( string[] Rows) { using (DataTable Table = new DataTable()) { Table.Columns.Add(new DataColumn("A")); Table.Columns.Add(new DataColumn("B")); Table.Columns.Add(new DataColumn("C")); Table.Columns.Add(new DataColumn("D")); for (short a = 0, b = (short)Rows.Length; a &lt; b; a++) { string[] Columns = Rows[a].Split(new char[1] { ',' }, StringSplitOptions.RemoveEmptyEntries); DataRow Row = Table.NewRow(); Row["A"] = Columns[0]; Row["B"] = Columns[1]; Row["C"] = Columns[2]; Row["D"] = Columns[3]; Table.Rows.Add(Row); }; return (Table); }; } static void SetSqlBulkCopy( DataTable Table, SqlConnection Connection) { using (SqlBulkCopy SqlBulkCopy = new SqlBulkCopy(Connection)) { SqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("A", "A")); SqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("B", "B")); SqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("C", "C")); SqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("D", "D")); SqlBulkCopy.BatchSize = Table.Rows.Count; SqlBulkCopy.DestinationTableName = "E"; SqlBulkCopy.WriteToServer(Table); }; } </code></pre> <p><strong>EDIT/FINAL CODE:</strong> So the app is now finished and works AMAZING, and quite speedy! @mattmc3, thanks for all the help! Here is the final code for anyone who may find it useful:</p> <pre><code>List&lt;string&gt; Rows = new List&lt;string&gt;(); using (StreamReader Reader = new StreamReader(@"?.csv")) { string Line = string.Empty; while (!String.IsNullOrWhiteSpace(Line = Reader.ReadLine())) { Rows.Add(Line); }; }; if (Rows.Count &gt; 0) { int RowsInserted = 0; DataTable Table = new DataTable(); Table.Columns.Add(new DataColumn("Id")); Table.Columns.Add(new DataColumn("A")); while ((RowsInserted &lt; Rows.Count) &amp;&amp; ((Rows.Count - RowsInserted) &gt;= 1000)) { Table = GetDataTable(Rows.Skip(RowsInserted).Take(1000).ToList(), Table); PerformSqlBulkCopy(Table); RowsInserted += 1000; Table.Clear(); }; Table = GetDataTable(Rows.Skip(RowsInserted).ToList(), Table); PerformSqlBulkCopy(Table); }; static DataTable GetDataTable( List&lt;string&gt; Rows, DataTable Table) { for (short a = 0, b = (short)Rows.Count; a &lt; b; a++) { string[] Columns = Rows[a].Split(new char[1] { ',' }, StringSplitOptions.RemoveEmptyEntries); DataRow Row = Table.NewRow(); Row["A"] = ""; Table.Rows.Add(Row); }; return (Table); } static void PerformSqlBulkCopy( DataTable Table) { using (SqlBulkCopy SqlBulkCopy = new SqlBulkCopy(@"", SqlBulkCopyOptions.TableLock)) { SqlBulkCopy.BatchSize = Table.Rows.Count; SqlBulkCopy.DestinationTableName = ""; SqlBulkCopy.WriteToServer(Table); }; } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGup3rSuR4c
    1. USGup3rSuR4c
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    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. POSql Server 2008 Tuning with large transactions (700k+ rows/transaction)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSql Server 2008 Tuning with large transactions (700k+ rows/transaction)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSql Server 2008 Tuning with large transactions (700k+ rows/transaction)
      1. USykatchou
      1. VTFavorite
    1. COA few suggestions if you want to speed this up even more. 1.) Instead of doing Reader.ReadToEnd(), make a loop and do Reader.ReadLine() one line at a time. It'll take less memory. 2.) If no one will be accessing your table during the time you're loading it, use the `SqlBulkCopyOptions.TableLock` option. 3.) Just to save you some code, the SqlBulkCopy object infers your column mappings if you name the columns the same as what's in your destination table, and since you're handling chunking yourself, there's no reason to set the .BatchSize either. Happy coding!
      singulars
      1. POSql Server 2008 Tuning with large transactions (700k+ rows/transaction)
      1. USmattmc3
    2. COOn the topic of infering the columns will it work if: `DBTable = { Id(PK,IDENTITY), A, B, C, D }`, but `DataTable = { A, B, C, D }`? I think it was giving me trouble, that's why I specified them, but then again, I could have been screwing it up somehow...
      singulars
      1. POSql Server 2008 Tuning with large transactions (700k+ rows/transaction)
      1. USGup3rSuR4c
    3. COWell, it's done! I implemented everything you recommended and it works AMAZING! The memory got cut in half to ~85MB and the whole operation takes about 45 seconds to complete. And I figured out the columns thing above, I was right, but I just added a placeholder for the `Id` and it worked. `THANK YOU FOR HELPING ME ON THIS AND FOR TEACHING ME ABOUT THINGS I NEVER KNEW!!!`
      singulars
      1. POSql Server 2008 Tuning with large transactions (700k+ rows/transaction)
      1. USGup3rSuR4c
 

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