The getting of prices is very slow

Dzmitry_Salauyou

Vote:

Hi.

Probable, somebody knows, why the getting of prices is very slow.

I get a price using IPriceDetailService.

var priceDetailService = ServiceLocator.Current.GetInstance();
int total;
var price = priceDetailService.List(productLink, marketId, new PriceFilter(), 0, 1, out total);

The command sends a request to sql server:(from sql profiler)

declare @p6 int
set @p6=1
exec ecf_PriceDetail_List @catalogEntryId=id,@catalogNodeId=default,@MarketId=N'marketId',@CurrencyCodes=@p4,@CustomerPricing=@p5,@totalCount=@p6 output,@pagingOffset=0,@pagingCount=1
select @p6

Reads - > 51 000

During - > 8 000

This request runs about 8 secs, when we execute from .net application. If we run this script using sql managment studio, we will spend < 1 sec.

I read the topic

http://stackoverflow.com/questions/2736638/sql-query-slow-in-net-application-but-instantaneous-in-sql-server-management-st

I tried to turn on the option(ARITHABORT). It helped me. But I am not sure, that this fix is a solution. Because We have this case for test db. And I don't have any problems for my local db.

#150785

Jun 29, 2016 11:41

Quan Mai

Vote:

Hi,

This is a very strange issue - I think it's an issue with the cached execution plan on your test db. The execution plan which was used for SSMS with ARITHABORT = ON is correct, but the execution plan for your application with ARITHABORT = OFF was somehow troublesome and SQL Server uses a non optimal/faulty execution plan for the query.

You can try to rebuild that execution plan, or even clear all the execution plans on that test db (as some others might have problem as well)

DBCC FREEPROCCACHE
GO

#150846

Jun 29, 2016 14:01

Dzmitry_Salauyou

Vote:

Hi Quan.

Thank your for your replay.

Yes, your proposal can fix this case now. But I am not sure that is solution because we have the same situation on all environments periodically.

EPiServer.Commerce.Core.9.14.1

Do you have any ideas?

When we have this situation again, I will share the screen of execution plan.

#150956

Jul 05, 2016 13:33

Quan Mai

Vote:

I don't think it's a Commerce's problem but more like SQL configuration issue. Does the problem come back after you clear the execution plan cache?

#150957

Jul 05, 2016 13:47

Dzmitry_Salauyou

Vote:

Yes, ths problem came back after clearing the execution plan cache.

I agree with you that's the issue of sql. But I am not sure that we have any problems with sql configuration. I think we have any troubles with sp ecf_PriceDetail_List. Seems this sp does extra joins.

#151027

Edited, Jul 07, 2016 9:57

Quan Mai

Vote:

Without access to your system, it's very hard to guess. I would suggest you to contact our developer support service for further assistance.

#151073

Jul 11, 2016 9:23

Quan Mai

Vote:

By the way, it's worth noting that if you want to get prices for displaying to end-customer, you should be using IPriceService - the default implementation has cache so it's much faster. IPriceDetailService should only be used if you want to edit price - as the default implementation has no cache at all so each call will hit the database.

#151089

Edited, Jul 11, 2016 16:09

Dzmitry_Salauyou

Vote:

Hi Quan Mai

Thank you for your advice. I will use the IPriceService instead of the IPriceDetailService.

But I wonder still, why the sp ecf_PriceDetail_List works slow sometimes.

#151503

Jul 26, 2016 9:11

Quan Mai

Vote:

As I said above - it seems to be a configuration issue. We've never seen such behavior in other environments

#151504

Jul 26, 2016 9:23