| Author |
 Topic  |
|
|
ucoxk
Starting Member
2 Posts |
 Posted - 08/24/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 08/24/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 08/24/2012 : 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 - 08/25/2012 : 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
India
47042 Posts |
Posted - 08/25/2012 : 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/
|
|
|
| |
Topic |
|
|
|
SQL Query optimization
