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?
Reprogramming of the PRODUCTS page for speed.
Posted - 02/07/2025 : 10:16:00
