Geek Answers Handbook

Better performance when reading millions of data records

I have a database with a lot of data (millions of rows), and also updated during the day with a lot of data, I have a backup copy of this database for reporting, so getting a data report does not affect the performance of the main database.

To synchronize the database with the main database, I wrote a Windows service that queries the main database and inserts new data into the backup database ... each time the request receives 5000 rows from the main database ...

EDIT:

The request is as follows:

const string cmdStr = "SELECT * FROM [RLCConvertor].[dbo].[RLCDiffHeader] WHERE ID >= @Start and ID <= @End";

Here is the code:

using (var conn = new SqlConnection(_connectionString))
{
            conn.Open();
            var cmd = new SqlCommand(cmdStr, conn);               
            cmd.Parameters.AddWithValue("@Start", start);
            cmd.Parameters.AddWithValue("@End", end);

            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

            while (reader.Read())
            {
                var rldDiffId = Convert.ToInt32(reader["ID"].ToString());
                var rlcDifHeader = new RLCDiffHeader
                {
                    Tech_head_Type = long.Parse(reader["Tech_head_Type"].ToString()),
                    ItemCode = long.Parse(reader["ItemCode"].ToString()),
                    SessionNumber = long.Parse(reader["SessionNumber"].ToString()),                        
                    MarketFeedCode = reader["MarketFeedCode"].ToString(),
                    MarketPlaceCode = reader["MarketPlaceCode"].ToString(),
                    FinancialMarketCode = reader["FinancialMarketCode"].ToString(),
                    CIDGrc = reader["CIDGrc"].ToString(),
                    InstrumentID = reader["InstrumentID"].ToString(),
                    CValMNE = reader["CValMNE"].ToString(),
                    DEven = reader["DEven"].ToString(),
                    HEven = reader["HEven"].ToString(),
                    MessageCodeType = reader["MessageCodeType"].ToString(),
                    SEQbyINSTandType = reader["SEQbyINSTandType"].ToString()                                            
                };
                newRLCDiffHeaders.Add(rldDiffId, rlcDifHeader);
            }
            conn.Close();
        }

... ... ? ? , dataReader ... DataTable SqlDataAdapter?

+4
1

. , .

Ad hoc

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks2012.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

http://technet.microsoft.com/en-us/library/ms187569.aspx http://technet.microsoft.com/en-us/library/ms190312.aspx

, , , db db. SP , OpenRowSet .

.

Insert into tbl
SELECT a.*
    FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
         'SELECT GroupName, Name, DepartmentID
          FROM AdventureWorks2012.HumanResources.Department
          ORDER BY GroupName, Name') AS a;

, SP. , openrowset, , . POC . .

+1

More articles:


All Articles