Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Sat Apr 26 17:39:45 CDT 2008
Hi John,
Additionally to the below e-mail - you can consider using SQLDataReader to
get data records batches as in this below example, then build parsed names'
arrays together with ids, then Join() these arrays into strings and pass
these strings as parameters to SPs, which will make actual updates by
parsing strings into dynamic update sqls or something like that - T-SQL
parsing functions are reasonably quick, or you can even consider managed SPs
to parse the strings on server side and perform the updates...
...but that latter could be an overkill giving very little gains because
.NET framework's communication with native MS SQL provider when using plain
simple C#/VB.NET is very effective I have heard...
..maybe you can just process your data and generate and run update temp SPs
to have update T-SQL statements batched into such temp SPs - that could give
you (much) quicker updates...
...etc...
--
Shamil
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Sunday, April 27, 2008 2:13 AM
To: 'Discussion concerning Visual Basic and related programming issues.'
Subject: Re: [dba-VB] ADO.Net
Hi John,
Try to use SqlDataReader and custom classes as in the sample code below
(sorry C# is used - real pleasure to use C# is also coming from the fact
that you can copy and paste code and do not care about line warps because
even "screwed by e-mail" code should be compiled well)....
...using ADO.NET datasets and bulk update will anyway result in a series of
updates on SQL server side therefore custom on-the-fly built sql updates or
calls to update SPs should work as quick as ADO.NET DataSets' batch
update...
...etc...
...as you can see my sample did update 25000 records in ~10 seconds - and
this only for starters - if that approach will work reasonably well for you
then the next step could be to introduce multi-threading etc...
...note also how MS SQL 2005's paging feature is used to get record's
batches...
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace ORS_TDD_Console
{
public class DataItem
{
public int ID;
public string Name;
public DataItem(int id, string name)
{
ID = id; Name = name;
}
public string ParsedName
{
get
{
return Name; // just for testing purposes
// return real parsed name here
}
}
}
public class DataProcessor
{
private const string COUNT_SQL = "select count(*) from
zsysUserSessionTrace";
private const string PAGED_SELECT_SQL =
"SELECT * from " +
"(" +
" select " +
" Cast(ROW_NUMBER() over (ORDER BY [UserSessionTraceId])
as int) as RowNum, " +
" UserSessionTraceId, " +
" HostAddress, " +
" UserBrowser " +
" from zsysUserSessionTrace " +
") s " +
"where s.RowNum between {0} and {1}";
private const string UPDATE_SQL = "update zsysUserSessionTrace set
UserBrowser = '{0}' where UserSessionTraceId = {1}";
private const int DEFAULT_BATCH_SIZE = 1000;
private int _totalCount = -1;
public int TotalCount { get { return _totalCount; } }
private int _currentBatchStartRowNum = 0;
private int _batchSize = 0;
private string _connectionString;
public DataProcessor(string connectionString)
{
initialize(connectionString, DEFAULT_BATCH_SIZE);
}
public DataProcessor(string connectionString, int batchSize)
{
initialize(connectionString, batchSize);
}
private void initialize(string connectionString, int batchSize)
{
_batchSize = batchSize;
_currentBatchStartRowNum = 1;
_connectionString = connectionString;
using (System.Data.SqlClient.SqlConnection cnn = new
System.Data.SqlClient.SqlConnection(connectionString))
{
cnn.Open();
System.Data.SqlClient.SqlCommand cmd = cnn.CreateCommand();
cmd.CommandText = COUNT_SQL;
cmd.CommandType = System.Data.CommandType.Text;
_totalCount = (int)cmd.ExecuteScalar();
}
}
public bool HasData
{
get
{
return (TotalCount > 0 && _currentBatchStartRowNum <
TotalCount);
}
}
public List<DataItem> GetData()
{
using (System.Data.SqlClient.SqlConnection cnn = new
System.Data.SqlClient.SqlConnection(_connectionString))
{
cnn.Open();
System.Data.SqlClient.SqlCommand cmd = cnn.CreateCommand();
string sql = string.Format(PAGED_SELECT_SQL,
_currentBatchStartRowNum, _currentBatchStartRowNum + _batchSize);
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
_currentBatchStartRowNum += _batchSize + 1;
System.Data.SqlClient.SqlDataReader rdr = cmd.ExecuteReader(
System.Data.CommandBehavior.SequentialAccess);
List<DataItem> list = new List<DataItem>();
while (rdr.Read())
{
list.Add (new DataItem((int)rdr[1], (string)rdr[3]));
}
return list;
}
}
public int Update(DataItem item)
{
using (System.Data.SqlClient.SqlConnection cnn = new
System.Data.SqlClient.SqlConnection(_connectionString))
{
cnn.Open();
System.Data.SqlClient.SqlCommand cmd = cnn.CreateCommand();
string sql = string.Format(UPDATE_SQL, item.ParsedName,
item.ID );
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
return cmd.ExecuteNonQuery();
}
}
}
public class A_MainEntry
{
//27.04.2008 1:53:30
//Count(*) = 25045
//Counter = 25045
//27.04.2008 1:53:40
public static void Main()
{
int count = 0;
try
{
//+ customize - set your connection string here
string connectionString =
AppConfigManager.DefaultConnectionString;
//-
Console.WriteLine(DateTime.Now);
DataProcessor p = new DataProcessor(connectionString);
while (p.HasData)
{
List <DataItem> list = p.GetData();
foreach (DataItem item in list)
{
p.Update(item);
count++;
}
}
Console.WriteLine("Count(*) = {0}", p.TotalCount);
Console.WriteLine("Counter = {0}", count);
Console.WriteLine(DateTime.Now);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Saturday, April 26, 2008 11:26 PM
To: VBA
Subject: [dba-VB] ADO.Net
I have been setting up an application to read a block of records,
perhaps 10K or something.
<<< the rest trimmed to get message through accessd >>>
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com