[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 >>>