On Nov 18, 2008, at 5:48 PM, Ryan Wells wrote:
While looking through our data layer code today, I ran across this
query:
SELECT
tasks.*,
clients.FirstName,
clients.LastName,
clients.MiddleInitial,
iteminfo.CreatedBy,
iteminfo.StationId,
iteminfo.CreatedDate,
changelog.LastModified,
changelog.LastModifiedBy,
changelog.LastModifiedAt,
ticklers.Due,
ticklers.Reminder
FROM tasks
LEFT JOIN clients ON tasks.ClientId = clients.ClientId
LEFT JOIN iteminfo ON tasks.Id = iteminfo.ItemId
LEFT JOIN changelog ON tasks.Id = changelog.ItemId
LEFT JOIN ticklers ON tasks.Id = ticklers.RelatedId
WHERE tasks.Id = '123456';
(I've cleaned it up so it's easier to read.)
The basic data structure is that we have a todo list that contains a
list of tasks which may or may not be associated with clients,
items, log entries, or ticklers (scheduled reminders).
The query works as intended: it returns a result-set with all the
necessary data to display in the todo list. The performance is not
a major concern, although it can be slow for large lists.
Since it works, my question is really more about principles: Given
that each of the tables in question will contain tens of thousands
of rows, is a nested join really the best way to approach this?
Thanks!
Ryan
To answer this better we need to know the exact relations between each
table.
For example, does each task have 0 or more clients, or 1 or more
clients?
If it's 1 or more, then you can properly use a JOIN instead of a LEFT
JOIN, this would make the operation faster I believe.
I hope I did explain myself correctly.
Ries
