After the changes i made 2 weeks agoregarding queries performance, few days ago i started to note that that same page was starting to perform very bad. Within this days loading time grew from 5-10 seconds to 18-20 seconds and then to over 50 seconds (to be said that the page run over 80 queries and QoQ some queries being very big with lots of joins and sub-queries). Yes there has been added rows to particular tables that made the total number of records in the play to grow essentially. Yes the DB is under a more or less serious load. But still there aren't millions of rows used so there shouldn't happen such performance decrease.
So i started to dig. Added WITH (NOLOCK)to some SELECT's, shrinked the DB, de-fragmented and re-built the indexes, tried some optimizations, etc. But without a real success. And here i noted the following structure in the query that was making problems this time:
CONVERT(varchar(10), displayDateTime, 101) = CONVERT(varchar(10), GETDATE(), 101)
Sometimes it may be ok to use such constructions, but as a general rule, datetimeconversions are quite heavy.
So what i did was i created a new field displayDateTimeFormatedas varcharand at INSERTdrop CONVERT(varchar(10), GETDATE(), 101)into it. And the above structure now becomes:
displayDateTimeFormated = CONVERT(varchar(10), GETDATE(), 101)
Just a small change, but the speed of the query increased from 56 seconds (from my tests) to just 3 seconds.

Just another things to keep in mind.

There are many ways SubmitFeed method can be used. Bellow is how i used it for price update:

<cfset signStr2 = "Action=SubmitFeed&FeedType=_POST_PRODUCT_PRICING_DATA_">

<cfsavecontent variable="prodXML"><?xml version="1.0" encoding="iso-8859-1" ?>

<AmazonEnvelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="amzn-envelope.xsd">

       <Header>

               <DocumentVersion>1.01</DocumentVersion>

               <MerchantIdentifier>#this.merchantID#</MerchantIdentifier>

       </Header>

       <MessageType>Price</MessageType>

       <Message>

               <MessageID>1</MessageID>

               <Price>

                       <SKU>#arguments.sku#</SKU>

                       <StandardPrice currency="USD">#NumberFormat(arguments.price, "_.__")#</StandardPrice>

               </Price>

       </Message>

</AmazonEnvelope>

</cfsavecontent>

<cfset var gn = GenerateSignedAmazonURL('POST', 'mws.amazonservices.com', '/', signStr2)>

<cfhttp url="#gn#" method="post" useragent="1SmartSolution MWS Component/1.0 (Language=ColdFusion; Platform=Windows/2003)">

       <cfhttpparam name="Content-Type" type="header" value="text/xml; charset=iso-8859-1">

       <cfhttpparam name="FeedContent" type="body" value="#prodXML#">

       <cfhttpparam type="header" name="Content-MD5" value="#ToBase64(BinaryDecode(Hash(prodXML), 'hex'))#">

</cfhttp>

Hope it helps.

This weekend and UFC 118, Randy Couture(multiple UFC Championand a member of the UFC Hall of Fame) welcomed James Toney(multiple Boxing Champion with 33 fights - 31 wins and 2 loses with 29 KOs) into the MMAworld. It took Randy few seconds to take Toney down and a total of 3 minutes 19 seconds to have "Lights Out" resign.

And, yeah... this has to do somehow with ColdFusion as their site is built with ColdFusion.

In my previous post about Amazon MWS folrot to mention that before URL generation the following strig should be created:
<cfset signStr2 = "Action=SubmitFeed&FeedType=_POST_PRODUCT_PRICING_DATA_">
Where Actionis set to the needed method and is followed by any other required parameters as being FeedTypefor SubmitFeedmethod.

There seem to be interest in Amazon MWS and because mostly their customer service is awe-full as well as the documentation, it's a real challenge when it comes to implement it.
First things you are going to need when dealing with MWS is signature generation. Bellow are the functions i use for it:

<cffunction name="HMAC_SHA256" returntype="binary" access="public" output="false">

<cfargument name="signMessage" type="string" required="true">

<cfset var jMsg = JavaCast("string", arguments.signMessage).getBytes("UTF-8")>

<cfset var jKey = JavaCast("string", this.theSecretAccessKey).getBytes("UTF-8")>

<cfset var key = createObject("java", "javax.crypto.spec.SecretKeySpec")>

<cfset var mac = createObject("java", "javax.crypto.Mac")/>

<cfset key = key.init(jKey, "HmacSHA256")>

<cfset mac = mac.getInstance(key.getAlgorithm())>

<cfset mac.init(key)>

<cfset mac.update(jMsg)>

<cfreturn mac.doFinal()>

</cffunction>

<cffunction name="GenerateSignedAmazonURL" returnType="string" output="yes">

<cfargument name="HTTPVerb" required="yes">

<cfargument name="HostHeader" type="string" required="yes">

<cfargument name="HTTPRequestURI" type="string" required="yes">

<cfargument name="RawQueryString" type="string" required="yes">

<cfset var signature = "">

<cfset var encodedQueryString = "">

<cfset var sortedQueryString = "">

<cfset var encodedSignature = "">

<cfset var name = "">

<cfset var value = "">

<cfset var i = "">

<!--- get your timestamp--->

<cfset var thenow = DateConvert("local2Utc", Now())>

<cfset var time_stamp = "#DateFormat(thenow,'yyyy-mm-dd')#T#TimeFormat(thenow,'HH:mm:ss')#.00Z">

<!--- append timestamp to query string --->

<cfset arguments.RawQueryString = arguments.RawQueryString & "&Marketplace=#this.marketplaceID#">

<cfset arguments.RawQueryString = arguments.RawQueryString & "&Merchant=#this.merchantID#">

<cfset arguments.RawQueryString = arguments.RawQueryString & "&SignatureMethod=HmacSHA256">

<cfset arguments.RawQueryString = arguments.RawQueryString & "&SignatureVersion=2">

<cfset arguments.RawQueryString = arguments.RawQueryString & "&Timestamp=#time_stamp#">

<cfset arguments.RawQueryString = arguments.RawQueryString & "&Version=2009-01-01">

<!--- start building signature --->

<cfset signature = arguments.HTTPVerb & Chr(10)>

<cfset signature = signature & LCase(arguments.HostHeader) & Chr(10)>

<cfset signature = signature & arguments.HTTPRequestURI & Chr(10)>

<!--- loop over the list and urlEncode each value --->

<cfloop list="#arguments.RawQuerySTring#" delimiters="&" index="i">

<cfset name = ListGetAt(i, 1, "=")>

<cfset value = "">

<!--- if this item has a value encode it --->

<cfif ListLen(i, "=") gt 1>

<cfset value = Replace(Replace(Replace(ListGetAt(i, 2, "="), ",", "%2C", "ALL"), ":", "%3A", "ALL"), " ", "%20", "ALL")>

</cfif>

<!--- build the new query string with encoded values --->

<cfset encodedQueryString = ListAppend(encodedQueryString, "#name#=#value#", "&")>

</cfloop>

<!--- next we need to canonically order the queryString params --->

<cfset sortedQueryString = ListSort(encodedQueryString, "textnocase", "asc", "&")>

<!--- append to the signature --->

<cfset signature = signature & "AWSAccessKeyId=#this.accessKeyId#&" & sortedQueryString>

<!--- encode the signature --->

<cfset encodedSignature = URLEncodedFormat(ToBase64(HMAC_SHA256(signature)))/>

<cfreturn "https://#arguments.HostHeader##arguments.HTTPRequestURI#?AWSAccessKeyId=#this.accessKeyId#&#sortedQueryString#&Signature=#encodedSignature#">

</cffunction>

The original version of GenerateSignedAmazonURLi found somewhere time ago when working with AWS and i think i slightly changed it.
The call would be like this:
<cfset generatedURL = GenerateSignedAmazonURL('POST', 'mws.amazonservices.com', '/', signStr2)>
A thing to note:POSTin generatedURL should go with POSTtype CFHTTPsand use GETin generatedURL for GETtype CFHTTPs.