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 intset @p6=1exec ecf_PriceDetail_List @catalogEntryId=id,@catalogNodeId=default,@MarketId=N'marketId',@CurrencyCodes=@p4,@CustomerPricing=@p5,@totalCount=@p6 output,@pagingOffset=0,@pagingCount=1select @p6
Reads - > 51 000
During - > 8 000This 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 topichttp://stackoverflow.com/questions/2736638/sql-query-slow-in-net-application-but-instantaneous-in-sql-server-management-stI 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.
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)
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.
Do you have any ideas?
When we have this situation again, I will share the screen of execution plan.
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?
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.
Without access to your system, it's very hard to guess. I would suggest you to contact our developer support service for further assistance.
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.
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.
As I said above - it seems to be a configuration issue. We've never seen such behavior in other environments