Ecommerce software home
Shopping Cart Software Forum for Ecommerce Templates
 
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

Find us on Facebook Follow us on Twitter View our YouTube channel
Search our site
Forum Search
Google Site Search
 All Forums
 General
 Suggestions - NOT QUESTIONS
 Reprogramming of the PRODUCTS page for speed.
Author « Topic »  

Graham Slaughter
Ecommerce Template Expert

836 Posts

Posted - 02/07/2025 :  10:16:00  
Howdy Vince,

This issue has continued to become a bigger issue as our database has become more and more complicated.

in the incpropductbody.asp file

It looks like we iterate through the sSQL data return using:
do while NOT rs.EOF AND localcount<rs.PageSize


instead of pulling the data into an array and iterating through that way.

If I'm not wrong, I think the current method is more server intensive and holds the connection open instead of pulling the data once and being done the database call.

I THINK that makes the page load much more slowly. You can experience the intensity of the slow page load using this page and just changing the ? querystring to a random number or something (otherwise the page loads from Cloudflare Cache quickly)
https://www.suspension.com/products/honda_civic_1995?124

https://www.suspension.com/products/honda_civic_1995?124

Yes we are only talking 3-5 seconds. Unfortunately, that falls outside of what most people want to wait these days. Page speed continues to become more and more critical not just to customer experience, but to SEO ranking as well.

This page in particular is one of the worst because it has so many products. Other pages are less of an issue.

SO! Why are we not modifying pagesize variable and paginating through the data?

#1 - our JS based FILTER doesn't work with data shown that way. It can only filter client side, not server side (use the filter and you'll understand)
#2 - paginating data is less SEO friendly (I think)
#3 - paginating creates more clicks (longer funnel) for a customer which is less desirable
#4 - people don't tend to browse past page #1, so anything at the end of that tail is almost never seen.

We can circumvent this issue somewhat by aggressively using Cloudflare caching, but anytime we need to purge the cache, the customer experience tanks until the cache is populated again.

Hopefully this is worth considering.

Graham Slaughter
Ecommerce Template Expert

836 Posts

Posted - 02/13/2025 :  10:06:14  
This is probably too complicated for most folks (sorry), but Vince could understand it well.

I prodded this issue for a couple hours today and learned that it's not strictly an issue with us not using pagination on a page with a couple hundred parts.

If I use a variable orprodsperpage=10 to utilize pagination
and utilize the standard incsidefilter.asp instead of the javascript filter one we created
the page takes almost just as long to load. The side filter alone takes 2-3 seconds to load before a single part is displayed

I put in some response.write NOW() at a few places to see what was code taking so long, and I would get a 3 second difference here:

response.write "5<br/>"
response.write NOW() & "<br/>"
rs.open sSQL,cnn,0,1
if NOT rs.EOF then ectfiltercache=rs.getrows
rs.close
response.write "6<br/>"
response.write NOW() & "<br/>"



that SQL query apparently is rather taxing on the server:

SELECT COUNT(DISTINCT products.pID) as tcount,scID,scName,scGroup,scOrder,scgTitle FROM ((searchcriteria INNER JOIN searchcriteriagroup ON searchcriteria.scGroup=searchcriteriagroup.scgID) INNER JOIN multisearchcriteria ON multisearchcriteria.mSCscID=searchcriteria.scID) INNER JOIN (products LEFT JOIN multisections ON products.pId=multisections.pId) ON multisearchcriteria.mSCpID=products.pID WHERE pDisplay<>0 and pSell=1 and pHide=0 AND (products.pSection IN (19) OR multisections.pSection IN (19)) AND (multisearchcriteria.mSCpID IN (SELECT products.pID FROM (products LEFT JOIN multisections ON products.pId=multisections.pId) WHERE 1=1)) GROUP BY scID,scName,scGroup,scOrder,scgOrder,scgID,scgTitle ORDER BY scgOrder,scgID,scOrder,scName


I worked on that query and found that if I move this piece of code that generates it:
if sectionids<>"" then sSQL=sSQL&" AND (products.pSection IN ("§ionids&") OR multisections.pSection IN (" & sectionids & "))"



DOWN to underneath:
if manid<>"0" AND manid<>"" then sSQL=sSQL & " AND pManufacturer=" & manid


It runs FAR faster, at least on our server.

final code looked like this in that section:

if manid<>"0" AND is_numeric(manid) then sSQL=sSQL & " AND pManufacturer=" & manid
if sectionids<>"" then sSQL=sSQL&" AND (products.pSection IN ("§ionids&") OR multisections.pSection IN (" & sectionids & "))"


Apparently how the query is structured matters in this case even though I think both versions of the query are pulling the same data.

I'm back to working poking at the issue with the page itself, but I thought this improvement of speed for the side filter worth mentioning.

Edited by - Graham Slaughter on 02/13/2025 10:07:58

Vince
Administrator

42916 Posts

Posted - 02/14/2025 :  05:05:35  
Hi Graham
Thanks for letting me know and it's certainly worth looking into. However I think this may well come down to if your database has more different manufacturers or sections / categories and therefore which can be discarded easier.

Vince

Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

Graham Slaughter
Ecommerce Template Expert

836 Posts

Posted - 02/14/2025 :  06:03:39  
The more that I've worked on this the more I think you're right. I think that our situation is just generally unique and that the cart software for the average user simply excels and works fantastically.

I'm going to try to find a way to modify it that works better for us specifically. I appreciate you even replying to the thread! I think we can probably just close it for now. if I do find anything that is functional and works really well, I'll share it

Graham Slaughter
Ecommerce Template Expert

836 Posts

Posted - 02/14/2025 :  16:46:25  
Hey Vince, I found at least one BIG improvement in making the queries on that page faster.

products table references a single sectionID and then multisections carries the rest of them.

Example:
WHERE 1=1 AND (products.pSection IN (17739) OR multisections.pSection IN (17739))


If I put ALL of my sectionIDs into multisections and then modify the query as such:
WHERE 1=1 AND (multisections.pSection IN (17739))


The query returns 3x faster. That's a pretty significant improvement in query performance and load relief for the server. If the next version of the cart moves that single sectionID out of products table, you'd be able to hard code that improvement for everyone.

Again, I get this is NOT going to effect most users. I'm going to code in my own changes going forward, so don't do anything on our account - but I wanted to share the finding with you just incase you were interested.

Vince
Administrator

42916 Posts

Posted - 02/15/2025 :  03:43:58  
Hi Graham
That is really interesting indeed and I will certainly take a look at this. I think the logical result of this would be to not have the idea of a "primary" section and that would be quite a big change, but I'll dig in when I get a moment.

Thanks for letting me know!!!

Vince

Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

Graham Slaughter
Ecommerce Template Expert

836 Posts

Posted - 02/15/2025 :  07:33:33  
I made the changes and the improvement in query response time has been dramatic.

For the record, this is my work flow. I was running this query in SSMS to check for the longest running queries:

SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;


The result was a laundry list of queries that included that products.pSection

I would take the long running queries and stick them in here like this (example is for a different query):

SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT pID,pName,sectionName,pTitle,pMetaDesc,pDescription FROM products INNER JOIN sections ON products.pSection=sections.sectionID WHERE pID='moog-rk623660' OR pStaticURL='moog-rk623660'
SET STATISTICS IO OFF
SET STATISTICS TIME OFF


The check the MESSAGES of the query response. It would detail out CPU time and ELAPSED time for that query. Example:


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'sections'. Scan count 0, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'products'. Scan count 9, logical reads 52535, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 268 ms, elapsed time = 44 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Completion time: 2025-02-15T10:10:31.0047316-05:00



The queries with products.pSection were pushing CPU times of 2000ms+ in some cases.

Then I would modify the query to remove those elements I wanted to test and run it again and compare the results. That allowed me to see if it was actually helping. In all cases, it eliminated CPU time (woot!)


I've been checking on other queries and I found another place that we can make some MINOR improvements with.

WHERE pID='moog-rk623660' OR pStaticURL='moog-rk623660'

We use the PARAMETER for seodetailurls, however, we don't have a single pStaticURL populated. We just use it for the clean rewrites that hide the proddetail.asp?prod=
That means that the query is firing an unnecessary OR.

Removing this unnecessary OR pStaticURL= from the queries removed CPU time from all the queries I tested. The speed improvement is marginal, but worth noting.

It might be worth setting an additional parameter for using StaticURLs in addition to seodetailurls

If you'd rather, I can just shoot you an email directly with anything else I find, or I can just post them here if that's useful?

Vince
Administrator

42916 Posts

Posted - 02/22/2025 :  09:17:28  
Hi Graham
I was looking into issues around this and there is a lot that is inconclusive and depends a lot on the setup of the store. But one thing that seems to give results is an optional cache I'm working on for subcategories. Is this something you'd be willing to try out?

Vince

Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

Graham Slaughter
Ecommerce Template Expert

836 Posts

Posted - 02/22/2025 :  10:40:27  
I wholeheartedly agree. These improvements are highly dependent on how a store is setup. For some people (maybe even most), they wouldn't do anything.

absolutely! Caching has been a great help for us, but we struggle a little bit with caching being on asp classic because we cant use more than 2GB of ram (32 bit software). For smaller stores, you'd never hit this limit, but we seem to smack into it pretty fast.

We setup caching in the web.config and focus it on the /products pages, but that hits the 2GB ram limit really fast.

<location path="products.asp">
<system.webServer>
<caching enabled="true" maxCacheSize="500" maxResponseSize="1024000">
<profiles>
<add extension=".asp" policy="CacheForTimePeriod" kernelCachePolicy="DontCache" duration="48.00:00:00" varyByQueryString="cat" />
</profiles>
</caching>
</system.webServer>
</location>


I've worked with a couple other ways to do some caching. I'd really LOVE to see what you're working on.

Vince
Administrator

42916 Posts

Posted - 02/22/2025 :  12:43:33  
Hi Graham
This is an inbuilt cache in the cart and the idea is that for sites that have a lot of categories and sub-categories it can take time to generate that sub-category list so the idea is to save that list in a database table and the next time you don't have to create it again. I was working on this with another site in mind that deals in car parts and has a lot of categories with respect to the products as the same product can often be in several different categories.

Vince

Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

Graham Slaughter
Ecommerce Template Expert

836 Posts

Posted - 02/22/2025 :  13:37:22  
yeah! I've always wanted to see something like that in action. It would be great to see your code and work with it. I'd love to give it a whirl and see if it can create some measurable improvements. Reading through your code and that from some other guys always fuels my excitement for playing with the cart code.

We have 39000+ sectionIDs and about 70K pIDs, but we have 1.6million multisection mappings in our database.

If you'd like, you can hit me up at my personal email address.

Graham Slaughter
Ecommerce Template Expert

836 Posts

Posted - 03/03/2025 :  12:50:57  
I really like the idea of what you've done with that caching concept.

Consolidating multiple iterations of SQL queries into a single query surely has the potential to be far more efficient, much faster, lower overhead on the server. Super smart how you took an existing function that's called all over the place and you improved it!

It makes me wonder just how much data we could technically cram into a table field. Apparently nvarchar(max) is up to 2GB!

Phil
ECT Moderator

United Kingdom
7725 Posts

Posted - 03/04/2025 :  03:38:00  
quote:
I was working on this with another site in mind that deals in car parts and has a lot of categories with respect to the products as the same product can often be in several different categories.


I'm interested in the outcome of this. I have two clients on PHP that deal in car parts and both their sites have over two thousand categories and almost 100,000 products



*Contact Us*
*Buy The PHP Capture Card Plugin*
*Rate Our Services/View Our Feedback*

Edited by - Phil on 03/04/2025 03:38:47

Vince
Administrator

42916 Posts

Posted - 03/04/2025 :  04:06:26  
Hi Graham
Did you have a change to check the performance with and without the cache?

Vince

Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

Graham Slaughter
Ecommerce Template Expert

836 Posts

Posted - 03/04/2025 :  05:40:52  
I only ran it on our testing domain yesterday. It's on our live database, but on pages that a customer wouldn't be able to access (yet).

The only place I saw it fire on our site was on /products.asp. I simply watched the database for updates as I navigated.

That location however only returns a 1to1 entry in the database, so it wouldn't improve performance there:


I see that that function is all over the place. It looks like some of these areas are ones we had to intentionally code around previously because they were so inefficient. (hence the need for the caching). For example, we dont allow search.asp based on categories anymore. That section of code has been commented out entirely on our sites.

Here is where I see it:
incsearch
incsidefilter
incproducts
incprods
incproddetail
incmailinglist
inccats
inccart
dumporders
ajaxservice
incstats

I haven't dug deeper yet, but it's possible in other places that this code would normally fire we have coded around over the years to improve site performance.

I see that some of those are on admin side pages. I'll port the code over to our administration domain and test it there further this morning.

Graham Slaughter
Ecommerce Template Expert

836 Posts

Posted - 03/04/2025 :  09:44:23  
I put this on our live website, including the administration domain. The only entries I get in that table are single 1 to 1 entries. none of them appear to have a comma delimited list as the code appears to intend.

Maybe I'm using the ECT categories differently from some people?
What specific scenario would trigger something other than a 1to1?

- Graham Slaughter

Vince
Administrator

42916 Posts

Posted - 03/05/2025 :  01:41:19  
Hi Graham
If you were higher up the category hierarchy then you would expect a longer list of categories.

Vince

Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

Graham Slaughter
Ecommerce Template Expert

836 Posts

Posted - 03/05/2025 :  07:21:14  
Oh! That makes sense. I guess we don't use the categories in that fashion. I'll have to look into that.
  « Topic »  
Jump To:
Shopping Cart Software Forum for Ecommerce Templates © 2002-2022 ecommercetemplates.com
This page was generated in 0.03 seconds. Snitz Forums 2000