Solved

Advanced T-SQL JOIN statment / stored procedure advice

Posted on 2006-11-13
6
262 Views
Last Modified: 2008-01-09
Hi,

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:

TABLE People:
      [PersonID] [smallint] IDENTITY(1,1) NOT NULL
      [FirstName] [varchar](24) NOT NULL
      [LastName] [varchar](24) NOT NULL

TABLE Widget:
      [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

Thanks!
0
Comment
Question by:CMES-IT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 11

Expert Comment

by:regbes
ID: 17932875
Hi CMES-IT,

how far are you in the devlopment process? i ask becuase your tables are not in 3rd normal form.

if it is still possible suggest you change your table design to the following

TABLE Widget:
     [WidgetID] [varchar](14) NOT NULL
     [WidgetName] [varchar](64) NOT NULL

TABLE WidgetOwners:
     [WidgetID] [varchar](14) NOT NULL
     [PersonID] [smallint] NOT NULL

im sure you can see how this will simplify the task you are describing
0
 

Author Comment

by:CMES-IT
ID: 17932906
Hi regbes,

Yep, I know. I inherited this mess from someone else (as usual, right?!) But that's why I said: "If necessary I can change the table structure so that the WidgetOwners are not in the same table as the WidgetID, WidgetName, etc."

For the purposes of my application, the WidgetOwners table would also have to have an OwnerNumber, because it's important for me to know if John Doe is Owner1, or Owner2. But anyhow, yes, the tables can be in 3NF if we need them to be.

Still, though, that would result in a join of three tables. And I'd still like some advice on how to make that join as fast and lightweight as possible.

Thanks!
0
 
LVL 11

Expert Comment

by:regbes
ID: 17933088
the issue with your current design is that your query's where caluse will have to look something like

where owner1 = 123
or  owner2 = 123
or  owner3 = 123
or  owner4 = 123
or  owner5 = 123

this is realy 5 seperate queries in one

assuming you already did some sort of lookup on person to get the ID if not the joins you would have to have are just plain ugly

you would also have to index all 5 cols for speed

on the other hand one or two well place indexes on each of your tables (clusterd on the id's non clusterd on people and widget names) will keep evrything simple and quick
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:CMES-IT
ID: 17933267
Hi regbes,

I agree. Assume the tables are in 3NF as you proposed above. How would you write the SQL for your solution?

Thanks.
0
 
LVL 11

Accepted Solution

by:
regbes earned 500 total points
ID: 17936270
CMES-IT,

as per your requirements we will alter the WidgetOwners table to have the owner number

TABLE WidgetOwners:
     [WidgetID] [varchar](14) NOT NULL
     [PersonID] [smallint] NOT NULL
     [OwnerNum] [tinyint] NOT NULL

the others should remain the same

this is a basic many to many join so your select and from clause will something like look like



select P.FirstName,P.LastName,WO.OwnerNum,W.WidgetName
FRom people P
    inner join WidgetOwners WO
        on P.PersonID = WO.PersonID
    inner join Widget W
        on WO.WidgetID = W.WidgetID

your where clause will change depending on what your are searching on


Search on Person


select P.FirstName,P.LastName,WO.OwnerNum,W.WidgetName
FRom people P
    inner join WidgetOwners WO
        on P.PersonID = WO.PersonID
    inner join Widget W
        on WO.WidgetID = W.WidgetID
where P.LastName = 'Saunders'


search on widget

select P.FirstName,P.LastName,WO.OwnerNum,W.WidgetName
FRom people P
    inner join WidgetOwners WO
        on P.PersonID = WO.PersonID
    inner join Widget W
        on WO.WidgetID = W.WidgetID
where W.WidgetName = 'Super Widget'

Finally in addition to having indexes on all the fields that hold an ID add indexes to

Widget - Widgetname
Person - Lastname,firstname
0
 

Author Comment

by:CMES-IT
ID: 17938573
Thanks regbes,

My joins looked like yours, so we're on the same wavelength. That's what I was looking for - another opinion, or sanity check ;) I appreciate your thoughts.

Thanks again.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
GeoClustering  and AOG 25 43
Rebooting Witness SQL Server 2 25
Copy data to New Year 9 33
Need quicker response from an Execption table 11 23
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question