large tables and left join - 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

General SQL Server Forums

New to SQL Server Programming

large tables and left join

Author

Topic

Davide
Starting Member

9 Posts

Posted - 2008-09-29 : 03:50:30

I have two large tables, on a MS SQL database. I am using MS Query from Excel. If I execute the following:

SELECT
A.ItemNumber,
A.ShipmentNumber,
A.ShipmentDate,
A.IssueType,
Max(B.TransactionDate)
FROM HistoryShipment A LEFT JOIN HistoryMOReceipt B
ON A.ItemNumber=B.ItemNumber
WHERE (B.Stockroom1<>99)
GROUP BY A.ItemNumber,
A.ShipmentNumber,
A.ShipmentDate,
B.IssueType,
ORDER BY A.ItemNumber, A.ShipmentNumber, A.ShipmentDate

it takes forever.

I have tried the following to avoid the join (do not know if it is logically the same thing):

SELECT A.ItemNumber,
A.ShipmentNumber,
A.ShipmentDate,
A.IssueType,
(SELECT Max(B.TransactionDate)
FROM HistoryMOReceipt B
WHERE A.ItemNumber=B.ItemNumber
AND (B.Stockroom1<>99)) AS Last
FROM HistoryShipment A

The result appears rather quickly in MS Query, but then when I close it and wait for the results to be written into the Excel spreadsheet , it hangs and I have to use the task manager to kill it.

1. How can I improve the speed of the left join?
2. what happens in the 2nd case? is the query logically correct (ie equivalent to the 1st)? why does it hang?

thank you very much to all experts

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 04:06:06

how many rows does your left join query return? also how long does below query take? is it giving the same result?

SELECT 
A.ItemNumber,
A.ShipmentNumber, 
A.ShipmentDate, 
A.IssueType,
B.MaxTranDate  
FROM HistoryShipment A 
LEFT JOIN (SELECT ItemNumber,MAX(TransactionDate) AS MaxTranDate 
FROM HistoryMOReceipt
WHERE B.Stockroom1<>99
GROUP BY ItemNumber) B 
ON A.ItemNumber=B.ItemNumber 
GROUP BY A.ItemNumber,
A.ShipmentNumber, 
A.ShipmentDate, 
B.IssueType, 
ORDER BY A.ItemNumber, A.ShipmentNumber, A.ShipmentDate

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-09-29 : 04:11:56

They are not the same.

How can B.Stockroom1 be <> 99 if there is no corresponding record in B? 99 <> null is null - you get no record. You have turned it into an inner join. You might mean ON A.ItemNumber=B.ItemNumber and (B.Stockroom1<>99)

Furthermore, the second query selects all rows from A, the first groups them. They will not be the same unless you have a unique constraint on itemNumber, ShipmentNumber, Date, IssueType for your group to be unique across all rows.
If the first one is right

I suggest you concentrate on your requirement first and performance second.
If your first is right then turn the join into an inner, which is what you've done with the <>99 bit.

In all cases, ensure your PK & FK are indexed and of the same type.

Take a look at the query plan (see BOL) to decide for yourself though.

Davide
Starting Member

9 Posts

Posted - 2008-09-29 : 04:46:41

Thanks to both of you guys.. Yes I do understand the 2 queries are not the same. Still have to think about it.
In the meantime I have tried Visakh16 query but I get an error msg:

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

Looks like it doesn't like the syntax. Suggestions?
Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 04:54:19

quote:
Originally posted by Davide

Thanks to both of you guys.. Yes I do understand the 2 queries are not the same. Still have to think about it.
In the meantime I have tried Visakh16 query but I get an error msg:

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

Looks like it doesn't like the syntax. Suggestions?
Thanks again

ah small typo.

SELECT 
A.ItemNumber,
A.ShipmentNumber, 
A.ShipmentDate, 
A.IssueType,
B.MaxTranDate  
FROM HistoryShipment A 
LEFT JOIN (SELECT ItemNumber,MAX(TransactionDate) AS MaxTranDate 
FROM HistoryMOReceipt
WHERE B.Stockroom1<>99
GROUP BY ItemNumber) B 
ON A.ItemNumber=B.ItemNumber 
GROUP BY A.ItemNumber,
A.ShipmentNumber, 
A.ShipmentDate, 
B.IssueType, 
ORDER BY A.ItemNumber, A.ShipmentNumber, A.ShipmentDate

Davide
Starting Member

9 Posts

Posted - 2008-09-29 : 04:55:05

For what concern requirements:

I want ALL the records from A. If their itemNumber can be found in some records in B and the B.stockroom is <>99, I want the MAX (B.TransactionDate).

Sounds like a left join ... opinions? thank you

Davide
Starting Member

9 Posts

Posted - 2008-09-29 : 05:03:23

Visakh, I now get the following error:

"No column was specified for column '2' of B"

thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 05:26:06

quote:
Originally posted by Davide

Visakh, I now get the following error:

"No column was specified for column '2' of B"

thanks again

nope i'm not getting that error.
Try again like below:-

SELECT 
A.ItemNumber,
A.ShipmentNumber, 
A.ShipmentDate, 
A.IssueType,
B.MaxTranDate  
FROM HistoryShipment A 
LEFT JOIN (SELECT ItemNumber,MAX(TransactionDate) AS MaxTranDate 
FROM HistoryMOReceipt
WHERE Stockroom1<>99
GROUP BY ItemNumber) B 
ON A.ItemNumber=B.ItemNumber 
ORDER BY A.ItemNumber, A.ShipmentNumber, A.ShipmentDate

Davide
Starting Member

9 Posts

Posted - 2008-09-29 : 08:28:52

Unfortunately I get the same error... no clue whatsoever. The same query in Access runs fine.
Thanks for helping

quote:
Originally posted by visakh16

quote:
Originally posted by Davide

Visakh, I now get the following error:

"No column was specified for column '2' of B"

thanks again

nope i'm not getting that error.
Try again like below:-
SELECT
A.ItemNumber,
A.ShipmentNumber,
A.ShipmentDate,
A.IssueType,
B.MaxTranDate
FROM HistoryShipment A
LEFT JOIN (SELECT ItemNumber,MAX(TransactionDate) AS MaxTranDate
FROM HistoryMOReceipt
WHERE Stockroom1<>99
GROUP BY ItemNumber) B
ON A.ItemNumber=B.ItemNumber
ORDER BY A.ItemNumber, A.ShipmentNumber, A.ShipmentDate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 09:45:26

Can you post query you used?i certainly think you're using something different.

Davide
Starting Member

9 Posts

Posted - 2008-09-29 : 11:47:31

here it is:

SELECT A.ItemNumber,
A.ShipmentNumber,
A.ShipmentDate,
A.IssueType,
A.ReversedQuantity,
B.LastMorvDate
FROM FSDBIT.dbo.FS_HistoryShipment A
LEFT JOIN (SELECT
ItemNumber,
Max(TransactionDate) AS LastMorvDate
FROM FSDBIT.dbo.FS_HistoryMOReceipt
WHERE Stockroom1<>'99'
GROUP BY ItemNumber) B
ON A.ItemNumber=B.ItemNumber
ORDER BY A.ItemNumber,
A.ShipmentNumber,
A.ShipmentDate;

Thank you!

quote:
Originally posted by visakh16

Can you post query you used?i certainly think you're using something different.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 12:36:56

quote:
Originally posted by Davide

here it is:

SELECT A.ItemNumber,
A.ShipmentNumber,
A.ShipmentDate,
A.IssueType,
A.ReversedQuantity,
B.LastMorvDate
FROM FSDBIT.dbo.FS_HistoryShipment A
LEFT JOIN (SELECT
ItemNumber,
Max(TransactionDate) AS LastMorvDate
FROM FSDBIT.dbo.FS_HistoryMOReceipt
WHERE Stockroom1<>'99'
GROUP BY ItemNumber) B
ON A.ItemNumber=B.ItemNumber
ORDER BY A.ItemNumber,
A.ShipmentNumber,
A.ShipmentDate;

Thank you!

quote:
Originally posted by visakh16

Can you post query you used?i certainly think you're using something different.

i'm sure that you wont get posted error for this query

"No column was specified for column '2' of B"

as you've named second column as LastMorvDate

Davide
Starting Member

9 Posts

Posted - 2008-09-29 : 12:41:31

Sorry, I don't quite get it.. do you mean it should work fine ? or someway the name of the 2nd column creates problems? thank you,

quote:
Originally posted by visakh16

quote:
Originally posted by Davide

here it is:

SELECT A.ItemNumber,
A.ShipmentNumber,
A.ShipmentDate,
A.IssueType,
A.ReversedQuantity,
B.LastMorvDate
FROM FSDBIT.dbo.FS_HistoryShipment A
LEFT JOIN (SELECT
ItemNumber,
Max(TransactionDate) AS LastMorvDate
FROM FSDBIT.dbo.FS_HistoryMOReceipt
WHERE Stockroom1<>'99'
GROUP BY ItemNumber) B
ON A.ItemNumber=B.ItemNumber
ORDER BY A.ItemNumber,
A.ShipmentNumber,
A.ShipmentDate;

Thank you!

quote:
Originally posted by visakh16

Can you post query you used?i certainly think you're using something different.

i'm sure that you wont get posted error for this query

"No column was specified for column '2' of B"

as you've named second column as LastMorvDate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 12:44:50

quote:
Originally posted by Davide

Sorry, I don't quite get it.. do you mean it should work fine ? or someway the name of the 2nd column creates problems? thank you,

i said i'm sure that you wont get this error. either your error is something else or you are using this as a part of some other query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources