Thread: Any DB Efficiency experts here?

Hi All,

I had about 400 orders in the space of a couple of hours last night.
This is of course great - but..... it could have been hundreds more.
The site slowed to a crawl & lots of people just couldn't be bothered to wait.
I asked the host (ChannelAdvisor) for help & they tell me that a DB query was causing the slowdown.

Here's the offending xmlpackage :-

HTML Code:

<?xml version="1.0" standalone="yes" ?>
<package version="2.1" displayname="subcat-top3.xml.config" debug="false" includeentityhelper="false">

	<!-- ###################################################################################################### -->
	<!-- Copyright AspDotNetStorefront.com, 1995-2008.  All Rights Reserved.					                -->
	<!-- http://www.aspdotnetstorefront.com														                -->
	<!-- For details on this license please visit  the product homepage at the URL above.		                -->
	<!-- THE ABOVE NOTICE MUST REMAIN INTACT.                                                                   -->
	<!--                                                                                                        -->
	<!-- ###################################################################################################### -->


    <query name="Products" rowElementName="Product">
        <sql>
            <![CDATA[
SELECT DISTINCT TOP 3
    s.ProductID,
    s.NumSales,
    s.Numpounds
FROM (

select ProductID, SUM(Quantity) AS NumSales, SUM(OrderedProductPrice) AS Numpounds
      from dbo.Orders_ShoppingCart

 sc
            join [dbo].Orders o on sc.OrderNumber = o.OrderNumber and o.OrderDate >= dateadd(dy, -7, getdate())
      group by ProductID
     ) s
    join [dbo].Product p with (nolock) on s.productid = p.productid
WHERE p.Deleted = 0
    and p.Published = 1  
	and p.ProductID in ( select ProductID from dbo.ProductCategory where CategoryID = @parent )
ORDER BY s.NumSales desc
            ]]>
        </sql>
        <queryparam paramname="@parent" paramtype="runtime"   requestparamname="parent" sqlDataType="int" defvalue="0" validationpattern="" />
    </query>

    <query name="Products30" rowElementName="Product">
        <sql>
            <![CDATA[
SELECT DISTINCT TOP 3
    s.ProductID,
    s.NumSales,
    s.Numpounds
FROM (

select ProductID, SUM(Quantity) AS NumSales, SUM(OrderedProductPrice) AS Numpounds
      from dbo.Orders_ShoppingCart

 sc
            join [dbo].Orders o on sc.OrderNumber = o.OrderNumber and o.OrderDate >= dateadd(dy, -30, getdate())
      group by ProductID
     ) s
    join [dbo].Product p with (nolock) on s.productid = p.productid
WHERE p.Deleted = 0
    and p.Published = 1  
	and p.ProductID in ( select ProductID from dbo.ProductCategory where CategoryID = @parent )
ORDER BY s.NumSales desc
            ]]>
        </sql>
        <queryparam paramname="@parent" paramtype="runtime"   requestparamname="parent" sqlDataType="int" defvalue="0" validationpattern="" />
    </query>

    <query name="Products365" rowElementName="Product">
        <sql>
            <![CDATA[
SELECT DISTINCT TOP 3
    s.ProductID,
    s.NumSales,
    s.Numpounds
FROM (

select ProductID, SUM(Quantity) AS NumSales, SUM(OrderedProductPrice) AS Numpounds
      from dbo.Orders_ShoppingCart

 sc
            join [dbo].Orders o on sc.OrderNumber = o.OrderNumber and o.OrderDate >= dateadd(dy, -365, getdate())
      group by ProductID
     ) s
    join [dbo].Product p with (nolock) on s.productid = p.productid
WHERE p.Deleted = 0
    and p.Published = 1  
	and p.ProductID in ( select ProductID from dbo.ProductCategory where CategoryID = @parent )
ORDER BY s.NumSales desc
            ]]>
        </sql>
        <queryparam paramname="@parent" paramtype="runtime"   requestparamname="parent" sqlDataType="int" defvalue="0" validationpattern="" />
    </query>

    <query name="Category" rowElementName="Cat">
        <sql>
            <![CDATA[
SELECT CategoryID, Name from dbo.Category where CategoryID = @parent
            ]]>
        </sql>
        <queryparam paramname="@parent" paramtype="runtime" requestparamname="parent" sqlDataType="int" defvalue="0"  validationpattern="" />
    </query>

    <PackageTransform>
    
        <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" exclude-result-prefixes="aspdnsf">
          <xsl:output method="html" omit-xml-declaration="yes" />

<xsl:param name="CatName"><xsl:value-of select="/root/Category/Cat/Name" /></xsl:param>

            <xsl:template match="/">
<xsl:param name="CatID"><xsl:value-of select="/root/QueryString/categoryid" /></xsl:param>

	    <xsl:choose> 
     		 <xsl:when test="count(/root/Products/Product) &lt;'3'"> 

	    <xsl:choose> 
     		 <xsl:when test="count(/root/Products30/Product) &lt;'3'"> 

	    <xsl:choose>
     		 <xsl:when test="count(/root/Products365/Product) &lt;'3'"> 



	<div class="new-products">
		<h2>Featured in <xsl:value-of select="$CatName" /></h2>
<div class="products-holder">


<xsl:value-of select="aspdnsf:XmlPackage('three.filler.random.xml.config', concat('parent=', $CatID))" disable-output-escaping="yes" />


	</div>
	</div>

		      </xsl:when>



<xsl:otherwise>



	<div class="new-products">
		<a class="see-more" href="e-topsellers.aspx?categoryid={/root/Category/Cat/CategoryID}">More Bestsellers</a>
		<h2>Bestsellers in <xsl:value-of select="$CatName" /></h2>
<div class="products-holder">




                <xsl:for-each select="/root/Products365/Product">

<xsl:variable name="prodid" select="ProductID" /> 

<xsl:value-of select="aspdnsf:XmlPackage('cat-best2.xml.config', concat('ProductID=', $prodid))" disable-output-escaping="yes" />

				</xsl:for-each>



	</div>
	</div>


		      </xsl:otherwise> 
    </xsl:choose>
		      </xsl:when>
		      <xsl:otherwise> 


	<div class="new-products">
		<a class="see-more" href="e-topsellers.aspx?categoryid={/root/Category/Cat/CategoryID}">More Bestsellers</a>
		<h2>Bestsellers in <xsl:value-of select="$CatName" /></h2>
<div class="products-holder">


                <xsl:for-each select="/root/Products30/Product">

<xsl:variable name="prodid" select="ProductID" /> 
<xsl:value-of select="aspdnsf:XmlPackage('cat-best2.xml.config', concat('ProductID=', $prodid))" disable-output-escaping="yes" />

				</xsl:for-each>


	</div>
	</div>


		      </xsl:otherwise> 
    </xsl:choose> 

		      </xsl:when> 

		      <xsl:otherwise> 


	<div class="new-products">
		<a class="see-more" href="e-topsellers.aspx?categoryid={/root/Category/Cat/CategoryID}">More Bestsellers</a>
		<h2>Bestsellers in <xsl:value-of select="$CatName" /></h2>
<div class="products-holder">


                <xsl:for-each select="/root/Products/Product">

<xsl:variable name="prodid" select="ProductID" /> 

<xsl:value-of select="aspdnsf:XmlPackage('cat-best2.xml.config', concat('ProductID=', $prodid))" disable-output-escaping="yes" />

				</xsl:for-each>

	</div>
	</div>



		      </xsl:otherwise> 
    </xsl:choose> 

            </xsl:template>

        </xsl:stylesheet>
    </PackageTransform>
</package>

Basically - it gets the 3 bestsellers for the past 7, 30 & 365 days.
It then calls another xmlpackage 3 time to display the bestsellers.
If there are not 3 entries in the past 7 days - it will switch to the last 30 days - if there are not 3 entries in the past 30 days it will switch to the last 365 days.

Channel advisor don't like it because it's "a nested select with a join that has a nested select & an additional join".

Is there a better (more DB efficient) way of achieving what I want?

I'd be grateful fo any input.



TTFN

BFG

I'm not too sure it's the sql statement causing the issue.

The reason being I tried making sql statements that used joins by themselves instead of using nested selects with joins. After filling the client stats for both I didn't see any improvements. I also looked at the execution plans other then branching off differently didn't seem all that different either. Granted I may not have the same table indexes or even that massive amount of data for those tables so that could skew the numbers.

What I would say is causing the issue is all 3 date range sql statements whether shown or not are executed every time the xmlpackage is ran. I would suggest spiting the Products30 and Products365 into their own xmlpackage. And only call the corresponding xmlpackage when the count is less then 3.

Hey Thanks !!

That's a great idea - I feel a little dumb that I didn't spot that myself.
I'll give it a go.

Also - the following has been suggested to me as a more efficient way of structuring the query(s) :-

C#/VB.NET Code:

SELECT TOP 3 p.ProductID, SUM(sc.Quantity) AS NumSales, SUM(sc.OrderedProductPrice) AS Numpounds
FROM dbo.Product p WITH (NOLOCK)
JOIN dbo.Orders_ShoppingCart sc WITH (NOLOCK) ON sc.ProductID = p.ProductID
JOIN dbo.Orders o WITH (NOLOCK) ON sc.OrderNumber = o.OrderNumber
WHERE                 o.OrderDate >= dateadd(dy, -7, getdate())
AND                  p.Deleted = 0
AND                  p.Published = 1
AND                  EXISTS
(
SELECT 1 FROM dbo.ProductCategory pc WITH (NOLOCK)
WHERE p.ProductID = pc.ProductID AND
pc.CategoryID = @parent
)
GROUP BY p.ProductID
ORDER BY SUM(Quantity) DESC
OPTION (FORCE ORDER) 


Any thoughts?


TTFN

BFG

Just another thought - would setting CacheEntityPageHTML to true solve my problem here?

As I understand it - it actually caches & serves the html so the DB queries don't run again?


TTFN

BFG

BFG

I would move the select to a stored procedure and call it that way, it should remain compiled in sql that way.

Personally I would run a daily procedure to store the top products in a db table and just select from that. You would save a lot of calls to the database that way.

Thanks Mate - Storing it in a daily updated table would be my preference - however - we're on a hosted solution (ChannelAdvisor) & I'm trying to minimise anything out of the ordinary - i.e. no source or DB mods as they're unsupported, may disappear at any time etc. etc.

I did think about storing it in an xmlfile & reading from that...... but I think that CacheEntityPageHTML may solve the problem......


TTFN (& Merry Christmas)

BFG

Originally Posted by BFG 9000

Just another thought - would setting CacheEntityPageHTML to true solve my problem here?

As I understand it - it actually caches & serves the html so the DB queries don't run again?


TTFN

BFG

Can anyone confirm this?


TTFN

BFG

I've been looking into caching to improve efficiency and had just finished writing my own system, which caches package output when I spotted CacheEntityPageHTML hiding in the code .

I don't know if my experience applies to your situation but I decided to stick with my system because I cache to the database instead of in the ASP.NET cache. This might sound counter-intuitive but we have massive entity structures and WebQuery calls so our efficiency savings need to be made by reducing web server memory and processor load. Caching every entity page in memory would probably kill our server.

It might be possible to divert ASP.NET cache to a database in the same way as you can with session data but it isn't something I have come across. Maybe it'd involve a custom handler or module.

Cheers,

Richard

Thanks Richard,

I'm still not 100% sure exactly what CacheEntityPageHTML does though.
My guess is that it caches & serves the html so the DB queries don't run again - but no-one seems able to confirm this......


TTFN

BFG

BFG

I am as confused as you. When I turned it on the site wouldnt load, so I left it there.

Al

CacheEntityPageHTML is used in one place, and one place only. This is in the ShowEntityPage class (App_Code/ShowEntityPage.cs(.vb)) and is used whenever an entity (Manufacturer, Category, Distributor, etc...) page is viewed on the site. When set to true, the xmlpackage for the entity page will not be executed, instead the page contents are retrieved from the cache (including prices, SE information, and xmlpackage results) and rendered. These values will remain cached for the duration of time specified in the CacheDurationMinutes appconfig parameter. If you must make any changes to any of the information that would be displayed on these pages, they won't take effect on the front end until the cache expires, or you turn CacheEntityPageHTML off, and click the Reset Cache link in the admin section (you can turn it back on then if you'd like).