Sproc Design Question

Question

Sproc Design Question

Luke L

SSC-Dedicated

Points: 35547
More actions
June 26, 2007 at 2:19 pm

#98474

I'm in the process of upgrading an application from vb6 to vb.net05. As I'm doing this I've been rewriting a lot of Sprocs to speed up performance and get them in line with best practices. As such I've run accross a delema. This is probably a rather academic exercise, but here goes...
Given the following data and DDL...
USE [tempdb]
CREATE TABLE CHARs(
    ID INT IDENTITY PRIMARY KEY,
    fname VARCHAR(50),
    lname VARCHAR(50))
CREATE TABLE CommTypes (
    ID INT IDENTITY PRIMARY KEY,
    Description VARCHAR(50))
CREATE TABLE MeansOfCommunications(
    ID INT IDENTITY PRIMARY KEY,
    CHARacter INT FOREIGN KEY REFERENCES CHARs(ID),     Type INT FOREIGN KEY REFERENCES CommTypes(ID),
    Value VARCHAR(50))
INSERT INTO CHARs VALUES ('test', 'dude')
INSERT INTO CHARs VALUES ('CrashTest', 'Dummy')
INSERT INTO CommTypes  VALUES ('Phone')
INSERT INTO CommTypes  VALUES ('Fax')
INSERT INTO CommTypes  VALUES ('Email')
INSERT INTO MeansOfCommunications VALUES (1, 1, <script></script> '(123)456-7890')
INSERT INTO MeansOfCommunications VALUES (1, 2, '(123)456-0987')
INSERT INTO MeansOfCommunications VALUES (2, 3, ' me@you.com')
Which Would be a better solution to getting the name of the characer and thier phone number?
SELECT c.fname, c.lname, moc.value AS PHONE
FROM CHARs C
    INNER JOIN MeansOfCommunications MOC
      ON c.id = moc.CHARacter
    INNER JOIN commtypes Ct
      ON ct.id = moc.type
      AND ct.description = 'Phone'
OR
SELECT c.fname, c.lname, moc.value AS PHONE
FROM CHARs C
    INNER JOIN MeansOfCommunications MOC
      ON c.id = moc.CHARacter
      AND [MOC].type = 1
I propose the first because it allows for future expandability if commtype 1 ever becomes something different and phone becomes 2 or something, but someone else is arguing for the other because it's one less table you need to join to. I was just looking for a second or third opinion.
Thanks in advance.
-Luke.
To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Michael Lysons SSCertifiable Points: 6490 More actions · Answer 1

You say '...if 1 becomes something different...', but what if the description for 'Phone' changes - then your first query won't be correct.

Consider adding a unique code column to the MeansOfCommunication table, so your code can reference that rather than the identity column. That way, if the table's data is ever reloaded such that the row previously referenced by 1 is now referenced by the value 2, you can still reference the never changing (!) code value of 'PHONE', and the query will work.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

but I already have a unique column, ID as the primary Key as there was really no good way to come up with a natural PK in this instance. How would I auto Generate this additional unique code column such that if the data was reloaded it would be the same without having some sort of table holding the next value and such...

To help us help you read this[/url]For better help with performance problems please read this[/url]

Andy Warren SSC Guru Points: 119902 More actions · Answer 3

I'd go with the second solution, with one change, adding a comment to indicate what the value of 1 means:

SELECT c.fname, c.lname, moc.value AS PHONE

FROM CHARs C

INNER JOIN MeansOfCommunications MOC

ON c.id = moc.CHARacter

AND [MOC].type = 1 --phone

You can only plan for so much change. I doubt their is much difference in performance, though its worth checking always. My rule is to only join to the table if I need a value from it.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

Thanks for the insights guys.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Michael Lysons SSCertifiable Points: 6490 More actions · Answer 5

Yes, I know you already have a unique ID column; you seem more concerned about PKs that can change their value. Hence adding a code column whereby you specify the value. However, if that's no good - as you basically say when mentioning it would have to be auto-generated - then if you went down this route you'd need a mechanism for ensuring you could always reproduce the same unique value for the same row. There's ways of doing that, but without knowing more about your requirements I wouldn't want to advise on a method.

And, what Andy said

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Eliminating a join will certainly be more efficient. As to what is better, I would say that if you control the dictionary table, use the known key values as you write the code if you can. Even if you don't - as someone else mentioned, if the label changes you are hosed too. May as well write efficient code from the get go.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Kory Becker Hall of Fame Points: 3585 More actions · Answer 7

To address this issue for developers I have created a unique column in the table that fall in this criteria that are application freindly unlike a numeric id. So oncle the data is created with the application specific value it can never be changed, which can be enforced via triggers and access from procs.

table: attribute

atributeId int identity --> Numeric id with pkey

attributeName varchar(36) --> User modifiable

atributeDescription varchar(100)

attributeAppId vatchar(36) -->Friendly system app data

cdate datetime --> Creation date

mtime datetime --> Modification date

So we can hard code a string in the application that is identifiable and not an id of numeric type that means nothing to humans. So if a user changed the attributeName of "type" to "not type" the attributeAppId is still "type" and the app will still function. On small lookup tables using a string for the actual id to search for will not introduce a performance issue.

Sergiy SSC Guru Points: 110206 More actions · Answer 8

How about this:

SELECT c.fname, c.lname, moc.value AS PHONE

FROM CHARs C

INNER JOIN MeansOfCommunications MOC

ON c.id = moc.CHARacter

INNER JOIN dbo.ProcUsesTypes PT ON [MOC].type = PT.TypeID

WHERE PT.ProcName = Object_Name(@@PROCID)

You may reference more than one type here, process name is not hardcoded, if you change procedure you need change/add corresponding records, and (main point!) you may let users change list of types from application, without bothering you!

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

OMG!!! Table driven code! What a concept 😉

Nice job, Serqiy...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)