Slow query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Slow query

Author

Topic

foamy
Starting Member

12 Posts

Posted - 2010-11-25 : 07:14:36

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-25 : 23:19:21

It is changing the exectution plan, and without seeing it and knowing what you are doing it is very tough to provide the correct answer (a or statement, or a subquery can drastically add to the workload of the server).

A quick solution that might help is to take the initial results and put them in a Temp table, then do the second filters you want on that temp table. This way you will be eliminating the excess tables scans that it is adding, but keep in mind there is probably a better solution if you properly indexed, and re-wrote the existing query a little better, but if you want assistance on that, please provide some sample data so we can see exactly what you are trying to accomplish..)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

foamy
Starting Member

12 Posts

Posted - 2010-11-26 : 09:26:18

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 :) Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources