I need help speeding up this query that has a table spool taking 48%cost and a table scan taking 49% cost. I am wondering if there is quicker way of retrieving this dataset.Currently to retieve the data it is taking 3 seconds per day that I retrieve and this dataset is mainly going to be used to retrieve a month at a time so its kinda doggy.I read some stuff on using Table Variables (Tables in memory) to speed things up but I have no clue on how to use them.Here is the query:SELECT CASE WHEN Zip.Zip IS NULL THEN '0' ELSE Zip.Zip END AS Zip, CASE WHEN ZipInfo.City IS NULL THEN 'N/A' ELSE ZipInfo.City END AS City, SUM(ej.dbo.ItemLine.Amount) AS Amount, COUNT(DISTINCT ej.dbo.TransactionNode.TransactionID) AS ZipTotal, SUM(ej.dbo.ItemLine.Amount) / COUNT(DISTINCT ej.dbo.TransactionNode.TransactionID) AS AverageFROM Zip INNER JOIN ej.dbo.TransactionNode ON Zip.TrxNum = ej.dbo.TransactionNode.TransactionNumber AND Zip.TrmNum = ej.dbo.TransactionNode.TerminalNumber INNER JOIN ej.dbo.ItemLine ON ej.dbo.TransactionNode.TransactionID = ej.dbo.ItemLine.TransactionID INNER JOIN ej.dbo.ItemTypeDictionary ON ej.dbo.ItemLine.ItemTypeID = ej.dbo.ItemTypeDictionary.ItemTypeID INNER JOIN item.dbo.DepartmentDefinition ON ej.dbo.ItemLine.Department = item.dbo.DepartmentDefinition.departmentNumber LEFT OUTER JOIN ZipInfo ON Zip.Zip = ZipInfo.ZipWHERE (Zip.Date BETWEEN '11/17/09' AND CONVERT(DATETIME, CONVERT(VARCHAR, '11/17/09', 101) + ' 23:59:59.999')) AND (ej.dbo.TransactionNode.TransactionDateTime BETWEEN '11/17/09' AND CONVERT(DATETIME, CONVERT(VARCHAR, '11/17/09', 101) + ' 23:59:59.999')) AND (Zip.Zip = ZipInfo.Zip)GROUP BY Zip.Zip, ZipInfo.CityORDER BY ZipTotal DESC
Need Help making this faster
Posted - 2009-12-10 : 17:43:48
