ADO.NET Transfer consistently slow on incremental table with no new rows

@william.sprangers do you have delete detection enabled?

We currently don’t handle deletes on this table, these are our table settings for data extraction 
 

This seems to be related to simple mode being enabled on this table, so the table in question is a simple mode table being loaded incrementally 

 

This is the incremental rule on the table in the ODX. The TimeXtender version is 20.10.30.64 so we use a business unit ODX 

 

And then on the ODX the table itself looks like this 
 

 The data source itself is an SQL server data source 

Hi @william.sprangers

It does look like it is adding only the new rows like it should. Apparently it takes as long as if it added all the rows every time.

How is the database set up? You mentioned it was an elastic pool something, so is it set like with serverless setup and will increase decrease in speed depending on what is being requested?

My idea is that it takes this long because it is taking time increasing the speed of the database before doing the transfer.

It could probably be tested for if you ran another table with a similar amount of rows first and then this table right after. It should then be up to speed and would be as fast as necessary.

The elastic pool setup is such that each database being run gets a set number of vcores available (in this case it’s 12 vcores available per database) and then the elastic pool allocates resources as needed to databases. I tried running a different table before the current table (Inventtransorigin) but still seem to get the same problem unfortunately. I’m unsure if the elastic pool has some sort of spin up time but since this seems to be happening every time I execute the table I don’t think so.  

I seem to have the same issue for multiple tables from the data source, it looks like any table over 10+ million rows has this issue 

This is the query that I found in the profiler, it’s split up on different batches so I’ll mark each batch:

-- Batch 1 --

SELECT [INVENTTRANSID],[REFERENCECATEGORY],[REFERENCEID],[ITEMID],[ITEMINVENTDIMID],[PARTY],[DATAAREAID],[RECVERSION],[PARTITION],[RECID],[Replicate_ChangeDate],[DW_SourceCode] FROM [dbo].[R3_AX_INVENTTRANSORIGIN_R] WHERE 1 = 0

-- Batch 2 --

select @@trancount; SET FMTONLY ON select * from [dbo].[R3_AX_INVENTTRANSORIGIN_R] SET FMTONLY OFF exec ..sp_tablecollations_100 N'[dbo].[R3_AX_INVENTTRANSORIGIN_R]'

-- Batch 3 --

insert bulk [dbo].[R3_AX_INVENTTRANSORIGIN_R] ([INVENTTRANSID] NVarChar(20) COLLATE SQL_Latin1_General_CP1_CI_AS, [REFERENCECATEGORY] Int, [REFERENCEID] NVarChar(20) COLLATE SQL_Latin1_General_CP1_CI_AS, [ITEMID] NVarChar(20) COLLATE SQL_Latin1_General_CP1_CI_AS, [ITEMINVENTDIMID] NVarChar(20) COLLATE SQL_Latin1_General_CP1_CI_AS, [PARTY] BigInt, [DATAAREAID] NVarChar(4) COLLATE SQL_Latin1_General_CP1_CI_AS, [RECVERSION] Int, [PARTITION] BigInt, [RECID] BigInt, [Replicate_ChangeDate] DateTime2(7), [DW_SourceCode] VarChar(15) COLLATE SQL_Latin1_General_CP1_CI_AS) with (TABLOCK)

-- Batch 4 --

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'R3_AX_INVENTTRANSORIGIN_R')
BEGIN
    SELECT SUM (row_count) as [RowCount]
    FROM sys.dm_db_partition_stats
    WHERE object_id=OBJECT_ID(N'[dbo].[R3_AX_INVENTTRANSORIGIN_R]')
    AND (index_id=0 or index_id=1)
END
ELSE
BEGIN
    SELECT 0
END

-- Batch 5 --

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'R3_AX_INVENTTRANSORIGIN')
BEGIN
    SELECT SUM (row_count) as [RowCount]
    FROM sys.dm_db_partition_stats
    WHERE object_id=OBJECT_ID(N'[dbo].[R3_AX_INVENTTRANSORIGIN]')
    AND (index_id=0 or index_id=1)
END
ELSE
BEGIN
    SELECT 0
END
 

Last time I did a full load on the table was yesterday and then it took the same 25 minutes, after the full load completed I ran a new load incrementally and that also took 20 + minutes with most going to the ado.net transfer. 

@Christian Hauggaard Sure, here are some screenshots that I took during the start of a load and in the middle (starting at around 11) 

 

And this is during the load where the log IO spikes a bit

 

Hi @william.sprangers it appears that log IO might be the bottleneck. Could you please test scaling up the Azure SQL db to Business critical or Premium service tier?

 

This seemed to be the issue, thank you Christian! 

I ran a couple of loads and saw that the Log was peaking a lot. 
 

We changed from General Purpose to Business Critical and saw that the performance improved right away.