VP-ASP :: Shopping Cart Software

Shopping Cart Software Solutions for anywhere in the World

US/Canada(Toll Free): +1 888 587 2278
Europe/UK: +44 (020) 7193 9408
Australia/New Zealand: +61 3 9016 4497

VP-ASP Shopping Cart Customer Forum

Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 VPCart Forum
 Add-ons for VP-ASP
 big yellow support HELP
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

devshb
Senior Member

United Kingdom
1898 Posts

Posted - July 20 2007 :  15:26:41  Show Profile  Visit devshb's Homepage  Reply with Quote
hi,
if the googlebase feed is taking a lot longer than the yahoo feed then it might be due to missing indexes on the prodcategories table.

The logic that the googlebase feed uses is a lot more complex than the yahoo feed, so generally it will be slower. For 43,000 items 15 minutes isn't too bad considering the logic/data it needs to go through to find/display things in the googlebase format, although checking if the prodcategories table is fully indexed (one index on intcategoryid, and another separate index on intcatalogid) should always be done as that'll speed up the feed as well as the cart generally.

so, 15 minutes for a 43,000 googlebase list might be considered normal, but it shouldn't be timing out or lasting anywhere near 2 hours; if that's happening then it's possible that either the tables aren't fully indexed, or there's a problem with the server. There's a connection and command timeout setting that can be used on sqlserver connections in asp; sometimes that needs to be set per connection otherwise a temporary pause on the server will cause the sqls which are running to hang, so it could be that.

If it's taking 15 minutes for just a few items, then it's definitely an index problem; you'd probably find that adding the right indexes will make it run just as quickly as the yahoo feed.

Simon Barnaby
Developer
[email protected]
www.BigYellowZone.com
Web Design, Online Marketing and VPASP addons

Edited by - devshb on July 20 2007 15:27:36
Go to Top of Page

RNN
VP-ASP New User

USA
54 Posts

Posted - July 20 2007 :  16:29:13  Show Profile  Visit RNN's Homepage  Reply with Quote
quote:
Originally posted by devshb

hi,
if the googlebase feed is taking a lot longer than the yahoo feed then it might be due to missing indexes on the prodcategories table.

The logic that the googlebase feed uses is a lot more complex than the yahoo feed, so generally it will be slower. For 43,000 items 15 minutes isn't too bad considering the logic/data it needs to go through to find/display things in the googlebase format,


I said 15 minutes for a few test items

quote:
although checking if the prodcategories table is fully indexed (one index on intcategoryid, and another separate index on intcatalogid) should always be done as that'll speed up the feed as well as the cart generally.


they are indexed?

quote:
or there's a problem with the server. There's a connection and command timeout setting that can be used on sqlserver connections in asp; sometimes that needs to be set per connection otherwise a temporary pause on the server will cause the sqls which are running to hang, so it could be that.


That is set at 40 minutes

It ran 8600 items on google before the time out.

I ran 5000 on Yahoo in 3-4 minutes.

Now trying to run the whole file with Yahoo and it created 36,000 records in 35 minutes.

Now the problem is Google???

We're getting there Simon

Thanks
Bob



Bob

Edited by - RNN on July 20 2007 16:32:58
Go to Top of Page

devshb
Senior Member

United Kingdom
1898 Posts

Posted - July 20 2007 :  16:40:57  Show Profile  Visit devshb's Homepage  Reply with Quote
hi bob,
yes, we're getting there, but we should really keep the thread in the support ticket rather than the vpasp forum, otherwise people'll get bored with reading something that doesn't relate to them.
yes, I know that your 15 minutes related to just the few items, and that's too long; it should be infinitely quicker than that. If yahoo is also taking 35 minutes (but for a full run) then that proves that some indexes are missing.
by default, even on the latest version of vpasp, the prodcategories, categories, and products tables aren't fully indexed when it comes to things like this, so it's definitely an index issue I think.
If you want us to check/tune it for you then we'd need to have access to the database, and vpasp admin so that we can check/add the relevant indexes depending on what your data and config options are setup like.

minimum indexes needed would be:
index1 on: prodcategories.intcategoryid
index2 on: prodcategories.intcatalogid
index3 on: products.ccategory
index4 on: categories.highercategoryid
index5 on: categories.hassubcategory

if any of those indexes are missing then they should be created, which will speed up the overall cart as well as the feeds.

indexes can make a huge difference; adding the right index can turn a 2-hour script into a 20 second script.

There are lots of different timeout settings:
1) script time outs
2) session timeouts
3) sqlserver connection timeouts
4) sqlserver command timeouts
(and some others as well I expect)
but, you shouldn't really need to change those if the database is properly indexed.

Simon Barnaby
Developer
[email protected]
www.BigYellowZone.com
Web Design, Online Marketing and VPASP addons

Edited by - devshb on July 20 2007 16:42:52
Go to Top of Page

RNN
VP-ASP New User

USA
54 Posts

Posted - July 28 2007 :  12:06:38  Show Profile  Visit RNN's Homepage  Reply with Quote
Hi Simon,

You said <but we should really keep the thread in the support ticket rather than the vpasp forum, otherwise people'll get bored with reading something that doesn't relate to them.>

I tried doing that too many time without a response, I know it's not you. You're very good on support and I don't want to imply anything but positive feedback for you.

With that said I think you need a better interface that can sort out spam.

Anyway I thought I had the indexing but it wasn't on those fields.

I did that and it ran but still took 5 hours.



Bob

Bob
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000