Linked Server MSSQL. Optimized performance 30 times

Initial data :

1. Two SQL Servers, which are in direct accessibility to each other, one of which is configured with Linked Server.

   
   
   
   
   

2. SQL query of the form:

   
   
   
   
   

insert into LocalDatabaseName.dbo.TableName (column1, column2, ..., columnN)
select column1, column2, ..., columnN
from LinkedServerName.RemoteDatabaseName.dbo.TableName
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Task : copy records from one server to another as quickly as possible

Faced the fact that a similar query is being executed on 40k (40000) records for more than a minute. With an increase in the number of such queries or the number of records, performance drops dramatically and there is no way to optimize the query using SQL. Using the ImportExportDataSql app , I was able to speed this query up to 2 seconds without using the Linked Server.

I created the ImportExportDataSql application for myself and constantly refined it for several years. The main requirements for creating an application are portability, work under all versions of Windows without installing third-party libraries (except for NET Framework 3.5), a simple interface and high performance.

ImportExportDataSql - universal data converter, alternative to "bcp"

( CSV Excel) ( CSV). bcp, . "bcp", , .

ImportExportDataSql , . :

ImportExportDataSql :

ImportExportDataSql.exe -ConnectionName="   " -TaskName="  1" -TaskName="  2" [-Log="C:\FolderName\LogFileName.log"]
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

-ConnectionName - , " " " "

-TaskName -

-Log - . . -, Logs\UserName\ImportExportDataSql.log

ImportExportDataSql

1. -

   
   
   
   
   

2. ( bcp) - bcp ( bat )

   
   
   
   
   

3. - varbinary

   
   
   
   
   

4. SQL - SELECT SQL

   
   
   
   
   

5. SQL ( INSERT)

   
   
   
   
   

6. SQL ( UPDATE)

   
   
   
   
   

7. SQL

   
   
   
   
   

8. Excel SQL

   
   
   
   
   

9. CSV

   
   
   
   
   

10. CSV SQL

    
    
    
    
    

11. CSV

    
    
    
    
    

12. SQL - SQL

    
    
    
    
    

13. - SELECT

    
    
    
    
    

, SQL , . (, ).

( ) Linked Server, 1 2 . C# System.Data.SqlClient: SqlConnection, SqlDataReader, SqlCommand SqlBulkCopy.

OutOfMemoryException, (). , . , :

1. SQL - ,

   
   
   
   
   

2. :

   
   
   
   
   

   - , " ", . (.) " " , .

   
   
   
   
   

   - . , ( , )

   
   
   
   
   

   - , . , 100 , " " = 10, 10 .

   
   
   
   
   

   -

   
   
   
   
   

" " , . , , , (insert into ... select ...), , tempdb ( " " ).

ImportExportDataSql

ImportExportDataSql . .

, CSV Excel.

CSV ( 1) , CSV. CSV, .

SQL , , "Messages" SQL Server Management Studio.

" SQL" (jobs), , .

C# SqlBulkCopy , Linked Server.

ImportExportDataSql

ImportExportDataSql

Quick Read CSV in C # article that discusses the shortcomings of bcp

VK community , for those who want to chat with the author