Improve speed of stored proc with lots of inserts [Azure-SQL] Running an S0. @@version = Microsoft SQL Azure (RTM) - 12.0.2000.8   Jul 21 2017 14:10:34   Copyright (C) 2017 Microsoft Corporation. All rights reserved.
The stored proc uses 3 tables. One main object with a relation to prices and a relation to features.



I have about 10.000 main objects with per piece 10 features and avg 3000 prices. So that will cause the prices table to have 30 million rows. 

I have a C# console app which runs my stored proc per mainobject and have speed up price and feature storage by using types and table values. I have a dictionary in my app with mainobject code and id so I can fill the id of items which do not need an insert. So that I can preserve the primary key, since they don't change much. I had 3 stored procedures at first. One per table, but instead of 3 stored proc executions, I moved to an all in one sp.
Here is my SQL code:

ALTER PROCEDURE [dbo].[OneSpToRuleThemAll]
(
    @code nvarchar(max),
    @name nvarchar(max),
    @lat float,
    @lng float,
    @country int,
    @operator int,
    @id int output,
    @dtFeatures as FeatureType READONLY,
    @dtPrices as PriceType READONLY
)
AS
BEGIN
    SET NOCOUNT ON
-- begin stored proc 1:
    IF (@id &gt; 0)
    begin
        UPDATE MainObject SET 
            [Name] = @name,
            Latitude = @lat,
            Longitude = @lng,
            CountryId = @country,
            OperatorId = @operator,
        WHERE code = @code;
    end
    else
    begin
        INSERT INTO [dbo].[MainObject]
           ([Code]
           ,[Name]
           ,[Latitude]
           ,[Longitude]
           ,[CountryId]
           ,[OperatorId])
     VALUES
           (@code, @name, @lat, @lng, @country, @operator);
            select @id = SCOPE_IDENTITY();
    end
-- begin stored proc 2:
    delete from features where MainObjectId = @id;
        INSERT INTO [dbo].[Features]
           ([ExternalId]
           ,[Mandatory]
           ,MainObjectId])
     select [ExternalId]
           ,[Mandatory]
           ,MainObjectId] from @dtFeatures;
-- begin stored proc 3:
    delete from prices where MainObjectid = @id;
    INSERT INTO [Prices]
        ([From]
        ,[To]
        ,[PriceInclDiscount]
        ,[PriceExclDiscount]
        ,[MainObjectId])
    select [From]
        ,[To]
        ,[PriceInclDiscount]
        ,[PriceExclDiscount]
        ,[MainObjectId] from @dtPrices;

END


edit as @S4v1n suggested, I had to post the execution plan. I used https://stackoverflow.com/a/4146573/169714 this code to get the full SQL command and pasted that in SSMS and got this queryplan for one of the mainobjects:



Here is the execution plan of the 2nd stored proc:
You should definitely separate it into multiple smaller procedures. Using just one procedure will cause a lot of locking and blocking, and generally degrade performance.

As for optimizing:

The first statement is update that updates data based on a @Code provided. Unless if its a clustered index created on it, you should create a nonclustered index on a Code column so you could prevent the whole table scan just to find the code.

Same logic could be applied for deleting. delete from prices where MainObjectid = @id; Having an index on MainObjectID, and generally on any Foreign Key column would provide you decent performance benefits (Whether you delete, or join the table, or even update records in parent table).

As you are inserting some data and deleting data based on ID of that insert, you could create a procedure that will do the first part of the code (Update or Insert if condition is met) and then return you the value of that ID(How to create objects with output parameters), which you could store in some int var in your c# app. Once you get a value you could execute the 2nd part of the procedure ,which includes deleting and some value inserting. Still it wont give you a best solution as you are inserting and deleting data in the same transaction - meaning it will most likely escalate into object lock, making you unable ( depending on transaction level) to make other select queries on those tables, but it will overall cut the % time of that update/insert initial statement.

In summary keep in mind that smaller and faster transactions are much more effective than larger and complex transactions, for performance and concurrency standpoint.