High database CPU usage while checking out on Ucommerce
We noticed the other day that a client's site was running really slowly and when calling `ExecuteBasketPipeline` or checking out was returning a mismatch in the number of affected rows.
We initially thought the issue was caused by the basket being updated twice but actually it was caused by a slow database. Digging into the query executions we found that there were missing indexes, the one causing the biggest impact was one missing from the payments:
That the highlighted a missing index which was:
CREATE NONCLUSTERED INDEX [IX_TSD_uCommerce_Payment] ON [dbo].[uCommerce_Payment]
(
[OrderId] ASC
)
INCLUDE([TransactionId],[PaymentMethodName],[Created],[PaymentMethodId],[Fee],[FeePercentage],[PaymentStatusId],[Amount],[FeeTotal],[ReferenceId],[Tax],[TaxRate],[GrossAmount],[Guid])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
This was getting triggered by the following huuuuuge statement which is presumably generated by NHibernate. We tracked down the parameters by running this query:
SELECT TOP 10
* FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_query_plan(plan_handle) as qp CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) WHERE r.database_id = DB_ID('Your-Database-Name')
Having performance issues with your Ucommerce instance? Give us a call on +44 121 31 45 374 or [email protected] to see how we can help.
This was the sort of error message we were seeing:
UCommerce.Pipelines.PipelineException: Exception occoured while processing pipeline 'UCommerce.Pipelines.Transactions.Baskets.Basket.BasketPipeline'. See inner exception for details. ---> NHibernate.StaleStateException: Batch update returned unexpected row count from update; actual row count: 16; expected: 20 at NHibernate.AdoNet.Expectations.VerifyOutcomeBatched(Int32 expectedRowCount, Int32 rowCount) at NHibernate.AdoNet.SqlClientBatchingBatcher.DoExecuteBatch(IDbCommand ps) at NHibernate.AdoNet.AbstractBatcher.ExecuteBatchWithTiming(IDbCommand ps) at NHibernate.AdoNet.AbstractBatcher.ExecuteBatch() at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session) at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event) at NHibernate.Impl.SessionImpl.Flush() at UCommerce.Pipelines.Common.SavePurchaseOrderTask.Execute(PurchaseOrder purchaseOrder) at UCommerce.Pipelines.Pipeline`1.Execute(T subject) --- End of inner exception stack trace --- at UCommerce.Pipelines.Pipeline`1.Execute(T subject) at UCommerce.Transactions.TransactionLibraryInternal.ExecuteOrderPipeline(String pipelineName) at Castle.DynamicProxy.AbstractInvocation.Proceed() at UCommerce.Infrastructure.Interceptor.ExceptionLoggingInterceptor.Intercept(IInvocation invocation) at Castle.DynamicProxy.AbstractInvocation.Proceed() at Castle.DynamicProxy.AbstractInvocation.Proceed() at UCommerce.Infrastructure.Interceptor.ExceptionLoggingInterceptor.Intercept(IInvocation invocation) at Castle.DynamicProxy.AbstractInvocation.Proceed()
If you're interested, this was the SQL it was firing:
(@p0 uniqueidentifier,@p1 uniqueidentifier,@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 uniqueidentifier,@p10 uniqueidentifier,@p11 uniqueidentifier,@p12 uniqueidentifier)
select purchaseor0_.OrderId as OrderId20358_, purchaseor0_.BasketId as BasketId20358_, purchaseor0_.OrderNumber as OrderNum3_20358_, purchaseor0_.CultureCode as CultureC4_20358_, purchaseor0_.CreatedDate as CreatedD5_20358_, purchaseor0_.ModifiedOn as ModifiedOn20358_, purchaseor0_.CompletedDate as Complete7_20358_, purchaseor0_.Note as Note20358_, purchaseor0_.VAT as VAT20358_, purchaseor0_.OrderTotal as OrderTotal20358_, purchaseor0_.Discount as Discount20358_, purchaseor0_.DiscountTotal as Discoun12_20358_, purchaseor0_.ShippingTotal as Shippin13_20358_, purchaseor0_.PaymentTotal as Payment14_20358_, purchaseor0_.TaxTotal as TaxTotal20358_, purchaseor0_.SubTotal as SubTotal20358_, purchaseor0_.OrderGuid as OrderGuid20358_, purchaseor0_.Guid as Guid20358_, purchaseor0_.CustomerId as CustomerId20358_, purchaseor0_.OrderStatusId as OrderSt20_20358_, purchaseor0_.CurrencyId as CurrencyId20358_, purchaseor0_.ProductCatalogGroupId as Product22_20358_, purchaseor0_.BillingAddressId as Billing23_20358_ from uCommerce_PurchaseOrder purchaseor0_ where purchaseor0_.BasketId=@p0;
select purchaseor0_.OrderId as OrderId20358_0_, orderaddre1_.OrderAddressId as OrderAdd1_20322_1_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, orderaddre1_.FirstName as FirstName20322_1_, orderaddre1_.LastName as LastName20322_1_, orderaddre1_.EmailAddress as EmailAdd4_20322_1_, orderaddre1_.PhoneNumber as PhoneNum5_20322_1_, orderaddre1_.MobilePhoneNumber as MobilePh6_20322_1_, orderaddre1_.Line1 as Line7_20322_1_, orderaddre1_.Line2 as Line8_20322_1_, orderaddre1_.PostalCode as PostalCode20322_1_, orderaddre1_.City as City20322_1_, orderaddre1_.[State] as column11_20322_1_, orderaddre1_.Attention as Attention20322_1_, orderaddre1_.CompanyName as Company13_20322_1_, orderaddre1_.AddressName as Address14_20322_1_, orderaddre1_.Guid as Guid20322_1_, orderaddre1_.CountryId as CountryId20322_1_, orderaddre1_.OrderId as OrderId20322_1_ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_OrderAddress orderaddre1_ on purchaseor0_.BillingAddressId=orderaddre1_.OrderAddressId where purchaseor0_.BasketId=@p1;
select purchaseor0_.OrderId as OrderId20358_0_, orderprope1_.OrderPropertyId as OrderPro1_20325_1_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, orderprope1_.[key] as column2_20325_1_, orderprope1_.Value as Value20325_1_, orderprope1_.Guid as Guid20325_1_, orderprope1_.OrderId as OrderId20325_1_, orderprope1_.OrderLineId as OrderLin6_20325_1_, orderprope1_.OrderId as OrderId0__, orderprope1_.OrderPropertyId as OrderPro1_0__ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_OrderProperty orderprope1_ on purchaseor0_.OrderId=orderprope1_.OrderId and (orderprope1_.OrderLineId is null) where purchaseor0_.BasketId=@p2;
select purchaseor0_.OrderId as OrderId20358_0_, discounts1_.DiscountId as DiscountId20311_1_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, discounts1_.CampaignName as Campaign2_20311_1_, discounts1_.CampaignItemName as Campaign3_20311_1_, discounts1_.Description as Descript4_20311_1_, discounts1_.AmountOffTotal as AmountOf5_20311_1_, discounts1_.CreatedOn as CreatedOn20311_1_, discounts1_.ModifiedOn as ModifiedOn20311_1_, discounts1_.CreatedBy as CreatedBy20311_1_, discounts1_.ModifiedBy as ModifiedBy20311_1_, discounts1_.Guid as Guid20311_1_, discounts1_.OrderId as OrderId20311_1_, discounts1_.OrderId as OrderId0__, discounts1_.DiscountId as DiscountId0__ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_Discount discounts1_ on purchaseor0_.OrderId=discounts1_.OrderId where purchaseor0_.BasketId=@p3;
select purchaseor0_.OrderId as OrderId20358_0_, payments1_.PaymentId as PaymentId20328_1_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, payments1_.TransactionId as Transact2_20328_1_, payments1_.PaymentMethodName as PaymentM3_20328_1_, payments1_.Created as Created20328_1_, payments1_.Fee as Fee20328_1_, payments1_.FeeTotal as FeeTotal20328_1_, payments1_.FeePercentage as FeePerce7_20328_1_, payments1_.Amount as Amount20328_1_, payments1_.ReferenceId as Referenc9_20328_1_, payments1_.Tax as Tax20328_1_, payments1_.TaxRate as TaxRate20328_1_, payments1_.GrossAmount as GrossAm12_20328_1_, payments1_.Guid as Guid20328_1_, payments1_.PaymentMethodId as Payment14_20328_1_, payments1_.PaymentStatusId as Payment15_20328_1_, payments1_.OrderId as OrderId20328_1_, payments1_.OrderId as OrderId0__, payments1_.PaymentId as PaymentId0__ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_Payment payments1_ on purchaseor0_.OrderId=payments1_.OrderId where purchaseor0_.BasketId=@p4;
select purchaseor0_.OrderId as OrderId20358_0_, orderstatu1_.OrderStatusId as OrderSta1_20327_1_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, orderstatu1_.Name as Name20327_1_, orderstatu1_.Sort as Sort20327_1_, orderstatu1_.RenderChildren as RenderCh4_20327_1_, orderstatu1_.RenderInMenu as RenderIn5_20327_1_, orderstatu1_.ExternalId as ExternalId20327_1_, orderstatu1_.IncludeInAuditTrail as IncludeI7_20327_1_, orderstatu1_.AllowUpdate as AllowUpd8_20327_1_, orderstatu1_.AlwaysAvailable as AlwaysAv9_20327_1_, orderstatu1_.Pipeline as Pipeline20327_1_, orderstatu1_.AllowOrderEdit as AllowOr11_20327_1_, orderstatu1_.Guid as Guid20327_1_, orderstatu1_.NextOrderStatusId as NextOrd13_20327_1_ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_OrderStatus orderstatu1_ on purchaseor0_.OrderStatusId=orderstatu1_.OrderStatusId where purchaseor0_.BasketId=@p5;
select purchaseor0_.OrderId as OrderId20358_0_, orderstatu1_.OrderStatusAuditId as OrderSta1_20326_1_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, orderstatu1_.CreatedOn as CreatedOn20326_1_, orderstatu1_.CreatedBy as CreatedBy20326_1_, orderstatu1_.Message as Message20326_1_, orderstatu1_.Guid as Guid20326_1_, orderstatu1_.NewOrderStatusId as NewOrder6_20326_1_, orderstatu1_.OrderId as OrderId20326_1_, orderstatu1_.OrderId as OrderId0__, orderstatu1_.OrderStatusAuditId as OrderSta1_0__ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_OrderStatusAudit orderstatu1_ on purchaseor0_.OrderId=orderstatu1_.OrderId where purchaseor0_.BasketId=@p6;
select purchaseor0_.OrderId as OrderId20358_0_, orderlines1_.OrderLineId as OrderLin1_20323_1_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, orderlines1_.Sku as Sku20323_1_, orderlines1_.ProductName as ProductN3_20323_1_, orderlines1_.Price as Price20323_1_, orderlines1_.Quantity as Quantity20323_1_, orderlines1_.CreatedOn as CreatedOn20323_1_, orderlines1_.CreatedBy as CreatedBy20323_1_, orderlines1_.Discount as Discount20323_1_, orderlines1_.UnitDiscount as UnitDisc9_20323_1_, orderlines1_.VAT as VAT20323_1_, orderlines1_.Total as Total20323_1_, orderlines1_.VATRate as VATRate20323_1_, orderlines1_.VariantSku as VariantSku20323_1_, orderlines1_.Guid as Guid20323_1_, orderlines1_.ShipmentId as ShipmentId20323_1_, orderlines1_.OrderId as OrderId20323_1_, orderlines1_.OrderId as OrderId0__, orderlines1_.OrderLineId as OrderLin1_0__ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_OrderLine orderlines1_ on purchaseor0_.OrderId=orderlines1_.OrderId where purchaseor0_.BasketId=@p7;
select purchaseor0_.OrderId as OrderId20358_0_, orderlines1_.OrderLineId as OrderLin1_20323_1_, orderprope2_.OrderPropertyId as OrderPro1_20325_2_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, orderlines1_.Sku as Sku20323_1_, orderlines1_.ProductName as ProductN3_20323_1_, orderlines1_.Price as Price20323_1_, orderlines1_.Quantity as Quantity20323_1_, orderlines1_.CreatedOn as CreatedOn20323_1_, orderlines1_.CreatedBy as CreatedBy20323_1_, orderlines1_.Discount as Discount20323_1_, orderlines1_.UnitDiscount as UnitDisc9_20323_1_, orderlines1_.VAT as VAT20323_1_, orderlines1_.Total as Total20323_1_, orderlines1_.VATRate as VATRate20323_1_, orderlines1_.VariantSku as VariantSku20323_1_, orderlines1_.Guid as Guid20323_1_, orderlines1_.ShipmentId as ShipmentId20323_1_, orderlines1_.OrderId as OrderId20323_1_, orderlines1_.OrderId as OrderId0__, orderlines1_.OrderLineId as OrderLin1_0__, orderprope2_.[key] as column2_20325_2_, orderprope2_.Value as Value20325_2_, orderprope2_.Guid as Guid20325_2_, orderprope2_.OrderId as OrderId20325_2_, orderprope2_.OrderLineId as OrderLin6_20325_2_, orderprope2_.OrderLineId as OrderLin6_1__, orderprope2_.OrderPropertyId as OrderPro1_1__ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_OrderLine orderlines1_ on purchaseor0_.OrderId=orderlines1_.OrderId left outer join uCommerce_OrderProperty orderprope2_ on orderlines1_.OrderLineId=orderprope2_.OrderLineId where purchaseor0_.BasketId=@p8;
select purchaseor0_.OrderId as OrderId20358_0_, orderlines1_.OrderLineId as OrderLin1_20323_1_, discount3_.DiscountId as DiscountId20311_2_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, orderlines1_.Sku as Sku20323_1_, orderlines1_.ProductName as ProductN3_20323_1_, orderlines1_.Price as Price20323_1_, orderlines1_.Quantity as Quantity20323_1_, orderlines1_.CreatedOn as CreatedOn20323_1_, orderlines1_.CreatedBy as CreatedBy20323_1_, orderlines1_.Discount as Discount20323_1_, orderlines1_.UnitDiscount as UnitDisc9_20323_1_, orderlines1_.VAT as VAT20323_1_, orderlines1_.Total as Total20323_1_, orderlines1_.VATRate as VATRate20323_1_, orderlines1_.VariantSku as VariantSku20323_1_, orderlines1_.Guid as Guid20323_1_, orderlines1_.ShipmentId as ShipmentId20323_1_, orderlines1_.OrderId as OrderId20323_1_, orderlines1_.OrderId as OrderId0__, orderlines1_.OrderLineId as OrderLin1_0__, discount3_.CampaignName as Campaign2_20311_2_, discount3_.CampaignItemName as Campaign3_20311_2_, discount3_.Description as Descript4_20311_2_, discount3_.AmountOffTotal as AmountOf5_20311_2_, discount3_.CreatedOn as CreatedOn20311_2_, discount3_.ModifiedOn as ModifiedOn20311_2_, discount3_.CreatedBy as CreatedBy20311_2_, discount3_.ModifiedBy as ModifiedBy20311_2_, discount3_.Guid as Guid20311_2_, discount3_.OrderId as OrderId20311_2_, discounts2_.OrderLineId as OrderLin2_1__, discounts2_.DiscountId as DiscountId1__ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_OrderLine orderlines1_ on purchaseor0_.OrderId=orderlines1_.OrderId left outer join uCommerce_OrderLineDiscountRelation discounts2_ on orderlines1_.OrderLineId=discounts2_.OrderLineId left outer join uCommerce_Discount discount3_ on discounts2_.DiscountId=discount3_.DiscountId where purchaseor0_.BasketId=@p9;
select purchaseor0_.OrderId as OrderId20358_0_, shipments1_.ShipmentId as ShipmentId20362_1_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, shipments1_.ShipmentName as Shipment2_20362_1_, shipments1_.CreatedOn as CreatedOn20362_1_, shipments1_.CreatedBy as CreatedBy20362_1_, shipments1_.ShipmentPrice as Shipment5_20362_1_, shipments1_.DeliveryNote as Delivery6_20362_1_, shipments1_.TrackAndTrace as TrackAnd7_20362_1_, shipments1_.Tax as Tax20362_1_, shipments1_.TaxRate as TaxRate20362_1_, shipments1_.ShipmentTotal as Shipmen10_20362_1_, shipments1_.ShipmentDiscount as Shipmen11_20362_1_, shipments1_.Guid as Guid20362_1_, shipments1_.ShipmentAddressId as Shipmen13_20362_1_, shipments1_.ShippingMethodId as Shippin14_20362_1_, shipments1_.OrderId as OrderId20362_1_, shipments1_.OrderId as OrderId0__, shipments1_.ShipmentId as ShipmentId0__ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_Shipment shipments1_ on purchaseor0_.OrderId=shipments1_.OrderId where purchaseor0_.BasketId=@p10;
select purchaseor0_.OrderId as OrderId20358_0_, shipments1_.ShipmentId as ShipmentId20362_1_, orderlines2_.OrderLineId as OrderLin1_20323_2_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, shipments1_.ShipmentName as Shipment2_20362_1_, shipments1_.CreatedOn as CreatedOn20362_1_, shipments1_.CreatedBy as CreatedBy20362_1_, shipments1_.ShipmentPrice as Shipment5_20362_1_, shipments1_.DeliveryNote as Delivery6_20362_1_, shipments1_.TrackAndTrace as TrackAnd7_20362_1_, shipments1_.Tax as Tax20362_1_, shipments1_.TaxRate as TaxRate20362_1_, shipments1_.ShipmentTotal as Shipmen10_20362_1_, shipments1_.ShipmentDiscount as Shipmen11_20362_1_, shipments1_.Guid as Guid20362_1_, shipments1_.ShipmentAddressId as Shipmen13_20362_1_, shipments1_.ShippingMethodId as Shippin14_20362_1_, shipments1_.OrderId as OrderId20362_1_, shipments1_.OrderId as OrderId0__, shipments1_.ShipmentId as ShipmentId0__, orderlines2_.Sku as Sku20323_2_, orderlines2_.ProductName as ProductN3_20323_2_, orderlines2_.Price as Price20323_2_, orderlines2_.Quantity as Quantity20323_2_, orderlines2_.CreatedOn as CreatedOn20323_2_, orderlines2_.CreatedBy as CreatedBy20323_2_, orderlines2_.Discount as Discount20323_2_, orderlines2_.UnitDiscount as UnitDisc9_20323_2_, orderlines2_.VAT as VAT20323_2_, orderlines2_.Total as Total20323_2_, orderlines2_.VATRate as VATRate20323_2_, orderlines2_.VariantSku as VariantSku20323_2_, orderlines2_.Guid as Guid20323_2_, orderlines2_.ShipmentId as ShipmentId20323_2_, orderlines2_.OrderId as OrderId20323_2_, orderlines2_.ShipmentId as ShipmentId1__, orderlines2_.OrderLineId as OrderLin1_1__ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_Shipment shipments1_ on purchaseor0_.OrderId=shipments1_.OrderId left outer join uCommerce_OrderLine orderlines2_ on shipments1_.ShipmentId=orderlines2_.ShipmentId where purchaseor0_.BasketId=@p11;
select purchaseor0_.OrderId as OrderId20358_0_, shipments1_.ShipmentId as ShipmentId20362_1_, discount3_.DiscountId as DiscountId20311_2_, purchaseor0_.BasketId as BasketId20358_0_, purchaseor0_.OrderNumber as OrderNum3_20358_0_, purchaseor0_.CultureCode as CultureC4_20358_0_, purchaseor0_.CreatedDate as CreatedD5_20358_0_, purchaseor0_.ModifiedOn as ModifiedOn20358_0_, purchaseor0_.CompletedDate as Complete7_20358_0_, purchaseor0_.Note as Note20358_0_, purchaseor0_.VAT as VAT20358_0_, purchaseor0_.OrderTotal as OrderTotal20358_0_, purchaseor0_.Discount as Discount20358_0_, purchaseor0_.DiscountTotal as Discoun12_20358_0_, purchaseor0_.ShippingTotal as Shippin13_20358_0_, purchaseor0_.PaymentTotal as Payment14_20358_0_, purchaseor0_.TaxTotal as TaxTotal20358_0_, purchaseor0_.SubTotal as SubTotal20358_0_, purchaseor0_.OrderGuid as OrderGuid20358_0_, purchaseor0_.Guid as Guid20358_0_, purchaseor0_.CustomerId as CustomerId20358_0_, purchaseor0_.OrderStatusId as OrderSt20_20358_0_, purchaseor0_.CurrencyId as CurrencyId20358_0_, purchaseor0_.ProductCatalogGroupId as Product22_20358_0_, purchaseor0_.BillingAddressId as Billing23_20358_0_, shipments1_.ShipmentName as Shipment2_20362_1_, shipments1_.CreatedOn as CreatedOn20362_1_, shipments1_.CreatedBy as CreatedBy20362_1_, shipments1_.ShipmentPrice as Shipment5_20362_1_, shipments1_.DeliveryNote as Delivery6_20362_1_, shipments1_.TrackAndTrace as TrackAnd7_20362_1_, shipments1_.Tax as Tax20362_1_, shipments1_.TaxRate as TaxRate20362_1_, shipments1_.ShipmentTotal as Shipmen10_20362_1_, shipments1_.ShipmentDiscount as Shipmen11_20362_1_, shipments1_.Guid as Guid20362_1_, shipments1_.ShipmentAddressId as Shipmen13_20362_1_, shipments1_.ShippingMethodId as Shippin14_20362_1_, shipments1_.OrderId as OrderId20362_1_, shipments1_.OrderId as OrderId0__, shipments1_.ShipmentId as ShipmentId0__, discount3_.CampaignName as Campaign2_20311_2_, discount3_.CampaignItemName as Campaign3_20311_2_, discount3_.Description as Descript4_20311_2_, discount3_.AmountOffTotal as AmountOf5_20311_2_, discount3_.CreatedOn as CreatedOn20311_2_, discount3_.ModifiedOn as ModifiedOn20311_2_, discount3_.CreatedBy as CreatedBy20311_2_, discount3_.ModifiedBy as ModifiedBy20311_2_, discount3_.Guid as Guid20311_2_, discount3_.OrderId as OrderId20311_2_, discounts2_.ShipmentId as ShipmentId1__, discounts2_.DiscountId as DiscountId1__ from uCommerce_PurchaseOrder purchaseor0_ left outer join uCommerce_Shipment shipments1_ on purchaseor0_.OrderId=shipments1_.OrderId left outer join uCommerce_ShipmentDiscountRelation discounts2_ on shipments1_.ShipmentId=discounts2_.ShipmentId left outer join uCommerce_Discount discount3_ on discounts2_.DiscountId=discount3_.DiscountId where purchaseor0_.BasketId=@p12;
Liked this post? Got a suggestion? Leave a comment