November Happy Hour will be moved to Thursday December 5th.
AI OnAI Off
The getting of prices is very slow
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
Jun 29, 2016 14:01
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.
Jul 05, 2016 13:33
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?
Jul 05, 2016 13:47
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.
Edited,
Jul 07, 2016 9:57
Without access to your system, it's very hard to guess. I would suggest you to contact our developer support service for further assistance.
.Q
Jul 11, 2016 9:23
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.
/Q
Edited,
Jul 11, 2016 16:09
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.
Jul 26, 2016 9:11
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.