How can I get better performance with my sql query in a SP?

How can I get better performance with my sql query in a SP? if you look at below my execution pan you will see that :

IF NOT EXISTS(SELECT * FROM Common.[CustomerEntityIds] WHERE EntityType = @EntityType AND CustomerId = @CustomerId)

Open in new window


has alot of memory usage. How  can I reduce that?

ALTER PROCEDURE [Common].[SaveCustomerEntityIds] 
(
	@EntityType	NVARCHAR(128),
	@CustomerId	INT,
	@EntityId	INT OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS(SELECT * FROM Common.[CustomerEntityIds] WHERE EntityType = @EntityType AND CustomerId = @CustomerId)
	BEGIN
		INSERT INTO Common.[CustomerEntityIds]
					([EntityId]
					,[CustomerId]
					,[EntityType])
				VALUES
					(0
					,@CustomerId
					,@EntityType)
	END

	UPDATE	Common.[CustomerEntityIds]
	SET		[EntityId] = ([EntityId]) + 1
	WHERE	[EntityType] = @EntityType
			AND CustomerId = @CustomerId

	SELECT	@EntityId = EntityId
	FROM	Common.[CustomerEntityIds]
	WHERE	[EntityType] = @EntityType
			AND CustomerId = @CustomerId
END

Open in new window





picture of execution planpicture of execution planExecutionPlan2.sqlplan
programmeristAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
I hope there aren't any indexes on Common.[CustomerEntityIds] for the columns EntityType and CustomerId columns..
Since all the WHERE conditions are based upon these 2 columns, kindly try creating this index if not available..
CREATE INDEX IX_CustomerEntitity ON Common.[CustomerEntityIds] (EntityType, CustomerId);

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Also try this modified version to reduce one SELECT at the end..
Since all the SELECT or UPDATE operation are based upon those 2 columns, creating the index shared above should help..
ALTER PROCEDURE [Common].[SaveCustomerEntityIds] 
(
	@EntityType	NVARCHAR(128),
	@CustomerId	INT,
	@EntityId	INT OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT @EntityId = [EntityId] + 1 FROM Common.[CustomerEntityIds] WHERE EntityType = @EntityType AND CustomerId = @CustomerId

	IF @EntityId IS NULL
	BEGIN
		INSERT INTO Common.[CustomerEntityIds]
					([EntityId]
					,[CustomerId]
					,[EntityType])
				VALUES
					(1
					,@CustomerId
					,@EntityType)
	END
	ELSE
	BEGIN 
	UPDATE	Common.[CustomerEntityIds]
	SET		[EntityId] = @EntityId
	WHERE	[EntityType] = @EntityType
			AND CustomerId = @CustomerId
	END

	SET @EntityId = ISNULL(@EntityId,1)
END

Open in new window

ste5anSenior DeveloperCommented:
Please post the table definition of Common.CustomerEntityIds. Include the existing indices. And explain your data, concurrency and process model.
Cause from a higher perspective, it seems that this procedure must be called only once per (CustomerId, EntityType). Which sounds not feasible by the procedure name and given information.

Then: it seems like you're using an EAV model. Here you will have always performance problems for some use-cases. The only thing which works here is (over-) indexing, if the disk space allows it.

You need an index on (CustomerID, EntityType) including (EntityID), but the order of the columns depend on the selectivity of that columns. So you should test both combinations.

Then you should carefully examine your main use-cases here, cause (CustomerID, EntityType) or (EntityType, CustomerID) are candidates for the first two columns in your clustered index (CIX).

And last but not least: How is your clustered index defined? Is it maybe the common my primary key (PK) is also CIX? Then I need to ask: Is your PK by any chance using EntityID? Cause then you need to change your procedure internals, cause you would insert your row at the beginning of the table or range and then move the row possibly again.

For the actual code, you can remove the table access in the last SELECT by using the OUTPUT clause, e.g.

ALTER PROCEDURE Common.SaveCustomerEntityIds (
    @EntityType NVARCHAR(128) ,
    @CustomerId INT ,
    @EntityId INT OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ID TABLE (
        ID INT
    );

    IF NOT EXISTS (   SELECT *
                      FROM   Common.CustomerEntityIds
                      WHERE  EntityType = @EntityType
                             AND CustomerId = @CustomerId )
    BEGIN
        SET @EntityId = 1;
        INSERT INTO Common.CustomerEntityIds ( EntityId ,
                                               CustomerId ,
                                               EntityType )
        VALUES ( @EntityId, @CustomerId, @EntityType );
    END;
    ELSE
    BEGIN
        UPDATE Common.CustomerEntityIds
        SET    EntityId = EntityId + 1
        OUTPUT Inserted.EntityID
        INTO @ID
        WHERE  EntityType = @EntityType
               AND CustomerId = @CustomerId;

        SELECT @EntityId = I.ID
        FROM   @ID I;
    END;
END;

Open in new window


And when your use-case allows this kind of logic: Take also a look at the MERGE statement.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.