Azure SQL very slow INSERT in stored procedure I have the following stored procedure, inserting new rows in a join table:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertNewRows]
    @PropertyId int,
    @ItemGroupId int,
    @Discriminator nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO ItemProperty (PropertyId, Discriminator, ItemId)
    SELECT @PropertyId, @Discriminator, Id FROM Item WHERE ItemTypeId IN
        (SELECT Id From ItemType WHERE ItemGroupId = @ItemGroupId)
END;


In this case, items belong to a type which in turn belongs to a group. I want to create the rows for all items in a group.

I execute the stored procedure for test purposes using:

BEGIN TRANSACTION

EXEC InsertNewRows 1, 10, 'Discriminator';
EXEC InsertNewRows 2, 10, 'Discriminator';
EXEC InsertNewRows 3, 10, 'Discriminator';
EXEC InsertNewRows 4, 10, 'Discriminator';
EXEC InsertNewRows 5, 10, 'Discriminator';
EXEC InsertNewRows 6, 10, 'Discriminator';
EXEC InsertNewRows 7, 10, 'Discriminator';
EXEC InsertNewRows 8, 10, 'Discriminator';
EXEC InsertNewRows 9, 10, 'Discriminator';
EXEC InsertNewRows 10, 10, 'Discriminator';

COMMIT


The group used for testing contains around 80,000 items.

On my local SQL Server 2014 Developer Edition, the execution takes around 10 seconds to perform. When i try to run it on a S2 instance of Azure SQL it takes around 1 minute per row so in total over 10 minutes. I even tried scaling up to a P15 for testing, and it performs worse (twice the time) than my local SQL Server, running on a regular laptop. Disabling the indices for ItemId and PropertyId before executing roughly halves the speed both locally and in Azure, but the relative performance is the same.

I am aware of the fact that Azure SQL is generally quite slow on large INSERTs, but it seems like at least a P15 instance should outperform a local SQL Server 2014 Developer Edition. Is there anything i can do to tweak my stored procedure or database setup to make it work faster in Azure?

EDIT: Here is the execution plan for Azure:



... and local:
This probably will not help but could you give it a try

INSERT INTO ItemProperty (PropertyId, Discriminator, ItemId)
SELECT @PropertyId, @Discriminator, Id 
  FROM Item 
  JOIN ItemType 
    ON ItemType.ID = Item.ItemTypeId  
   AND ItemType.ItemGroupId = @ItemGroupId