February 21, 2004 at 8:17 pm
Hi all,
Been trying to improve a query and I cant seem to get it done without using a derived or temp table or a view. Trying to decrease the queries overhead as it is run EVERY time the intranet order basket is viewed;
Problem;
Grab the most recent selling price for every item in the current new order basket for the account currently being viewed.
Two tables...one with order details (Account holder etc) and the order transaction tables with the selling prices.
Currently I am grabbing every transaction for each product in the basket. If the item is a popular one the current account may have purchased it many hundreds of times previously. The returned recordset is thus very much larger than it needs to be and I have to move through it programmatically (it is ordered by order date) and as soon as I get a match to the current basket item grb the selling price then move back to the first recordset and move through it again for the next item in the basket....not very efficient or elegant at all.
Any ideas would be greatly appreciated. If more info is needed on table design etc leave a message.
Thanks again
Roo
February 21, 2004 at 8:48 pm
PS this is the current query which returns the correct rows but is way too slow...takes almost 12 seconds to execute.
SELECT Trans1.ITEM_NUMBER, Trans1.SELLING_UNIT_PRICE
FROM SOR_ORDER_HISTORY Ord1 INNER JOIN
SOR_HISTORY_TRANS Trans1 ON Ord1.THIS_RECORD = Trans1.PARENT_RECORD
WHERE (Trans1.PARENT_RECORD =
(SELECT TOP 1 Trans2.Parent_Record
FROM Sor_Order_History Ord2 INNER JOIN
Sor_History_Trans Trans2 ON Ord2.This_record = Trans2.Parent_Record
WHERE Trans2.Item_number = Trans1.Item_number AND Ord2.Account_Number = 'Rolf'
ORDER BY Ord2.Order_date DESC)) AND (Ord1.ACCOUNT_NUMBER = 'rolf') AND (NOT (Ord1.THEIR_REFERENCE = 'cancelled order'))
ORDER BY Trans1.ITEM_NUMBER
February 21, 2004 at 8:48 pm
It will help me (and I believe others) to see the table structure, sample data for both tables, and the query used. Without seeing the query I might suggest things you are already doing.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 21, 2004 at 8:54 pm
OK have pasted the current query above (urgh nasty looking thing) here is some more table info;
Order transactions;
Item_Number, Selling_Unit_Price, Parent_record (order number), This_Record (unique reference KEY), Quantity, Quantity_On_Order
Order Table;
Account_Number, Order_Total, Vat_Total, Dispatch, This_Record (Unique reference ORDER NUMBER KEY), Order_Date
There are a few other columns that are not relevent here.
Thanks for the help.
Roo
February 21, 2004 at 9:05 pm
It seems to me that the first part of the where clause is only meant to get one row. This modification might work:
WHERE EXISTS (SELECT Trans2.Parent_Record
FROM Sor_Order_History Ord2
INNER JOIN Sor_History_Trans Trans2 ON Ord2.This_record = Trans2.Parent_Record
WHERE Trans2.Item_number = Trans1.Item_number AND Ord2.Account_Number = 'Rolf'
AND Trans2.Parent_Record = Trans1.PARENT_RECORD)
If not then someone else better have a go at this query, becuase it is late for me and I'll be logging off soon.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 21, 2004 at 9:09 pm
I would normally be using a dynamically created list of itme numbers in an IN clause to grab all the items that are currently in the basket.
So there would be a further clause in the where statement as so;
AND ITEM_NUMBER IN (x, x1, x2, ..n)
Roo
February 23, 2004 at 11:45 am
USE Northwind
GO
CREATE TABLE #Basket(
CustomerID nchar(5),
ProductID int,
Qty smallint)
INSERT #Basket
SELECT 'SAVEA',1,4
UNION ALL SELECT 'SAVEA',2,2
UNION ALL SELECT 'SAVEA',13,1
SELECT ProductId,
(SELECT TOP 1 UnitPrice
FROM [Order Details] d JOIN Orders o ON d.OrderID = o.OrderID
WHERE o.CustomerID = b.CustomerId AND d.ProductID = b.ProductID
ORDER BY o.OrderDate DESC) Price
FROM #Basket b
Not sure what logic you use if the customer hasn't ordered that product before; perhaps something like this:
SELECT ProductId, COALESCE(
(SELECT TOP 1 UnitPrice
FROM [Order Details] d JOIN Orders o ON d.OrderID = o.OrderID
WHERE o.CustomerID = b.CustomerId AND d.ProductID = b.ProductID
ORDER BY o.OrderDate DESC),
(SELECT UnitPrice
FROM Products
WHERE ProductID = b.ProductID)) Price
FROM #Basket b
If queries like these are slow, you probably haven't got the indexes right.
--Jonathan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply