Advanced T-SQL JOIN statment / stored procedure advice
Posted on 2006-11-13
I'm trying to create a stored procedure for use on our website. It will be used a lot. I can write the stored procedure, but it's a non-trivial JOIN, and I'm worried about the performance. I'd like for someone who (hopefully) knows a lot more about SQL than I do to provide a very quick, lightweight solution for SQL Server 2005.
Here's what I'm dealing with:
I've got a people table that contains, among other things, a PeopleID, FirstName, and LastName field.
I've got a 'widget' table that contains, among other things, a WidgetID and WidgetName. A Widget can also have up to five owners. So, right now, the widget table contains five fields, WidgetOwner1, WidgetOwner2, etc. that are related to the PeopleID field in the People table.
The application that is to sit on the website will be a Widget Search. I'd like to encapsulate the Widget Search into a single stored procedure. Users will be able to search by WidgetID, WidgetName, and WidgetOwner. Let's say someone searches for all widgets where John Doe is the owner. The results will be (roughly):
ID: Name: Owners:
001 | BlueWidget | John Doe, Molly Johnson
004 | RedWidget | Jane Doe, John Doe, George Williams
009 | GreenWidget | Jack Doe, Molly Johnson, John Doe
I'm concerned because John Doe can be WidgetOwner1, WidgetOwner2, etc. but once he's found, this will have to join back to the People table to get his name. Every owner will go through this. Also, the list could be quite long, and run often.
So... I know how to do this, but probably not the "best way" or "fastest way" to do it. So, I'm looking for some advice.
How would you do it? Why is your method fast? Can it be encapsulated in a single Stored Procedure?
If necessary I can change the table structure so that the WidgetOwners are not in the same table as the WidgetID, WidgetName, etc. The solution can have as many tables as you like.
Here is the current table structure:
[PersonID] [smallint] IDENTITY(1,1) NOT NULL
[FirstName] [varchar](24) NOT NULL
[LastName] [varchar](24) NOT NULL
[WidgetID] [varchar](14) NOT NULL
[WidgetName] [varchar](64) NOT NULL
[WidgetOwner1] [smallint] NOT NULL
[WidgetOwner2] [smallint] NULL
[WidgetOwner3] [smallint] NULL
[WidgetOwner4] [smallint] NULL
[WidgetOwner5] [smallint] NULL