Slow query

[foamy]

Hi all,

I'm attempting to get some numbers from an XAL database (MS SQL Server 2005). My query is below.

There are some conditions commented out. If I add either one of the lower ones the query executes in a matter of seconds but if I replace them with the top one it takes forever (7 minutes and counting).

Can anyone give me some pointers for how to get both conditions in my query without it taking forever?

SELECT DISTINCT
SALESTRANS.MONTSLUT AS INVOICEDATE,
SALESTABLE.DATASET,
DEBTABLE.[NAME] AS DEBITORNAME,
DEBTABLE.ACCOUNTNUMBER AS ACCOUNTNUMBER,
SALESTABLE.SALESREP,
SALESTABLE.SALESNUMBER,
SALESTRANS.DEPARTMENT,
SALESTRANS.CENTRE,
SALESTRANS.PURPOSE,
SALESTRANS.QTYORDERED AS ANTAL_BESTILT,
STOCKTABLE.ITEMNUMBER AS VARENUMMER,
STOCKTABLE.ITEMNAME AS VARENAVN,

(SALESTRANS.LINEAMOUNT * (CASE WHEN SALESTABLE.EXCHANGECODE <> '' THEN ex.EXCHRATE ELSE 1 END))
AS SALESPRICEDKK,
SALESTRANS.LINEAMOUNT,
SALESTRANS.QTYORDERED AS ANTAL,
ex.EXCHRATE AS EXCHRATE,
ex.EXCHANGECODE,
ex.ROWNUMBER

FROM SALESTRANS
INNER JOIN SALESTABLE ON SALESTRANS.SALESNUMBER = SALESTABLE.SALESNUMBER
INNER JOIN STOCKTRANS ON SALESTRANS.TRANSID = STOCKTRANS.TRANSID
INNER JOIN DEBTABLE ON SALESTABLE.DEBTORACCOUNT = DEBTABLE.ACCOUNTNUMBER
INNER JOIN STOCKTABLE ON SALESTRANS.ITEMNUMBER = STOCKTABLE.ITEMNUMBER
LEFT JOIN EXCHANGECODERATE ex ON SALESTRANS.EXCHANGECODE = ex.EXCHANGECODE
WHERE (SALESTABLE.DATASET='DAT')
AND (STOCKTABLE.DATASET='DAT')
AND (STOCKTRANS.SALESPROJ = ('          ' + SALESTABLE.SALESNUMBER))
AND (SALESTABLE.SALESNUMBER = SALESTRANS.SALESNUMBER)
AND SALESTRANS.MONTSLUT > '01-01-2008'
AND SALESTABLE.SALESPHASE <> 0

--AND (SALESTABLE.EXCHANGECODE = ''
--OR ex.ROWNUMBER = (SELECT MAX(ROWNUMBER) FROM EXCHANGECODERATE WHERE EXCHANGECODE = ex.EXCHANGECODE))

--AND SALESTABLE.EXCHANGECODE = ''

--AND ex.ROWNUMBER = (SELECT MAX(ROWNUMBER) FROM EXCHANGECODERATE WHERE EXCHANGECODE = ex.EXCHANGECODE

ORDER BY SALESTABLE.SALESNUMBER

If you have access, check to see if the table EXCHANGECODERATE has an index for ROWNUMBER since you are performing a subquery on each row in your primary query to find the max row number. If there isn't one, placing an index on that column for that table could improve the queries performance. You can check out this article from technet about using query plans to find out what could improve the query http://technet.microsoft.com/en-us/library/cc917694.aspx.

[foamy]

Cheers :) I've been able to solve the problem. My solution (workaround, really) was to create a table using SELECT INTO. Using this table to house my results I was able to get it working.