Avoid output

[g-lashuk]

Description

I have a problem with BulkUpdate method. According to logs from sql profiler the generated sql includes the select statement after merge into, which takes half of the time of the whole query. Is there any setting to disable that?

exec sp_executesql N'CREATE TABLE #ZZZProjects_cb0585d1_510f_4ef0_b6d0_c30fbe1f5982z ( [$action] VARCHAR(100) NULL, ZZZ_Index INT NULL, ZZZ_Deleted INT NULL, [id] [sys].[bigint] NULL, [tenant_id] nvarchar(100) COLLATE Latin1_General_100_CI_AS NULL );
SELECT ''DELETE'' AS [$action], A.* , B.[id] AS [id_zzzdeleted], B.[tenant_id] AS [tenant_id_zzzdeleted], B.[create_date] AS [create_date_zzzdeleted], B.[update_date] AS [update_date_zzzdeleted], B.[update_user_id] AS [update_user_id_zzzdeleted], B.[hash] AS [hash_zzzdeleted], B.[name] AS [name_zzzdeleted], B.[index] AS [index_zzzdeleted], B.[program_list_id] AS [program_list_id_zzzdeleted], B.[payload] AS [payload_zzzdeleted], B.[protocol_id] AS [protocol_id_zzzdeleted], B.[hash_type] AS [hash_type_zzzdeleted], B.[revision] AS [revision_zzzdeleted], B.[test] AS [test_zzzdeleted] FROM (
SELECT TOP 100 PERCENT * FROM #ZZZProjects_cb0585d1_510f_4ef0_b6d0_c30fbe1f5982
WHERE ZZZ_Index >= @IndexStart AND ZZZ_Index <= @IndexEnd ORDER BY ZZZ_Index
) AS A
INNER JOIN [programs] AS B  ON  (B.[id] = A.[id] OR (B.[id] IS NULL AND A.[id] IS NULL)) AND (B.[tenant_id] = A.[tenant_id] OR (B.[tenant_id] IS NULL AND A.[tenant_id] IS NULL))
WHERE 1 = 1 
;
MERGE INTO [programs]  AS DestinationTable
USING
(
SELECT TOP 100 PERCENT * FROM #ZZZProjects_cb0585d1_510f_4ef0_b6d0_c30fbe1f5982
WHERE ZZZ_Index >= @IndexStart AND ZZZ_Index <= @IndexEnd ORDER BY ZZZ_Index
) AS StagingTable
ON DestinationTable.[id] = StagingTable.[id] AND DestinationTable.[tenant_id] = StagingTable.[tenant_id]
WHEN MATCHED   THEN
    UPDATE
    SET     [update_date] = StagingTable.[update_date], [hash] = StagingTable.[hash], [revision] = StagingTable.[revision]
OUTPUT
    $action,
    StagingTable.ZZZ_Index,
    1,
    INSERTED.[id], INSERTED.[tenant_id]
INTO #ZZZProjects_cb0585d1_510f_4ef0_b6d0_c30fbe1f5982z

;
SELECT   A.* ,B.[id] AS [id_zzzinserted], B.[tenant_id] AS [tenant_id_zzzinserted], B.[create_date] AS [create_date_zzzinserted], B.[update_date] AS [update_date_zzzinserted], B.[update_user_id] AS [update_user_id_zzzinserted], B.[hash] AS [hash_zzzinserted], B.[name] AS [name_zzzinserted], B.[index] AS [index_zzzinserted], B.[program_list_id] AS [program_list_id_zzzinserted], B.[payload] AS [payload_zzzinserted], B.[protocol_id] AS [protocol_id_zzzinserted], B.[hash_type] AS [hash_type_zzzinserted], B.[revision] AS [revision_zzzinserted], B.[test] AS [test_zzzinserted] FROM #ZZZProjects_cb0585d1_510f_4ef0_b6d0_c30fbe1f5982z AS A
INNER JOIN [programs] AS B  ON  (A.[id] = B.[id] OR (A.[id] IS NULL AND B.[id] IS NULL)) AND (A.[tenant_id] = B.[tenant_id] OR (A.[tenant_id] IS NULL AND B.[tenant_id] IS NULL))
;
DROP TABLE #ZZZProjects_cb0585d1_510f_4ef0_b6d0_c30fbe1f5982z;',N'@IndexStart int,@IndexEnd int',@IndexStart=0,@IndexEnd=8204

Exception

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message:
Stack trace:

Fiddle or Project (Optional)

If you are able,

Provide a Fiddle that reproduces the issue: https://dotnetfiddle.net/kV5RHX

Or provide a project/solution that we can run to reproduce the issue.

• Make sure the project compile
• Make sure to provide only the code that is required to reproduce the issue, not the whole project
• You can send private code here: info@zzzprojects.com

Otherwise, make sure to include as much information as possible to help our team to reproduce the issue.

Note: More information you provide, faster we can implement a solution.

Further technical details

• Dapper version: [Dapper v2.0.0.0]
• Dapper Plus version: [Dapper Plus v5.0.4.0]
• Database Server version: [SQL Server 2019]
• Database Provider version (NuGet): [System.Data.SqlClient v6.0.0.0]

[JonathanMagnan]

Hello @g-lashuk ,

I will first recommend you to add an index on the id and tenantid columns if you don't already have one. Something looks wrong if the SELECT part takes that much time.

As for disabling the output, I'm unsure exactly why our library is currently outputting all values. One cause could be if you have the audit feature enabled? Otherwise, could you show us how you did your mapping and how you call the bulk update (we are most interested in which options you have enabled) so we will be able to let you know which options are currently causing our library's output values?

Best Regards,

Jon

[JonathanMagnan]

Yes, the audit will enable the output of all values before and after the update is performed.

Your table also probably contains a trigger or something like this, which doesn't allow us to output those values directly (so we have to use a temporary table and then make a JOIN after).

Best Regards,

Jon

[g-lashuk]

Is there any setting to exclude columns from auditing? For example if I have a model with properties: Prop1 Prop2 and table with columns Prop1 Prop2 Prop3. As far as I see even if I've configured mapping only for Prop1 and Prop2. Prop3 is also selected after merge into statement.

P.S you can see that even in my example, Payload is not presented in my DTO.

I'm asking because some of the columns (Payload in particular) contains a lot of data. But we updating only update_date hash and revision via the model without Payload, but anyway it's selected after the merge and leads to perf decrease.

[JonathanMagnan]

Hello @g-lashuk ,

Sure, you can choose properties to exclude:

DapperPlusManager.Entity<EntitySimple>().Table("EntitySimples")
    .Identity(x => x.ID)
    .Map(x => x.ColumnString) 
    .Map(x => x.ColumnInt, "ColumnInt") 
    .AuditMode(x => x.ColumnString, ColumnMappingAuditModeType.Exclude)

or properties to include (you exclude them all at first):

DapperPlusManager.Entity<EntitySimple>().Table("EntitySimples")
    .Identity(x => x.ID)
    .Map(x => x.ColumnString) 
    .Map(x => x.ColumnInt, "ColumnInt") 
    .AuditMode(AuditModeType.ExcludeAll)
    .AuditMode(x => x.ColumnInt, ColumnMappingAuditModeType.Include)
    .AuditMode(x => x.IDPatate, ColumnMappingAuditModeType.Include);

So, in your case, it is probably to exclude them all and include only those you want as, by default, we select everything.

Let me know if that answers your question correctly.

Best Regards,

Jon

[g-lashuk]

Cool, currently I'm on vacation. But some of my colleagues will check it next week and resolve the issue if it helps. Thank you!

[JonathanMagnan]

Hello @g-lashuk ,

Since our last conversation, we haven't heard from you.

Were you able to solve your issue?

Don't hesitate to contact us if you need further assistance.

Best regards,

Jon

[g-lashuk]

Hello @JonathanMagnan, yes we've managed to resolve our issue.

Thanks for the help!