SQL Query optimization - 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)

SQL Query optimization

Author

Topic

ucoxk
Starting Member

2 Posts

Posted - 2012-08-24 : 06:25:24

My database size is 120 MB and the Table invoice TransactionBody contain millions of rows. I have also created indexes on my table but when i run following query it takes almost 1.50 to 2 min on my Core 2 duo CPU 2.53 GHz with 2GB RAM to return the result while i need to run it fast in seconds as my database size can increases to in GBS. I have also run Execution plan which shows sort cost 50%. Plz let me know how can i optimize my query


SELECT  IT.DistributorId, IT.InvoiceDate,HPro.HOProductId, HPro.ProductName,PG.GroupId,PG.GroupName, DTown.TownId,DTown.TownName,
	Sum(ITB.Quantity) as Qty,Sum(ITB.Bonus) as Bonus,Sum(ITB.NetAmount) as NetAmount
	FROM         
		  InvoiceTransactions AS IT INNER JOIN
		  InvoiceTransactionBody AS ITB ON IT.STId = ITB.STId INNER JOIN
		  DistProducts AS DPro ON ITB.ProductId = DPro.ProductId INNER JOIN
		  HOProducts AS HPro ON DPro.HOProductId = HPro.HOProductId INNER JOIN
		  ProductGroups PG on PG.GroupId=HPro.GroupId INNER JOIN
		  DistCustomers AS DCus ON IT.CustomerId = DCus.CustomerId INNER JOIN
		  DistTowns AS DTown ON DCus.TownId = DTown.TownId
		  Where IT.InvoiceTypeId=3
		  
	group by IT.InvoiceDate,IT.DistributorId,HPro.HOProductId,HPro.ProductName,Pg.GroupId,pg.GroupName, DTown.TownId,DTown.TownName
	Order by IT.InvoiceDate, HPro.ProductName, DTown.TownId,DTown.TownName

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-24 : 06:51:16

groupinhg by wide columns is a bat idea. Maybe

SELECT IT.DistributorId, IT.InvoiceDate,HPro.HOProductId,PG.GroupId,DTown.TownId,
Sum(ITB.Quantity) as Qty,Sum(ITB.Bonus) as Bonus,Sum(ITB.NetAmount) as NetAmount
FROM
InvoiceTransactions AS IT INNER JOIN
InvoiceTransactionBody AS ITB ON IT.STId = ITB.STId INNER JOIN
DistProducts AS DPro ON ITB.ProductId = DPro.ProductId INNER JOIN
HOProducts AS HPro ON DPro.HOProductId = HPro.HOProductId INNER JOIN
Where IT.InvoiceTypeId=3

group by IT.InvoiceDate,IT.DistributorId,HPro.HOProductId,Pg.GroupId,DTown.TownId

Use that as a derived table or cte - or put it in a temp table then join to the other tables to get the name texts

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-24 : 06:58:14

See you need the customer to get the town for the groups so it would be something like this

;with cte as
(
SELECT IT.DistributorId, IT.InvoiceDate,HPro.HOProductId,PG.GroupId,DTown.TownId,
Sum(ITB.Quantity) as Qty,Sum(ITB.Bonus) as Bonus,Sum(ITB.NetAmount) as NetAmount
FROM
InvoiceTransactions AS IT INNER JOIN
InvoiceTransactionBody AS ITB ON IT.STId = ITB.STId
join DistProducts AS DPro ON ITB.ProductId = DPro.ProductId
join HOProducts AS HPro ON DPro.HOProductId = HPro.HOProductId
join ProductGroups PG on PG.GroupId=c.GroupId INNER JOIN
join DistCustomers AS DCus ON c.CustomerId = DCus.CustomerId INNER JOIN
join DistTowns AS DTown ON c.TownId = DTown.TownId
Where IT.InvoiceTypeId=3
group by IT.InvoiceDate,IT.DistributorId,HPro.HOProductId,Pg.GroupId,DTown.TownId
)
select c.DistributorId, c.InvoiceDate, c.HOProductId, c.GroupId, c.TownId, HPro.ProductName, PG.GroupName, DTown.TownName
, c.Qty, C.Bonus, C.NetAmount
from cte c
join HOProducts AS HPro ON c.HOProductId = HPro.HOProductId
join ProductGroups PG on PG.GroupId=c.GroupId INNER JOIN
join DistTowns AS DTown ON c.TownId = DTown.TownId

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ucoxk
Starting Member

2 Posts

Posted - 2012-08-25 : 00:55:12

Thanks nigelrivett for reply but the problem is due to joining a number of tables as each table has thousands of number of rows. if i reduce joins and ordering it become better but that is not solution. Any other help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-25 : 13:25:12

i dont think so you need further join from cte. you can directly do below itself


SELECT IT.InvoiceDate,IT.DistributorId,HPro.HOProductId,Pg.GroupId,DTown.TownId,HPro.ProductName,Pg.GroupName,DTown.TownName,
Sum(ITB.Quantity) as Qty,Sum(ITB.Bonus) as Bonus,Sum(ITB.NetAmount) as NetAmount
FROM 
InvoiceTransactions AS IT INNER JOIN
InvoiceTransactionBody AS ITB ON IT.STId = ITB.STId
join DistProducts AS DPro ON ITB.ProductId = DPro.ProductId
join HOProducts AS HPro ON DPro.HOProductId = HPro.HOProductId
join ProductGroups PG on PG.GroupId=c.GroupId INNER JOIN
join DistCustomers AS DCus ON c.CustomerId = DCus.CustomerId INNER JOIN
join DistTowns AS DTown ON c.TownId = DTown.TownId
Where IT.InvoiceTypeId=3
group by IT.InvoiceDate,IT.DistributorId,HPro.HOProductId,Pg.GroupId,DTown.TownId,HPro.ProductName,Pg.GroupName,DTown.TownName

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources