Make query quicker - 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 2008 Forums

Transact-SQL (2008)

Make query quicker

Author

Topic

Ats
Starting Member

32 Posts

Posted - 2010-03-08 : 09:51:07

Hi could anyone help me I have a query which seems to work fine but it takes an awfully long time to run, admittadly the data base has over a billion rows and the query takes about 5 - 6 hours to run.

SELECT Category.[Category Description], Category.[LLPG Description], category.[Item Description], Category.Barcode, Category.Brand, Stores.[ANA Code], Sum([QuantityIndicator]+' '+[Quantity]) AS Qty, Sum([ValueIndicator]+' '+[Value])/1000.0 AS Value1, Dates.[PP W/C Week], COUNT(distinct salesdaily.TransactionDate +''+ salesdaily.TransactionTime +''+ salesdaily.TransactionNumber +''+ salesdaily.ANACode +''+ salesdaily.TillNumber) as Transactions, Dates.Period, Dates.[W/C Week], Dates.Week, controlgroups.[control group], Product.[brand group],Product.USE1 as produse, controlgroups.use1 as storeuse
FROM (Dates INNER JOIN (Category INNER JOIN (controlgroups INNER JOIN (StoreAttributes INNER JOIN (Stores INNER JOIN SalesDaily ON Stores.[ANA Code] = SalesDaily.ANACode) ON StoreAttributes.ANA = SalesDaily.ANACode) ON controlgroups.[Ana MAX] = SalesDaily.ANACode) ON Category.ProductID2 = SalesDaily.ProductID) ON Dates.Date1 = SalesDaily.TransactionDate) INNER JOIN Product ON Category.Barcode = Product.Barcode
WHERE category.Category = '1520' and Category.[LLPG Description] NOT LIKE 'cream%' and Dates.Date2 between '2009-08-19' and '2010-02-22'
GROUP BY category.[Category Description], category.[LLPG Description], category.[Item Description], Dates.[PP W/C Week], category.[Item Description], Category.Category, Stores.[ANA Code], Dates.Period, Category.Barcode, Dates.[W/C Week], Dates.Week, controlgroups.[control group], Product.[brand group], Category.Brand,Product.USE1,controlgroups.use1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 11:20:09

do you really need to group by all these fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-08 : 11:37:05

This is an interesting FROM clause!


FROM
	(Dates INNER JOIN
		(Category INNER JOIN
			(controlgroups INNER JOIN
				(StoreAttributes INNER JOIN
					(Stores INNER JOIN SalesDaily ON Stores.[ANA Code] = SalesDaily.ANACode)
				ON StoreAttributes.ANA = SalesDaily.ANACode)
			ON controlgroups.[Ana MAX] = SalesDaily.ANACode)
		ON Category.ProductID2 = SalesDaily.ProductID)
	ON Dates.Date1 = SalesDaily.TransactionDate)

	INNER JOIN Product ON Category.Barcode = Product.Barcode

I thought you might be doing some sort of impicit cross join but I guess not.

Have you checked the query plan? Have good indices?

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-08 : 11:59:09

Do you have indexes on Dates.Date2 and category.Category ?

If not, add the two indexes. That may cut half running time ....

The main problem is here : Category.[LLPG Description] NOT LIKE 'cream%'.

If you want better performance, you should user full text search for that column.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-08 : 12:04:44

Category.[LLPG Description] NOT LIKE 'cream%'.

Should still be able to use an index on [LLPG Description]. If it was '%cream%'. then you wouldn't

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources