More Efficient 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 2000 Forums

SQL Server Development (2000)

More Efficient Query ?

Author

Topic

ccomstock2
Starting Member

18 Posts

Posted - 2003-08-12 : 16:32:43

Passed parameters @loginUsername, @startDate, @endDate. It is taking forever to finish because it has to be done for each month. It is used to draw a graph of shipping costs. Any suggestions to improve the speed it takes to execute ?

SELECT isNull(SUM(shippingCost), 0) AS shippingCost
FROM tblBoxes, tblOrders
WHERE tblOrders.orderID IN
(select orderID FROM tblOrders WHERE orderID IN
(select orderID FROM tblOrders_Products WHERE itemID IN
(select itemID FROM tblProducts WHERE productTypeID IN
(select productTypeID FROM tblProductType WHERE divisionID IN (select divisionID FROM tblLogins_Divisions WHERE userID IN
(select userID FROM tblLogins WHERE loginUserName=@loginUserName))))))
AND dateShipped BETWEEN @startDate AND @endDate
AND tblBoxes.orderID=tblOrders.orderID

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-12 : 16:57:37

quote:

SELECT isNull(SUM(shippingCost), 0) AS shippingCost
FROM tblBoxes, tblOrders
WHERE tblOrders.orderID IN
(select orderID FROM tblOrders WHERE orderID IN
(select orderID FROM tblOrders_Products WHERE itemID IN
(select itemID FROM tblProducts WHERE productTypeID IN
(select productTypeID FROM tblProductType WHERE divisionID IN (select divisionID FROM tblLogins_Divisions WHERE userID IN
(select userID FROM tblLogins WHERE loginUserName=@loginUserName))))))
AND dateShipped BETWEEN @startDate AND @endDate
AND tblBoxes.orderID=tblOrders.orderID

OK ....

Let's see, we have:

Boxes
Orders
Orders_Products
Products
ProductType
Logins_Divisions
Logins

it looks like a ProductType has a division? and you want total shipping cost for the division that the LoginName belongs to, for a date range?

What table does DateShipped come from? what table does ShippingCost come from?

what about:


SELECT
  SUM(Orders.ShippingCost) as TotalShippingCost
FROM 
  ORders
INNER JOIN
  ORders_Products ON Orders.OrderID = Orders_Products.ORderID
INNER JOIN
  ProductType ON Orders_Products.ProductTypeID = ProductType.ProductTypeID
INNER JOIN
  Logins_Divisions ON Logins_Divisions.DivisionID  = ProductType.DivisionID
INNER JOIN
  Logins ON Logins.UserID = Logins_Divisions.UserID
WHERE
  Logins.LogInUserName = @userName AND
  DateShipped BETWEEN @StartDate and @EndDate

read more about INNER JOIN's in books on-line..

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-13 : 03:53:30

Jeff, you've got rid of the Boxes! Unless all orders have one box each, that must have either changed the meaning (shippingCost and dateShipped both columns of Orders) or make it not work (at least one of them in Boxes). I'd be interested to know whether, apart from the spurious line (select orderID FROM tblOrders WHERE orderID IN, there's any difference between the subquery and the join.

quote:

Boxes
Orders
Orders_Products
Products
ProductType
Logins_Divisions
Logins

How many were going to St Ives?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-13 : 07:48:14

AH!! the boxes ... minor details ....

ccomskock2, give us some specific details about what you want. It is very UNCLEAR from your example !

- Jeff

ccomstock2
Starting Member

18 Posts

Posted - 2003-08-13 : 10:00:56

The inner join of all the tables did not work because tblOrders_Products has more then one entry for orders there for adding the shipping cost up more then once if there was more then one item. Here is the solution I have come up with, it is quicker, but not as quick as I would like.

SELECT
isNull(sum(shippingCost), 0) as shippingCost
FROM
tblBoxes, tblOrders
WHERE
tblOrders.orderID in (Select orderID from tblOrders_Products
INNER JOIN
tblProducts ON tblProducts.itemID=tblOrders_Products.itemID
INNER JOIN
tblProductType ON tblProducts.ProductTypeID = tblProductType.ProductTypeID
INNER JOIN
tblLogins_Divisions ON tblLogins_Divisions.DivisionID = tblProductType.DivisionID
INNER JOIN
tblLogins ON tblLogins.UserID = tblLogins_Divisions.UserID
where tblLogins.loginUserName=@loginUserName) AND

tblBoxes.dateShipped BETWEEN @startDate and @endDate
AND tblBoxes.orderID=tblOrders.orderID

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-13 : 12:26:36

Try this out, I think it'll be much faster for you:

SELECT
isNull(sum(shippingCost), 0) as shippingCost
FROM
tblBoxes
INNER JOIN
tblOrders
ON tblBoxes.orderID=tblOrders.orderID
INNER JOIN
(Select Distinct orderID from tblOrders_Products
INNER JOIN
tblProducts ON tblProducts.itemID=tblOrders_Products.itemID
INNER JOIN
tblProductType ON tblProducts.ProductTypeID = tblProductType.ProductTypeID
INNER JOIN
tblLogins_Divisions ON tblLogins_Divisions.DivisionID = tblProductType.DivisionID
INNER JOIN
tblLogins ON tblLogins.UserID = tblLogins_Divisions.UserID
where tblLogins.loginUserName=@loginUserName) a
ON
tblOrders.OrderID = a.OrderID
WHERE
tblBoxes.dateShipped BETWEEN @startDate and @endDate

- Jeff

ccomstock2
Starting Member

18 Posts

Posted - 2003-08-13 : 14:07:30

There isn't a considerable difference in the time that I can notice but that does look better. The sproc is run once for each of the last 12 months. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources