public bool TryWriteValues(IEnumerable <KeyValuePair <UInt256, WriteValue <ImmutableArray <Transaction> > > > values) { using (var conn = this.OpenConnection()) using (var trans = conn.BeginTransaction()) using (var cmd = conn.CreateCommand()) { cmd.Transaction = trans; cmd.CommandText = @" MERGE INTO BlockTransactions USING (SELECT CAST(@blockHash AS CHAR(32) CHARACTER SET OCTETS) AS BlockHash, CAST(@txIndex AS INTEGER) AS TxIndex FROM RDB$DATABASE) AS Param ON (BlockTransactions.BlockHash = Param.BlockHash AND BlockTransactions.TxIndex = Param.TxIndex) WHEN NOT MATCHED THEN INSERT ( BlockHash, TxIndex, TxHash, TxBytes ) VALUES ( @blockHash, @txIndex, @txHash, @txBytes );" ; cmd.Parameters.Add(new FbParameter { ParameterName = "@blockHash", FbDbType = FbDbType.Char, Charset = FbCharset.Octets, Size = 32 }); cmd.Parameters.Add(new FbParameter { ParameterName = "@txIndex", FbDbType = FbDbType.Integer }); cmd.Parameters.Add(new FbParameter { ParameterName = "@txHash", FbDbType = FbDbType.Char, Charset = FbCharset.Octets, Size = 32 }); cmd.Parameters.Add(new FbParameter { ParameterName = "@txBytes", FbDbType = FbDbType.Binary }); foreach (var keyPair in values) { var blockHash = keyPair.Key; cmd.Parameters["@blockHash"].Value = blockHash.ToDbByteArray(); for (var txIndex = 0; txIndex < keyPair.Value.Value.Length; txIndex++) { var tx = keyPair.Value.Value[txIndex]; var txBytes = StorageEncoder.EncodeTransaction(tx); cmd.Parameters["@txIndex"].Value = txIndex; cmd.Parameters["@txHash"].Value = tx.Hash.ToDbByteArray(); cmd.Parameters["@txBytes"].Size = txBytes.Length; cmd.Parameters["@txBytes"].Value = txBytes; cmd.ExecuteNonQuery(); } } trans.Commit(); return(true); } }
public bool TryWriteValues(IEnumerable <KeyValuePair <UInt256, WriteValue <ImmutableArray <Transaction> > > > values) { using (var conn = this.OpenConnection()) using (var trans = conn.BeginTransaction()) using (var cmd = conn.CreateCommand()) using (var deleteCmd = conn.CreateCommand()) { // give writes low deadlock priority, a flush can always be retried using (var deadlockCmd = conn.CreateCommand()) { deadlockCmd.Transaction = trans; deadlockCmd.CommandText = "SET DEADLOCK_PRIORITY LOW"; deadlockCmd.ExecuteNonQuery(); } deleteCmd.Transaction = trans; cmd.Transaction = trans; deleteCmd.CommandText = @" DELETE FROM BlockTransactionsChunked WHERE BlockHash = @blockHash"; deleteCmd.Parameters.Add(new SqlParameter { ParameterName = "@blockHash", DbType = DbType.Binary, Size = 32 }); cmd.CommandText = @" INSERT INTO BlockTransactionsChunked ( BlockHash, MinTxIndex, MaxTxIndex, TxChunkBytes ) VALUES ( @blockHash, @minTxIndex, @maxTxIndex, @txChunkBytes );" ; cmd.Parameters.Add(new SqlParameter { ParameterName = "@blockHash", DbType = DbType.Binary, Size = 32 }); cmd.Parameters.Add(new SqlParameter { ParameterName = "@minTxIndex", DbType = DbType.Int32 }); cmd.Parameters.Add(new SqlParameter { ParameterName = "@maxTxIndex", DbType = DbType.Int32 }); cmd.Parameters.Add(new SqlParameter { ParameterName = "@txChunkBytes", DbType = DbType.Binary }); var chunkSize = 100.THOUSAND(); var maxChunkSize = 1.MILLION(); var chunk = new byte[maxChunkSize]; foreach (var keyPair in values) { var blockHash = keyPair.Key; deleteCmd.Parameters["@blockHash"].Value = blockHash.ToDbByteArray(); deleteCmd.ExecuteNonQuery(); cmd.Parameters["@blockHash"].Value = blockHash.ToDbByteArray(); var minTxIndex = 0; var maxTxIndex = 0; var chunkOffset = 0; for (var txIndex = 0; txIndex < keyPair.Value.Value.Length; txIndex++) { var tx = keyPair.Value.Value[txIndex]; var txBytes = StorageEncoder.EncodeTransaction(tx); if (txBytes.Length > maxChunkSize) { throw new Exception(); } if (chunkOffset + txBytes.Length > chunkSize && chunkOffset > 0) { var dbChunk1 = new byte[chunkOffset]; Buffer.BlockCopy(chunk, 0, dbChunk1, 0, chunkOffset); cmd.Parameters["@minTxIndex"].Value = minTxIndex; cmd.Parameters["@maxTxIndex"].Value = maxTxIndex; cmd.Parameters["@txChunkBytes"].Size = dbChunk1.Length; cmd.Parameters["@txChunkBytes"].Value = dbChunk1; cmd.ExecuteNonQuery(); chunkOffset = 0; minTxIndex = txIndex; } maxTxIndex = txIndex; Buffer.BlockCopy(txBytes, 0, chunk, chunkOffset, txBytes.Length); chunkOffset += txBytes.Length; } var dbChunk2 = new byte[chunkOffset]; Buffer.BlockCopy(chunk, 0, dbChunk2, 0, chunkOffset); cmd.Parameters["@minTxIndex"].Value = minTxIndex; cmd.Parameters["@maxTxIndex"].Value = maxTxIndex; cmd.Parameters["@txChunkBytes"].Size = dbChunk2.Length; cmd.Parameters["@txChunkBytes"].Value = dbChunk2; cmd.ExecuteNonQuery(); } trans.Commit(); return(true); } }
public bool TryWriteValues(IEnumerable <KeyValuePair <UInt256, WriteValue <ImmutableArray <Transaction> > > > values) { var stopwatch = new Stopwatch(); var count = 0; try { using (var conn = this.OpenConnection()) using (var trans = conn.BeginTransaction()) using (var cmd = conn.CreateCommand()) { cmd.Transaction = trans; cmd.CommandText = @" MERGE BlockTransactions AS target USING (SELECT @blockHash, @txIndex) AS source (BlockHash, TxIndex) ON (target.BlockHash = source.BlockHash AND target.TxIndex = source.TxIndex) WHEN NOT MATCHED THEN INSERT (BlockHash, TxIndex, TxHash, TxBytes) VALUES (@blockHash, @txIndex, @txHash, @txBytes);" ; cmd.Parameters.Add(new SqlParameter { ParameterName = "@blockHash", DbType = DbType.Binary, Size = 32 }); cmd.Parameters.Add(new SqlParameter { ParameterName = "@txIndex", DbType = DbType.Int32 }); cmd.Parameters.Add(new SqlParameter { ParameterName = "@txHash", DbType = DbType.Binary, Size = 32 }); cmd.Parameters.Add(new SqlParameter { ParameterName = "@txBytes", DbType = DbType.Binary }); foreach (var keyPair in values) { var blockHash = keyPair.Key; cmd.Parameters["@blockHash"].Value = blockHash.ToDbByteArray(); for (var txIndex = 0; txIndex < keyPair.Value.Value.Length; txIndex++) { var tx = keyPair.Value.Value[txIndex]; var txBytes = StorageEncoder.EncodeTransaction(tx); cmd.Parameters["@txIndex"].Value = txIndex; cmd.Parameters["@txHash"].Value = tx.Hash.ToDbByteArray(); cmd.Parameters["@txBytes"].Size = txBytes.Length; cmd.Parameters["@txBytes"].Value = txBytes; count++; cmd.ExecuteNonQuery(); } } stopwatch.Start(); trans.Commit(); stopwatch.Stop(); return(true); } } finally { //Debug.WriteLine("flushed {0,5}: {1:#,##0.000000}s @ {2:#,##0.000}/s".Format2(count, stopwatch.ElapsedSecondsFloat(), count / stopwatch.ElapsedSecondsFloat())); } }