Converting Subqueries to JOINS problem 2

[rogerl101]

I am trying to convert the following query from using a subquery in the SELECT statement to using JOINS instead. I have been working on this a while now without much success.

Select a.CustID, a.CustOrdType, a.OrdDate, (SELECT TOP 1 b.OrdDate FROM OrderTable b WHERE b.CustID = a.CustID AND b.CustOrdType = a.CustOrdType AND b.OrdDate < a.OrdDate ORDER BY OrdDate Desc) AS LastOrdDate
FROM OrderTable a

This code runs extremely slow so I have been trying to use the tips I found on this site to convert the sub select to a JOIN.

I am hoping to get the last order date for a customer and a certain order type just prior to each orders order date.

thanks

 

[r937]

select a.CustID
     , a.CustOrdType
     , a.OrdDate
     , ( SELECT max(OrdDate)
           FROM OrderTable 
          WHERE CustID = a.CustID 
            AND CustOrdType = a.CustOrdType 
            AND OrdDate < a.OrdDate ) 
         AS LastOrdDate
  from OrderTable a

r937.com | rudy.ca

 

[rogerl101]

Thanks for the response r937,

I guess I should have spelled out the whole problem.

After I Identify the previous OrdDate for this customer, I need to pull some other information from the same row. Here is what I was doing.

Select a.CustID,
       a.OrdNumber,
       a.CustOrdType,
       a.OrdDate, 
       (SELECT TOP 1 b.OrdDate FROM OrderTable b
         WHERE b.CustID = a.CustID
         AND b.CustOrdType = a.CustOrdType
         AND b.OrdDate < a.OrdDate
         ORDER BY OrdDate Desc) AS LastOrdDate,
       (SELECT TOP 1 b.OrdNumber FROM OrderTable b
         WHERE b.CustID = a.CustID
         AND b.CustOrdType = a.CustOrdType
         AND b.OrdDate < a.OrdDate
         ORDER BY OrdDate Desc) AS LastOrdNumber
FROM OrderTable a

There is actually 10 different fields that I am pulling from the previous order row in this fashion. This query can take anywhere from 20 to 40 minutes to run when the server is busy.

Do you know if placing this in a JOIN will speed this query up? If you thing it might, do you know the correct coding?

Thank You!

 

[travisbrown]

This should be a little more efficient:

Select 
a.CustID,
a.OrdNumber,
a.CustOrdType,
a.OrdDate,
b.OrdDate AS LastOrdDate,
b.OrderNumber AS LastOrdNumber
FROM OrderTable a 
INNER JOIN 
(SELECT TOP 1 b.OrdNumber, b.OrdDate FROM OrderTable b
         AND b.OrdDate < a.OrdDate
         ORDER BY OrdDate Desc) b
ON b.CustID = a.CustID AND b.CustOrdType = a.CustOrdType

 

[vongrunt]

TOP 1s in subqueries tend to be sloooow... what is primary key in table OrderTable?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[r937]

select a.CustID
     , a.CustOrdType
     , a.OrdNumber
     , a.OrdDate
     , b.OrdNumber 
     , b.OrdDate
     , b.foo
     , b.bar
  from OrderTable a
inner
  join OrderTable b
    on a.CustID = b.CustID
   and a.CustOrdType = b.CustOrdType
   and b.OrdDate = 
     ( SELECT max(OrdDate)
         FROM OrderTable 
        WHERE CustID = a.CustID 
          AND CustOrdType = a.CustOrdType 
          AND OrdDate < a.OrdDate )

r937.com | rudy.ca

 

[travisbrown]

I was concerned about TOP too, but I didn't think MAX was any faster and I couldn't think of a better way. Is MAX faster, Vongrunt, r937?

 

[rogerl101]

Thanks for all the suggestions, I will try these later today and let you know how it turns out.

 

[vongrunt]

Rudy, correct me if I'm wrong but wouldn't INNER JOIN eat first row in every group (e.g. with lowest OrdDate)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[r937]

eat? that's a new concept on me, what does eat mean?



the original spec: "I am hoping to get the last order date for a customer and a certain order type just prior to each orders order date."

i interpret this to mean each order should be matched with the immediately preceding order by order date

r937.com | rudy.ca

 

[travisbrown]

I was thinking that too. That's why I joined it with a subquery rather than just a self join, but I was thinking with the WHERE clause. Might work like Rudy shows with the > date as a join condition. With an inner join, it would only work if there is a previous invoice. If Rudy's works maybe a Left Join would be better to show orders that have no b.OrdNumber b.OrdDate.

 

[r937]

yes, LEFT OUTER instead of INNER will get you all orders, even if an order has no preceding order

r937.com | rudy.ca

 

[vongrunt]

Lemme ask again: what is primary/unique key on table OrderTable?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rogerl101]

Primary Key is OrderID(autonumber,identity column)

I am running tests against the Database now using your suggestions.

Thanks again, I will post results.

 

[rogerl101]

r937, your example worked great! I changed the INNER JOIN to LEFT OUTER to keep records with no previous history.

travisbrown, I tried your code and I keep getting

The column prefix 'a' does not match with a table name or alias name used in the query.

I also changed the AND to WHERE in the subquery, not sure if this is necessary, but it looked wrong. Either way, I get the same error message.

Thanks to all of you. I am going to continue testing this against my sample data.

 

[travisbrown]

Yeah, I mistyped. Should have been WHERE in there. Didn't have a SQL Server handy to test when I was typing.

Here it is again. Initally I'd used TOP, like you'd specifed, but MAX is probably better. I'm guessing though because I've heard no authority on the matter. Vongrunt is right that TOP is slow in subqueries. With Left Join if you want records with no previous order date.

SELECT     
a.CustID, 
a.OrdNumber, 
a.CustOrdType, 
a.OrdDate, 
a.OrderNumber, 
b.LastOrdDate, 
b.LastOrdNumber
FROM         
dbo.OrderTable a 
LEFT OUTER JOIN
(SELECT c.CustID, c.CustOrdType, MAX(c.OrdDate) AS LastOrdDate, c.OrdNumber AS LastOrdNumber
FROM dbo.OrderTable c
GROUP BY OrdDate, OrdNumber, CustID, c.CustOrdType) b 
ON a.CustOrdType = b.CustOrdType AND b.CustID = a.CustID