Mailing List Archive

Quoting IC (ic@tvcables.co.uk):
>
> I've noticed that IC pages take much longer to render than similar
> oscommerce pages on the same server, looking at the standard flypage code it
> seems to run an sql query on every item or item-field tag, is this correct?
>
> On some of our flypages we call the item-field tag a lot of times, wouldn't
> it be more efficient to have a single query tag with select * from products
> where sku = 'somesku' then use sql-param down the page?

Yes, [item-field] does a database lookup, it seems.

I just tried using [item-param] and it doesn't work, as only the "code"
is populated with the item SKU.

Usually since flypage is only looking up a certain number of fields, and
not doing complex queries, the performance has been sufficient. (As
compared to something like a category results page.)

But you're right, something like the [query] tag would be more
efficient. You could also consider using [timed-build] on your flypage.

Patches to Strap demo flypage.html are always welcome :-)

--
Josh Lavin
End Point Corporation

_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

On 09/16/2016 11:54 PM, IC wrote:
> Hi folks,
>
> I've noticed that IC pages take much longer to render than similar
> oscommerce pages on the same server, looking at the standard flypage code it
> seems to run an sql query on every item or item-field tag, is this correct?
>
> On some of our flypages we call the item-field tag a lot of times, wouldn't
> it be more efficient to have a single query tag with select * from products
> where sku = 'somesku' then use sql-param down the page?
>
> Regards,
> Andy

Hello Andy,

if I remember correctly, there is a possibility to "prefetch" product data.

You can define a "SpecialSub flypage" and return a structure with the product
data, which would be available on flypage.

I can't give you more details at the moment, but this SpecialSub is
called in Vend::Interpolate::fly_page.

Regards
Racke


--
Ecommerce and Linux consulting + Perl and web application programming.
Debian and Sympa administration.

_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

> Hello Andy,
>
> if I remember correctly, there is a possibility to "prefetch" product
data.
>
> You can define a "SpecialSub flypage" and return a structure with the
> product
> data, which would be available on flypage.
>
> I can't give you more details at the moment, but this SpecialSub is
> called in Vend::Interpolate::fly_page.
>
> Regards
> Racke
>

Hi Racke,

I need to run some tests on our current flypage, its running a crazy number
of queries, 271 for a single page load, we do use a highly customised
products table and liberal use of [item-field somefield] but not that many!
We also use timed build as well and I tested logged out in a new session.

Looking at the mysql log it ran this select sku query 124 times in 1 page
load:-

3807628 Query select sku from products where sku = 'my-test-sku'

Regards,
Andy


_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

On 09/17/2016 03:57 PM, IC wrote:
>> Hello Andy,
>>
>> if I remember correctly, there is a possibility to "prefetch" product
> data.
>>
>> You can define a "SpecialSub flypage" and return a structure with the
>> product
>> data, which would be available on flypage.
>>
>> I can't give you more details at the moment, but this SpecialSub is
>> called in Vend::Interpolate::fly_page.
>>
>> Regards
>> Racke
>>
>
> Hi Racke,
>
> I need to run some tests on our current flypage, its running a crazy number
> of queries, 271 for a single page load, we do use a highly customised
> products table and liberal use of [item-field somefield] but not that many!
> We also use timed build as well and I tested logged out in a new session.
>
> Looking at the mysql log it ran this select sku query 124 times in 1 page
> load:-
>
> 3807628 Query select sku from products where sku = 'my-test-sku'
>
> Regards,
> Andy

Hello Andy,

in that case it would really pay out to gather all the data needed
in the flypage SpecialSub and create a custom flypage.

Regards
Racke


--
Ecommerce and Linux consulting + Perl and web application programming.
Debian and Sympa administration.

_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

> >> if I remember correctly, there is a possibility to "prefetch" product
> > data.
> >>
> >> You can define a "SpecialSub flypage" and return a structure with the
> >> product
> >> data, which would be available on flypage.
> >>
> >> I can't give you more details at the moment, but this SpecialSub is
> >> called in Vend::Interpolate::fly_page.
> >>
> >> Regards
> >> Racke

Hi Racke,

I can't find much info on using SpecialSub flypage other than it will call a
block of perl code?

As a sanity check I tried a default flypage and although better it still
made 32 queries, 15 of these were the same select 'sku query' repeated,
before every query it repeats the same select sku query, its doubling up the
number of queries with repeats.

With a totally empty flypage.html IC stills runs 6 queries:-

3830956 Query select sku from products where sku = 'test-sku'
3830956 Query select template_page from products where sku = 'test-sku'
3830956 Query select sku from products where sku = 'test-sku'
3830956 Query select description from products where sku = 'test-sku'
3830956 Query select sku from products where sku = 'test-sku'
3830956 Query select category from products where sku = 'test-sku'

This is crazy, the whole page should be generated with 3 or so queries, but
IC is using 6 queries with no tags on a blank page, they need to be
aggregated into efficient queries.

I can't find in the core where these flypage queries are generated?

Regards,
Andy


_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

On Sat, 17 Sep 2016, IC wrote:

> With a totally empty flypage.html IC stills runs 6 queries:-
>
> 3830956 Query select sku from products where sku = 'test-sku'
> 3830956 Query select template_page from products where sku = 'test-sku'
> 3830956 Query select sku from products where sku = 'test-sku'
> 3830956 Query select description from products where sku = 'test-sku'
> 3830956 Query select sku from products where sku = 'test-sku'
> 3830956 Query select category from products where sku = 'test-sku'
>
> This is crazy, the whole page should be generated with 3 or so queries, but
> IC is using 6 queries with no tags on a blank page, they need to be
> aggregated into efficient queries.
>
> I can't find in the core where these flypage queries are generated?

They are generated by [item-field], as someone already mentioned.

You can rework the flypage to use a [query] tag to fetch whatever you want
in a single query and then use [sql-param] to access the values without
any further hits to the database.

Jon


--
Jon Jensen
End Point Corporation
https://www.endpoint.com/

_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

> > 3830956 Query select sku from products where sku = 'test-sku'
> > 3830956 Query select description from products where sku = 'test-sku'
> > 3830956 Query select sku from products where sku = 'test-sku'
> > 3830956 Query select category from products where sku = 'test-sku'
> >
> > This is crazy, the whole page should be generated with 3 or so queries,
> but
> > IC is using 6 queries with no tags on a blank page, they need to be
> > aggregated into efficient queries.
> >
> > I can't find in the core where these flypage queries are generated?
>
> They are generated by [item-field], as someone already mentioned.
>
> You can rework the flypage to use a [query] tag to fetch whatever you want
> in a single query and then use [sql-param] to access the values without
> any further hits to the database.
>
> Jon

Hi Jon,

Those 6 queries are generated by a *completely blank* flypage.html with no
[item] tag on it, there is no reason to run the query 'select sku from
products' 3 times without even a single tag on the page, where are these
queries generated in the core, I cannot find it??

Regards,
Andy



_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

On Sat, 17 Sep 2016, IC wrote:

> Those 6 queries are generated by a *completely blank* flypage.html with
> no [item] tag on it, there is no reason to run the query 'select sku
> from products' 3 times without even a single tag on the page, where are
> these queries generated in the core, I cannot find it??

I don't know why there are 6 queries, but at least one is required to know
whether you have a product with that SKU or if instead the missing.html
special page should be shown.

See sub Vend::Interpolate::fly_page for all the details.

Jon


--
Jon Jensen
End Point Corporation
https://www.endpoint.com/

_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

> I don't know why there are 6 queries, but at least one is required to know
> whether you have a product with that SKU or if instead the missing.html
> special page should be shown.
>
> See sub Vend::Interpolate::fly_page for all the details.

Well, I think I've found the cause of this, I can't write perl code like
this and I certainly couldn't have coded this but I can see what's wrong
with it.

It first checks to see if the sku exists, then it looks for a template page,
this is 2 queries.

Then in Track.pm (sub view_product) it gets the description and category
(why?), when it does this though it checks again the sku exists each time,
so that's another 4 queries.

That's the minimum 6 queries that even a blank flypage will generate.

Then each time an [item] tag is used or an [if-item] not only does it
generate a query for the tag it checks if the sku exists every time, so if
you have 20 [item] tags you get a further pointless 20 'select sku' queries,
this is why the strap and standard flypage generate over 30 queries for a
single product view.

The IC core is highly inefficient, it generates 2 mysql queries for every
item tag.

I don't know what the best fix for this is?

Regards,
Andy


_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

On Sat, 17 Sep 2016, IC wrote:

> Then in Track.pm (sub view_product) it gets the description and category
> (why?), when it does this though it checks again the sku exists each
> time, so that's another 4 queries.

You can disable that with `UserTrack no` in your catalog.cfg. Almost
nobody uses the UserTrack feature and it defaults to off in core and in
the Strap catalog template.

> Then each time an [item] tag is used or an [if-item] not only does it
> generate a query for the tag it checks if the sku exists every time, so
> if you have 20 [item] tags you get a further pointless 20 'select sku'
> queries, this is why the strap and standard flypage generate over 30
> queries for a single product view.
>
> The IC core is highly inefficient, it generates 2 mysql queries for
> every item tag.
>
> I don't know what the best fix for this is?

As I mentioned in a previous email in this thread, don't use the
[item-field] tag. It does a new SQL query for every invocation by design;
that's just not the tag you should use. I wrote before:

> You can rework the flypage to use a [query] tag to fetch whatever you
> want in a single query and then use [sql-param] to access the values
> without any further hits to the database.

HTH,
Jon


--
Jon Jensen
End Point Corporation
https://www.endpoint.com/

_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

> > Then in Track.pm (sub view_product) it gets the description and category
> > (why?), when it does this though it checks again the sku exists each
> > time, so that's another 4 queries.
>
> You can disable that with `UserTrack no` in your catalog.cfg. Almost
> nobody uses the UserTrack feature and it defaults to off in core and in
> the Strap catalog template.

Thanks, I'll disable that.

> [item-field] tag. It does a new SQL query for every invocation by design;

Why? Surely this is insane?

The whole IC distribution, even the latest version is littered with the item
and item-field tag, what is the point in designing a tag that is so
inefficient, in most cases it retrieves or checks a single item of data,
hence has to be used multiple times but uses 2 sql queries to do it.

I can get every item of data I need for the flypage from a single query with
left joins.

Regards,
Andy


_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

On Mon, 19 Sep 2016, IC wrote:

>> [item-field] tag. It does a new SQL query for every invocation by
>> design;
>
> Why? Surely this is insane?

I wasn't there ca. 1996 or 1997 when it was introduced, but I suspect the
reason is that [item-field] it predates Interchange using SQL databases at
all. At that time it used only flat files or DBM-like files, and plucking
each value out as requested was actually more efficient because you
fetched only what you need, when you needed it, and there was no external
database inefficiency in looking up individual fields.

> The whole IC distribution, even the latest version is littered with the
> item and item-field tag, what is the point in designing a tag that is so
> inefficient, in most cases it retrieves or checks a single item of data,
> hence has to be used multiple times but uses 2 sql queries to do it.

Patches are welcome if you would like to improve the efficiency of the
demo sites that come with Interchange!

Most of us running serious ecommerce sites using Interchange use very
little of that demo code, and replace it with customized code, not just
for efficiency but to suit our use cases. Thus no busy site I and most
others work with uses [item-field] for much of anything.

> I can get every item of data I need for the flypage from a single query
> with left joins.

That is what I recommend you do. :)

Jon


--
Jon Jensen
End Point Corporation
https://www.endpoint.com/

_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users

On 20/09/16 07:06, IC wrote:
>> [item-field] tag. It does a new SQL query for every invocation by design;
>
> Why? Surely this is insane?

It is, but as Jon pointed out it's for very old legacy reasons. I think
it *should* at least be fixed so it only does *one* query and not *two*,
though. I'll see if I can push that out sometime today, it shouldn't be
difficult.


Peter

_______________________________________________
interchange-users mailing list
interchange-users@icdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchange-users