SqlBulkCopy.WriteToServer executes automatically a Select * on the table

c# sqlbulkcopy

Question

My C# 4.5 program reads data from the internet and publishes it to a SQL Server table.

I'm quickly inserting a DataTable object into a SQL table using a SqlBulkCopy.

I'm using the following code:

public bool BulkCopy(string tableName, DataTable dataToInsert)
{
   try{              
        using (SqlBulkCopy tmpBulkCopy = new SqlBulkCopy(dbaseConnection))
        {
           // Set the destination table.
           tmpBulkCopy.DestinationTableName = tableName;

           // Define column mappings 
           foreach (DataColumn dc in dataToInsert.Columns)
           {
              tmpBulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
           }

           // Perform the Bulk Copy
           tmpBulkCopy.WriteToServer(dataToInsert);
        }
      }
      catch (Exception)
      {
         return false;
      }

      return true;
}

The SQL Table may have up to 500M entries, but the DataTable object typically has 1,000 records.

Debugging our program to find potential bottlenecks led me to the realization that every time WriteToServer is used, a Select * on the SQL database is executed.

I'm using this query to retrieve the 50 most recent database queries that have been run:

SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS  ProcName,
execution_count,s2.objectid,
(SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
  ( (CASE WHEN statement_end_offset = -1
 THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
 ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
   last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
--and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
ORDER BY last_execution_time DESC

Is this how the SqlBulkCopy object normally behaves? Since the SQL Table is somewhat large, my current worries are on how that Select query may affect the overall performance.

1
0
5/29/2017 11:09:35 AM

Accepted Answer

SET FMTONLY ON is used to perform the select *. No row is thus processed. To get details about the destination table, you must run this line.

No rows are processed or sent to the client because of the request when SET FMTONLY is turned ON.

Refer to https://docs.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql

Here's an illustration:

select @@trancount; SET FMTONLY ON select * FROM tag SET FMTONLY OFF exec tempdb..sp_tablecollations_100 N'.[#ZZZProjects_f384e3cc_1428_459d_8853_a50ed818ccca]'
3
5/29/2017 11:16:11 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow