0

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.

enter image description here

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 > 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:

enter image description here

Here is the execution plan of the 2nd stored proc:

enter image description here

1 Answer 1

1

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.

5
  • I had it split up before, but because I wanted to leverage the Async opening of a connection and Async query execution. I'd merge the stored procs, because Azure has a workerprocess limit of 60 for an S0 instance. And Async methods seem to ignore the max pool size in the connection string causing errors... Thanks for the index suggestions. I will index my foreign keys and move back to separate stored procs. ps. currently running everything in sync. Nothing async because of the max of 60 worker processes. Jul 26, 2017 at 13:48
  • ps. do you recommend wrapping the 3 stored procs in a transaction for perf? or not? Due to flushing the log to disk? Jul 26, 2017 at 14:22
  • No, 3 completely separated transactions, otherwise running 1 or 3 procedures within a same transactions makes no difference
    – S4V1N
    Jul 26, 2017 at 15:49
  • I have tested it. With a transaction wrapping all 3 takes 4:29 and without a transaction it takes 4:35. Both for storing the same 100 instances of mainobjects. Will look into adding indexes to foreign keys. Jul 27, 2017 at 8:05
  • Yeah please do. Regardless i would keep those procedures separated, for other reasons. Also if indexes wont help you, update a question and post an execution plan, alongside procedure definition so we could try to work something out
    – S4V1N
    Jul 27, 2017 at 8:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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