1

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:

Execution plan Azure

... and local:

Execution plan Local

closed as off-topic by LowlyDBA, hot2use, Erwin Brandstetter, Marco, Erik Darling Apr 7 '18 at 1:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, hot2use, Marco, Erik Darling
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 4
    What does the execution plan look like? statistics Io results? Any wait types for the sessions inserting? – Martin Smith Oct 3 '16 at 5:48
  • 1
    Please post the table defs – paparazzo Oct 3 '16 at 13:49
  • 1
    Looks like the eager spool is causing the performance degradation. Those are performed inside tempdb, which is out of your control in Azure. Additionally, you might want to update statistics on ItemProperty at various stages during the batch, because that could also be causing problems. – Randolph West Oct 4 '16 at 5:32
  • 2
  • 1
    @TobiasBengtsson No, but this was edited over a year and a half ago and the only answer received no response. Generally a question open this long without an update does not get answered and ends up not being useful. If you're still interested in the answer, I'd recommend using pastetheplan.com to upload the query plans if you still have them instead of using pictures. That will increase your odds of getting responses. I can only retract my downvote if you edit again, but I will if I can. – LowlyDBA Apr 3 '18 at 13:39
0

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 

Not the answer you're looking for? Browse other questions tagged or ask your own question.