• I would start by changing all those SELECT TOP 1. You're reading the tables multiple times when you can read them just once.

    Your WHERE clause is not SARGable so you're reading the whole table (or index) to look for the appropriate values.

    This is an example based on your query:

    SELECT min(WCI_ITNO) AS 'NASKU'
        ,wi.WIImageURL AS 'IMAGE'
        ,wi.WILargeImageURL AS 'WILargeImageURL'
        ,min(WCI_DESC) AS 'ITEM_DESC'
        ,min(WCI_IUM) AS 'UNIT'
        ,min(WCI_XFNO) AS 'ITEM_NUMBER'
        ,min(WCI_ITYP) AS 'WCI_ITYP'
        ,wi.WIPDFImageURL AS 'WIPDFImageURL'
        ,min(WCI_DSC2) AS 'CASE'
        ,min(WCIBrand) AS 'BRAND'
    FROM WWW_CatalogItem
    INNER JOIN IG2011 ON IGITNO = WCI_ITNO
            AND IGCTYP = WCIType
            AND IGCODE = WCICatalog
    LEFT JOIN [WWW_CatalogRetail] ON WCRType = WCIType
                AND [WCRCatalog] = WCICatalog
                AND [WCRCode] = WCIRetail
                AND WCIRetail <> 'ZZZ'
    /*LEFT JOIN [WebImage] ON wiitno = wci_itno*/--Apparently this is not used.
    LEFT JOIN [NA ASI]..t_products Promo ON WciProdId = Promo.ProdId
    INNER JOIN TS2011 ON IGCTID = TSCTID
            AND TSCTYP = IGCTYP
            AND TSCODE = IGCODE
            AND TSCNT != 0
    LEFT JOIN ItemInformation ON IIITNO = WCI_ITNO
               AND IIRID = 'CAT'
               AND IITDSC <> ''
               AND IITDSC IS NOT NULL
    OUTER APPLY(SELECT TOP 1 WIImageURL, WILargeImageURL, WIPDFImageURL
            FROM [WebImage]
            WHERE wiitno = wci_itno
            ORDER BY wiseq) wi

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2