![]() ![]() The DataTable is ready, it is just a simple statement to insert all the data at Specific columns of a row, etc, until all the data is exactly what you want. NET DataTable is basically an in memory representation of an MS SQL Server table.ĭataTable allow you to create the table in memory, add rows to it, edit values in Then I googled and find better way with SQLīulk Copy operation in C# by using. But when I test with huge data, I have faced performance issue due to continues inserting process. Duration = 00:10:00.1098163.Recently, I was in need to fetch and insert data in a specific interval (day or hour) from another DataBase system. BatchSize = 100 000Ĭopy-SqlTable finished with success. BatchSize = 10 000Ĭopy-SqlTable finished with success. BatchSize = 1 000Ĭopy-SqlTable finished with success. 134 308 637 rows 12.3 GB dataĬopy-SqlTable finished with success. The first measure is a basic run with default batch size.Ĭopy-SqlTable finished with success. (NAME = N'taget_data', FILENAME = N'M:\MSSQL_data\',įILENAME = N'M:\MSSQL_data\er_data00.ndf',įILENAME = N'M:\MSSQL_data\er_data01.ndf',įILENAME = N'M:\MSSQL_data\er_data02.ndf',įILENAME = N'M:\MSSQL_data\er_data03.ndf',įILENAME = N'M:\MSSQL_data\er_data04.ndf',įILENAME = N'M:\MSSQL_data\er_data05.ndf',įILENAME = N'M:\MSSQL_data\er_data06.ndf',įILENAME = N'M:\MSSQL_data\er_data07.ndf',įILENAME = N'C:\MSSQL_translog\target_log.ldf',ĪLTER DATABASE SET QUERY_STORE = ONĪLTER DATABASE SET QUERY_STORE (OPERATION_MODE = READ_WRITE) The target database and table is created in similar way but on another drive to further optimize I/O: (NAME = N'source_data', FILENAME = N'C:\MSSQL_data\',įILENAME = N'C:\MSSQL_data\er_data00.ndf',įILENAME = N'C:\MSSQL_data\er_data01.ndf',įILENAME = N'C:\MSSQL_data\er_data02.ndf',įILENAME = N'C:\MSSQL_data\er_data03.ndf',įILENAME = N'C:\MSSQL_data\er_data04.ndf',įILENAME = N'C:\MSSQL_data\er_data05.ndf',įILENAME = N'C:\MSSQL_data\er_data06.ndf',įILENAME = N'C:\MSSQL_data\er_data07.ndf',įILENAME = N'C:\MSSQL_translog\source_log.ldf',ĪLTER DATABASE SET QUERY_STORE = ON ĪLTER DATABASE SET QUERY_STORE (OPERATION_MODE = READ_WRITE) The source database and table is created simple but still with parallel I/O in mind: ![]() Using the batch delimiter " GO" in Management Studio or SQLCMD with the number of rows as the count parameter value to generate several rows of source data: Actually I enabled the SQL Server Query Store on both tables on creation to have some extra figures to look at. ![]() The T-SQL statements are clocked default by Management Studio.Īlso I kept an eye on Memory and CPU usage in Windows Performance Monitor on the Process object with all counters (*) on the processes sqlsrvr.exe and powershell/powershell_ise during each run.įinally I caught actual execution plan on T-SQL statements and kept an eye on the SQL Server Activity Monitor, e.g. "Z $Message" -f ::UtcNow | Write-OutputĮxecution time is measured on each run. I made the PowerShell function Copy-Sqltable to handle the copy of the data:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |