Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>basic concept - use paralleism to speed this up. </p> <p>step 1 - give your threadpool enough threads to pull this off - ThreadPool.SetMinThreads(1024, 256);</p> <p>step 2 - use partitions. I use guids as Ids, i use the last to characters to split into 256 unique partitons (actually I group those into N subsets in my case 48 partitions)</p> <p>step 3 - insert using tasks, i use object pooling for table refs</p> <pre><code>public List&lt;T&gt; InsertOrUpdate(List&lt;T&gt; items) { var subLists = SplitIntoPartitionedSublists(items); var tasks = new List&lt;Task&gt;(); foreach (var subList in subLists) { List&lt;T&gt; list = subList; var task = Task.Factory.StartNew(() =&gt; { var batchOp = new TableBatchOperation(); var tableRef = GetTableRef(); foreach (var item in list) { batchOp.Add(TableOperation.InsertOrReplace(item)); } tableRef.ExecuteBatch(batchOp); ReleaseTableRef(tableRef); }); tasks.Add(task); } Task.WaitAll(tasks.ToArray()); return items; } private IEnumerable&lt;List&lt;T&gt;&gt; SplitIntoPartitionedSublists(IEnumerable&lt;T&gt; items) { var itemsByPartion = new Dictionary&lt;string, List&lt;T&gt;&gt;(); //split items into partitions foreach (var item in items) { var partition = GetPartition(item); if (itemsByPartion.ContainsKey(partition) == false) { itemsByPartion[partition] = new List&lt;T&gt;(); } item.PartitionKey = partition; item.ETag = "*"; itemsByPartion[partition].Add(item); } //split into subsets var subLists = new List&lt;List&lt;T&gt;&gt;(); foreach (var partition in itemsByPartion.Keys) { var partitionItems = itemsByPartion[partition]; for (int i = 0; i &lt; partitionItems.Count; i += MaxBatch) { subLists.Add(partitionItems.Skip(i).Take(MaxBatch).ToList()); } } return subLists; } private void BuildPartitionIndentifiers(int partitonCount) { var chars = new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f' }.ToList(); var keys = new List&lt;string&gt;(); for (int i = 0; i &lt; chars.Count; i++) { var keyA = chars[i]; for (int j = 0; j &lt; chars.Count; j++) { var keyB = chars[j]; keys.Add(string.Concat(keyA, keyB)); } } var keySetMaxSize = Math.Max(1, (int)Math.Floor((double)keys.Count / ((double)partitonCount))); var keySets = new List&lt;List&lt;string&gt;&gt;(); if (partitonCount &gt; keys.Count) { partitonCount = keys.Count; } //Build the key sets var index = 0; while (index &lt; keys.Count) { var keysSet = keys.Skip(index).Take(keySetMaxSize).ToList(); keySets.Add(keysSet); index += keySetMaxSize; } //build the lookups and datatable for each key set _partitions = new List&lt;string&gt;(); for (int i = 0; i &lt; keySets.Count; i++) { var partitionName = String.Concat("subSet_", i); foreach (var key in keySets[i]) { _partitionByKey[key] = partitionName; } _partitions.Add(partitionName); } } private string GetPartition(T item) { var partKey = item.Id.ToString().Substring(34,2); return _partitionByKey[partKey]; } private string GetPartition(Guid id) { var partKey = id.ToString().Substring(34, 2); return _partitionByKey[partKey]; } private CloudTable GetTableRef() { CloudTable tableRef = null; //try to pop a table ref out of the stack var foundTableRefInStack = _tableRefs.TryPop(out tableRef); if (foundTableRefInStack == false) { //no table ref available must create a new one var client = _account.CreateCloudTableClient(); client.RetryPolicy = new ExponentialRetry(TimeSpan.FromSeconds(1), 4); tableRef = client.GetTableReference(_sTableName); } //ensure table is created if (_bTableCreated != true) { tableRef.CreateIfNotExists(); _bTableCreated = true; } return tableRef; } </code></pre> <p>result - 19-22kops storage account maximum</p> <p>hit me up if your interested in the full source</p> <p>need moar? use multiple storage accounts!</p> <p>this is from months of trial and error, testing, beating my head against a desk. I really hope it helps.</p>
    singulars
    1. This table or related slice is empty.
    1. POPainfully slow Azure table insert and delete batch operations
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJTtheGeek
    1. USJTtheGeek
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for the answer. I went ahead and implemented a simpler variation of this, still using parallel Tasks as you suggest, with a max of 100 items in each partition. Inserting 28k rows this way took over 3 minutes on my dev machine, with my CPU pretty much maxed out. This is about 155 rops which is terrible and pretty much the same rate as without parallelism. The same test in Azure fared much worse on a small instance (as I would expect) with maxed CPU slow inserts.
      singulars
      1. PO
      1. USKeith Murray
    2. COso you need to batch those in batches of 100, each batch needs to contain only a specific partition set and use that in tasks. It sounds like your mixing items and not batching correctly, if u can send me a small test project showing your issue and ill take a look
      singulars
      1. PO
      1. USJTtheGeek
    3. COThat is exactly what I am doing, inserting batches of 100, each batch with a unique PartitionKey, all in parallel. And I should clarify that even inserting one batch of 100 takes several seconds, both locally and in Azure.
      singulars
      1. PO
      1. USKeith Murray
 

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