Coldfusion SQL queries very slow

Question

we have a few coldfusion queries that run and compare data from two databases, one MS SQL and one MYSQL to perform the task of updating the prices on our webshop from our instore database.

The problem is that this query is taking upwards of four minutes to complete.Can we streamline this query at all to speed it up or are we faced with a restriction from our host?

The query -:

<cfsetting requestTimeOut = "1440">

<cftry>
    <cfset request.timing = structNew()>

    <cfset getOffline = getTickCount()>
    <cfquery datasource="MSSQLDATABSE" name="get_offlineproduct2" timeout="240"> 
        SELECT p.id, p.code, pl.salesPrice3
        FROM products p with(nolock)
            INNER JOIN productposlocations pl with(nolock)
                ON pl.ProductID = p.ID
        WHERE pl.posLocationID = 1
    </cfquery>
    <cfset request.timing.getOffline = (getTickCount() - getOffline)/1000>

<cfquery datasource="MYSQLDATABASE" name="get_onlineproducts" timeout="240">
        SELECT s.SKU_ID, s.SKU_MerchSKUID, s.SKU_Price, s.SKU_ShowWeb, b.brand_Name
        FROM tbl_skus AS s
        INNER JOIN tbl_prdtbrand_rel r
        ON s.SKU_ProductID = r.prdt_brand_rel_Product_ID
        INNER JOIN tbl_prdtbrands b
        ON r.prdt_brand_rel_Brand_ID = b.brand_ID
        ORDER BY b.brand_Name
    </cfquery>

    <cfquery name="qPriceCheck" dbtype="query">
        SELECT *
        FROM get_onlineproducts, get_offlineproduct2
        WHERE SKU_MerchSKUID = code
            AND SKU_Price <> salesPrice3
    </cfquery>

    <table border="1" cellspacing="2" cellpadding="2">
        <tr>
            <th>SKU ID</th>
            <th>Brand</th>
            <th>SKU Merchant ID</th>
            <th>SKU Price</th>
            <th>Stock Price</th>
            <th>Price Updated?</th>
        </tr>
        <cfoutput query="get_onlineproducts">
            <cfquery name="qPC" dbtype="query">
                SELECT *
                FROM get_offlineproduct2
                WHERE code = '#get_onlineproducts.SKU_MerchSKUID#'
            </cfquery>
            <tr>
                <td>#get_onlineproducts.SKU_ID#</td>
                <td>#get_onlineproducts.brand_Name#</td>
                <td>#get_onlineproducts.SKU_MerchSKUID#</td>
                <td>#get_onlineproducts.SKU_Price#</td>
                <cfif qPC.recordCount>
                    <td>#qPC.salesPrice3#</td>
                    <cfif qPC.salesPrice3 neq get_onlineproducts.SKU_Price>
                        <td>Yes</td>
                        <cfquery datasource="MYSQLDATABASE" name="UpdateonlineproductsPrices">
                            UPDATE tbl_skus
                            SET SKU_Price = '#qPC.SalesPrice3#'
                            WHERE SKU_MerchSKUID = '#get_onlineproducts.SKU_MerchSKUID#'
                        </cfquery>
                    </cfif> 
                </cfif>
            </tr>
        </cfoutput>
    </table>

    <cfset request.timing.totalTime = (getTickCount() - getOffline)/1000>
    <cfdump var="#request.timing#">

<cfcatch type="Any" >
    <cfdump var="#cfcatch#" label="cfcatch">
</cfcatch>
</cftry>

Answer 1

Regarding comment, "How would you suggest removing the query from the cfoutput? I'm not sure of the best way to do this", I suggest this:

<cfquery name="qPC" dbtype="query">
SELECT field1, field2, etc
 FROM get_offlineproduct2
 WHERE code in ( <cfqueryparam 
value = #ValueList(get_onlineproducts.SKU_MerchSKUID)# list="yes" )
 </cfquery>

You can then loop through these results and do what you need to do. It's only 1 trip to the database.