BasketItems Are Killing Me!! Help!

[keats76]

Ok now this is just plain odd ... we just went live with our new 7.0.3 site yesterday and we found that almost no one was able to checkout. I ran across some posts that mentioned the lack of indexes on the database and that led me to look at the sql server. It was pegged at 100%. I kicked off the profiler and I found some interesting results. The system was continually adding more and more basketitems to the db. So many, that now I have almost 600,000 records in the basket items table (only 265 in the baskets table).

Help! I killed the AC site and the process that was connecting to the database and the activity settled down. Here is a snapshot of the queries that were being run when I checked things out:

exec sp_executesql N'SELECT COUNT(*) As RecordCount FROM ac_BasketItems WHERE BasketItemId = @basketItemId',N'@BasketItemId int',@BasketItemId=1352643

exec sp_executesql N'UPDATE ac_BasketItems SET ParentItemId = @ParentItemId, BasketId = @BasketId, BasketShipmentId = @BasketShipmentId, ProductId = @ProductId, OptionList = @OptionList, TaxCodeId = @TaxCodeId, Name = @Name, Sku = @Sku, Price = @Price, Weight = @Weight, Quantity = @Quantity, LineMessage = @LineMessage, OrderItemTypeId = @OrderItemTypeId, OrderBy = @OrderBy, WrapStyleId = @WrapStyleId, GiftMessage = @GiftMessage, WishlistItemId = @WishlistItemId, ShippableId = @ShippableId, LastModifiedDate = @LastModifiedDate, TaxRate = @TaxRate, TaxAmount = @TaxAmount, KitList = @KitList, CustomFields = @CustomFields WHERE BasketItemId = @BasketItemId',N'@BasketItemId int,@ParentItemId int,@BasketId int,@BasketShipmentId int,@ProductId int,@OptionList nvarchar(4000),@TaxCodeId int,@Name nvarchar(25),@Sku nvarchar(2),@Price decimal(1,0),@Weight decimal(1,0),@Quantity smallint,@LineMessage nvarchar(4000),@OrderItemTypeId smallint,@OrderBy smallint,@WrapStyleId int,@GiftMessage nvarchar(4000),@WishlistItemId int,@ShippableId tinyint,@LastModifiedDate datetime,@TaxRate decimal(1,0),@TaxAmount decimal(1,0),@KitList nvarchar(4000),@CustomFields nvarchar(4000)',@BasketItemId=1352642,@ParentItemId=1201103,@BasketId=378,@BasketShipmentId=2209,@ProductId=0,@OptionList=NULL,@TaxCodeId=1,@Name=N'MCINTOSH, AL (WASHINGTON)',@Sku=N'18',@Price=0,@Weight=0,@Quantity=1,@LineMessage=NULL,@OrderItemTypeId=3,@OrderBy=20467,@WrapStyleId=NULL,@GiftMessage=NULL,@WishlistItemId=NULL,@ShippableId=0,@LastModifiedDate='2009-10-03


The profiler kept repeating this entry over and over again. Do you think someone killed the checkout process mid stride and created a loop somehow? This is very confusing.

Stream of consciousness here ... ok, looking at the basketitems table I see many tens of thousands of entries for tax records. Most of them are for the same basketid ... so that leads me to believe that the system is trying to enter a tax record over and over again and it must be missing out on some stop event.

Any thoughts? I had so many tax records from the old system (pre release 7) that I copied them over to the new database 7.0.3. Since the only real change was the core AC product, it leads me to believe that the manner in which the data was copied over wasn't ideal. Do you have any idea on how to fix this? Should I delete all of my tax entries and try to do a dataport?

Help!

Thanks, MIke

[jmestep]

I don't know if this is relative or not, but how many tax rules do you have? We had one client who had hundreds and every time you added something to the cart, the add to basket process slowed down exponentially. He had to go back to just a few rules.

[keats76]

Yeah, I have many thousands of rows (to accommodate every state in the USA ... although not perfectly). It appears when the OnePageCheckout is called for taxable orders the DB gets hammered and the system locks up. Able pumps row after row of tax records into the basketitems table. Can anyone help? I'm dead in the water at the moment.

Anyone know how fast I could have a service like Avalara up and running? I'm really hurting over here. Thanks, MIke

[keats76]

I have 3896 tax rules. Perhaps my problem is not understanding the new tax changes from AC7 to 7.0.3. It appears that when OnePageCheckout calls: basket.Recalculate(); the system is creating basketitems for each tax rule in my database (so far as I can tell). Is there a reason why the Postal Code Filter is not coming into play? Please advise. thnx

[keats76]

Ahhh crap ... it looks like the upgrade from 7 to 7.0.1 completely changed how the postal code filter was used. Someone please correct me if I'm wrong, but I guess now I need to rewrite my tax rules so that each old "postalcodefilter" is now a region or zone, and then the tax rule has to tie back to the new zone .... ugh. Guess I'll get to work.

[jmestep]

That might help, but I'm not sure. The client I worked with had around 500 tax rules for different zip codes and he had to set only a few tax rules in order to get his performance back.
If you have your tax settings set to not show taxes on basket, you might be able to code to have the basket and mini basket act like there were no taxes, then turn it back on at the checkout page. Since there isn't international VAT involved, that might work OK with your customers.

[keats76]

I reverted to version 7.0. It handled my ~4000 tax rules with ease. I finally got it working in 7.0.3 only to realize that my customers can't wait 10 minutes to check out while Able calculates tax. Someone really needs to take a look at this. Either there should be some stock 3rd party tax provider interfaces (a la payment gateways and shipping), or they should have usable tax handling out of the box. This was very frustrating.

--Mike

[jmestep]

I believe Logan is looking at this since I had posted a question/bug earlier. He was going to work on a way to speed it up.