BulkInsertAsync not using compound PK index

[tivivi63]

Hello,

I catched the following command that matches a bulk insert operation on LOT table.

CREATE TABLE #ZZZProjects_512e2641_424b_4666_813f_744702acbed7z ( [$action] VARCHAR(100) NULL, ZZZ_Index INT NULL, [LOT_ID] [sys].[int] NULL );
MERGE INTO [LOT]  AS DestinationTable
USING
(
SELECT TOP 100 PERCENT * FROM #ZZZProjects_512e2641_424b_4666_813f_744702acbed7
WHERE ZZZ_Index >= @IndexStart AND ZZZ_Index <= @IndexEnd ORDER BY ZZZ_Index
) AS StagingTable
ON 1 = 2
WHEN NOT MATCHED THEN
    INSERT ( [SPE_CODE], [BUN_CODE], [... tons of columns ...] )
    VALUES ( [SPE_CODE], [BUN_CODE], [... tons of columns ...] )
OUTPUT
    $action,
    StagingTable.ZZZ_Index,
    INSERTED.[LOT_ID]
INTO #ZZZProjects_512e2641_424b_4666_813f_744702acbed7z

;
SELECT   A.* ,B.[LOT_ID] AS [LOT_ID_zzzinserted] FROM #ZZZProjects_512e2641_424b_4666_813f_744702acbed7z AS A
INNER JOIN [LOT] AS B  ON  A.[LOT_ID] = B.[LOT_ID]
;
DROP TABLE #ZZZProjects_512e2641_424b_4666_813f_744702acbed7z;

Problem is that primary key is compound (SPE_CODE, BUN_CODE, LOT_ID) and, in INNER JOIN clause, the 2 leading columns((SPE_CODE, BUN_CODE) are not mentioned, so query is scanning the primary key index, instead of seeking properly as expected.

a) What am I supposed to do to make this Z-generated TSQL command reference the 2 missing columns in INNER JOIN clause ? b) Why isn't there any clustered index on #ZZZProjects temporary table, as it is expected bulk operation, hence with high cardinality ?

Thanks,

[JonathanMagnan]

Hello @tivivi63 ,

What am I supposed to do to make this Z-generated TSQL command reference the 2 missing columns in INNER JOIN clause ?

What do your model and mapping look like for this entity?

Our library should takes the same key as the one you mapped.

Why isn't there any clustered index on #ZZZProjects temporary table, as it is expected bulk operation, hence with high cardinality ?

There is no need for an index on this temporary table, as ALL rows need to be read and not only a part of the table. However, we certainly need to use the right key and the corresponding index on the LOT table.

Best Regards,

Jon

[JonathanMagnan]

Hello @tivivi63

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

Let me know if you need further assistance.

Best regards,

Jon

[tivivi63]

Hello Jon,

we haven't heard from you

What do you expect from me?

However, we certainly need to use the right key and the corresponding index on the LOT table

I thought you'd provide with some magic fix regarding the compound key not being entirely taken into account 😁

Jean-Yves

[JonathanMagnan]

Hello @tivivi63 ,

Question:

• What do your model and mapping look like for this entity?

In short, we want to reproduce your issue since it’s not happening on our side. Any details you can share would help, and even better would be a small runnable project that reproduces the problem. It doesn’t need to be your full project, just a new solution with the minimum code required.

You can send it privately to: [email protected]

Best Regards,

Jon

[JonathanMagnan]

Hello @tivivi63,

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

Let me know if you need more information.

Best regards,

Jon

[JonathanMagnan]

Hello again,

A simple reminder that we are here to assist you.

Dont hesitate to contact us if you have any questions.

Best regards,

Jon