Optimize Query with SubQs in the Select

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

Transact-SQL (2000)

Optimize Query with SubQs in the Select

Author

Topic

baldeep
Starting Member

18 Posts

Posted - 2002-12-03 : 20:33:54

I need some help optimizing a query that "flattens" two tables into one view. Table A has a one-to-many relationship with Table B. My resultset needs to contain details from both, displaying every record from table A once and then displaying the details from Table B in a number of predetermined columns in the same set.

I've tried to implement this using sub-queries in the select statement, but this is painfully slow. Is there a better way I can accomplish this?

Thanks in advance,

--Baldeep


CREATE TABLE Item (
	ItemId INT IDENTITY(1, 1) PRIMARY KEY,
	Prop1 INT NULL, --These property values are useless here
	Prop2 INT NULL, --but I left them in for the feeling of
	Prop3 INT NULL, --realism ;-)
	Prop4 INT NULL)

CREATE TABLE Event (
	EventId INT IDENTITY(1, 1) PRIMARY KEY,
	EventTypeId INT NULL, --Normally this would be an FK.
	ItemId INT NULL REFERENCES Item(ItemId),
	DateOccurred DATETIME NULL)

INSERT INTO Item (Prop1, Prop2, Prop3, Prop4)
SELECT 
	CAST(CAST(NEWID() AS VARBINARY) AS INT),
	CAST(CAST(NEWID() AS VARBINARY) AS INT),
	CAST(CAST(NEWID() AS VARBINARY) AS INT),
	CAST(CAST(NEWID() AS VARBINARY) AS INT) FROM 
syscomments A CROSS JOIN syscomments B  --17161 records for me

INSERT INTO Event (ItemId, EventTypeId, DateOccurred)
SELECT
	I.ItemId, N.Num, RAND(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * 64000
FROM 
	Item I CROSS JOIN 
	(SELECT 1 Num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
	SELECT 4 UNION ALL SELECT 5) N

--This is the query of interest:
SELECT I.*,
	(SELECT TOP 1 DateOccurred FROM Event WHERE ItemId = I.ItemId AND EventTypeId = 1) Event1Date,
	(SELECT TOP 1 DateOccurred FROM Event WHERE ItemId = I.ItemId AND EventTypeId = 2) Event2Date,
	(SELECT TOP 1 DateOccurred FROM Event WHERE ItemId = I.ItemId AND EventTypeId = 3) Event3Date,
	(SELECT TOP 1 DateOccurred FROM Event WHERE ItemId = I.ItemId AND EventTypeId = 4) Event4Date,
	(SELECT TOP 1 DateOccurred FROM Event WHERE ItemId = I.ItemId AND EventTypeId = 5) Event5Date
FROM Item I

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-03 : 21:40:19

To "flatten" the Events table, use something like:

SELECT
Item,
MAX(CASE EventTypeID WHEN 1 THEN DateOccured ELSE Null END) as Event1Date,
MAX(CASE EventTypeID WHEN 2 THEN DateOccured ELSE Null END) as Event2Date,
MAX(CASE EventTypeID WHEN 3 THEN DateOccured ELSE Null END) as Event3Date,
MAX(CASE EventTypeID WHEN 4 THEN DateOccured ELSE Null END) as Event4Date,
MAX(CASE EventTypeID WHEN 5 THEN DateOccured ELSE Null END) as Event5Date
FROM
Event
GROUP BY
ItemID

Notice I did NOT include the Item table in the above; that is because you want all of the fields in that table. If I included it, we would have to GROUP BY all of the fields, which is unnecessary and slow. It would require a JOIN between all records in the Item table with the Event table as well. We are better off GROUPing the Events table by ItemID first, and THEN joining in the Items table. Much more efficient (many less joins!).

A good rule of thumb: If you are GROUPing a table by more than just its primary key, take it OUT of that query and join it in later as a subquery.

So, the final result would be:

SELECT Item.*, Event1Date, Event2Date, Event3Date, Event4Date, Event5Date
FROM
Item
INNER JOIN
(
SELECT
Item,
MAX(CASE EventTypeID WHEN 1 THEN DateOccured ELSE Null END) as Event1Date,
MAX(CASE EventTypeID WHEN 2 THEN DateOccured ELSE Null END) as Event2Date,
MAX(CASE EventTypeID WHEN 3 THEN DateOccured ELSE Null END) as Event3Date,
MAX(CASE EventTypeID WHEN 4 THEN DateOccured ELSE Null END) as Event4Date,
MAX(CASE EventTypeID WHEN 5 THEN DateOccured ELSE Null END) as Event5Date
FROM
Event
GROUP BY ItemID
) A
ON
Item.ItemID = A.ItemID

This should be much faster than what you've been trying to do.

Good luck!

- Jeff

Edited by - jsmith8858 on 12/03/2002 21:42:02

baldeep
Starting Member

18 Posts

Posted - 2002-12-03 : 23:07:07

This looks like what I'm looking for.

In discussing this with others I found another solution that works as long as each Item doesn't have more than one Event of the same type. Here it is:


SELECT 
	I.*, 
	E1.DateOccurred Event1Date, 
	E2.DateOccurred Event2Date,
	E3.DateOccurred Event3Date, 
	E4.DateOccurred Event4Date, 
	E5.DateOccurred Event5Date
FROM 
	Item I 
	LEFT JOIN Event E1 ON I.ItemId = E1.ItemId AND E1.EventTypeId = 1
	LEFT JOIN Event E2 ON I.ItemId = E2.ItemId AND E2.EventTypeId = 2
	LEFT JOIN Event E3 ON I.ItemId = E3.ItemId AND E3.EventTypeId = 3
	LEFT JOIN Event E4 ON I.ItemId = E4.ItemId AND E4.EventTypeId = 4
	LEFT JOIN Event E5 ON I.ItemId = E5.ItemId AND E5.EventTypeId = 5

It's slightly faster than your method, but yours will work when there are several events of a particular type associated with one item.

Thanks for the help!

--Baldeep

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources