CODEWITHSUNDEEP
c#sqlbulkcopy
Slevin
Slevin

Reputation: 191

SqlBulkCopy.WriteToServer executes automatically a Select * on the table

I have a C# 4.5 application, that receives records from the network and writes them into a Sql Server table.

I am using a SqlBulkCopy to fast insert a DataTable object into a SQL table.

This is the code I am using:

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;
}

Usually the DataTable object contains 1,000 records, while the SQL Table could have ~500M entries.

I was performing some debug, to identify possible bottlenecks in our application, and I have discovered that each time the WriteToServer is invoked, a Select * on the sql table is performed.

I am using this query to get the list of latest 50 queries run on the database:

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 a normal behavior of SqlBulkCopy object? Due to the fact that the SQL Table is pretty big, my concerns are now about the impact of that Select query on the overall performances.

Upvotes: 0

Views: 991

Answers (1)

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

The select * is executed with SET FMTONLY ON. So no row is processed. This statement must be executed to return information about the destination table.

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

Ref: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql

Here is an example:

select @@trancount; SET FMTONLY ON select * FROM tag SET FMTONLY OFF exec tempdb..sp_tablecollations_100 N'.[#ZZZProjects_f384e3cc_1428_459d_8853_a50ed818ccca]'

Upvotes: 3

Related Questions